[postgres@pg01 ~]$ psql -c "ALTER SYSTEM SET archive_command = 'test ! -f /var/lib/pgsql/18/backups/%f && cp %p /var/lib/pgsql/18/backups/%f'"
ALTER SYSTEM

[postgres@pg01 ~]$ psql -c "ALTER SYSTEM SET archive_mode = 'on'"
ALTER SYSTEM

[root@pg01 ~]# systemctl restart postgresql-18

[postgres@pg01 ~]$ psql -c "SHOW archive_command"
                               archive_command                                
------------------------------------------------------------------------------
 test ! -f /var/lib/pgsql/18/backups/%f && cp %p /var/lib/pgsql/18/backups/%f
(1 row)

[postgres@pg01 ~]$ psql -c "SHOW archive_mode"
 archive_mode 
--------------
 on
(1 row)

Hozzunk létre teszt adatokat

[postgres@pg01 ~]$ createuser test

[postgres@pg01 ~]$ createdb -O test test

[postgres@pg01 ~]$ psql -U test -d test -c 'CREATE TABLE test (id INT, data TEXT)'
CREATE TABLE

[postgres@pg01 ~]$ psql -U test -d test -c "INSERT INTO test (id, data) VALUES (1, 'backup előtt')"
INSERT 0 1

[postgres@pg01 ~]$ pg_basebackup -D /var/lib/pgsql/18/backups/backup_1 -X stream -P
31412/31412 kB (100%), 1/1 tablespace

[postgres@pg01 ~]$ psql -U test -d test -c "INSERT INTO test (id, data) VALUES (2, 'backup után')"
INSERT 0 1

[postgres@pg01 ~]$ psql -U test -d test -c "SELECT now()"
              now              
-------------------------------
 2026-04-22 02:57:53.405918+02
(1 row)

[postgres@pg01 ~]$ psql -U test -d test -c "INSERT INTO test (id, data) VALUES (3, 'törlés előtt')"
INSERT 0 1

[postgres@pg01 ~]$ psql -U test -d test -c "SELECT now()"
              now              
-------------------------------
 2026-04-22 02:58:26.462712+02
(1 row)

[postgres@pg01 ~]$ psql -U test -d test -c "DROP TABLE test"
DROP TABLE

[postgres@pg01 18]$ psql -U test -d test -c "SELECT now()"
              now              
-------------------------------
 2026-04-22 02:59:25.744184+02
(1 row)

[postgres@pg01 ~]$ psql -c "SELECT * FROM test"
ERROR:  relation "test" does not exist
LINE 1: SELECT * FROM test

[postgres@pg01 ~]$ psql -c "SELECT pg_switch_wal()"
 pg_switch_wal 
---------------
 0/1000C300
(1 row)

                     ^

[root@pg03 ~]# rsync -a --delete root@pg01:/var/lib/pgsql/18/backups/ /var/lib/pgsql/18/backups/
root@pg01's password: 

[root@pg03 ~]# systemctl stop postgresql-18

[root@pg03 ~]# rm -rf /var/lib/pgsql/18/data/*

[root@pg03 ~]# cp -a /var/lib/pgsql/18/backups/backup_1/* /var/lib/pgsql/18/data/

[root@pg03 ~]# cat /var/lib/pgsql/18/data/postgresql.auto.conf 
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
archive_command = 'test ! -f /var/lib/pgsql/18/backups/%f && cp %p /var/lib/pgsql/18/backups/%f'
archive_mode = 'on'

[root@pg03 ~]# export RECOVERY_TIMESTAMP='2026-04-22 04:09:07.322546+02'

[root@pg03 ~]# cat > /var/lib/pgsql/18/data/postgresql.auto.conf <<EOF
restore_command = 'cp /var/lib/pgsql/18/backups/%f %p'
recovery_target_time = '$RECOVERY_TIMESTAMP'
recovery_target_action = 'promote'
EOF

[root@pg03 ~]# cat /var/lib/pgsql/18/data/postgresql.auto.conf 
restore_command = 'cp /var/lib/pgsql/18/backups/%f %p'
recovery_target_time = '2026-04-22 04:09:07.322546+02'
recovery_target_action = 'promote'

[root@pg03 ~]# touch /var/lib/pgsql/18/data/recovery.signal

[root@pg03 ~]# chown -R postgres:postgres /var/lib/pgsql/18/data/

[root@pg03 ~]# rm -rf /var/lib/pgsql/18/data/log/*

[root@pg03 ~]# systemctl restart postgresql-18

[root@pg03 ~]# psql -U test -d test -c "SELECT * FROM test"
 id |     data     
