Re: Continuous archiving and restore questions

On Wed, Jan 29, 2014 at 5:26 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Wed, Jan 29, 2014 at 6:15 AM, Pedro Salgueiro <
> pedro(dot)salgueiro(at)cortex-intelligence(dot)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.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'
>>
>>
> 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
>