Re: Continuous archiving and restore questions

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 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