----+--------------
  1 | backup előtt
  2 | backup után
  3 | törlés előtt
(3 rows)

[root@pg03 ~]# cat /var/lib/pgsql/18/data/log/postgresql-Wed.log 
2026-04-22 04:45:22.243 CEST [1249] LOG:  starting PostgreSQL 18.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-11), 64-bit
2026-04-22 04:45:22.243 CEST [1249] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2026-04-22 04:45:22.243 CEST [1249] LOG:  listening on IPv6 address "::", port 5432
2026-04-22 04:45:22.243 CEST [1249] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
2026-04-22 04:45:22.244 CEST [1249] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2026-04-22 04:45:22.247 CEST [1256] LOG:  database system was interrupted; last known up at 2026-04-22 02:56:01 CEST
cp: cannot stat '/var/lib/pgsql/18/backups/00000002.history': No such file or directory
2026-04-22 04:45:22.347 CEST [1256] LOG:  starting backup recovery with redo LSN 0/B000028, checkpoint LSN 0/B0000B8, on timeline ID 1
2026-04-22 04:45:22.355 CEST [1256] LOG:  restored log file "00000001000000000000000B" from archive
2026-04-22 04:45:22.362 CEST [1256] LOG:  starting point-in-time recovery to 2026-04-22 04:09:07.322546+02
2026-04-22 04:45:22.364 CEST [1256] LOG:  redo starts at 0/B000028
2026-04-22 04:45:22.372 CEST [1256] LOG:  restored log file "00000001000000000000000C" from archive
2026-04-22 04:45:22.385 CEST [1256] LOG:  restored log file "00000001000000000000000D" from archive
2026-04-22 04:45:22.397 CEST [1256] LOG:  restored log file "00000001000000000000000E" from archive
2026-04-22 04:45:22.404 CEST [1256] LOG:  completed backup recovery with redo LSN 0/B000028 and end LSN 0/B000158
2026-04-22 04:45:22.404 CEST [1256] LOG:  consistent recovery state reached at 0/B000158
2026-04-22 04:45:22.404 CEST [1249] LOG:  database system is ready to accept read-only connections
2026-04-22 04:45:22.415 CEST [1256] LOG:  restored log file "00000001000000000000000F" from archive
2026-04-22 04:45:22.430 CEST [1256] LOG:  restored log file "000000010000000000000010" from archive
cp: cannot stat '/var/lib/pgsql/18/backups/000000010000000000000011': No such file or directory
2026-04-22 04:45:22.440 CEST [1256] LOG:  recovery stopping before commit of transaction 784, time 2026-04-22 04:09:58.572915+02
2026-04-22 04:45:22.440 CEST [1256] LOG:  redo done at 0/1000A220 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.07 s
2026-04-22 04:45:22.440 CEST [1256] LOG:  last completed transaction was at log time 2026-04-22 04:09:03.049934+02
2026-04-22 04:45:22.446 CEST [1256] LOG:  restored log file "000000010000000000000010" from archive
cp: cannot stat '/var/lib/pgsql/18/backups/00000002.history': No such file or directory
2026-04-22 04:45:22.458 CEST [1256] LOG:  selected new timeline ID: 2
cp: cannot stat '/var/lib/pgsql/18/backups/00000001.history': No such file or directory
2026-04-22 04:45:22.475 CEST [1256] LOG:  archive recovery complete
2026-04-22 04:45:22.476 CEST [1254] LOG:  checkpoint starting: end-of-recovery immediate wait
2026-04-22 04:45:22.487 CEST [1254] LOG:  checkpoint complete: wrote 36 buffers (0.2%), wrote 3 SLRU buffers; 0 WAL file(s) added, 0 removed, 5 recycled; write=0.003 s, sync=0.004 s, total=0.013 s; sync files=30, longest=0.001 s, average=0.001 s; distance=81960 kB, estimate=81960 kB; lsn=0/1000A220, redo lsn=0/1000A220
2026-04-22 04:45:22.490 CEST [1249] LOG:  database system is ready to accept connections

Tipikus helyzetek

• Az archiválás nem tartalmazza a szükséges WAL-t ezért nem tudunk visszaállni egy megadott időpontra. Ilyenkor a forrás oldalon futtatni kell a '' psql -c „SELECT pg_switch_wal()” '' utasítást és újra áttölteni a helyreállító gépre.
• Ha nem hozzuk létre a '' recovery.signal '' állományt, akkor a pg_basebackup állaporta tér vissza és nem foglalkozik a recovery_target_time értékével.
• A recovery_target_action meghatározza, milyen állapotba kerüljön a postgresql a recovery során.