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?