Thread: Restoring database from false update
Hi! Yesterday I updated the version of posgtresql from 12.0 to 12.5-1. Today I found that for some reason (*) there was bug inpackage update script which caused the postgresql to think that it should be updated from 12 to 13. I followed instructionsto update postgresql (**). What I did: 1) copied postgresql folder to backup destination (mv) 2) created new directories (mkdir) and changed permissions (chown) 3) executed 'initdb' inside new folder Then I tried to upgrade the database via 'pg_upgrade' but realised that I cannot do that because in my distro binaries ofprevious package version are gone, so I cannot supply arguement to the '-b' parameter. At this point I realized that theupgrade step should not be necessary for transition from 12 to 12.5. I found the issue in bugzilla (*) and updated postgresqlfrom 12.5-1 12.5-3. Later, I stopped the service and moved backup folder to the usual place. For some reason psqlshows that there are no relations found in the database, although the database is listed. My next step was to copy datafrom file-system level backup (about 4-5 days ago) but the result was the same. How I can restore the database? * https://bugs.archlinux.org/task/68601 ** https://wiki.archlinux.org/index.php/PostgreSQL#Upgrading_PostgreSQL
On Fri, Nov 13, 2020 at 1:56 PM Maksim Fomin <maxim@fomin.one> wrote:
Later, I stopped the service and moved backup folder to the usual place. For some reason psql shows that there are no relations found in the database, although the database is listed. My next step was to copy data from file-system level backup (about 4-5 days ago) but the result was the same.
How I can restore the database?
Assuming you have a complete and valid v12 data directory backup created from a shutdown server, and containing good WAL files...and that the server is presently running a v12 instance of PostgreSQL you are able to connect to using psql.
What do the following show?
select version();
show data_directory;
Assuming that the version is 12.x you want to ensure that your data directory backup replaces the entire contents of wherever data_directory is pointing (while the PostgreSQL process is stopped).
Having done that, and starting the server back up, you should find the old cluster to have been restored.
If that doesn't work:
Reviewing log files can help.
If you can report the startup command that is run to launch the postgres process that would help too.
Showing before and after directory structures can help.
David J.
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Sunday, November 15, 2020 4:47 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Fri, Nov 13, 2020 at 1:56 PM Maksim Fomin <maxim@fomin.one> wrote:Later, I stopped the service and moved backup folder to the usual place. For some reason psql shows that there are no relations found in the database, although the database is listed. My next step was to copy data from file-system level backup (about 4-5 days ago) but the result was the same.How I can restore the database?Assuming you have a complete and valid v12 data directory backup created from a shutdown server, and containing good WAL files...and that the server is presently running a v12 instance of PostgreSQL you are able to connect to using psql.What do the following show?select version();
version
------------------------------------------------------------------------------
PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 10.2.0, 64-bit
(1 row)
show data_directory;
data_directory
-----------------------
/home/postgresql/data
(1 row)
ls -alh /home
drwxr-xr-x 1 postgres postgres 8 Nov 13 19:59 postgresql
It points to the data directory I have backed up and restored. According to ncdu utility, the data folder has approx. 10.5GiB
Assuming that the version is 12.x you want to ensure that your data directory backup replaces the entire contents of wherever data_directory is pointing (while the PostgreSQL process is stopped).Having done that, and starting the server back up, you should find the old cluster to have been restored.If that doesn't work:
The server starts and I can connect to my database, but there are no tables. I start the server with systemctl start postgresql.service:
Nov 15 07:03:41 localhost systemd[1]: Starting PostgreSQL database server...
Nov 15 07:03:41 localhost postgres[59696]: 2020-11-15 07:03:41.819 GMT [59696] LOG: starting PostgreSQL 12.5 on x86_64-pc-linux-gnu, c>
Nov 15 07:03:41 localhost postgres[59696]: 2020-11-15 07:03:41.821 GMT [59696] LOG: listening on IPv6 address "::1", port 5432
Nov 15 07:03:41 localhost postgres[59696]: 2020-11-15 07:03:41.821 GMT [59696] LOG: listening on IPv4 address "127.0.0.1", port 5432
Nov 15 07:03:41 localhost postgres[59696]: 2020-11-15 07:03:41.885 GMT [59696] LOG: listening on Unix socket "/run/postgresql/.s.PGSQL>
Nov 15 07:03:42 localhost postgres[59697]: 2020-11-15 07:03:42.139 GMT [59697] LOG: database system was shut down at 2020-11-15 07:03:>
Nov 15 07:03:42 localhost postgres[59696]: 2020-11-15 07:03:42.286 GMT [59696] LOG: database system is ready to accept connections
Nov 15 07:03:42 localhost systemd[1]: Started PostgreSQL database server.
> 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.
Reviewing log files can help.If you can report the startup command that is run to launch the postgres process that would help too.Showing before and after directory structures can help.
I have replaced 'new' data structure from backup, this is the structure of my backup and current data:
sudo ls -alh /home/postgresql/data/
total 56K
drwx------ 1 postgres postgres 512 Nov 15 07:03 .
drwxr-xr-x 1 postgres postgres 8 Nov 13 19:59 ..
drwx------ 1 postgres postgres 90 May 24 09:13 base
drwx------ 1 postgres postgres 668 Nov 15 07:04 global
drwx------ 1 postgres postgres 0 Apr 4 2020 pg_commit_ts
drwx------ 1 postgres postgres 0 Apr 4 2020 pg_dynshmem
-rw------- 1 postgres postgres 4.5K Apr 4 2020 pg_hba.conf
-rw------- 1 postgres postgres 1.6K Apr 4 2020 pg_ident.conf
drwx------ 1 postgres postgres 76 Nov 15 07:03 pg_logical
drwx------ 1 postgres postgres 28 Apr 4 2020 pg_multixact
drwx------ 1 postgres postgres 8 Nov 15 07:03 pg_notify
drwx------ 1 postgres postgres 0 Apr 4 2020 pg_replslot
drwx------ 1 postgres postgres 0 Apr 4 2020 pg_serial
drwx------ 1 postgres postgres 0 Apr 4 2020 pg_snapshots
drwx------ 1 postgres postgres 0 Nov 15 07:03 pg_stat
drwx------ 1 postgres postgres 92 Nov 15 07:07 pg_stat_tmp
drwx------ 1 postgres postgres 8 Apr 4 2020 pg_subtrans
drwx------ 1 postgres postgres 0 Apr 4 2020 pg_tblspc
drwx------ 1 postgres postgres 0 Apr 4 2020 pg_twophase
-rw------- 1 postgres postgres 3 Apr 4 2020 PG_VERSION
drwx------ 1 postgres postgres 2.6K Nov 5 06:46 pg_wal
drwx------ 1 postgres postgres 8 Apr 4 2020 pg_xact
-rw------- 1 postgres postgres 88 Apr 4 2020 postgresql.auto.conf
-rw------- 1 postgres postgres 27K Apr 4 2020 postgresql.conf
-rw------- 1 postgres postgres 47 Nov 15 07:03 postmaster.opts
-rw------- 1 postgres postgres 99 Nov 15 07:03 postmaster.pid
Anyway, thanks for reply.
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.
‐‐‐‐‐‐‐ 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 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.
Best regards,
Maxim Fomin
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.
‐‐‐‐‐‐‐ 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.
Best regards,
Maxim Fomin
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
\dn
David J.
‐‐‐‐‐‐‐ 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)