From: Gareth Vaughan <gareth@specialisedstructures.co.nz>
To: pgsql-bugs@lists.postgresql.org
Subject: PITR restores incorrect state
Date: Wed, 17 Feb 2021 14:53:41 +1300
Message-ID: <001501d704cf$b9979760$2cc6c620$@specialisedstructures.co.nz> (raw)
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
Attachments:image001.jpg (image/jpeg, 28.9K) downloadview image
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: gareth@specialisedstructures.co.nz, pgsql-bugs@lists.postgresql.org
Subject: Re: PITR restores incorrect state
In-Reply-To: <001501d704cf$b9979760$2cc6c620$@specialisedstructures.co.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox