Thread

1. Simulate a PITR in postgresql 16

   Franklin Anderson de Oliveira Souza <franklinbr@gmail.com> — 2025-06-30T19:35:24Z

   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
   
   
   
   

2. Re: Simulate a PITR in postgresql 16

   Ron <ronljohnsonjr@gmail.com> — 2025-06-30T19:45:24Z

   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 PM 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=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
   >
   >
   >
   
   -- 
   Death to <Redacted>, and butter sauce.
   Don't boil me, I'm still alive.
   <Redacted> lobster!
   

3. Re: Simulate a PITR in postgresql 16

   Adrian Klaver <adrian.klaver@aklaver.com> — 2025-06-30T20:43:12Z

   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
   
   
   
   
   

4. Re: Simulate a PITR in postgresql 16

   Christoph Moench-Tegeder <cmt@burggraben.net> — 2025-06-30T21:03:31Z

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

5. Re: Simulate a PITR in postgresql 16

   raphi <raphi@crashdump.ch> — 2025-07-01T04:35:37Z

   
   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
   
   
   
   
   

6. Re: Simulate a PITR in postgresql 16

   Franklin Anderson de Oliveira Souza <franklinbr@gmail.com> — 2025-07-02T14:32:51Z

   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 às 15:35, Franklin Anderson de Oliveira Souza <
   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=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
   >