Restoring database from false update
От | Maksim Fomin |
---|---|
Тема | Restoring database from false update |
Дата | |
Msg-id | EkXln-Gp_kFQQDyBJhJ3NY3ziTiaYflsf0amwXiK_Q1kuVxiHJtRJ_UgAdRYRDWo50E7X_hqFeUz7zj73D2xRfSgR3B3k60POQFBgpLSDOs=@fomin.one обсуждение исходный текст |
Ответ на | Re: Restoring database from false update ("David G. Johnston" <david.g.johnston@gmail.com>) |
Список | pgsql-general |
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Sunday, November 15, 2020 8:23 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Sunday, November 15, 2020, Maksim Fomin <maxim@fomin.one> wrote:‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐On Sunday, November 15, 2020 4:32 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:On Sunday, November 15, 2020, Maksim Fomin <maxim@fomin.one> wrote:‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐On Sunday, November 15, 2020 7:27 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:On Sunday, November 15, 2020, Maksim Fomin <maxim@fomin.one> wrote:> plsql -d tsvtpsql (12.5)Type "help" for help.tsvt=# \dt+List of relationsSchema | 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 connection databasetemplate0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7809 kB | pg_default | unmodifiable empty database| | | | | 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 |(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\dnDavid 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)
В списке pgsql-general по дате отправления:
Следующее
От: Morris de OryxДата:
Сообщение: Re: ERROR: could not find tuple for statistics object - is there a way to clean this up?