MIME-Version: 1.0
References: <CAB_Px=yO5y-6NcZjyDxLUGb4m6VX=-UXhSiuB7eLXp10eRfdQw@mail.gmail.com>
In-Reply-To: <CAB_Px=yO5y-6NcZjyDxLUGb4m6VX=-UXhSiuB7eLXp10eRfdQw@mail.gmail.com>
From: Franklin Anderson de Oliveira Souza <franklinbr@gmail.com>
Date: Wed, 2 Jul 2025 10:32:51 -0400
Message-ID: <CAB_Px=wdunZdhk65JSVbx-Z4QD5tcdzeceEzV+ghhcX65VycNQ@mail.gmail.com>
Subject: Re: Simulate a PITR in postgresql 16
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000007838440638f32318"
Archived-At: <https://www.postgresql.org/message-id/CAB_Px%3DwdunZdhk65JSVbx-Z4QD5tcdzeceEzV%2BghhcX65VycNQ%40mail.gmail.com>
Precedence: bulk

--0000000000007838440638f32318
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

I don't know exactly what I did wrong but redoing what I described in the
email worked perfectly! Thanks everyone!


Enviado do Gmail para celular


Em seg., 30 de jun. de 2025 =C3=A0s 15:35, Franklin Anderson de Oliveira So=
uza <
franklinbr@gmail.com> escreveu:

> 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=3Dlocalhost --port=3D1111
> --username=3DUserReplication --slot=3Dslot_wals --verbose --no-loop
> --directory=3D/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=3Dstream --checkpoint=3Dfast -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 =3D '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
>

--0000000000007838440638f32318
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div><div>I don&#39;t know exactly what I did wrong but redoing what I desc=
ribed in the email worked perfectly! Thanks everyone!</div><br clear=3D"all=
"><br clear=3D"all"><div><div dir=3D"ltr" class=3D"gmail_signature" data-sm=
artmail=3D"gmail_signature">Enviado do Gmail para celular</div></div></div>=
<div><br></div><div><br><div class=3D"gmail_quote gmail_quote_container"><d=
iv dir=3D"ltr" class=3D"gmail_attr">Em seg., 30 de jun. de 2025 =C3=A0s 15:=
35, Franklin Anderson de Oliveira Souza &lt;<a href=3D"mailto:franklinbr@gm=
ail.com">franklinbr@gmail.com</a>&gt; escreveu:<br></div><blockquote class=
=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padd=
ing-left:1ex">I&#39;m trying to simulate a PITR in postgresql 16 with the f=
ollowing steps:<br>
<br>
directorys:<br>
/data/primary<br>
/data/base<br>
/data/wals<br>
/data/csv<br>
<br>
1- Create cluster to primary postgresql:<br>
/usr/pgsql-16/bin/initd -D /data/primary<br>
<br>
2- Start Cluster (1111 port)<br>
/usr/pgsql-16/bin/pg_ctl -D /data/primary start<br>
<br>
3- Create Database, table, user and slot:<br>
create database &quot;Foo&quot;;<br>
\c Foo;<br>
create table public.acme(hash text);<br>
create role &quot;UserReplication&quot; with login password &#39;123&#39; r=
eplication;<br>
select pg_create_physical_replication_slot(&#39;slot_wals&#39;);<br>
<br>
4- Start pg_receivewal:<br>
/usr/pgsql-16/bin/pg_receivewal --host=3Dlocalhost --port=3D1111<br>
--username=3DUserReplication --slot=3Dslot_wals --verbose --no-loop<br>
--directory=3D/data/wals<br>
<br>
5- Load file csv on primary server<br>
\COPY public.acme(hash) FROM &#39;/data/csv/file_21444.csv&#39; WITH (FORMA=
T csv);<br>
<br>
example of file contents:<br>
$ head -n 10 /data/csv/file_21444.csv<br>
hash<br>
0b035a242b54076056a59<br>
4be85c5bcc5fe22191933<br>
bb5632427c397b421b928<br>
6ad913964b556d93379d7<br>
99a072b776804e115bb2e<br>
199834e3fe2d244e09543<br>
ba296a09a91423401c901<br>
9657bfcae2e017e9d6f42<br>
0e2d4b2594006930da843<br>
<br>
At this point wals were created in the /data/wals directory by<br>
pg_receivewal due to the applied load of item 5 on the primary server.<br>
<br>
6- Create pg_basebackup<br>
pg_basebackup -U UserReplication -h localhost -p1111 -P -v<br>
--wal-method=3Dstream --checkpoint=3Dfast -D /data/base/<br>
<br>
7- New load file csv on primary server<br>
\COPY public.acme(hash) FROM &#39;/data/csv/file_38629.csv&#39; WITH (FORMA=
T csv);<br>
<br>
more wals were created by pg_receivewal with new data load.<br>
<br>
Now I will start the cluster generated by pg_basebackup with the<br>
restore_command parameter<br>
configured to feed on the wals logs generated from the last data load,<br>
thus leaving it updated with the same data as the primary:<br>
<br>
8- Restore Command Parameter<br>
restore_command =3D &#39;cp /data/wals/%f %p&#39; # command to use to resto=
re an<br>
archived WAL file<br>
<br>
and<br>
<br>
touch /data/base/recovery.signal<br>
<br>
9- Start Cluster (2222 port)<br>
/usr/pgsql-16/bin/pg_ctl -D /data/base start<br>
<br>
but when I see the logs I have a surprise<br>
<br>
-----------------<br>
LOG: starting PostgreSQL 16.3 on x86_64-pc-linux-gnu, compiled by gcc<br>
(GCC) 8.5.0 20210514 (Red Hat 8.5.0-22), 64-bit<br>
LOG: listening on IPv6 address &quot;::1&quot;, port 2222<br>
LOG: listening on IPv4 address &quot;127.0.0.1&quot;, port 2222<br>
LOG: listening on Unix socket &quot;/run/postgresql/.s.PGSQL.2222&quot;<br>
LOG: listening on Unix socket &quot;/tmp/.s.PGSQL.2222&quot;<br>
LOG: database system was shut down at 2025-06-30 12:15:28 -04<br>
cp: cannot stat &#39;/dados/temp/wals/00000002.history&#39;: No such file o=
r directory<br>
-----------------<br>
<br>
<br>
The restore_command requires the .history file but it does not exist<br>
in any of the clusters in this simple test, which is wrong in this<br>
example ? Tanks<br>
<br>
-- <br>
foobar<br>
</blockquote></div></div>

--0000000000007838440638f32318--