‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Sunday, November 15, 2020 4:47 AM, David G. Johnston <david.g.johns...@gmail.com> wrote:
> On Fri, Nov 13, 2020 at 1:56 PM Maksim Fomin <ma...@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.
