Thread: Base Backups to a remote location

Base Backups to a remote location

From

Boris Bukowski

Date:

06 December 2013, 10:05:59

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?
2. between these backups i do not save the archive logs, is this secure?
3. is it save to empty the archive directory befor i do a backup with
pg_start_backup()?

best Regards
Boris Bukowski

Re: Base Backups to a remote location

From

Giuseppe Broccolo

Date:

06 December 2013, 11:47:11

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

Re: Base Backups to a remote location

From

Boris Bukowski

Date:

06 December 2013, 14:43:09

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

Re: Base Backups to a remote location

From

Craig James

Date:

06 December 2013, 15:01:46

On Fri, Dec 6, 2013 at 6:43 AM, Boris Bukowski <bukowski@louis.info> wrote:

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

Why not try it? Start Postgres running on your backup machine, and see if it works. You should be able to scan through all of your tables with simple "select ..." statements and verify that they're all correct. Then stop Postgres.

One of the nice things about rsync is that it will erase any changes you make to your backup. After you test your backup, it will no longer match your production version. But the next time you run rsync, it will erase all of the changes to your backup and make it match the production version again.

Craig

best Regards
Boris Bukowski

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Base Backups to a remote location

From

Scott Ribe

Date:

06 December 2013, 15:10:47

On Dec 6, 2013, at 7:43 AM, Boris Bukowski <bukowski@louis.info> wrote:

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

Yes. Configuring WAL archiving is only necessary if you're maintaining a replica via WAL shipping.

Now, of course, you haven't shown the contents of excludes.txt, so we have to assume that you're not excluding anything
necessary;-) 


--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

Re: Base Backups to a remote location

From

Giuseppe Broccolo

Date:

06 December 2013, 15:23:50

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