4

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)

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.