Restoring database from false update

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

> On Fri, Nov 13, 2020 at 1:56 PM Maksim Fomin <maxim(at)fomin(dot)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.