Thread: No aswer WAS: Droping a database even if someone is con nected

I can't answer your original question (i.e. if it's possible to drop a
DB while somebody is connected to it), but I think it's possible to
forced close down all connections. Take a look at:
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=app-pg-ctl.html
You might find a way to restart the server so it is accepting
connections only from the replication script, and then restart it again
to accept connections from the outside world.
You could also import the data in a temporary DB (while the old data is
still online), restart, drop the old database, recreate it using as a
template the temporary DB with the new data, restart online, drop the
temporary DB.

HTH,
Csaba.


On Fri, 2003-03-14 at 15:16, Eric Audet wrote:
> I guess I won't get any anwers for this. And after many searches, it seems
> not possible.
>
>
>
> Maybe some can help me. I am in a situation where someone designed
> something, and now I was put in charge of making sure it is working.
>
>
>
> 1)       we have a database inside our firewall.
>
> 2)       Updates are done inside the firewall during the day
>
> 3)       At night, there is a job dumping the database and ftping the dump
> on an external server
>
> 4)       On the external server, I have a job droping the database, creating
> the database and restoring the database that was ftped.
>
>
>
> There are so many places where this could fail: 1) while dumping, 2) while
> ftping, 3) cronjob synchronization on 2 different machines, 4) user
> connected to database so drop/create database don't work, etc
>
>
>
> What we want is to replicate the inside the firewall database to outside the
> firewall database.
>
> Or we could simplify this: we want to replicate a database into a different
> machine.
>
>
>
> Any help?
>
>
>
> Eric
>
>
>
> -----Original Message-----
> From: Eric Audet [mailto:eaudet@scc.ca]
> Sent: March 13, 2003 2:20 PM
> To: pgsql-general
> Subject: [GENERAL] Droping a database even if someone is connected
>
>
>
> Is there a way to drop a database even if someone is connected to it?
>
> I use a perl script that
> 1) drop the database, (dropdb)
> 2) create a new database, (createdb)
> 3) restore a database from a dump (psql databasename <dumpfile)
>
> When the drop does not work, everything else fails!
>
> Thanks in advance
> Eric
>
>
>

On Fri, Mar 14, 2003 at 03:46:18PM +0100, Csaba Nagy wrote:
> I can't answer your original question (i.e. if it's possible to drop a
> DB while somebody is connected to it), but I think it's possible to
> forced close down all connections.

You can also probably hack something to close all connections to the
specific DB, using ps(1) and perl or sh scripting.  Then you can drop
the database.  Maybe you should set pg_database.datallowcon to false on
the database you want to drop before starting to close connections, so
it doesn't accept future connections before you can do the DROP
DATABASE.

HTH,

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"A wizard is never late, Frodo Baggins, nor is he early.
He arrives precisely when he means to."  (Gandalf, en LoTR FoTR)