Re: Base Backups to a remote location

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256

Hi Boris,

Il 06/12/2013 15:43, Boris Bukowski ha scritto:
> 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.

Ok, now I understand what you were talking about. Continuous archiving
of WALs is needed in case of point-in-time recovery, starting from a
base backup. If you are just interested to a weekly backup, what you
are doing is enough.
Remember that backup contains a complete snapshot of your database at
the moment of the pg_start_backup() launch. If it takes time (as
explained in the previous mail), your snapshot is refered at the
moment of its start, not of its end! You should archive WALs during
this execution time to obtain a snapshot refered to the end of
pg_start_backup().

Giuseppe.
- --
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe(dot)broccolo(at)2ndQuadrant(dot)it | www.2ndQuadrant.it
-----BEGIN PGP SIGNATURE-----
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBCAAGBQJSoeyQAAoJELtGpyN/y3jem5oP/jVieHFKgQjmwWh+ATSFsFKz
uKdoItyWRA2aedJhuVEqcn+kDJ7+KTiXX7C1+Gu7YNdrVWdqNii8ZLoUdPx3G8SA
GiO0qZucoM7lHl8jrqqNIktrgMEj8I+szVVZ562W+hJmW+daR3+LFsxQgz+TuZFh
b31o45pwTHXMcJBOTM5MuRBEsYYyYByl5y0C+Lh5mWhK3dDy4FY7HQlRvzB/nT5q
MJIgwjBkKm0J47Omi7qF4JIl4HDQ4Bsbe6ecobk7d4OiA5YCIdpnqD3NzYI599Q0
PRc/k4uaOlWy2eYe0ZmmqMQD0gQxCh+2J0Iii+PjFCPISqv8w179VMPtZYoeUNeK
orTMm5v8XDW+4xg/sxzsUQ8qV5DIVQztHLaKljvVMyRJ8BWC6zaExvhxLw1U+OGD
h4Zmx4SO1SP8gfqCrY60yTspmb3xZifHTVKk2UVvG6xkOg4nwpAyzi/C4qSB71mK
K385FYl7SW5mLz3za7sp/Uv67spAyfE66iIy5ANxTcUKZbcDFFNjckcdir/t9bcJ
i7DHmoyLl1Qc9VfeylnWCimNAoKcO9vJ4u9mWMrEIiMD6+0nN2osx6EMpJsY1Cnv
yXyBxksyrTcSkNgMNFRRO2C+RHfpPc5XFUoy7SBa1T4WyYlZxCIty+OZ1maTb5tU
aD9OqcDtV4p3vy2Wtd6r
=Ixum
-----END PGP SIGNATURE-----