Postgres Pro
Downloads
Home   >   mailing lists

Thread: backup server - how to disconnect

backup server - how to disconnect

From
"Mark Steben"
Date:

We have a server that backups and then recreates our production database on a nightly basis

In order to drop and recreate the database we would stop and restart the server - this would

Effectively kick off any straggling users so we could get our refresh done.  No problem.

Now we have more than one database and stopping and restarting the server to clean up

The backup database would also kill any process going on in the new database.

 

Is there a command in postgres to disconnect all users short of restarting

The server?  I looked at pg_ctl kill TERM but there I would need to

Specify specific PIDs.  Not something I want to do manually at 1 am every morning.

 

We are at postgres 8.2.5.  Thanks for your help

 

Committed to Creating @utoEnthusiasts. Please provide us with your feedback.

Mark Steben│Database Administrator│ @utoRevenue™
95 Ashley Ave. West Springfield, MA., 01089 
413-243-4800 x1512 (Phone) │ 413-732-1824 (Fax)

A Division of Dominion Enterprises

 

 

 

 

Re: backup server - how to disconnect

From
Emmanuel BERTHOULE
Date:
Hi,

you can use 

pg_ctl stop -m fast 
pg_ctl start 

who kill client and abort current transaction 
and if you have multiple database you can use the -D option for specify database directory


-manu
 

Le 15 oct. 08 à 16:11, Mark Steben a écrit :

We have a server that backups and then recreates our production database on a nightly basis
In order to drop and recreate the database we would stop and restart the server - this would
Effectively kick off any straggling users so we could get our refresh done.  No problem.
Now we have more than one database and stopping and restarting the server to clean up
The backup database would also kill any process going on in the new database.
 
Is there a command in postgres to disconnect all users short of restarting
The server?  I looked at pg_ctl kill TERM but there I would need to
Specify specific PIDs.  Not something I want to do manually at 1 am every morning.
 
We are at postgres 8.2.5.  Thanks for your help
 
Mark Steben│Database Administrator│ @utoRevenue™
95 Ashley Ave. West Springfield, MA., 01089 
413-243-4800 x1512 (Phone) │ 413-732-1824 (Fax)
A Division of Dominion Enterprises
 
 
 
 

Re: backup server - how to disconnect

From
Fabrízio de Royes Mello
Date:
Hello Mark,

I don't know a command in postgres to do that, but if you're running
postgres on Linux try it on the command line:

for pid in `psql -A -t -c "select procpid from pg_stat_activity"`; do
pg_ctl kill TERM $i; done


Best regards.

Ps: Sorry, but my english isn't so good.

--
Fabrízio de Royes Mello
Coordenador Desenvolvimento de Software
fmello@dbseller.com.br
DBSeller Informática Ltda. - http://www.dbseller.com.br
(51) 3076-5101

Re: backup server - how to disconnect

From
Fabrízio de Royes Mello
Date:
Hi all,

Sorry, but I found a little bug in the command line...

To solve just replace "$i" for "$pid":

for pid in `psql -A -t -c "select procpid from pg_stat_activity"`; do
pg_ctl kill TERM $pid; done

Sorry... :-)

Fabrízio de Royes Mello escreveu:
> Hello Mark,
>
> I don't know a command in postgres to do that, but if you're running
> postgres on Linux try it on the command line:
>
> for pid in `psql -A -t -c "select procpid from pg_stat_activity"`; do
> pg_ctl kill TERM $i; done
>
>
> Best regards.
>
> Ps: Sorry, but my english isn't so good.
>

Cordialmente,

--
Fabrízio de Royes Mello
Coordenador Desenvolvimento de Software
fmello@dbseller.com.br
DBSeller Informática Ltda. - http://www.dbseller.com.br
(51) 3076-5101