2

I would like to request if there is any software for Windows which can schedule PostgreSQL backup at a certain time.

We are maintaining multiple servers on different locations, so I want to know if there is any easy way around scheduling PostgreSQL database backup remotely, because there are certain peak times when the database is getting load, and I want to schedule it at nights so that no hassle can occur.

Asclepius
  • 57,944
  • 17
  • 167
  • 143
Shakoor Alam
  • 187
  • 1
  • 5
  • 20
  • If any Windows users are keen to fund it, or volunteer to do the work to make it happen, PgBarman shouldn't be too hard to test and package for Windows. – Craig Ringer Jan 07 '14 at 23:04

4 Answers4

3

Tools like Areca Backup, Bacula (if you have a Linux server for backup storage), or the half-million commercial backup "solutions" can pretty much all be used to backup PostgreSQL.

Logical backups

Let an existing backup management product take care of retention, rotation, and scheduling. What you should be doing is providing a pre-backup script to plug into your backup product. This script will run pg_dumpall --globals-only, and run pg_dump -Fc ... for each DB, to save the DB contents to the dump directory. That's the usual process as shown in the regular backup/restore docs. Then back that up. In the post-backup script, delete the dump output, since it'll now be archived in the backup management tool's archives.

I've never seen a backup management product that doesn't have pre- and post- hooks.

Physical backups

You can use a similar strategy for physical backups, not just the logical backups above. The general process is documented, again, in the regular backup/restore docs. Integrating it into a backup manager is complicated by the need to copy the WAL generated after pg_stop_backup(). Something like this:

  • Make certain that WAL archiving is enabled and that the archive directory is readable from the host running the backups.
  • In the pre-backup hook, connect to the DB and run pg_start_backup().
  • Let the backup product, which must support Volume Shadow Copy Service copying, copy the data directory including pg_xlog, all tablespaces, etc.
  • In the post-backup hook, run pg_stop_backup()
  • Define another backup fileset that runs after the main DB backup. This fileset must back up all archived WAL up to the point in the backup label. Failure to do this will result in a backup you cannot restore.

If physical backups seem too complicated, stick to using logical backup dumps (pg_dump, pg_dumpall, etc) and use streaming replication / PITR + pg_basebackup for your physical backups.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
1

I made postgres backups work on Windows Server 2008 R2 a little while back in my environment. There is a related question here .

I haven't seen a piece of software for Windows that does PG backups. I wrote a bunch of DOS batch files and combined them with Windows Task Scheduler. It was a pretty involved endeavor.

Unless you can shut down your PG instance, backing up Postgres is not as simple as taking a snapshot of the "data" directory. I suggest that the way to get started is to throughly read and understand Chapter 11 of PostgreSQL 9 Administration Cookbook (Simon Riggs and Hannu Krosing).

Community
  • 1
  • 1
sevzas
  • 701
  • 2
  • 5
  • 13
  • Actually, you can use any tool that uses the Volume Shadow Copy Snapshot Service to copy a directory to back Pg up, so long as you `pg_start_backup()` first and `pg_stop_backup()` afterwards, and so long as you have WAL archiving enabled. Most people will want *logical* backups, though. – Craig Ringer Jan 07 '14 at 23:05
  • I just came across HandyBackup which claims to back up Postgres although I haven't used it and cannot recommend it. http://www.handybackup.net/postgresql-backup.shtml – sevzas Jan 30 '14 at 20:21
  • Wow, their site is misleading, they're trying to make it sound like you need their product to do hot backups, and like dumps are unsafe. Not impressed. – Craig Ringer Jan 30 '14 at 23:53
1

It seems like there is a pretty good page on wiki.postgresql.org documenting what you need to do here. If this documentation is insufficient for your needs, I suggest you enhance your original posting.

David S
  • 12,967
  • 12
  • 55
  • 93
0

I suggest you check out http://www.navicat.com/products/navicat-premium. I don't work for them but it is an amazing cross platform tool.

It has saved me a couple of times and what's more? It helps me out with database ER diagrams.

Asclepius
  • 57,944
  • 17
  • 167
  • 143
iChux
  • 2,266
  • 22
  • 37