Re: Backup options?

Magnus Persson wrote:

> What implications does a call to pg_dumpall have on my databases?

It will cause load (mostly I/O).
It will start long-running transaction that can cause autovacuum to lag behind
on cleaning up busy tables, leading to database bloat.

> Is there even a remote chance that a lock could be started by pg_dumpall?

The only locks are on tables, and they only block ALTER/DROP TABLE.

> If we consider that I for some reason or the other can't use pg_dumpall against the production
> clusters, what are my options? One idea is that of using asynchronous replication and pull the dumps
> off of them. Are there any pitfalls related to the replication?

Yes; unless configured properly, the dump may fail because of conflicts.
You'd have to use hot_standby_feedback=on, and that again can cause bloat
on the master cluster as described above.

> During normal operations, will
> postgres ensure that the state on the slaves always reflect the state of the masters? In effect it
> would work similar to if I did the dump on the production servers? I recall reading something about
> asynchronous replication, but I'm unsure of what it was exactly or if it affects backups.

The standby database is identical to the master database, but it may be
a little behind. That is no problem at all for a backup.
Don't use synchronous replication for this purpose.

Yours,
Laurenz Albe