Home / server / Questions / 525846
Accepted
NinjaCat
Asked: 2013-07-24 23:05:31 +0800 CST

Backing up remote Postgres database

  • 772

I have a Windows 2008 Server machine with a postgres database running on it. I'd like to back it up daily and have it sent to another machine (running Ubuntu or Windows). What's the best way to set this up?

windows-server-2008

1 Answers

  1. Best Answer

    Per the postgresql documentation, backup options include:

    • pg_dump, which operates entirely over a PostgreSQL connection so it'll back up from anywhere you can connect to the server from. This is the simple and obvious option that you should prefer unless you know you need something else. Use pg_dumpall --globals-only followed by a pg_dump -Fc of each database you want to back up.

    • pg_basebackup to take a backup over the streaming replication protocol. With 9.2 and above, the --xlog-method=stream option allows you to do the entire backup over streaming replication with no WAL archiving setup required on the origin server.

    • WAL archiving with a base backup for point-in-time recovery.

    • pg_start_backup(), rsync, and pg_stop_backup() with WAL archiving set up.

    The first two are wholly remote-initiated backup options.

    The latter two require that the server "push" WAL archives to a remote host via the configured archive_command, but it still gives you remote backup storage.

    • 0