How do I prevent changes to my PostgreSQL database?

One work-around:

Change the port of PostgreSQL on prod-server-old. This way it is unlikely that clients connect to the DB during pg_dumpall --port=OTHER_PORT.

Two ways to do that:

• If you don't mind about downtime (easy way):

revoke the connect privilege to the public group. That will prevent everyone but the superusers to connect. Then restart the server or terminate all connections, then proceed with the backup using a superuser account.

REVOKE CONNECT TO DATABASE (database) FROM public

• If you do mind about downtime:

Put your database in read-only mode:

ALTER DATABASE (database) SET default_transaction_read_only = true;

Proceed with pg_dumpall. No restart required and no downtime.

Of course, you have to repeat this on every database you want to "lock". If you have a significative number of databases, you can make the entire cluster read-only: set default_transaction_read_only = on; in your postgresql.conf then reload the postgres service.

Please note that I don't have tested any of these commands, proceed at your own risk.

I am a SQL Server admin (not Postgres) so I'm not 100% sure, but if downtime is not an issue can you:

• kill all connections
• prevent remote log ons
• log on to the db server and log into the db as super user
• do the pg_dumpall
• re-enable remote log ons

?