pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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) download view image

view thread (2+ messages)  latest in thread

Message-ID: <001501d704cf$b9979760$2cc6c620$@specialisedstructures.co.nz>
Permalink:  ../001501d704cf$b9979760$2cc6c620$@specialisedstructures.co.nz/
Also on:    postgresql.org/message-id/001501d704cf$b9979760$2cc6c620$@specialisedstructures.co.nz

 · 

reply

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