3

I want to set up Postgres to do incremental backups. I'm using PostgreSQL 9.4 on Windows with a database that weighs about 1GB and has a growing 10000 records each day.

I understood that the steps necessary to configure the backup are:

  1. Configure "write_ahead_logs" to "on" and "wal_level" in "archives/hot_standby"
  2. Make a "start_basebackup" and copy the data folder
  3. After copying the files do a "stop_basebackup"
  4. Finally copy to another location the WAL files generated

But I have doubts about the Postgres documentation:

  1. How often it is recommended or advisable to make a new base_backup and, if necessary?
  2. If you can have a slave server configured with hot_standby and have the incremental backup at the same time?

Also I wonder if there is a PITR manager for Windows, as the Barman for linux.

Thanks

2

To address your first question, if you take a single basebackup of your database and just keep archiving away the WAL segments, the time to restore that database will grow longer and longer as the amount of WAL segments increases. That is not really an ideal situation to be in if you have downtime and need to bring a working database back up.

Most people figure out how much time they can spend restoring, and what recovery requirements they need to meet, and keep multiple basebackups and extra WAL segments as their storage budget and their recovery requirements allow.

Additionally, with very, very, very, very large WAL archives, sometimes things might happen to individual segments (which is yet another great reason to always test your backups!) and if a WAL segment fails to restore, no other segments following that failed segment will restore. At that point, you're out of luck.

One general rule I follow is to take a weekly basebackup, and keep 3 weeks of WAL segments and 2 basebackups, which allows me enough peace of mind to know I have one known-good backup, and a known-good set of WAL segments, and a reasonable restore time.

For your second question, you can have a hot standby server, and incremental backups, if you set it up that way. Your hot standby server only requires an initial basebackup from the primary, and a stream of WAL segments or WAL records (in the case of Streaming Replication, which 9.4 supports and is much nicer than simple WAL shipping).

I would recommend having a hot standby using Streaming Replication, and then taking your basebackups with pg_basebackup from the hot standby server, which will reduce the resource load on your primary considerably.

For an incremental (PITR) backup, you just need to have your primary server send the WAL segments it generates somewhere else (it's best that it's different hardware, of course) using an archive_command or pg_receivexlog.

Pushing the weekly basebackup you created with pg_basebackup there as well will make keeping track of all the necessary components for incremental restoration much easier. Plus, having everything together makes testing those backups trivial.

As for the third question, I don't know of a PITR manager that runs on Windows. In theory, since it's just Python, it could run on Windows, but as the FAQ says, it hasn't been tested.

Magnus Hagander has a great talk on backups from PgCon 2013 that you might find helpful: PostgreSQL Backup Strategies

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.