Re: Base Backups to a remote location

-----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@${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.

> 3. is it save to empty the archive directory befor i do a backup
> with pg_start_backup()?

This operation is quite awkward and it should be managed not "by hand"
but using pg_archivecleanup. You can find further info:

http://www.postgresql.org/docs/9.0/static/pgarchivecleanup.html


As a usefull guide line, for backup issues you can take a look here:

http://www.postgresql.org/docs/9.1/static/continuous-archiving.html


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

iQIcBAEBCAAGBQJSobnIAAoJELtGpyN/y3jeFLYP/RNe4cKIcnitcmsLnMyIQ7e7
FbP4fzYF9sdR8ghXXMX+qlWqA9vTbS6aUPKNeCMSqo/h+c04qZVOAgvQ8Nug2bUV
RYTneoXXafsnvUiCB7j6td5Ou0SK6K1e3JCoV5Q4zxXsW0/C9b720Z0InVERpOxG
3czKiqjMLLnRg0hadmD2STSN9xqoCgdQbA4H/Mic+uZl5BPP/mvEBU14CUStVRB+
42V9C2tTYYNNktG8Oomq6ZRwrDCbsD8f/ajfy8yt7xHJRNWxtHZDB873Wo3Cjm86
PKbJ8fjrZ0qBdPSv9f3h5ATRqaGL8L/lBCbtDbaUn4kzYduV3bTauphgvEhsHjyJ
mp0becRNL5UqIu3cCvrTVBSpXSI57zTwj12cgmUQFmKLpjozO3WggtPU/EWvXdVk
jeH99rkTLEVnbb+Y6kZ8V71LKAazHIKfJgBoHwoBW24hdPyLaD1LVTByUgFsFBOr
C61eqLz2zpM7L5DM1dRkfu/3gTuSknFwwTWDi8Afa0A2vMbvplIyQtIWG2x1veBO
2SbY/UlJEBYqlSXV4tu88mpr4HVourVRGJaHegkLS6CLiVekcvwE/RFnJ7WTLQmj
ELJQqP6NbHZAGgs747egmawuHz73SveBwRPje7jENTLlYqCVe1lHASFAMLpbCD34
4fH7mK7mwUA6/g/L1QK1
=kGa2
-----END PGP SIGNATURE-----