backup from standby is unstable

[mischaqu]

hi, I ran a backup test from standby. I found restore from fullback the data was complete, but when add delta backup, after restore, some data between full and delta backup is lost. I did the test several times,whether archive or stream ,stream mode is more obvious,almost 4 of 5 some data was lost.

pg_probackup version 2.5.2 (install from rpm) pg version 11.14 steps to reproduce

1. add instance pg_probackup-11 add-instance -B /var/lib/pgsql/backdir/ -D /var/lib/pgsql/test/ --instance standbytest --remote-proto=ssh --remote-host=192.168.56.15 --remote-port=22 --remote-user=postgres --remote-path=/usr/bin
2. add master info for switch wal pg_probackup-11 set-config --instance standbytest -U backup -h 192.168.56.20 -p 5432
3. take a full backup pg_probackup-11 backup --instance standbytest --remote-user=postgres --remote-host=192.168.56.15 --remote-port=22 -b full
4. insert some rows and create a new table 5.take a delta backup pg_probackup-11 backup --instance standbytest --remote-user=postgres --remote-host=192.168.56.15 --remote-port=22 -b delta 6 restore pg_probackup-11 restore --instance standbytest --recovery-target-time='2022-02-18 16:04:16' -D /var/lib/pgsql/test

[mischaqu]

this problem occured again the restore was successful，but I can't find a table. I can found the table file.

on backupdb test=# select relname,relfilenode from pg_class where relfilenode='24644'; relname | relfilenode ---------+------------- quch | 24644 [postgres@pgdbtest2 ~]$ ll repmgr/base/24639/24644 -rw------- 1 postgres postgres 8192 Apr 1 20:34 repmgr/base/24639/24644

the backup file and retored dbfile I still found this file. [postgres@pgdbtest2 ~]$ ll backdir/backups/repmgr/R9NT7S/database/base/24639/24644 -rw------- 1 postgres postgres 67 Apr 1 19:57 backdir/backups/repmgr/R9NT7S/database/base/24639/24644

restoredb [postgres@pgdbtest1 ~]$ ll repmgr1/base/24639/24644 -rw------- 1 postgres postgres 8192 Apr 1 20:51 repmgr1/base/24639/24644 but I can't find it in db test=# select relname,relfilenode from pg_class where relfilenode='24644'; relname | relfilenode ---------+------------- (0 rows)

So does that mean I can't archive wals and do backup from standbydb?

[mischaqu]

I test again， find the only new table has the problem ，why？

[deric]

@mischaqu You need to make sure that all the changes on primary are synced to WAL. There are multiple configuration flags, that migh influence this, e.g.:

• wal_level
• synchronous_commit
• wal_writer_flush_after
• wal_writer_delay

Make sure that the changes you've made, are large enough to be written to a WAL and synced to standby.

[gsmolk]

Hello! Cannot reproduce unfortunately. Can you provide a shell script, reproducing the described behavior?