Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Have you checked the docs? It says "By default, pg_start_backup can take a long time to finish. This is because it performs a checkpoint, and the I/O required for the checkpoint will be spread out over a significant period of time, by default half your inter-checkpoint interval (see the configuration parameter checkpoint_completion_target). This is usually what you want, because it minimizes the impact on query processing." What this means in practice (and in your case) is not quite clear, however.
–
dezsoDec 17 '12 at 18:05

2 Answers
2

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.

After understanding that pg_start_backup() is mostly "a thing of controlled checkpoint'ing", we earned the confidence to simply try and see. It seems the impact on the running application was negligible. (master main datadir on SSD) :-) The "untested & possibly unsafe" idea you proposed is a bit above our level of competence, and lust for adventure.
–
DanielDec 21 '12 at 12:44

Oh, and we didn't ionice the rsync on the first try. Because we actually wanted to see the additional load on the master. Since we never needed a second rsync run, all is well. We learned something from that.
–
DanielDec 21 '12 at 12:48

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.