Thread: Simulate a PITR in postgresql 16

I'm trying to simulate a PITR in postgresql 16 with the following steps:

directorys:
/data/primary
/data/base
/data/wals
/data/csv

1- Create cluster to primary postgresql:
/usr/pgsql-16/bin/initd -D /data/primary

2- Start Cluster (1111 port)
/usr/pgsql-16/bin/pg_ctl -D /data/primary start

3- Create Database, table, user and slot:
create database "Foo";
\c Foo;
create table public.acme(hash text);
create role "UserReplication" with login password '123' replication;
select pg_create_physical_replication_slot('slot_wals');

4- Start pg_receivewal:
/usr/pgsql-16/bin/pg_receivewal --host=localhost --port=1111
--username=UserReplication --slot=slot_wals --verbose --no-loop
--directory=/data/wals

5- Load file csv on primary server
\COPY public.acme(hash) FROM '/data/csv/file_21444.csv' WITH (FORMAT csv);

example of file contents:
$ head -n 10 /data/csv/file_21444.csv
hash
0b035a242b54076056a59
4be85c5bcc5fe22191933
bb5632427c397b421b928
6ad913964b556d93379d7
99a072b776804e115bb2e
199834e3fe2d244e09543
ba296a09a91423401c901
9657bfcae2e017e9d6f42
0e2d4b2594006930da843

At this point wals were created in the /data/wals directory by
pg_receivewal due to the applied load of item 5 on the primary server.

6- Create pg_basebackup
pg_basebackup -U UserReplication -h localhost -p1111 -P -v
--wal-method=stream --checkpoint=fast -D /data/base/

7- New load file csv on primary server
\COPY public.acme(hash) FROM '/data/csv/file_38629.csv' WITH (FORMAT csv);

more wals were created by pg_receivewal with new data load.

Now I will start the cluster generated by pg_basebackup with the
restore_command parameter
configured to feed on the wals logs generated from the last data load,
thus leaving it updated with the same data as the primary:

8- Restore Command Parameter
restore_command = 'cp /data/wals/%f %p' # command to use to restore an
archived WAL file

and

touch /data/base/recovery.signal

9- Start Cluster (2222 port)
/usr/pgsql-16/bin/pg_ctl -D /data/base start

but when I see the logs I have a surprise

-----------------
LOG: starting PostgreSQL 16.3 on x86_64-pc-linux-gnu, compiled by gcc
(GCC) 8.5.0 20210514 (Red Hat 8.5.0-22), 64-bit
LOG: listening on IPv6 address "::1", port 2222
LOG: listening on IPv4 address "127.0.0.1", port 2222
LOG: listening on Unix socket "/run/postgresql/.s.PGSQL.2222"
LOG: listening on Unix socket "/tmp/.s.PGSQL.2222"
LOG: database system was shut down at 2025-06-30 12:15:28 -04
cp: cannot stat '/dados/temp/wals/00000002.history': No such file or directory
-----------------


The restore_command requires the .history file but it does not exist
in any of the clusters in this simple test, which is wrong in this
example ? Tanks

-- 
foobar

On 6/30/25 12:35, Franklin Anderson de Oliveira Souza wrote:
> I'm trying to simulate a PITR in postgresql 16 with the following steps:
> 

> -----------------
> LOG: starting PostgreSQL 16.3 on x86_64-pc-linux-gnu, compiled by gcc
> (GCC) 8.5.0 20210514 (Red Hat 8.5.0-22), 64-bit
> LOG: listening on IPv6 address "::1", port 2222
> LOG: listening on IPv4 address "127.0.0.1", port 2222
> LOG: listening on Unix socket "/run/postgresql/.s.PGSQL.2222"
> LOG: listening on Unix socket "/tmp/.s.PGSQL.2222"
> LOG: database system was shut down at 2025-06-30 12:15:28 -04
> cp: cannot stat '/dados/temp/wals/00000002.history': No such file or directory
> -----------------

Where is /dados/temp/wals/ coming from?

> 
> 
> The restore_command requires the .history file but it does not exist
> in any of the clusters in this simple test, which is wrong in this
> example ? Tanks
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

## Franklin Anderson de Oliveira Souza (franklinbr@gmail.com):

> LOG: database system was shut down at 2025-06-30 12:15:28 -04
> cp: cannot stat '/dados/temp/wals/00000002.history': No such file or directory
> -----------------
> 
> 
> The restore_command requires the .history file but it does not exist
> in any of the clusters in this simple test, which is wrong in this
> example ? Tanks

Everything is fine - as long as the next log line starts with "starting
backup recovery". Your cluster starts on timeline 1, and the (default)
recovery_target_timeline is "latest", so the recovery process needs
to check if other timelines exist and what the latest timeline is.
It's just that the stderr from cp ends up in your log.
See
https://www.postgresql.org/docs/17/continuous-archiving.html#BACKUP-TIMELINES
for details on timelines.

Regards,
Christoph

-- 
Spare Space

Am 30.06.2025 um 21:45 schrieb Ron Johnson:
> Using PgBackRest might be more convenient, since it handles everything 
> you need, is multithreaded, never removes too many wal files, 
> compresses files if you want and also encrypts them if you want.
>
I agree, with pgBackRest it's basically: pgbackrest --stanza=demo 
--delta --type=time "--target=2025-05-05 15:37:03.157376+00" 
--target-action=promote restore

and you are done. It will also restore configure files and you can also 
selectively restore only one DB in the cluster if you want to speed up 
things. Works very well with S3 too and is very easy to integrate in an 
ansible playbook to have a generic way to do PITRs.

Check out the official user guide: https://pgbackrest.org/user-guide.html

have fun
raphi