Re: Base Backups to a remote location

On 06.12.2013 12:49, Giuseppe Broccolo wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA256
>
> Il 06/12/2013 11:05, Boris Bukowski ha scritto:
>> Hi,
>>
>> i use the following script to backup servers every day to a remote
>> location.
>>
>> ---snip--- REMOTE=xyz123.louis.info DATE=`date +%Y%m%d%H%M%S`
>>
>> ssh xyz123.louis.info "echo \"checkpoint; SELECT
>> pg_start_backup('${DATE}');\" |psql"
>>
>> rsync -avxz --numeric-ids
>> --exclude-from=/backup1/config/excludes.txt --delete-excluded
>> --inplace --delete root(at)${REMOTE}:/
>> /mnt/backup1/remote/xyz123/full
>>
>> ssh xyz123.louis.info "echo \"SELECT pg_stop_backup();\" |psql"
>>
>> ---snip---
>>
>> i have some questions about this: 1. will this always work?
>
> In principle, this could be enough. The important thing is to be sure
> that WAL archiving is enabled and working before performing the
> backup, configuring the 'archive_command' parameter. In this way,
> archiving of these files happens automatically since you have already
> configured archive_command and pg_stop_backup() does not return until
> the last segment has been archived.
> I just want to remark that pg_start_backup() already performs a
> checkpoint: this is the reason it sometimes takes a significant period
> of time. So you can omit it.
>
> Just to make the script more readable, you can change the command
>
> echo "SELECT pg_start_backup('${DATE}');" |psql
>
> in
>
> psql -c "SELECT pg_start_backup('${DATE}');"
>
>> 2. between these backups i do not save the archive logs, is this
>> secure?
>
> What are you meaning? As I said before, you have to be sure that
> 'archive_command' is opportunely set, considering also a compression.
These are complete VM's with Webserver and Postgres. Once per day I
take a complete snapshot of everything and feed it in our backup system.

Is this enough to get my Database always up again or do i miss
something? Continuous Archiving is not my intention.

best Regards
Boris Bukowski