Make Postgres database temporarily read-only (for performing volume snapshots)

Question

The PostgreSQL built-in backup mechanism isn't always very suitable. Sometimes, you want to put the application in a quiescent state, because it has external data with which you want to backup at the same time you back up the PG data. But the only way to put the application in a quiescent state is to "lock" the database also. PG lacks a database-wide or cluster-wide locking mechanism. Putting PG into a read-only state would be a piece in the following solution:

1. Quiesce application data (disable logins)
2. Quiesce database (by making it read-only)
3. Perform a PG checkpoint or pg_xlog_switch()
4. Create a snapshot of the App and Data volumes
5. Resume the database (make it RW again)
6. Resume the application
7. Backup the snapshots

• Erwin Brandstetter: I think this related answer should serve you well: dba.stackexchange.com/questions/11893/… (maybe duplicate?)
• Otheus: @ErwinBrandstetter thanks, it's in the right direction.

Answer 1

After culling answers elsewhere on the internet, I devised a solution. The other answers were in and of themselves, incomplete. So I am presenting an answer here in hopes it will benefit others.

The Strategy

1. Disable connections to the database (not the cluster).
2. Set the database's default_transaction_read_only setting to true.
3. Terminate the existing connections to that database.
4. Re-enable (read-only) connections.

Once that is done, you would (in my solution):

5. Perform the CHECKPOINT (I think this is the safest, but a pg_xlog_switch() would be appropriate for very high-load servers)
6. Take the volume snapshot
7. Reverse the previous steps. (But this is tricky!)

Pitfalls

1. Terminating connections while they are mid-transaction is probably a bad idea. Better to kill idle connections, wait for a few seconds, then kill idle ones, wait a few more, repeat until they are all gone.
2. At some point, you'll have to kill open/hung queries or abort the backup.
3. At the start of a transaction of session, Postgresql takes a kind of a snapshot of the process table. You have to reset this snapshot every time you go to check if unwanted processes are still there. See pg_stat_clear_snapshot()

4. Restoring the read-write state is not so simple. If read-only connections now exist, you must terminate them in order for the new read-write status to take effect. But new connections might arrive while killing existing ones. So again, you must

   1. Disable connections to the database
   2. change default_transaction_read_only status to false
   3. kill the existing connections
   4. Re-enable (r/w) connections to the database

Alternate strategy

Another strategy is to change the permissions on the role used by the application. This can be quite messy and is less general.

For instance, you'd have to revoke/re-grant on not just tables, but sequences, large objects, and probably the schema itself. Further, what exactly is the behavior of existing connections when you change the access? Probably no impact, which means you also need to kill those backends. Finally, let's say the application has read-write access to most tables, but not to others in the schema. You'd have to make sure your re-granting doesn't include those objects as well.

Another possibility is to LOCK all the tables, by querying the catalog and performing a dynamic query. That seemed to perilous for my tastes.

Implementation

Pause_service

The database instance name is 'gitlabhq' and the username of the application is 'gitlab'. Replace it with your own:

 psql -Upostgres <<'PAUSE_DB'
 -- 1. disable new connections
 alter database gitlabhq_production with allow_connections = off;
 -- 2. Make DB read-only
 alter database gitlabhq set default_transaction_read_only = true;
 -- 3. Inobtrusively but safely terminate current connections
 DO $X$ BEGIN
 -- kill open idle connections, try up to 9x. Last time, kill regardless
 FOR i IN 1..10 LOOP
 PERFORM pg_terminate_backend(pid) from pg_stat_activity where usename = 'gitlab'
 and (i >= 10 OR state in ('idle', 'disabled' ));
 PERFORM pg_stat_clear_snapshot();
 EXIT WHEN NOT EXISTS ( select pid from pg_stat_activity where usename = 'gitlab' );
 RAISE NOTICE 'pg backends still open: sleeping 2 seconds';
 PERFORM pg_sleep(2);
 PERFORM pg_stat_clear_snapshot();
 END LOOP;
 -- send notice if still open connections
 IF EXISTS ( select pid from pg_stat_activity where usename = 'gitlab' ) THEN
 RAISE NOTICE 'Hung backends. Backup might not be 100%% consistent';
 END IF;
 END;$X$;
 -- 4. Allow read-only connections while checkpointing/snapshotting
 alter database gitlabhq with allow_connections = on;
 CHECKPOINT;

