0

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?

NinjaCat
  • 576
  • 1
  • 9
  • 21
  • http://www.postgresql.org/docs/9.3/static/backup.html – user9517 Jul 24 '13 at 07:10
  • @Iain better to link to `/docs/current/static/` so links don't get stale. Otherwise, add a couple of words and post as an answer; a "no research done first" question can be quite reasonably answered with a "here's the manual" answer. – Craig Ringer Jul 24 '13 at 08:45
  • @CraigRinger: Cheers, didn't know, all I did was google postgres backup. The postgresql docs are though good in that you can just click the relevant release. I couldn't possibly leave a link only answer, I've downvoted and deleted far too many to get away with that. – user9517 Jul 24 '13 at 10:49
  • I am familiar with those links, and on how to do a backup. I was looking for how to do a remote backup, or if there's a better way to do this. – NinjaCat Jul 24 '13 at 11:29
  • @NinjaCat What do you mean by a "remote backup"? – voretaq7 Jul 24 '13 at 15:58

1 Answers1

0

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.

Craig Ringer
  • 11,083
  • 9
  • 40
  • 61