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.