From: Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
To: pgsql-admin@postgresql.org
Subject: Re: Base Backups to a remote location
Date: Fri, 06 Dec 2013 12:49:33 +0100
Message-ID: <52A1B9CD.2060505@2ndquadrant.it> (raw)
In-Reply-To: <MP75BCF26F00D12852A1A181.6040300@louis.info>
References: <MP75BCF26F00D12852A1A181.6040300@louis.info>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
-----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-----
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: giuseppe.broccolo@2ndquadrant.it
Subject: Re: Base Backups to a remote location
In-Reply-To: <52A1B9CD.2060505@2ndquadrant.it>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox