Restoring database from false update

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Sunday, November 15, 2020 8:23 PM, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Sunday, November 15, 2020, Maksim Fomin <maxim(at)fomin(dot)one> wrote:
>
>> ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
>> On Sunday, November 15, 2020 4:32 PM, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>
>>> On Sunday, November 15, 2020, Maksim Fomin <maxim(at)fomin(dot)one> wrote:
>>>
>>>> ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
>>>> On Sunday, November 15, 2020 7:27 AM, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>>>
>>>>> On Sunday, November 15, 2020, Maksim Fomin <maxim(at)fomin(dot)one> wrote:
>>>>>
>>>>>>> plsql -d tsvt
>>>>>> psql (12.5)
>>>>>> Type "help" for help.
>>>>>>
>>>>>> tsvt=# \dt+
>>>>>> List of relations
>>>>>> Schema | Name | Type | Owner | Size | Description
>>>>>> --------+------+-------+----------+---------+-------------
>>>>>> public | test | table | postgres | 0 bytes |
>>>>>> (1 row)
>>>>>>
>>>>>> It should have tables 'trade', 'trade4' and some others.
>>>>>
>>>>> What about?
>>>>>
>>>>> \l+ (Letter “el”)
>>>>>
>>>>> David J.
>>>>
>>>> Well, it gives something:
>>>>
>>>> tsvt=# \l+
>>>> List of databases
>>>> Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
>>>>
>>>> -----------+----------+----------+-------------+-------------+-----------------------+---------+------------+---------------------------
>>>> -----------------
>>>> postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7809 kB | pg_default | default administrative con
>>>> nection database
>>>> template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7809 kB | pg_default | unmodifiable empty databas
>>>> e
>>>> | | | | | postgres=CTc/postgres | | |
>>>> template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8161 kB | pg_default | default template for new d
>>>> atabases
>>>> | | | | | postgres=CTc/postgres | | |
>>>> tsvt | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8089 MB | pg_default |
>>>> (4 rows)
>>>>
>>>> tsvt is needed database and it has 8089 MB which sounds promising.
>>>
>>> Show search_path;
>>>
>>> ?
>>>
>>> David J.
>>
>> It has:
>> tsvt=# Show search_path;
>> search_path
>> -----------------
>> "$user", public
>> (1 row)
>>
>> 'user' is literally the name of the current linux user.
>
> How about
>
> \dn
>
> David J.

OK, the issue is fixed. I just tried to fix myself (by looking at security and user access right doc pages) and screwed the database even more. Then I restored the db from backup and it worked. I have no idea why. In previous cases I was shutting down postgresql normally (via systemctl stop) as just now, why restoring didn't work in previous cases remains a mystery for me. Just for the record, this is output from working db:

tsvt=# Show search_path;
search_path
-----------------
"$user", public
(1 row)

tsvt=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Descripti
on
-------------+----------+----------+-------------+-------------+-----------------------+---------+------------+-------------------------
-------------------
gmc_finance | user | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 1729 MB | pg_default |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7809 kB | pg_default | default administrative c
onnection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7809 kB | pg_default | unmodifiable empty datab
ase
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8161 kB | pg_default | default template for new
databases
| | | | | postgres=CTc/postgres | | |
tsvt | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8089 MB | pg_default |
user | user | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7809 kB | pg_default |
(6 rows)

tsvt=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)

tsvt=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+--------+-------+-------+---------+-------------
public | strana | table | user | 40 kB |
public | tnved | table | user | 6408 kB |
public | trade | table | user | 6011 MB |
public | trade4 | table | user | 2064 MB |
(4 rows)