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
