0

I found this article http://www.howtogeek.com/135533/how-to-use-rsync-to-backup-your-data-on-linux/ but it looks outdated and I felt I may get a better answer at AsuUbuntu :)

I have a server running ubuntu 12.04 and my laptop has 12.10, now I want to daily take backup of postgres database on the server, how can I do it ?

1 Answer 1

1

First, don't just rsync a database, or anything else that's getting actively written to. You're likely to get an unusable copy. This is true for most databases, mail spools ... practically any "live" data. This is why you test your backups!

PostgreSQL has an extensive chapter on backup and restore, which would be a good starting point.

For basic use a nightly pg_dump backups is generally sufficient. If you need more, look into running hot standbys, WAL archiving with PgBarman, etc.

You actually can rsync a PostgreSQL database so long as:

  • You run pg_start_backup() first
  • There's nothing writing to the destination other than the rsync
  • You run pg_stop_backup() afterwards *and copy the required archive files from pg_xlog.

Because of the need to copy archives, it's usually best combined with WAL archiving.

Start with basic pg_dump dumps:

  • pg_dumpall --globals-only; plus
  • pg_dump -Fc for each database

If you need more, I've given you some info on where to look to find what fits your needs best.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .