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"?
> 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
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
In response to
pgsql-novice by date
|Next:||From: Michael Fuhr||Date: 2006-07-13 04:09:57|
|Subject: Re: The name of the game (was Re: postgre linkage with non-postgre db)|
|Previous:||From: Tom Lane||Date: 2006-07-13 03:42:54|
|Subject: The name of the game (was Re: postgre linkage with non-postgre db)|