pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / 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:

image001.jpg (image/jpeg, 28.9K) download view image
^ permalink raw reply [nested|flat] 2+ messages in thread

* 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