Skip site navigation (1) Skip section navigation (2)

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

From: John Purser <jmpurser(at)gmail(dot)com>
To: tlm <tlm(dot)1(dot)905+novice(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: cron job failing with 'database "xyz" is being
Date: 2006-07-13 03:49:41
Message-ID: 20060712204941.eec3c68e.jmpurser@gmail.com (view raw or flat)
Thread:
Lists: pgsql-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

In response to

pgsql-novice by date

Next:From: Michael FuhrDate: 2006-07-13 04:09:57
Subject: Re: The name of the game (was Re: postgre linkage with non-postgre db)
Previous:From: Tom LaneDate: 2006-07-13 03:42:54
Subject: The name of the game (was Re: postgre linkage with non-postgre db)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group