Detail There Is 1 Other Session Using the Database Postgres

Summary: in this tutorial, you will learn how to use the PostgreSQL DROP DATABASE statement to drop a database.

Introduction to PostgreSQL DROP DATABASE statement

Once a database is no longer needed, you can drop it by using the DROP DATABASE statement.

The following illustrates the syntax of the DROP DATABASE statement:

            
                              DROP                DATABASE                [IF                EXISTS] database_name;            
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

To delete a database:

• Specify the name of the database that you want to delete after the DROP DATABASE clause.
• Use IF EXISTS to prevent an error from removing a non-existent database. PostgreSQL will issue a notice instead.

The DROP DATABASE statement deletes catalog entries and data directory permanently. This action cannot be undone so you have to use it with caution.

Only superusers and the database owner can execute the DROP DATABASE statement. In addition, you cannot execute the DROP DATABASE statement if the database still has active connections. In this case, you need to disconnect from the database and connect to another database e.g., postgres to execute the DROP DATABASE statement.

PostgreSQL also provides a utility program named dropdbthat allows you to remove a database. The dropdb program executes the DROP DATABASE statement behind the scenes.

1) Drop a database that has active connections

To delete the database that has active connections, you can follow these steps:

First, find the activities associated with the database by querying the pg_stat_activity view:

            
                              SELECT                *                FROM                pg_stat_activity                WHERE                datname =                '<database_name>';            
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

Second, terminate the active connections by issuing the following query:

            
                              SELECT                pg_terminate_backend (pid)                FROM                pg_stat_activity                WHERE                pg_stat_activity.datname =                '<database_name>';            
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

Notice that if you use PostgreSQL version 9.1 or earlier, use the procpidcolumn instead of the pidcolumn because PostgreSQL changed procidcolumn to pidcolumn since version 9.2

Third, execute the DROP DATABASE statement:

            
              DROP DATABASE                <database_name>;            
                          Code language:              HTML, XML              (              xml              )                      

PostgreSQL DROP DATABASE examples

We will use the databases created in the PostgreSQL create database tutorial for the demonstration.

If you haven't created this database yet, you can use the following CREATE DATABASE statements to create them:

            
                              CREATE                DATABASE                hrdb;                CREATE                DATABASE                testdb1;            
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

1) Drop a database that has no active connection example

To remove the hrdbdatabase, use the hrdbowner to connect to a database other than hrdbdatabase e.g., postgresand issue the following statement:

            
                              DROP                DATABASE                hrdb;            
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

PostgreSQL deleted the hrdbdatabase.

2) Drop a database that has active connections example

The following statement deletes the testdb1database:

            
                              DROP                DATABASE                testdb1;            
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

However, PostgreSQL issued an error as follows:

            
              ERROR: database                "testdb1"                is being accessed by other users SQL state:                55006                Detail: There is                1                other session using the database.            
                          Code language:              JavaScript              (              javascript              )                      

To drop the testdb1 database, you need to terminate the active connection and drop the database.

First, query the pg_stat_activityview to find what activities are taking place against the testdb1database:

            
                              SELECT                *                FROM                pg_stat_activity                WHERE                datname =                'testdb1';            
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

The testdb1database has one connection from localhosttherefore it is safe to terminate this connection and remove the database.

Second, terminate the connection to the testdb1database by using the following statement:

            
                              SELECT                pg_terminate_backend (pg_stat_activity.pid)                FROM                pg_stat_activity                WHERE                pg_stat_activity.datname =                'testdb1';            
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

Third, issue the DROP DATABASE command to remove the testdb1database:

            
                              DROP                DATABASE                testdb1;            
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

PostgreSQL drops the testdb1permanently.

In this tutorial, you have learned how to use the PostgreSQL DROP DATABASE statement to drop a database. In addition, you also learned how to delete a database that has active connections.

Was this tutorial helpful ?

Detail There Is 1 Other Session Using the Database Postgres

Source: https://www.postgresqltutorial.com/postgresql-drop-database/

Share this post