Összehasonlítás

A kiválasztott és az jelenlegi változat között az alábbi különbségek fordulnak elő:

--- postgresql:backup-restore [2026/04/29 11:43] – riba.zoltan
+++ postgresql:backup-restore [2026/04/29 12:27] (aktuális) – [WAL állományok ellenőrzése] riba.zoltan
@@ Sor 426: / Sor 426: @@
 [root@pg03 ~]# rm -rf /var/lib/pgsql/18/data/log/*
 </code>
+==== Visszaállás megadott időpontra ====
 Hozzá kell adni a ''$PGDATA/postgresql.conf'' VAGY a ''$PGDATA/postgresql.auto.conf'' fájlhoz az helyreállításhoz szükséges adatokat és létre kell hozni a ''$PGDATA/recovery.signal'' állományt. A példában a tábla törlését megelőző pillanatot adtam meg.
 <code>
-[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.
 [root@pg03 ~]# cat /var/lib/pgsql/18/data/postgresql.auto.conf
 # Do not edit this file manually!
@@ Sor 653: / Sor 652: @@
 rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/0603D6B8, prev 0/0603D270, desc: RUNNING_XACTS nextXid 770 latestCompletedXid 769 oldestRunningXid 770
 rmgr: XLOG        len (rec/tot):     24/    24, tx:          0, lsn: 0/0603D6F0, prev 0/0603D6B8, desc: SWITCH
+</code>
+==== Visszaállás megadott LSN-re ====
+Hozzá kell adni a ''$PGDATA/postgresql.conf'' VAGY a ''$PGDATA/postgresql.auto.conf'' fájlhoz az helyreállításhoz szükséges adatokat és létre kell hozni a ''$PGDATA/recovery.signal'' állományt. A példában a tábla törlését megelőző pillanatot adtam meg.
-[postgres@pg01 data]$ /usr/pgsql-18/bin/pg_waldump /var/lib/pgsql/18/backups/000000010000000000000010
+<code>
-rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/10000028, prev 0/0F000158, desc: RUNNING_XACTS nextXid 782 latestCompletedXid 781 oldestRunningXid 782
+[root@pg03 ~]# cat /var/lib/pgsql/18/data/postgresql.auto.conf
-rmgr: Heap        len (rec/tot):     54/   182, tx:        782, lsn: 0/10000060, prev 0/10000028, desc: INSERT off: 2, flags: 0x00, blkref #0: rel 1663/16396/16402 blk 0 FPW
+# Do not edit this file manually!
-rmgr: Transaction len (rec/tot):     34/    34, tx:        782, lsn: 0/10000118, prev 0/10000060, desc: COMMIT 2026-04-22 04:08:32.694902 CEST
+# It will be overwritten by the ALTER SYSTEM command.
-rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/10000140, prev 0/10000118, desc: RUNNING_XACTS nextXid 783 latestCompletedXid 782 oldestRunningXid 783
-rmgr: Heap        len (rec/tot):     75/    75, tx:        783, lsn: 0/10000178, prev 0/10000140, desc: INSERT off: 3, flags: 0x00, blkref #0: rel 1663/16396/16402 blk 0
+[root@pg03 ~]# export RECOVERY_LSN='0/0603CAA8'
-rmgr: Transaction len (rec/tot):     34/    34, tx:        783, lsn: 0/100001C8, prev 0/10000178, desc: COMMIT 2026-04-22 04:09:03.049934 CEST
-rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/100001F0, prev 0/100001C8, desc: RUNNING_XACTS nextXid 784 latestCompletedXid 783 oldestRunningXid 784
-rmgr: Standby     len (rec/tot):     42/    42, tx:        784, lsn: 0/10000228, prev 0/100001F0, desc: LOCK xid 784 db 16396 rel 16402
+[root@pg03 ~]# cat >>/var/lib/pgsql/18/data/postgresql.auto.conf <<EOF
-rmgr: XLOG        len (rec/tot):     49/  7753, tx:        784, lsn: 0/10000258, prev 0/10000228, desc: FPI_FOR_HINT , blkref #0: rel 1663/16396/2608 blk 12 FPW
+# Do not edit this file manually!
-rmgr: Standby     len (rec/tot):     42/    42, tx:        784, lsn: 0/100020C0, prev 0/10000258, desc: LOCK xid 784 db 16396 rel 16405
+# It will be overwritten by the ALTER SYSTEM command.
-rmgr: Standby     len (rec/tot):     42/    42, tx:        784, lsn: 0/100020F0, prev 0/100020C0, desc: LOCK xid 784 db 16396 rel 16406
+restore_command = 'cp /var/lib/pgsql/18/backups/%f %p'
-rmgr: XLOG        len (rec/tot):     49/  4397, tx:        784, lsn: 0/10002120, prev 0/100020F0, desc: FPI_FOR_HINT , blkref #0: rel 1663/16396/1247 blk 14 FPW
+recovery_target_lsn = '$RECOVERY_LSN'
-rmgr: XLOG        len (rec/tot):     49/  8221, tx:        784, lsn: 0/10003250, prev 0/10002120, desc: FPI_FOR_HINT , blkref #0: rel 1663/16396/2610 blk 1 FPW
+recovery_target_action = 'promote'
-rmgr: XLOG        len (rec/tot):     49/  2173, tx:        784, lsn: 0/10005288, prev 0/10003250, desc: FPI_FOR_HINT , blkref #0: rel 1663/16396/1249 blk 55 FPW
+EOF
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10005B08, prev 0/10005288, desc: DELETE xmax: 784, off: 1, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/2610 blk 1
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10005B40, prev 0/10005B08, desc: DELETE xmax: 784, off: 14, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/1249 blk 55
+[root@pg03 ~]# cat /var/lib/pgsql/18/data/postgresql.auto.conf
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10005B78, prev 0/10005B40, desc: DELETE xmax: 784, off: 15, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/1249 blk 55
+# Do not edit this file manually!
-rmgr: Heap        len (rec/tot):     59/  7959, tx:        784, lsn: 0/10005BB0, prev 0/10005B78, desc: DELETE xmax: 784, off: 40, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/1259 blk 15 FPW
+# It will be overwritten by the ALTER SYSTEM command.
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10007AE0, prev 0/10005BB0, desc: DELETE xmax: 784, off: 126, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/2608 blk 12
+restore_command = 'cp /var/lib/pgsql/18/backups/%f %p'
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10007B18, prev 0/10007AE0, desc: DELETE xmax: 784, off: 127, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/2608 blk 12
+recovery_target_lsn = '0/0603CAA8'
-rmgr: Standby     len (rec/tot):     66/    66, tx:          0, lsn: 0/10007B50, prev 0/10007B18, desc: LOCK xid 784 db 16396 rel 16402 xid 784 db 16396 rel 16405 xid 784 db 16396 rel 16406
+recovery_target_action = 'promote'
-rmgr: Standby     len (rec/tot):     54/    54, tx:          0, lsn: 0/10007B98, prev 0/10007B50, desc: RUNNING_XACTS nextXid 785 latestCompletedXid 783 oldestRunningXid 784; 1 xacts: 784
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10007BD0, prev 0/10007B98, desc: DELETE xmax: 784, off: 13, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/1249 blk 55
+[root@pg03 ~]# touch /var/lib/pgsql/18/data/recovery.signal
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10007C08, prev 0/10007BD0, desc: DELETE xmax: 784, off: 12, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/1249 blk 55
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10007C40, prev 0/10007C08, desc: DELETE xmax: 784, off: 11, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/1249 blk 55
+[root@pg03 ~]# chown -R postgres:postgres /var/lib/pgsql/18/data/
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10007C78, prev 0/10007C40, desc: DELETE xmax: 784, off: 10, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/1249 blk 55
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10007CB0, prev 0/10007C78, desc: DELETE xmax: 784, off: 9, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/1249 blk 55
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10007CE8, prev 0/10007CB0, desc: DELETE xmax: 784, off: 8, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/1249 blk 55
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10007D20, prev 0/10007CE8, desc: DELETE xmax: 784, off: 5, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/1249 blk 55
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10007D58, prev 0/10007D20, desc: DELETE xmax: 784, off: 6, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/1249 blk 55
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10007D90, prev 0/10007D58, desc: DELETE xmax: 784, off: 7, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/1249 blk 55
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10007DC8, prev 0/10007D90, desc: DELETE xmax: 784, off: 39, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/1259 blk 15
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10007E00, prev 0/10007DC8, desc: DELETE xmax: 784, off: 128, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/2608 blk 12
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10007E38, prev 0/10007E00, desc: DELETE xmax: 784, off: 23, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/1247 blk 14
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10007E70, prev 0/10007E38, desc: DELETE xmax: 784, off: 124, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/2608 blk 12
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10007EA8, prev 0/10007E70, desc: DELETE xmax: 784, off: 22, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/1247 blk 14
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10007EE0, prev 0/10007EA8, desc: DELETE xmax: 784, off: 123, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/2608 blk 12
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10007F18, prev 0/10007EE0, desc: DELETE xmax: 784, off: 4, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/1249 blk 55
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10007F50, prev 0/10007F18, desc: DELETE xmax: 784, off: 3, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/1249 blk 55
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10007F88, prev 0/10007F50, desc: DELETE xmax: 784, off: 2, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/1249 blk 55
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10007FC0, prev 0/10007F88, desc: DELETE xmax: 784, off: 1, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/1249 blk 55
-rmgr: XLOG        len (rec/tot):     49/  7921, tx:        784, lsn: 0/10007FF8, prev 0/10007FC0, desc: FPI_FOR_HINT , blkref #0: rel 1663/16396/1249 blk 54 FPW
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10009F08, prev 0/10007FF8, desc: DELETE xmax: 784, off: 58, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/1249 blk 54
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10009F40, prev 0/10009F08, desc: DELETE xmax: 784, off: 57, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/1249 blk 54
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10009F78, prev 0/10009F40, desc: DELETE xmax: 784, off: 55, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/1249 blk 54
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10009FB0, prev 0/10009F78, desc: DELETE xmax: 784, off: 56, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/1249 blk 54
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/10009FE8, prev 0/10009FB0, desc: DELETE xmax: 784, off: 41, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/1259 blk 15
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/1000A038, prev 0/10009FE8, desc: DELETE xmax: 784, off: 125, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16396/2608 blk 12
-rmgr: XLOG        len (rec/tot):     49/   373, tx:        784, lsn: 0/1000A070, prev 0/1000A038, desc: FPI_FOR_HINT , blkref #0: rel 1664/0/1214 blk 0 FPW
-rmgr: Heap        len (rec/tot):     54/    54, tx:        784, lsn: 0/1000A1E8, prev 0/1000A070, desc: DELETE xmax: 784, off: 5, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1664/0/1214 blk 0
-rmgr: Transaction len (rec/tot):   1089/  1089, tx:        784, lsn: 0/1000A220, prev 0/1000A1E8, desc: COMMIT 2026-04-22 04:09:58.572915 CEST; rels: base/16396/16402 base/16396/16405 base/16396/16406; dropped stats: 2/16396/16406 2/16396/16405 2/16396/16402; inval msgs: catcache 34 catcache 7 catcache 6 catcache 7 catcache 6 catcache 57 catcache 56 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 57 catcache 56 catcache 82 catcache 81 catcache 82 catcache 81 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 57 catcache 56 relcache 16406 relcache 16405 snapshot 2608 relcache 16405 snapshot 2608 snapshot 2608 snapshot 2608 relcache 16402 snapshot 2608 snapshot 1214
-rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/1000A668, prev 0/1000A220, desc: RUNNING_XACTS nextXid 785 latestCompletedXid 784 oldestRunningXid 785
-rmgr: Heap2       len (rec/tot):     64/    64, tx:          0, lsn: 0/1000A6A0, prev 0/1000A668, desc: PRUNE_ON_ACCESS snapshotConflictHorizon: 784, isCatalogRel: F, nplans: 0, nredirected: 0, ndead: 3, nunused: 1, dead: [38, 39, 40], unused: [41], blkref #0: rel 1663/16396/1259 blk 15
-rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/1000A6E0, prev 0/1000A6A0, desc: RUNNING_XACTS nextXid 785 latestCompletedXid 784 oldestRunningXid 785
-rmgr: XLOG        len (rec/tot):     49/  6769, tx:          0, lsn: 0/1000A718, prev 0/1000A6E0, desc: FPI_FOR_HINT , blkref #0: rel 1663/16396/2663 blk 2 FPW
-rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/1000C1A8, prev 0/1000A718, desc: RUNNING_XACTS nextXid 785 latestCompletedXid 784 oldestRunningXid 785
-rmgr: XLOG        len (rec/tot):     30/    30, tx:          0, lsn: 0/1000C1E0, prev 0/1000C1A8, desc: CHECKPOINT_REDO wal_level replica
-rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/1000C200, prev 0/1000C1E0, desc: RUNNING_XACTS nextXid 785 latestCompletedXid 784 oldestRunningXid 785
-rmgr: XLOG        len (rec/tot):    114/   114, tx:          0, lsn: 0/1000C238, prev 0/1000C200, desc: CHECKPOINT_ONLINE redo 0/1000C1E0; tli 1; prev tli 1; fpw true; wal_level replica; xid 0:785; oid 24594; multi 1; offset 0; oldest xid 722 in DB 1; oldest multi 1 in DB 4; oldest/newest commit timestamp xid: 0/0; oldest running xid 785; online
-rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/1000C2B0, prev 0/1000C238, desc: RUNNING_XACTS nextXid 785 latestCompletedXid 784 oldestRunningXid 785
-rmgr: XLOG        len (rec/tot):     24/    24, tx:          0, lsn: 0/1000C2E8, prev 0/1000C2B0, desc: SWITCH
 </code>
+Adatbázis kezelő indítása
+<code>
+[root@pg03 ~]# systemctl restart postgresql-18
+</code>
+Adatok ellenőrzése
+<code>
+[root@pg03 ~]# psql -U test -d test -c "SELECT * FROM test"
+ id |     data
+----+--------------
+| backup előtt
+| backup után
+| törlés előtt
+(3 rows)
+</code>
+===== WAL állományok ellenőrzése =====
+A WAL ellenőrzése kritikus a sikeres helyreállításhoz. Az alábbi lépések segítségével meggyőződhetünk arról, hogy helyreállítható-e a rendszer.
+Archiver állapotának lekérdezése.
+<code>
+[root@pg01 ~]# psql -U postgres -x -c "SELECT archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time FROM pg_stat_archiver"
+-[ RECORD 1 ]------+------------------------------
+archived_count     | 4
+last_archived_wal  | 000000010000000000000003
+last_archived_time | 2026-04-29 13:26:05.951808+02
+failed_count       | 0
+last_failed_wal    |
+last_failed_time   |
+</code>
+A ''000000010000000000000003'' előtti fájlokra nincs szükség amennyiben a legutóbbi ''pg_basebackup'' futtatása utáni állapotra kell visszaállni.