Issue when trying to create a full backup after restore the database to point in time

[TeodorChakalov]

After restoring the database, I want to do a backup again but I receive this error:

cat pg_rman.ini ARCLOG_PATH='/opt/pdbelsng/backups/backup/pg_wal' SRVLOG_PATH='/opt/pdbelsng/data/pg_log'

BACKUP_MODE = F COMPRESS_DATA = YES KEEP_ARCLOG_FILES = 10 KEEP_DATA_GENERATIONS = 3 KEEP_SRVLOG_FILES = 10

postgres=# show archive_command; archive_command

gzip < %p > /opt/pdbelsng/backups/backup/pg_wal/%f.gz (1 row)

postgres=# show log_directory; log_directory

pg_log (1 row)

shell>: pg_rman backup INFO: copying database files INFO: copying archived WAL files ERROR: could not open destination file "/opt/pdbelsng/backups/20231010/141025/arclog/archive_status/00000002.history.done": No such file or directory

Please suggest how to fix?

[TeodorChakalov]

Please give me any update?

[zwyan0]

@TeodorChakalov

Hi, I have tried it may not be the same procedure, but As follows.It seems no problems. If possible, could you please let me know the procedure and the version of PostgreSQL.

[testuser-pg15@rocky8 15]$ pg_rman show
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status
=====================================================================
2023-10-12 09:29:59  2023-10-12 09:30:02  FULL    11MB     1  OK
[testuser-pg15@rocky8 15]$ pg_rman backup
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
INFO: start deleting old archived WAL files from ARCLOG_PATH (keep files = 10)
INFO: start deleting old backup (keep generations = 3)
INFO: does not include the backup just taken
INFO: backup "2023-10-12 09:29:59" should be kept
DETAIL: This is the 1st latest full backup.

[testuser-pg15@rocky8 15]$ pg_rman validate
INFO: validate: "2023-10-12 09:31:06" backup and archive log files by CRC
INFO: backup "2023-10-12 09:31:06" is valid

[testuser-pg15@rocky8 15]$ pg_rman show
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status
=====================================================================
2023-10-12 09:31:06  2023-10-12 09:31:08  FULL  5983kB     1  OK
2023-10-12 09:29:59  2023-10-12 09:30:02  FULL    11MB     1  OK

[testuser-pg15@rocky8 15]$ cat pg_rman/pg_rman.ini
ARCLOG_PATH='/home/testuser/pg/15/wal_archive'
SRVLOG_PATH='/home/testuser/pg/15/data/log'

BACKUP_MODE = F
COMPRESS_DATA = YES
KEEP_ARCLOG_FILES = 10
KEEP_DATA_GENERATIONS = 3
KEEP_SRVLOG_FILES = 10

[testuser-pg15@rocky8 15]$ tail -n 3 data/postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'gzip < %p > /home/testuser/pg/15/wal_archive/%f.gz'

[TeodorChakalov]

postgres=# show archive_command; archive_command gzip < %p > /opt/pdbelsng/backups/backup/pg_wal/%f.gz (1 row)

postgres=# show log_directory; log_directory pg_log (1 row)

echo $BACKUP_PATH /opt/pdbelsng/backups

pg_rman init INFO: ARCLOG_PATH is set to '/opt/pdbelsng/backups/backup/pg_wal' INFO: SRVLOG_PATH is set to '/opt/pdbelsng/data/pg_log'

cat backups/pg_rman.ini ARCLOG_PATH='/opt/pdbelsng/backups/backup/pg_wal' SRVLOG_PATH='/opt/pdbelsng/data/pg_log'

BACKUP_MODE = F COMPRESS_DATA = YES KEEP_ARCLOG_FILES = 10 KEEP_DATA_GENERATIONS = 3 KEEP_SRVLOG_FILES = 10

pg_rman backup INFO: copying database files INFO: copying archived WAL files INFO: backup complete INFO: Please execute 'pg_rman validate' to verify the files are correctly copied. INFO: start deleting old archived WAL files from ARCLOG_PATH (keep files = 10) INFO: start deleting old backup (keep generations = 3) INFO: does not include the backup just taken

pg_rman validate INFO: validate: "2023-10-12 06:43:20" backup and archive log files by CRC INFO: backup "2023-10-12 06:43:20" is valid

pg_rman show 2023-10-12 06:43:20 2023-10-12 06:43:24 FULL 4212kB 3 OK

pg_rman show 2023-10-12 06:43:20 BACKUP_MODE=FULL FULL_BACKUP_ON_ERROR=false WITH_SERVERLOG=false COMPRESS_DATA=true TIMELINEID=3 START_LSN=0/74000028 STOP_LSN=0/74000100 START_TIME='2023-10-12 06:43:20' END_TIME='2023-10-12 06:43:24' RECOVERY_XID=876 RECOVERY_TIME='2023-10-12 06:43:23' TOTAL_DATA_BYTES=34974407 READ_DATA_BYTES=34974239 READ_ARCLOG_BYTES=33147 WRITE_BYTES=4212142 BLOCK_SIZE=8192 XLOG_BLOCK_SIZE=8192 STATUS=OK

