Re: locking all databases

On Tue, 24 Jun 2003, Michiel Lange wrote:

> Hello everybody,
>
> I wondered if it was possible to make all databases quiet for a while (e.g.
> for a dump) so there are no updates in any database during that time? But
> all transactions get into the before image, instead of being commited,
> until the quiet state is ended?

If you're worried about getting a consistent snapshot of an individual
database, don't. pg_dump snapshots the database at the point in time when
it starts a backup. Any transactions in progress will be wholly ignored
during the backup.

If you've got multiple databases that loosely tied together and therefore
you need to stop all transactions to ensure the data across those
databases are coherent, then you've got a bad design, but hey, who hasn't
had to deal with these things before.

anyway, you can edit the pg_hba.conf file to only allow connections on
something odd like local port 127.8.9.10 and then connect to that port for
the backups.

Mostly, it's not necessary to block access to get coherent backups, one of
the best features of postgresql, hot backups are free and they work very
well.