Thread: Continuous archiving and restore questions
Hi,
In the past couple of days I have been trying Continuous Archiving and Point-in-Time Recovery (PITR) and I have some doubts.
I successfully configured postgresql to perform the archive of the wal files, using the following properties in postgresql.conf
archive_mode = on
wal_level = archive
archive_command = 'cp %p /opt/postgres-wal-backups/wal-files/%f'
max_wal_senders = 3
To perform the base backup, I am using the pg_basebackup tool:
pg_basebackup --format tar --xlog -D - | gzip > ${BASE_BACKUP_FOLDER}/base_backup.tar.gz
After making a base backup, I made some changes on the database, including creating new tables and adding data to them. Then I moved the data folder to a safe place, restored the base backup, created the recovery.conf file, copied the WAL files that were unarchived back to the restored data folder, and restarted postgresql.
I used the following recovery.conf file:
restore_command = 'cp /opt/postgres-wal-backups/wal-files/%f %p'
archive_cleanup_command = 'pg_archivecleanup /opt/postgres-wal-backups/wal-files %r'
The restore procedure worked like a charm, and all data was recovered.
Then I created some more tables and added more data. Then made the same restore procedure as before, using the same base backup. Apparently the restore was successful and without errors, but the newly created data was not restored, only the one which was created before the first restore.
Everything that was made after the first restore was lost.
Then I tried to make a fresh base backup, make some changes on the database, and then, issue the restore procedure just as before, but using the new base backup. This time, the changes made after the base backup were restored successfully.
It seems that after a restore is made, I need to make a fresh base backup in order to be able to make future restores. Is this correct, or am I doing something wrong?
Thank you,
Pedro Salgueiro
Found the problem.
When a restore is made, a new timeline is created.
The problem is that when a restore is made, the default behavior is to restore the timeline used while creating the base backup, thus ignoring the new timeline together with the changes made after the restore.
The solution to this problem is to include the following line in the recovery.conf to always restore the last available timeline:
recovery_target_timeline = 'latest'
Best regards,
Pedro
On Wed, Jan 29, 2014 at 2:15 PM, Pedro Salgueiro <pedro.salgueiro@cortex-intelligence.com> wrote:
Hi,In the past couple of days I have been trying Continuous Archiving and Point-in-Time Recovery (PITR) and I have some doubts.I successfully configured postgresql to perform the archive of the wal files, using the following properties in postgresql.confarchive_mode = on
wal_level = archive
archive_command = 'cp %p /opt/postgres-wal-backups/wal-files/%f'
max_wal_senders = 3To perform the base backup, I am using the pg_basebackup tool:pg_basebackup --format tar --xlog -D - | gzip > ${BASE_BACKUP_FOLDER}/base_backup.tar.gzAfter making a base backup, I made some changes on the database, including creating new tables and adding data to them. Then I moved the data folder to a safe place, restored the base backup, created the recovery.conf file, copied the WAL files that were unarchived back to the restored data folder, and restarted postgresql.I used the following recovery.conf file:restore_command = 'cp /opt/postgres-wal-backups/wal-files/%f %p'
archive_cleanup_command = 'pg_archivecleanup /opt/postgres-wal-backups/wal-files %r'The restore procedure worked like a charm, and all data was recovered.Then I created some more tables and added more data. Then made the same restore procedure as before, using the same base backup. Apparently the restore was successful and without errors, but the newly created data was not restored, only the one which was created before the first restore.Everything that was made after the first restore was lost.Then I tried to make a fresh base backup, make some changes on the database, and then, issue the restore procedure just as before, but using the new base backup. This time, the changes made after the base backup were restored successfully.It seems that after a restore is made, I need to make a fresh base backup in order to be able to make future restores. Is this correct, or am I doing something wrong?Thank you,Pedro Salgueiro
On Wed, Jan 29, 2014 at 6:15 AM, Pedro Salgueiro <pedro.salgueiro@cortex-intelligence.com> wrote:
Hi,In the past couple of days I have been trying Continuous Archiving and Point-in-Time Recovery (PITR) and I have some doubts.I successfully configured postgresql to perform the archive of the wal files, using the following properties in postgresql.confarchive_mode = on
wal_level = archive
archive_command = 'cp %p /opt/postgres-wal-backups/wal-files/%f'
max_wal_senders = 3To perform the base backup, I am using the pg_basebackup tool:pg_basebackup --format tar --xlog -D - | gzip > ${BASE_BACKUP_FOLDER}/base_backup.tar.gzAfter making a base backup, I made some changes on the database, including creating new tables and adding data to them. Then I moved the data folder to a safe place, restored the base backup, created the recovery.conf file, copied the WAL files that were unarchived back to the restored data folder, and restarted postgresql.I used the following recovery.conf file:restore_command = 'cp /opt/postgres-wal-backups/wal-files/%f %p'
archive_cleanup_command = 'pg_archivecleanup /opt/postgres-wal-backups/wal-files %r'
Why are you cleaning up the archive?
The restore procedure worked like a charm, and all data was recovered.Then I created some more tables and added more data. Then made the same restore procedure as before, using the same base backup. Apparently the restore was successful and without errors, but the newly created data was not restored, only the one which was created before the first restore.
If your previous use of archive_cleanup_command deleted files that the new recover would have have needed, then the recovery would have to end at the first missing file.
Cheers,
Jeff
On Wed, Jan 29, 2014 at 5:26 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
On Wed, Jan 29, 2014 at 6:15 AM, Pedro Salgueiro <pedro.salgueiro@cortex-intelligence.com> wrote:Hi,In the past couple of days I have been trying Continuous Archiving and Point-in-Time Recovery (PITR) and I have some doubts.I successfully configured postgresql to perform the archive of the wal files, using the following properties in postgresql.confarchive_mode = on
wal_level = archive
archive_command = 'cp %p /opt/postgres-wal-backups/wal-files/%f'
max_wal_senders = 3To perform the base backup, I am using the pg_basebackup tool:pg_basebackup --format tar --xlog -D - | gzip > ${BASE_BACKUP_FOLDER}/base_backup.tar.gzAfter making a base backup, I made some changes on the database, including creating new tables and adding data to them. Then I moved the data folder to a safe place, restored the base backup, created the recovery.conf file, copied the WAL files that were unarchived back to the restored data folder, and restarted postgresql.I used the following recovery.conf file:restore_command = 'cp /opt/postgres-wal-backups/wal-files/%f %p'
archive_cleanup_command = 'pg_archivecleanup /opt/postgres-wal-backups/wal-files %r'Why are you cleaning up the archive?
The idea was to remove WAL files that are no longer needed, WAL files that are someway included in the base-backup. Any way, that was not the problem, as I tested the same procedure without the archive_cleanup_command.
Pedro
The restore procedure worked like a charm, and all data was recovered.Then I created some more tables and added more data. Then made the same restore procedure as before, using the same base backup. Apparently the restore was successful and without errors, but the newly created data was not restored, only the one which was created before the first restore.If your previous use of archive_cleanup_command deleted files that the new recover would have have needed, then the recovery would have to end at the first missing file.Cheers,Jeff