Re: cron job failing with 'database "xyz" is being

On Wed, 12 Jul 2006 20:49:03 -0400
tlm <tlm(dot)1(dot)905+novice(at)gmail(dot)com> wrote:

> [ 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?
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 9: In versions below 8.0,
> the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match

Tlm,

You have users that that are careless and or don't do what you tell
them to do? Huh. Well, I guess that had to happen somewhere.

Each connection to Postgresql creates a new process to handle the
connection. On Linux you can run 'ps aux' and see the connection
processes including the user and database they're connected to. Perhaps
a script to grab the proc IDs and send them a signal?

You can also subscribe to the pgsql-general mailing list and ask
questions there. Also you might want to check out the IRC channel for
postgresql.

John Purser