pg.ddx.io pgsql-bugs@postgresql.org mailing list archivehelp / color / mirror / Atom feed
PITR restores incorrect state 2+ messages / 2 participants [nested] [flat]
* PITR restores incorrect state @ 2021-02-17 01:53 Gareth Vaughan <gareth@specialisedstructures.co.nz> 2021-02-17 17:06 ` Re: PITR restores incorrect state Euler Taveira <euler@eulerto.com> 0 siblings, 1 reply; 2+ messages in thread From: Gareth Vaughan @ 2021-02-17 01:53 UTC (permalink / raw) To: pgsql-bugs@lists.postgresql.org 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:^ permalink raw reply [nested|flat] 2+ messages in threadimage001.jpg (image/jpeg, 28.9K) download
view image
* Re: PITR restores incorrect state 2021-02-17 01:53 PITR restores incorrect state Gareth Vaughan <gareth@specialisedstructures.co.nz> @ 2021-02-17 17:06 ` Euler Taveira <euler@eulerto.com> 0 siblings, 0 replies; 2+ messages in thread From: Euler Taveira @ 2021-02-17 17:06 UTC (permalink / raw) To: Gareth Vaughan <gareth@specialisedstructures.co.nz>; pgsql-bugs@lists.postgresql.org On Tue, Feb 16, 2021, at 10:53 PM, Gareth Vaughan wrote: > 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 > This is not a bug. Your forgot step 7: create a $PGDATA/recovery.signal [1]. It was changed in version 12 when the recovery settings was moved to postgresql.conf. [1] https://www.postgresql.org/docs/13/continuous-archiving.html#BACKUP-PITR-RECOVERY -- Euler Taveira EDB https://www.enterprisedb.com/ ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-02-17 17:06 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-02-17 01:53 PITR restores incorrect state Gareth Vaughan <gareth@specialisedstructures.co.nz> 2021-02-17 17:06 ` Euler Taveira <euler@eulerto.com>
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