Resume

 alter database gitlabhq_production with allow_connections = off;
 alter database gitlabhq set default_transaction_read_only = false;
 SELECT pg_stat_clear_snapshot();
 SELECT pg_terminate_backend(pid) from pg_stat_activity where usename = 'gitlab';
 alter database gitlabhq with allow_connections = on;

There's a possibility that in this last step you will kill long-running read-only/SELECT queries, but in my experience, such long-running queries can last minutes if not hours, and it's acceptable to kill these in order to ensure uptime for everyone else.

• user1822: alter database gitlabhq set default_transaction_read_only = true; doesn't really help because new connections can simply do start transaction read write or set default_transaction_read_only = false;
• Craig Ringer: and some clients, poolers, etc will do so by default unless explicitly asked for a read-only txn by the app. So whether this works depends a lot on the app.
• Otheus: @a_horse_with_no_name If your app does this, then you'll have to keep the connections disallowed for that app.
• Otheus: @CraigRinger What poolers / apps do this?
• A. Scherbaum: Even if you disconnect all clients, the database is not read-only. Background jobs like VACUUM might still change the database!

Answer 2

I think it'd be desirable to have this functionality as an official PostgreSQL feature, personally.

Doing it simply

If you don't want to get your hands dirty with C coding for a PostgreSQL extension, you can just put a connection pooler in front of PostgreSQL. Like pgBouncer.

pgBouncer has the ability to pause application activity built-in. Though to make it very useful you need to connect directly (not via pgbouncer) and cancel active connections once you've paused new ones from coming in. Just select pg_terminate_backend(pid) from pg_stat_activity where pid <> pg_backend_pid().

Doing it right

If you're willing to get your hands dirty, though, it can be done with a C extension. The extension must:

• Be loaded in shared_preload_libraries so it can register a small static shared memory segment with a boolean flag like db_is_locked.

• Register a ProcessUtility_hook and ExecutorStart_hook that tests the is-locked flag in shmem and, if set, sleeps in a WaitLatch loop until it sees the flag has been cleared again. (You could possibly use a parser hook instead).

• Write two SQL-callable functions in C. One sets the flag. Another clears the flag and iterates through PGPROC setting the latch of all user processes, so they know to wake up immediately.

• Optionally write a 3rd function that, if the flag is set, iterates through PGXACT to find open write transactions and signals them to terminate.

All this has already been implemented as part of the BDR extension, but it's part of a much larger system. You could quite likely extract the relevant parts into your own extension. See bdr_locks.c, bdr_commandfilter.c, bdr_executor.c, bdr.c, etc.

Note that this won't make PostgreSQL read-only on disk - the checkpointer will continue to run, the bgwriter will continue to run, the archiver will still run, etc. So it's not enough to let you make a DB backup without an atomic file system snapshot or pg_start_backup() / pg_stop_backup(). But it's fine for your use case, pausing application actiivity at the DB.

• Otheus: The bouncer container might be the best option. I'm using this for a dockerized gitlab setup, and I want to eventually move the databases to their own server without drastically reconfiguring the docker setup.
• Otheus: Checking the shared memory segment for each process/executor might be a tad expensive, no? Wouldn't it be better simply to load the extension before the snapshot and unload it afterward?
• Craig Ringer: @Otheus Huh? No, really not. If you want to ensure reliable ordering you must have a LWLock or spinlock on the shmem flag, and acquire it before testing the flag. But that's still ridiculously cheap compared to all the things PostgreSQL does in execution of even a simple query. Trace the query execution path for SELECT * FROM tablename and you'll be amazed at what happens. Trust me, accessing a simple shmem segment that's preloaded in shared_preload_libraries costs nothing in comparision.
• Craig Ringer: @Otheus But more importantly, what you describe won't work. You don't have any way to load the extension into existing, running backends, unless you can intervene on each connection on the application side to run a LOAD or similar. And if you can do that, you can just pause all your queries app-side so you don't need the extension at all. You need the extension preloaded and running in all backends, so it's already there and already testing the "read only" flag when you decide to actually set it.
• Craig Ringer: To give you an idea, if I sudo perf probe -x /usr/pgsql-9.6/bin/postgres --add LWLockAcquire then in a psql session select pg_backend_pid() and trace that session with sudo perf record -e probe_postgres:LWLockAcquire -p 26367, run SELECT * FROM some_empty_table, and end the perf record session, that PostgreSQL backend has taken over 400 LWLocks. So really, a single shared memory test is practically free. If you can show it makes 0.1% difference in performance I'll be impressed.