[ NB: I posted this several hours ago, but it never appeared. Below
is essentially the same post, but I corrected a significant oversight.
]
Hi everyone.
I have a Perl/DBI script, which runs as a monthly cron job; it
downloads data from a remote site and uses it to rebuild a local
database (let's call this DB "xyz"). The script first builds the new
database under a temporary name ( e.g. "xyz_tmp"), and when the newly
built DB passes all the tests, it drops the old version and renames
the new version to "xyz".
The script works fine most of the time, but it fails occasionally with
the error
ERROR: 'database "xyz" is being accessed by other users
This is always due to the fact that, despite the reminders that get
sent every month to all the users of "xyz", one of them has left a
psql session, connected to "xyz", running overnight (the script runs
at 2 AM, as a cron job).
The only way I know around this is to kill and restart the Pg server
before running the script, thereby killing *all* ongoing sessions
(even those not connected to "xyz").
Is there a more "civilized"/clueful approach?
[Here's the bit I missed in my original post.]
More importantly, killing and restarting the server requires that the
cron job be run by the postgres superuser, which I would like to
avoid. Instead, the job is being run by a user that can connect as
the owner of "xyz". Is there a way for the owner of "xyz" to
disconnect all the other users connecting to "xyz"?
Thanks!
tlm
PS: Is there a better list to send this query to than psql-novice?