PgBarman – Using for PostgreSQL 9.3 Backup

backuppostgresqlpostgresql-9.3

I'm setting up a backup strategy for a PostgreSQL 9.3 db. My job includes:

take db backups every day/week/month
use a Synology NAS as remote backup device

I'm thinking of using Postgres [base_backup + WAL archiving] + [rsync to NAS] for accomplishing the task. The db is a low activity one so I should force wal archiving via pg_switch_xlog or similar. One aspect of this is I would find myself with partially empty WAL files and I would like to reduce their size (I've been suggested to use pg_clearxlogtail).

Searching the Internet I found PgBarman which looks like a good aid in accomplishing the task but the documentation on the site assumes:

I have Postgres installed on the backup machine
I install PgBarman on the backup machine

I don't think I can install either of the two on the NAS (am I wrong?).

So I'm rather planning to:

install PgBarman on the actual Postgres server machine
use it to backup the db locally and manage eventual recovery
archive the base backup and WAL files to the remote NAS

The main goal of the backup is not failure recovery as the Postgres server is hosted by a cloud hosting provider which ensures availability and no data loss, but rather for human error recovery.

Does this strategy make sense? Can anyone point me to a good resource to find out how to implement it? Would there be a better solution?
Any suggestions on implementing it or adopting alternative approaches would be greatly appreciated.

(I've already been suggested streaming replication many times but I don't think I need it and moreover I wouldn't know how to set that up in a NAS)

Best Answer

You are looking for point in time recovery (for them awkward moments when you forget the where clause "delete from customers")

Barman sounds like a good way to go. Barman uses built in Postgres functionality but makes managing the base backups and WAL logs a lot easier (backup and WAL log cataloger, Point in time recovery manager, manage many servers from 1 location)

Your suggested strategy sounds fine, you can set the backup location in the barman conf to point at your NAS, you can then set the archive_command in postgresql.conf to rsync WAL's to your NAS as they are rotated.

There is documentation on their website http://docs.pgbarman.org/#introduction

Related Solutions

Postgresql – WAL archiving from a standby backup strategy Postgresql 9.3

Postgresql provides a pg_receivexlog utility that can be used instead of archive_command to do wal log archiving.

From the Documentation:

pg_receivexlog is used to stream transaction log from a running PostgreSQL cluster. The transaction log is streamed using the streaming replication protocol, and is written to a local directory of files. This directory can be used as the archive location for doing a restore using point-in-time recovery

PostgreSQL Backup – Decrease WAL File Size

If I recall correctly, PostgreSQL now zeroes the end of the WAL segment after an archive timeout, before archiving the file. (Update): This is true for 9.4; for prior versions you still need to use pg_clearxlogtail utility to zero the end of any WAL segment as part of your archive script before you gzip your archive files.

In 9.4 and above you just need to gzip the file, with no need for pg_clearxlogtail.

The change was made commit 9a20a9b, but it's only in tag REL9_4_BETA1, so the change will first appear in 9.4:

commit 9a20a9b
Author: Heikki Linnakangas <heikki.linnakangas@iki.fi>
Date:   Mon Jul 8 11:23:56 2013 +0300

    Improve scalability of WAL insertions.

    This patch replaces WALInsertLock with a number of WAL insertion slots,
    allowing multiple backends to insert WAL records to the WAL buffers
    concurrently. This is particularly useful for parallel loading large amounts
    of data on a system with many CPUs.

    This has one user-visible change: switching to a new WAL segment with
    pg_switch_xlog() now fills the remaining unused portion of the segment with
    zeros. This potentially adds some overhead, but it has been a very common
    practice by DBA's to clear the "tail" of the segment with an external
    pg_clearxlogtail utility anyway, to make the WAL files compress better.
    With this patch, it's no longer necessary to do that.

BTW, you should really be using an archive timeout, rather than manually switching xlogs. Consider having a streaming replica for additional protection.

Update: As Jeff noted, pg_clearxlogtail hasn't been updated for 9.3. The incompatibility is very simple though; the definition of XLogRecPtr changed from

typedef struct XLogRecPtr
{
        uint32          xlogid;                 /* log file #, 0 based */
        uint32          xrecoff;                /* byte offset of location in log file */
} XLogRecPtr;

typedef uint64 XLogRecPtr;

when support for platforms with compilers that don't support 64-bit long integers was removed from PostgreSQL in 9.3. So all we need to do is add a macro or function that tests equality in a way that works for both.

The following patch adds support for 9.3, but is completely untested so use entirely at your own risk. If it eats your data then sets fire to your house, well, that's how things are. Patch here, as an attachment to the linked pgfoundry issue.

Best Answer

Related Solutions

Postgresql – WAL archiving from a standby backup strategy Postgresql 9.3

PostgreSQL Backup – Decrease WAL File Size

Related Question