I have some installation tools which in some cases need to replace a
postgres database.
For this example, assume my database name is 'fred'.
Also, assume that no other processes are accessing this database. Any
database access will come from my running installer.
First, after validating some passwords, my installer will run this command:
psql -c "select datname from pg_stat_activity where datname='fred'" -U
username -d template1
I then scan the output looking for 'fred'. My (perhaps incorrect)
assumption is that if I don't find the database name 'fred' in the
output, then I can assume that no process is using this database, and
I should be able to drop it.
Later, my installer runs this code:
psql -c "drop database fred" -U username -d template1
99% of the time, everything works fine.
1% of the time, the drop fails, and I get this:
ERROR: database "fred" is being accessed by other users
My two part question is why, and what can I do about it? At a
minimum, I need to be able to reliably determine if I can drop the
database. Ideally, I would like to be able to drop the database even
though it is still in use - force an override of some sort - kick out
the offending user.
Thanks for any advice....
Dan