PITR restores incorrect state

Hi Postgres

I'm trying to update to postgres 13 (from 10) and part of that process is
testing WAL archiving and restoration. I have been able to perform PITR in
version 10 but I've not managed to achieve this in version 13.

Specifically, the restore process always restores the state of the base
backup, it won't restore to a later time.

Here are the steps that I have used

1) install postgresql-13.2-1-windows-x64.exe and install this from an
elevated cmd :

postgresql-13.2-1-windows-x64.exe --superaccount postgres --superpassword
"password123" --serviceaccount postgres --servicepassword "password123"
--disable-components "stackbuilder, pgAdmin" --serverport 5432 --mode
unattended

postgresql-13.2-1-windows-x64.exe --superpassword "password123"
--servicepassword "password123" --disable-components "stackbuilder, pgAdmin"
--serverport 5432 --mode unattended

2) create folders and grant full control to the postgres user name

c:\temp\pitr_test\base_backup

c:\temp\pitr_test\wal_archive

3) run these queries

create table somedata (numbers int);

insert into somedata (numbers) values (1),(2),(3);

4) edit postgres.conf

wal_level = replica

archive_mode = on

archive_command = 'copy "%p" "C:\\temp\\pitr_test\\wal_archive\\%f"'

5) restart postgresql-x64-13

6) generate a base backup in an elevated cmd

"C:\Program Files\PostgreSQL\13\bin\pg_basebackup.exe" -p 5432 -D
c:\temp\pitr_test\base_backup -U postgres

and note the time

'2021-02-17 14:39:00' for me

C:\Program Files\PostgreSQL\13\data\pg_wal contains files

000000010000000000000002

000000010000000000000002.00000060.backup

000000010000000000000003

C:\temp\pitr_test\wal_archive contains

000000010000000000000001

000000010000000000000002

000000010000000000000002.00000060.backup

7) wait a minute and then run these queries and note the time

insert into somedata (numbers) values (4),(5),(6);

select pg_switch_wal();

'2021-02-17 14:42:00' for me

C:\Program Files\PostgreSQL\13\data\pg_wal also contains

000000010000000000000004

C:\temp\pitr_test\wal_archive also contains

000000010000000000000003

8) wait a minute, run these queries and note the time

insert into somedata (numbers) values (7),(8),(9);

select pg_switch_wal();

'2021-02-17 14:43:00' for me

C:\Program Files\PostgreSQL\13\data\pg_wal also contains

000000010000000000000005

C:\temp\pitr_test\wal_archive also contains

000000010000000000000004

9) wait a minute. edit c:\temp\pitr_test\base_backup\postgres.conf

restore_command = 'copy "C:\\temp\\pitr_test\\wal_archive\\%f" "%p"'

10) in an elevated cmd run these:

net stop postgresql-x64-13

DEL /F/Q/S "c:\Program Files\PostgreSQL\13\data\*" > NUL

RMDIR /Q/S "c:\Program Files\PostgreSQL\13\data\"

mkdir "c:\Program Files\PostgreSQL\13\data\"

xcopy C:\temp\pitr_test\base_backup\* "c:\Program Files\PostgreSQL\13\data\"
/e /q

icacls "c:\Program Files\PostgreSQL\13\data" /grant postgres:(OI)(CI)F

net start postgresql-x64-13

11) run this query: select * from somedata

returns 1,2, 3

I am expecting that the database will be recovered to its state at the
latest time possible so this query should have returned 1,...9

12) edit c:\temp\pitr_test\data_base_backup\postgres.conf

recovery_target_time = '2021-02-17 14:42:30'

and re-run the commands from 9)

13) run this query: select * from somedata

returns 1,2,3

I am expecting that the database will be recovered to its state at 14:42 so
this query should have returned 1,...6

Regards,

Gareth Vaughan

Senior Software Developer

Specialised Structures NZ Ltd

T: 0508 STRUCTURE M: 027 7666 070

362 Bay View Road I Dunedin I 9012

<http://www.specialisedstructures.co.nz/> www.specialisedstructures.co.nz