MIME-Version: 1.0
From: Franklin Anderson de Oliveira Souza <franklinbr@gmail.com>
Date: Mon, 30 Jun 2025 15:35:24 -0400
Message-ID: <CAB_Px=yO5y-6NcZjyDxLUGb4m6VX=-UXhSiuB7eLXp10eRfdQw@mail.gmail.com>
Subject: Simulate a PITR in postgresql 16
To: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAB_Px%3DyO5y-6NcZjyDxLUGb4m6VX%3D-UXhSiuB7eLXp10eRfdQw%40mail.gmail.com>
Precedence: bulk

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