How to allow in-progress transactions to complete when shutting down PostgreSQL?

Question

I'm working on automating as much as possible the mechanics of resizing an EBS volume on which we store all databases. We're going to host a large number of large-but-infrequently-used databases on a single EC2 instance, where the secondary EBS volume hosts PGDATA (aka all the data files), whose configuration we've embedded in this script here.

One of the issues we've run into is that when extending the filesystem using fsadm under a running PostgreSQL, it determines that the target is busy and won't umount the volume. This is expected.

I would like to shut down the database as gracefully as possible - while I'm fine to kill of open idle connections, I'd rather not kill it in the middle of any in-progress transactions. I'm hoping to find some kind of mechanism that allows me to block new connections, block any further transactions from existing connections, and allow existing transactions to complete.

As far as I can tell, the most native way to shut down the database is using pg_ctl stop -m, where the -m takes one of three options: smart, fast and immediate.

From my experiments, it appears that

• smart will fail if there are any open idle connections (which is too generous for my needs, as my developers may leave long-lived connections open in either their Django app or their PGAdmin consoles).
• fast will kill all connections whether active or idle, which seems to set me up to risk corrupting an in-progress transaction

Does anyone have any suggestions for more gracefully shutting down PostgreSQL than killing off all active connections? Are there SQL commands I can run before firing off pg_ctl stop?

Answer 1

I don't know of a way to do this automatically. You could start a smart shutdown, and then loop through live connections terminating them if they are idle. This would be a bit fragile, as if your grim reaper script were to lose its connection it could not re-establish it. A less fragile way would be to edit pg_hba.conf to block all connections except from the grim reaper (instead of starting a smart shutdown), then do the same loop as above. Once there are no other connections, you can do a regular fast shutdown.

But this might not be necessary because:

fast will kill all connections whether active or idle, which seems to set me up to risk corrupting an in-progress transaction

There will be no corruption It will either commit, or will rollback with an error reported to the client. The only risk is that a poorly written client might not correctly deal with the error, but if that is the case you probably have bigger problems than just expanding your EBS.

Answer 2

1. connect to the database as a superuser role (eg: postgres)

2. begin a smart shutdown

3. repeatedly execute this SQL in your connection.

   select pid, case when xact_start is not null then state else pg_terminate_backend(pid) ::text end from pg_stat_activity;

when that returns only a single row all other users have been disconnected.