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
[email protected] backups$ cd backup/
[email protected] 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
[email protected] backup$ cd pg_wal/
[email protected] 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 .