Incorrect behavior during partial recovery

[bomuva]

I want to use a partial recovery to save disk space and recovery time when I need to recover just single db.
But when I specify --db-include=db_1 in some situations, several tables from other databases (db_2, db_3 etc.) eventually have non-zero sizes, defeating the purpose of partial restore.

[fukanchik]

Hi, can you provide specific example? Are these system tables? Are they located in a tablespace?

[bomuva]

Additional tablespaces are not used. We are talking about user (non-system) tables.
Unfortunately, I couldn't reproduce this behavior on test databases yet. This situation is observed in a high load prod database, but the pattern of its occurrence is not yet clear.

[fukanchik]

@bomuva we would be grateful for any additional information. If this is indeed a bug in probackup we should fix it. On our side we would take another look at the code for excluded table nullification.

[bomuva]

@fukanchik I was able to reproduce similar behavior, here are the steps

-- create database d1
create database d1;
-- create table t1 in d1
create table t1 as select q from (select generate_series(1,100000) q) t;
-- get command for file list
select 'ls -l ~/15/data/base/'||d.oid||'/'||t.oid||'*' from pg_database d, pg_class t where datname='d1' and t.relname='t1';
ls -l ~/15/data/base/16998/17000*

-- create database d2
create database d2; 
-- create table t2 in d2
create table t2 as select q from (select generate_series(1,100000) q) t;
-- get command for file list
select 'ls -l ~/15/data/base/'||d.oid||'/'||t.oid||'*' from pg_database d, pg_class t where datname='d2' and t.relname='t2'; --16951
ls -l ~/15/data/base/16999/17004*

-- switch wal
select pg_switch_wal();

--create full backup using pg_probackup
......

-- switch wal
select pg_switch_wal();

-- add data to table t2
insert into t2 select q from (select generate_series(1,100000) q) t;

-- switch wal
select pg_switch_wal();

--recover database on new empty server with options:
--recovery-target=latest --db-include=postgres --db-include=d1

--check files on new server after restoring
[postgres@CentosR ~]$ ls -l ~/15/data/base/16998/17000*
-rw-------. 1 postgres postgres 3629056 Mar 11 19:56 /var/lib/pgsql/15/data/base/16998/17000
-rw-------. 1 postgres postgres   24576 Mar 11 19:56 /var/lib/pgsql/15/data/base/16998/17000_fsm
[postgres@CentosR ~]$ ls -l ~/15/data/base/16999/17004*
-rw-------. 1 postgres postgres 7249920 Mar 11 19:56 /var/lib/pgsql/15/data/base/16999/17004
-rw-------. 1 postgres postgres   24576 Mar 11 19:56 /var/lib/pgsql/15/data/base/16999/17004_fsm
-rw-------. 1 postgres postgres    8192 Mar 11 19:56 /var/lib/pgsql/15/data/base/16999/17004_vm

--we see the both databases are resored with table data 

The pg_switch_wal() command is important because without it this bug does not appear.

[fukanchik]

@bomuva thanks alot! We would take a few days to investigate on our side.

[fukanchik]

I confirm the problem exists. Will investigate further.

[fukanchik]

@bomuva I was wrong, I can't reproduce this on 2.5 and 2.6.

What versions of PG/probackup/OS do you have?

[bomuva]

PostgreSQL 15.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit
pg_probackup-15 2.5.12 (PostgreSQL 15.0)
CentOS Stream 8