sv force-stop /opt/pdbelsng/services/pdbelsng_adm kill: run: /opt/pdbelsng/services/pdbelsng_adm: (pid 1006034) 147835s, want down, got TERM

pg_rman restore '2023-10-12 06:43:23' INFO: the recovery target timeline ID is not given INFO: use timeline ID of current database cluster as recovery target: 3 INFO: calculating timeline branches to be used to recovery target point INFO: searching latest full backup which can be used as restore start point INFO: found the full backup can be used as base in recovery: "2023-10-12 06:43:20" INFO: copying online WAL files and server log files INFO: clearing restore destination INFO: validate: "2023-10-12 06:43:20" backup and archive log files by SIZE INFO: backup "2023-10-12 06:43:20" is valid INFO: restoring database files from the full mode backup "2023-10-12 06:43:20" INFO: searching incremental backup to be restored INFO: searching backup which contained archived WAL files to be restored INFO: backup "2023-10-12 06:43:20" is valid INFO: restoring WAL files from backup "2023-10-12 06:43:20" INFO: restoring online WAL files and server log files INFO: create pg_rman_recovery.conf for recovery-related parameters. INFO: remove an 'include' directive added by pg_rman in postgresql.conf if exists INFO: append an 'include' directive in postgresql.conf for pg_rman_recovery.conf INFO: generating recovery.signal INFO: removing standby.signal if exists to restore as primary INFO: restore complete HINT: Recovery will start automatically when the PostgreSQL server is started. After the recovery is done, we recommend to remove recovery-related parameters configured by pg_rman.

sv start /opt/pdbelsng/services/pdbelsng_adm ok: run: /opt/pdbelsng/services/pdbelsng_adm: (pid 2385927) 0s

pdbelsng$ pg_rman backup INFO: copying database files INFO: copying archived WAL files ERROR: could not open destination file "/opt/pdbelsng/backups/20231012/064642/arclog/archive_status/00000002.history.done": No such file or directory

This is the procedure. The last step cannot finish. Try to do a backup after restoring. Maybe the timelineID is different, I don't know. Could you please check the error and give me any update?

PostgreSQL version: PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.3.1 20220421 (Red Hat 11.3.1-2), 64-bit

[mikecaat]

Can you try to use the different path for archive_command and pg_rman?/opt/pdbelsng/backups seems to be used for two purposes.

For example, try to change archive_command to gzip < %p > /tmp/backups/backup/pg_wal/%f.gz for test.

[TeodorChakalov]

Thank you. Now is working.

Could you please explain then why we have backup folder and in this folder we have pg_wal and srvlog folders? When I try to use pg_wal folder from archive_command as you see I receive an error?

drwx------. 5 pdbelsng_adm pdbelsng_adm 4096 Oct 12 07:21 . drwxr-xr-x. 17 pdbelsng_adm pdbelsng_adm 4096 Oct 12 07:20 .. drwx------. 4 pdbelsng_adm pdbelsng_adm 4096 Oct 12 07:22 20231012 drwx------. 4 pdbelsng_adm pdbelsng_adm 4096 Oct 12 07:20 backup -rw-------. 1 pdbelsng_adm pdbelsng_adm 183 Oct 12 07:20 pg_rman.ini -rw-------. 1 pdbelsng_adm pdbelsng_adm 40 Oct 12 07:20 system_identifier drwx------. 2 pdbelsng_adm pdbelsng_adm 4096 Oct 12 07:20 timeline_history pdbelsng_adm@kde0119-bos-t01.0119.de.kaufland backups$ cd backup/ pdbelsng_adm@kde0119-bos-t01.0119.de.kaufland backup$ ls -la total 16 drwx------. 4 pdbelsng_adm pdbelsng_adm 4096 Oct 12 07:20 . drwx------. 5 pdbelsng_adm pdbelsng_adm 4096 Oct 12 07:21 .. drwx------. 2 pdbelsng_adm pdbelsng_adm 4096 Oct 12 07:22 pg_wal drwx------. 2 pdbelsng_adm pdbelsng_adm 4096 Oct 12 07:21 srvlog pdbelsng_adm@kde0119-bos-t01.0119.de.kaufland backup$ cd pg_wal/ pdbelsng_adm@kde0119-bos-t01.0119.de.kaufland pg_wal$ ls -la

[mikecaat]

Could you please explain then why we have backup folder and in this folder we have pg_wal and srvlog folders?

The reason is that pg_rman takes backup of the wal and server logs.

When I try to use pg_wal folder from archive_command as you see I receive an error?

Sorry, I don't have enough time to investigate the detail. But I assume that the pg_rman deleted the /opt/pdbelsng/backups/20231012/064642/arclog/archive_status/ when to restore because the archive directory is not useless.

Anyway, you need to prepare empty directory(BACKUP_PATH) for only pg_rman .