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: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Mon, 30 Jun 2025 15:45:24 -0400
Message-ID: <CANzqJaBNufPWAhkNXHBHy2hySOKhPPcYy1sZi=ZHFp-D9j-o5g@mail.gmail.com>
Subject: Re: Simulate a PITR in postgresql 16
To: Franklin Anderson de Oliveira Souza <franklinbr@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000089464a0638cf45d7"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaBNufPWAhkNXHBHy2hySOKhPPcYy1sZi%3DZHFp-D9j-o5g%40mail.gmail.com>
Precedence: bulk

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

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.

(In 2025, I also leave pg_wal on the same mount point as data/.  Disk space
is plentiful and it's just more convenient.)

On Mon, Jun 30, 2025 at 3:35=E2=80=AFPM Franklin Anderson de Oliveira Souza=
 <
franklinbr@gmail.com> wrote:

> 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
>
>
>

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr">Using PgBackRest might be more convenient, since it handle=
s everything you need, is multithreaded, never removes too many wal files, =
compresses files if you want and also encrypts them if you want.<div><br></=
div><div>(In 2025, I also leave pg_wal on the same mount point as data/.=C2=
=A0 Disk space is plentiful and it&#39;s just more convenient.)</div></div>=
<br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=
=3D"gmail_attr">On Mon, Jun 30, 2025 at 3:35=E2=80=AFPM Franklin Anderson d=
e Oliveira Souza &lt;<a href=3D"mailto:franklinbr@gmail.com">franklinbr@gma=
il.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"m=
argin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left=
:1ex">I&#39;m trying to simulate a PITR in postgresql 16 with the following=
 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>
<br>
<br>
</blockquote></div><div><br clear=3D"all"></div><div><br></div><span class=
=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_s=
ignature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div=
>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobs=
ter!</div></div></div></div></div>

--00000000000089464a0638cf45d7--