I don't think it has anything to do with the pg_hba.conf file. Archive seems missing. I will suggest taking a full backup, as you have mentioned that you are able to take full backup.
Regards, Ikram On Fri, Jul 26, 2024 at 11:24 AM KK CHN <kkchn...@gmail.com> wrote: > here the log outputs.... > > When I ran > > $sudo -u enterprisedb pgbackrest --stanza=Demo_Repo --delta > --set=20240719-122703F_20240724-094727I --target-timeline=current > --type=time "--target=2024-07-24 09:44:01.3255+05:30" > --target-action=promote restore > > > > The Edb server log out put shows > ........................................................................... > ................................................................ > 2024-07-23 15:34:12 IST ERROR: syntax error at or near "selecct" at > character 1 > 2024-07-23 15:34:12 IST STATEMENT: selecct current_timestamp > 2024-07-23 15:34:26 IST FATAL: database "enterprisedb" does not exist > 2024-07-24 09:38:20 IST FATAL: database "enterprisedb" does not exist > 2024-07-24 09:42:17 IST FATAL: database "test1" does not exist > 2024-07-24 09:43:56 IST FATAL: database "enterprisedb" does not exist > 2024-07-24 09:45:07 IST FATAL: database "enterprisedb" does not exist > 2024-07-24 09:45:16 IST ERROR: relation "important_table" does not exist > at character 58 > 2024-07-24 09:45:16 IST STATEMENT: begin; drop table important_table; > commit; select * from important_table; > 2024-07-24 09:46:00 IST LOG: checkpoint starting: time > 2024-07-24 09:46:02 IST LOG: checkpoint complete: wrote 16 buffers > (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=1.506 s, > sync=0.001 s, total=1.509 s; sync files=9, longest=0.001 s, average=0.001 > s; distance=93 kB, estimate=171 kB; lsn=0/3C045768, redo lsn=0/3C045730 > 2024-07-24 09:47:27 IST LOG: checkpoint starting: immediate force wait > 2024-07-24 09:47:27 IST LOG: checkpoint complete: wrote 2 buffers (0.0%); > 0 WAL file(s) added, 0 removed, 1 recycled; write=0.001 s, sync=0.001 s, > total=0.003 s; sync files=1, longest=0.001 s, average=0.001 s; > distance=16106 kB, estimate=16106 kB; lsn=0/3D000060, redo lsn=0/3D000028 > 2024-07-24 09:47:27.788 P00 INFO: archive-push command begin 2.52.1: > [pg_wal/00000009000000000000003C] --exec-id=34036-c1d6de0b > --log-level-console=info --log-level-file=debug > --pg1-path=/var/lib/edb/as16/data --pg-version-force=16 > --repo1-host=10.10.20.7 --repo1-host-user=postgres --stanza=Demo_Repo > 2024-07-24 09:47:28.130 P00 INFO: pushed WAL file > '00000009000000000000003C' to the archive > 2024-07-24 09:47:28.230 P00 INFO: archive-push command end: completed > successfully (444ms) > > > > When I ran $ sudo systemctl start edb-as-16.service > > 4-07-26 11:32:56 IST LOG: starting PostgreSQL 16.3 (EnterpriseDB Advanced > Server 16.3.0) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 > 20231218 (Red Hat 11.4.1-3), 64-bit > 2024-07-26 11:32:56 IST LOG: listening on IPv4 address "0.0.0.0", port > 5444 > 2024-07-26 11:32:56 IST LOG: listening on IPv6 address "::", port 5444 > 2024-07-26 11:32:56 IST LOG: listening on Unix socket "/tmp/.s.PGSQL.5444" > 2024-07-26 11:32:56 IST LOG: database system was interrupted; last known > up at 2024-07-24 09:47:27 IST > 2024-07-26 11:32:56 IST LOG: starting point-in-time recovery to > 2024-07-24 09:44:01.3255+05:30 > 2024-07-26 11:32:56 IST LOG: starting backup recovery with redo LSN > 0/3D000028, checkpoint LSN 0/3D000060, on timeline ID 9 > 2024-07-26 11:32:56.475 P00 INFO: archive-get command begin 2.52.1: > [00000009.history, pg_wal/RECOVERYHISTORY] --exec-id=43293-1d583a8e > --log-level-console=info --log-level-file=debug > --pg1-path=/var/lib/edb/as16/data --pg-version-force=16 > --repo1-host=10.10.20.7 --repo1-host-user=postgres --stanza=Demo_Repo > 2024-07-26 11:32:56.709 P00 INFO: found 00000009.history in the repo1: > 16-1 archive > 2024-07-26 11:32:56.809 P00 INFO: archive-get command end: completed > successfully (335ms) > 2024-07-26 11:32:56 IST LOG: restored log file "00000009.history" from > archive > 2024-07-26 11:32:56.816 P00 INFO: archive-get command begin 2.52.1: > [00000009000000000000003D, pg_wal/RECOVERYXLOG] --exec-id=43295-55c5d6f7 > --log-level-console=info --log-level-file=debug > --pg1-path=/var/lib/edb/as16/data --pg-version-force=16 > --repo1-host=10.10.20.7 --repo1-host-user=postgres --stanza=Demo_Repo > 2024-07-26 11:32:56.999 P00 INFO: found 00000009000000000000003D in the > repo1: 16-1 archive > 2024-07-26 11:32:57.099 P00 INFO: archive-get command end: completed > successfully (285ms) > 2024-07-26 11:32:57 IST LOG: restored log file "00000009000000000000003D" > from archive > 2024-07-26 11:32:57 IST LOG: redo starts at 0/3D000028 > 2024-07-26 11:32:57.114 P00 INFO: archive-get command begin 2.52.1: > [00000009000000000000003E, pg_wal/RECOVERYXLOG] --exec-id=43297-34867c05 > --log-level-console=info --log-level-file=debug > --pg1-path=/var/lib/edb/as16/data --pg-version-force=16 > --repo1-host=10.10.20.7 --repo1-host-user=postgres --stanza=Demo_Repo > 2024-07-26 11:32:57.282 P00 INFO: found 00000009000000000000003E in the > repo1: 16-1 archive > 2024-07-26 11:32:57.382 P00 INFO: archive-get command end: completed > successfully (270ms) > 2024-07-26 11:32:57 IST LOG: restored log file "00000009000000000000003E" > from archive > 2024-07-26 11:32:57.400 P00 INFO: archive-get command begin 2.52.1: > [00000009000000000000003F, pg_wal/RECOVERYXLOG] --exec-id=43299-e2db2e1b > --log-level-console=info --log-level-file=debug > --pg1-path=/var/lib/edb/as16/data --pg-version-force=16 > --repo1-host=10.10.20.7 --repo1-host-user=postgres --stanza=Demo_Repo > 2024-07-26 11:32:57.521 P00 INFO: unable to find > 00000009000000000000003F in the archive > 2024-07-26 11:32:57.621 P00 INFO: archive-get command end: completed > successfully (222ms) > 2024-07-26 11:32:57 IST LOG: completed backup recovery with redo LSN > 0/3D000028 and end LSN 0/3D000100 > 2024-07-26 11:32:57 IST LOG: consistent recovery state reached at > 0/3D000100 > 2024-07-26 11:32:57 IST LOG: database system is ready to accept read-only > connections > 2024-07-26 11:32:57.632 P00 INFO: archive-get command begin 2.52.1: > [00000009000000000000003F, pg_wal/RECOVERYXLOG] --exec-id=43301-f613dae9 > --log-level-console=info --log-level-file=debug > --pg1-path=/var/lib/edb/as16/data --pg-version-force=16 > --repo1-host=10.10.20.7 --repo1-host-user=postgres --stanza=Demo_Repo > 2024-07-26 11:32:57.761 P00 INFO: unable to find > 00000009000000000000003F in the archive > 2024-07-26 11:32:57.861 P00 INFO: archive-get command end: completed > successfully (231ms) > 2024-07-26 11:32:57 IST LOG: redo done at 0/3E000060 system usage: CPU: > user: 0.00 s, system: 0.00 s, elapsed: 0.75 s > 2024-07-26 11:32:57 IST FATAL: recovery ended before configured recovery > target was reached > 2024-07-26 11:32:57 IST LOG: startup process (PID 43292) exited with exit > code 1 > > > ONLY inference I can make is > > INFO unable to find 00000009000000000000003F in the archive ( This > means the EDB server (10.10.20.6 ) unable to push the archives to the > Repo server(10.10.20.7 ) ? Is that the reason for the recovery and > start backing of edb server fails ? > > > the pg_hba.conf entry in the EDB Server machine is as > > host all all 127.0.0.1/32 ident > host all all 10.10.20.7/32 > scram-sha-256 > #host all all 10.10.20.7/32 trust > # IPv6 local connections: > host all all ::1/128 ident > #host all all 10.10.20.7/24 trust > > # Allow replication connections from localhost, by a user with the > # replication privilege. > local replication all peer > host replication all 10.10.20.7/32 > scram-sha-256 > host replication all 127.0.0.1/32 ident > host replication all ::1/128 ident > > > Do I have to change anything in pg_hba.conf ? > > > my EDB Server conf as this > > archive_mode = on > archive_command = 'pgbackrest --stanza=Demo_Repo archive-push %p' > log_filename = 'postgresql.log' > max_wal_senders = 5 > wal_level = replica > > > Any help ? > > Krishane > > > > > On Fri, Jul 26, 2024 at 10:45 AM Muhammad Ikram <mmik...@gmail.com> wrote: > >> Hi KK CHN >> >> Could you check server logs ? >> Your service trace suggests that it started server and then failure >> happened >> >> ul 26 09:48:49 service01 systemd[1]: Started EDB Postgres Advanced Server >> 16. >> Jul 26 09:48:50 service01 systemd[1]: edb-as-16.service: Main process >> exited, code=exited, status=1/FAILURE >> >> >> >> Regards, >> Ikram >> >> >> On Fri, Jul 26, 2024 at 10:04 AM KK CHN <kkchn...@gmail.com> wrote: >> >>> List, >>> >>> Reference: https://pgbackrest.org/user-guide-rhel.html#pitr >>> I am following the PTR on RHEL9 EPAS16. >>> I am able to do a backup(Full, diff and incr) and restore from a >>> full backup and restart of EPAS16 works fine. >>> >>> But when I do an incremental backup after doing the procedures >>> mentioned in the PTR section of the above reference link and try >>> restoring the EDB database from the INCR backup and then starting up the >>> EPAS16 the server always results in dead state >>> >>> My repo server is another machine. If I do a full restore on the >>> DB server ( sudo -u enterprisedb pgbackrest --stanza=Demo_Repo --delta >>> restore) it works and the server starts without any issue. >>> Restoring from Incremental backup tty output shows successful but edb >>> service start results in failure. >>> >>> Any help is much appreciated. >>> >>> Krishane. >>> >>> >>> >>> >>> STEPS followed: >>> >>> after dropping the table pg-primary *⇒* Drop the important table >>> ([section] >>> stopped the EDB server. >>> >>> $ sudo -u enterprisedb pgbackrest --stanza=Demo_Repo --delta >>> --set=20240719-122703F_20240724-094727I --target-timeline=current >>> --type=time "--target=2024-07-24 09:44:01.3255+05:30" >>> --target-action=promote restore >>> ..................................................... >>> >>> 2024-07-26 09:48:06.343 P00 INFO: restore command end: completed >>> successfully (1035ms) >>> >>> >>> But >>> >>> [root@rservice01 ~]# sudo systemctl start edb-as-16.service >>> [root@service01 ~]# sudo systemctl status edb-as-16.service >>> × edb-as-16.service - EDB Postgres Advanced Server 16 >>> Loaded: loaded (/etc/systemd/system/edb-as-16.service; disabled; >>> preset: disabled) >>> * Active: failed* (Result: exit-code) since Fri 2024-07-26 09:48:50 >>> IST; 8s ago >>> Duration: 242ms >>> Process: 41903 ExecStartPre=/usr/edb/as16/bin/edb-as-16-check-db-dir >>> ${PGDATA} (code=exited, status=0/SUCCESS) >>> Process: 41908 ExecStart=/usr/edb/as16/bin/edb-postgres -D ${PGDATA} >>> (code=exited, status=1/FAILURE) >>> Main PID: 41908 (code=exited, status=1/FAILURE) >>> CPU: 331ms >>> >>> Jul 26 09:48:48 service01 systemd[1]: Starting EDB Postgres Advanced >>> Server 16... >>> Jul 26 09:48:48 service01 edb-postgres[41908]: 2024-07-26 09:48:48 IST >>> LOG: redirecting log output to logging collector process >>> Jul 26 09:48:48 service01 edb-postgres[41908]: 2024-07-26 09:48:48 IST >>> HINT: Future log output will appear in directory "log". >>> Jul 26 09:48:49 service01 systemd[1]: Started EDB Postgres Advanced >>> Server 16. >>> Jul 26 09:48:50 service01 systemd[1]: edb-as-16.service: Main process >>> exited, code=exited, status=1/FAILURE >>> Jul 26 09:48:50 service01 systemd[1]: edb-as-16.service: Killing process >>> 41909 (edb-postgres) with signal SIGKILL. >>> Jul 26 09:48:50 service01 systemd[1]: edb-as-16.service: Failed with >>> result 'exit-code'. >>> [root@service01 ~]# >>> >>> why is it unable to perform a restore and recovery from an incr backup >>> ? >>> >>> >>> >>> >>> >>> >>> >>> On The Repo Server >>> [root@service02 ~]# sudo -u postgres pgbackrest --stanza=Demo_Repo info >>> stanza: Demo_Repo >>> status: ok >>> cipher: aes-256-cbc >>> >>> db (current) >>> wal archive min/max (16): >>> 000000020000000000000021/0000000B0000000000000041 >>> >>> full backup: 20240719-122703F >>> timestamp start/stop: 2024-07-19 12:27:03+05:30 / 2024-07-19 >>> 12:27:06+05:30 >>> wal start/stop: 00000002000000000000002A / >>> 00000002000000000000002A >>> database size: 61.7MB, database backup size: 61.7MB >>> repo1: backup size: 9.6MB >>> >>> incr backup: 20240719-122703F_20240719-123353I >>> timestamp start/stop: 2024-07-19 12:33:53+05:30 / 2024-07-19 >>> 12:33:56+05:30 >>> wal start/stop: 00000002000000000000002C / >>> 00000002000000000000002C >>> database size: 61.7MB, database backup size: 6.4MB >>> repo1: backup size: 6.2KB >>> backup reference list: 20240719-122703F >>> >>> diff backup: 20240719-122703F_20240719-123408D >>> timestamp start/stop: 2024-07-19 12:34:08+05:30 / 2024-07-19 >>> 12:34:10+05:30 >>> wal start/stop: 00000002000000000000002E / >>> 00000002000000000000002E >>> database size: 61.7MB, database backup size: 6.4MB >>> repo1: backup size: 6.4KB >>> backup reference list: 20240719-122703F >>> >>> incr backup: 20240719-122703F_20240723-110212I >>> timestamp start/stop: 2024-07-23 11:02:12+05:30 / 2024-07-23 >>> 11:02:15+05:30 >>> wal start/stop: 000000070000000000000038 / >>> 000000070000000000000038 >>> database size: 48MB, database backup size: 6.4MB >>> repo1: backup size: 9.8KB >>> backup reference list: 20240719-122703F, >>> 20240719-122703F_20240719-123408D >>> >>> incr backup: 20240719-122703F_20240723-141818I >>> timestamp start/stop: 2024-07-23 14:18:18+05:30 / 2024-07-23 >>> 14:18:22+05:30 >>> wal start/stop: 00000008000000000000003C / >>> 00000008000000000000003C >>> database size: 75.4MB, database backup size: 33.8MB >>> repo1: backup size: 4.7MB >>> backup reference list: 20240719-122703F, >>> 20240719-122703F_20240719-123408D, 20240719-122703F_20240723-110212I >>> >>> incr backup: 20240719-122703F_20240724-094727I >>> timestamp start/stop: 2024-07-24 09:47:27+05:30 / 2024-07-24 >>> 09:47:30+05:30 >>> wal start/stop: 00000009000000000000003D / >>> 00000009000000000000003D >>> database size: 62MB, database backup size: 11.5MB >>> repo1: backup size: 72KB >>> backup reference list: 20240719-122703F, >>> 20240719-122703F_20240719-123408D, 20240719-122703F_20240723-110212I, >>> 20240719-122703F_20240723-141818 >>> >>> >>> >>> >> >> -- >> Muhammad Ikram >> >> -- Muhammad Ikram
