How do I prevent changes to my PostgreSQL database?

My steps:

• run the final pg_dumpall on a server prod-server-old

• Shut down prod-server-old for ever.

• copy the output to a different server (prod-server-new)

• Restore the db there.

• All traffic goes to prod-server-new now

How can I avoid modification to the database during pg_dumpall (between step1 and step2), so that modifications during this time cannot be lost?

In my case it is 100% OK to have a small down time.

There are several databases in the "cluster" (btw, I do not like the word "cluster". I guess most people think a "cluster" is a group of several computers, but in this case it means one Postgres server, which holds several databases).

My question was marked as possible duplicate to "Make Postgres database temporarily read-only (for performing volume snapshots)". I don't think it is a duplicate, since in my case is different since I don't ask for a temporary read-only state.

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.

StackExchange Database Administrators Q#186045, answer score: 4