Installation and Administration

I Can't Delete My PostgreSQL Database

Updated: April 9, 2019 Page Information Edit on GitHub

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
      
      

 

4 months ago manonlumeau Relocated assets to fit new URL structure for 710 - fix
3 years ago Solen Guitter 23
5 years ago Solen Guitter 22
5 years ago Solen Guitter 21
5 years ago Solen Guitter 20
9 years ago Julien Carsique 18
9 years ago Julien Carsique 19 | Migrated to Confluence 4.0
9 years ago Stéfane Fermigier 17
9 years ago Stéfane Fermigier 16
9 years ago Stéfane Fermigier 15
9 years ago Stéfane Fermigier 14
9 years ago Stéfane Fermigier 13
9 years ago Stéfane Fermigier 12
9 years ago Stéfane Fermigier 11
9 years ago Stéfane Fermigier 10
9 years ago Stéfane Fermigier 9
9 years ago Stéfane Fermigier 8
9 years ago Stéfane Fermigier 7
9 years ago Stéfane Fermigier 6
9 years ago Stéfane Fermigier 5
9 years ago Stéfane Fermigier 4
9 years ago Stéfane Fermigier 3
9 years ago Stéfane Fermigier 2
9 years ago Stéfane Fermigier 1
History: Created by Stéfane Fermigier

We'd love to hear your thoughts!

All fields required