cron job failing with 'database "xyz" is being accessed by other users' errors

[ 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?

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