PostgreSQL: Can I do pg_start_backup() on a live, running db under load?

pg_start_backup will perform a checkpoint, as dezso notes. This does have an impact, but your database performs checkpoints quite regularly anyway, and must do so to function, so they're clearly not a problem for you. An early checkpoint means that less data has been accumulated, meaning that if anything a checkpoint from pg_start_backup will be lower-impact than normal.

Where you need to worry is the rsync or equivalent pg_basebackup step. The read I/O from this won't be too bad since it's sequential, but it'll still probably significantly hurt your database's I/O performance, and it'll also tend to push hot data out of RAM cache in favour of less-used data, causing cache thrashing as the more-needed data is then read back in.

You can use nice and ionice to help limit the I/O impact (but not the cache impact); however, there's a cost to that. The backup will take longer, and until you complete the backup and run pg_stop_backup your system is - as I understand it - accumulating WAL it cannot delete, accumulating checkpoint debt for a BIG checkpoint at the end of the backup run, and is accumulating table and index bloat because it can't clean up dead rows. So you really can't afford to have the backup take forever, especially if you have very high churn tables.

In the end, it's hard to say whether you can safely use pg_start_backup and pg_stop_backup for hot backups in your environment. Most people can, but if you're close to the edge of what your hardware can do, have tight timing requirements, cannot afford the risk of a stall, and have very high churn tables as well as very big tables, it might be troublesome.

Unfortunately, you pretty much need to test it and see.

If you can, it might be worth issuing a CHECKPOINT then taking an atomic snapshot of the volume your database is on instead using LVM, your SAN's tools, EBS, or whatever you're on. If you can do this, you can then copy the snapshot at your leisure. This approach isn't suitable for taking a base backup for PITR/warm standby/hot standby, but it's perfectly good for a static backup copy, and is much lower impact on the system. You can only do this if your snapshots are atomic and your entire database including WAL is on a single volume, though.

One possibility I haven't yet investigated is combining the two approaches. It occurs to me that one could possibly (untested and possibly wrong and unsafe, I don't know yet):

  • pg_start_backup
  • Trigger snapshots of all tablespaces, the main datadir, and the xlog volume
  • pg_stop_backup
  • Copy WAL up to the final archive from pg_stop_backup
  • Copy the data from the snapshotted volumes

Essentially, the idea is to reduce how long the DB has to be delaying its checkpoints by taking a point-in-time of each volume that you can copy at your leisure.


This is a grave digging but I have to correct something here.

The previous answer is stating :

You can use nice and ionice to help limit the I/O impact (but not the cache impact); however, there's a cost to that. The backup will take longer, and until you complete the backup and run pg_stop_backup your system is - as I understand it - accumulating WAL it cannot delete, accumulating checkpoint debt for a BIG checkpoint at the end of the backup run, and is accumulating table and index bloat because it can't clean up dead rows. So you really can't afford to have the backup take forever, especially if you have very high churn tables.

That's not true. The system will keep the number of WAL stated in your configuration (cf the online documentation). So basically, the higher value between :

  • (2 + checkpoint_completion_ratio) * checkpoint_segments + 1
  • wal_keep_segments

Let's imagine this case :

  • your backup is taking a long time, as there's hundreds of gigs to copy
  • you have a small WAL retention (checkpoint_segments to 3, for example)
  • you don't have setup the WAL archiving

then after initiating "pg_start_backup()", your WAL files will rotate during your backup. When your backup will be finished, you'll then try to restore it on another database engine. The engine at launch will ask for at least the WAL file generated when you issued "pg_start_backup()".

pg_start_backup 
-----------------
B/D0020F18
(1 row)

The database will not accept to boot until you provide WAL file "0000000x0000000B000000D0" (where x is your TimelineID). This WAL file is the bare minimum for the system to boot. Of course, with only this file, you will lose data, as the rest of the data are located in the WAL files you don't have, but at least, you'll have a working database engine.

So either you must do WAL archiving, or you must save the needed WAL files by yourself, but Postgresql will not do it for you.


As for my experience with PostgreSQL it is relatively safe operation unless you have a really big performance impact on that moment. If you have it then it's better to temporary pause writing from all of your clients.

I had only one critical case while syncing my master to slave under load and it was caused by OOM killer (yes, you really should COMPLETELY disable OOM Killer on database nodes, I didn't knew it that day).

So I've restored database from nightly backup and gave to postgres all WAL segments from pg_archive directory for replay (just copied them into pg_xlog folder). Everything went fine but downtime was inevitable, of course.

Tags:

Postgresql

Replication

Recent Posts