Delete my PostgreSQL Database

Manon_Lumeau — Wed, 23 Mar 2016 14:30:09 GMT

Hi,

I can't delete my PostgreSQL Database, can you tell the steps to follow to do so?

Thanks,

Re: Delete my PostgreSQL Database

Manon_Lumeau — Wed, 23 Mar 2016 14:37:20 GMT

Hi Manon,

When you try to delete your PostgreSQL database you may get an error:

postgres=# DROP DATABASE nuxeo;
ERROR:  database "nuxeo" is being accessed by other users

If you're sure there's no other user connected, then it may be because PostgreSQL has a pending prepared transaction that's never been committed or rolled back, which should never happen except if the database is shut down in an unclean manner (machine crash). To check for this:

1.Run:

SELECT database, gid FROM pg_prepared_xacts;

2.Depending on the number of results, follow one of the steps below.

If you get a result, then for each transaction gid you must execute a ROLLBACK from the database having the problem:

ROLLBACK PREPARED 'the_gid';

For instance:

nuxeo=# SELECT database, gid FROM pg_prepared_xacts;
 database | gid
----------+-----
 nuxeo | 131075_MS03ZjAwMDEzYTo0YTg5MjA5NzoxMDczMg==
(1 row)
nuxeo=# ROLLBACK PREPARED '131075_MS03ZjAwMDEzYTo0YTg5MjA5NzoxMDczMg==';
ROLLBACK PREPARED

If you have lots of transactions you can run this psql scripts:

\t
\a
\o /tmp/remove-transactions.sql
SELECT 'ROLLBACK PREPARED ''' || gid || ''';'  AS cmd
  FROM pg_prepared_xacts
  WHERE database=current_database();
\o
\i /tmp/remove-transactions.sql

Kind regards,

topic Delete my PostgreSQL Database in Nuxeo Forum

Delete my PostgreSQL Database

Re: Delete my PostgreSQL Database