Restore of deleted postgres database service instance
Article ID: 387174
Updated On: 01-31-2025
Products
Issue/Introduction
This issue covers the steps to follow if a postgres database is deleted and the customer has updated backups created for the service instance/database.
Cause
A postgres database is deleted for any reason while it is still necessary and needs to be restored.
Resolution
There are 2 scenarios.
Scenario 1: The database was deleted, but the service instance is still available. The ADBR process can be followed to restore in this case.
There are 2 versions of this process:
- Backup and Restore with Bosh Backup and Restore (Legacy): https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-for-postgres-on-cloud-foundry/1-2/postgres-1-2/backup-restore-bbr.html
- Backup and Restore with ADBR plugin: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-for-postgres-on-cloud-foundry/1-2/postgres-1-2/backup-restore-adbr.html
Scenario 2: The service instance was completely deleted and is not available.
In this case the Stream incremental backup allows to restore the backup of the original Service Instance to a newly created Service Instance to host the database. This process has a version to restore NON-HA and TLS NON-HA databases and HA databases. Both can be found in the following link.
- Stream Incremental Backup and Restore: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-for-postgres-on-cloud-foundry/1-2/postgres-1-2/backup-restore-incremental.html
It is important to make sure that the new service instance is configured correctly. If any of the configurations are configured to point to the old service instance GUID you can run into errors like the following.
ERROR: [103]: unable to find a valid repository:
repo1: [ArchiveMismatchError] PostgreSQL version 15, system-id xxxxxxxxxxxxxxxxxxx do not match repo1 stanza version 15, system-id xxxxxxxxxxxxxxxxxxx
HINT: are you archiving to the correct stanza?
2025-01-30 20:17:32.803 P00 INFO: archive-push command end: aborted with exception [103]
2025-01-30 20:17:32.804 GMT: LOG: archive command failed with exit code 103
2025-01-30 20:17:32.804 GMT: DETAIL: The failed archive command was: /var/vcap/jobs/postgres/bin/pgbackrest_archive.sh pg_wal/00000002.history
2025-01-30 20:17:35.814 P00 INFO: archive-push command begin 2.51: [pg_wal/00000002.history] --archive-async --config=/var/vcap/store/pgbackrest/pgbackrest.conf --exec-id=xxxxx-xxxxxxxx --log-level-console=info --log-level-file=debug --log-path=/var/vcap/sys/log/pgbackrest --pg1-path=/var/vcap/store/postgres/postgres-15.1 --process-max=2 --repo1-path=/taspre-phx-postgres --repo1-s3-bucket=backups --repo1-s3-endpoint=<repository_url>:9021 --repo1-s3-key=<redacted> --repo1-s3-key-secret=<redacted> --repo1-s3-region=<region_id> --repo1-s3-uri-style=path --repo1-type=s3 --spool-path=/var/vcap/store/pgbackrest/pgbackrest_spool_dir --stanza=service-instance_xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
ERROR: [103]: unable to find a valid repository:
repo1: [ArchiveMismatchError] PostgreSQL version 15, system-id xxxxxxxxxxxxxxxxxxx do not match repo1 stanza version 15, system-id xxxxxxxxxxxxxxxxxxx
HINT: are you archiving to the correct stanza?
2025-01-30 20:17:36.122 P00 INFO: archive-push command end: aborted with exception [103]
2025-01-30 20:17:36.123 GMT: LOG: archive command failed with exit code 103
2025-01-30 20:17:36.123 GMT: DETAIL: The failed archive command was: /var/vcap/jobs/postgres/bin/pgbackrest_archive.sh pg_wal/00000002.history
2025-01-30 20:17:36.123 GMT: WARNING: archiving write-ahead log file "00000002.history" failed too many times, will try again laterIf you find error similar to this one, please review the configurations to ensure it is pointing at the newly created service instance.
Feedback
