How to drop database postgresql

PostgreSQL Drop Database with Examples

Home » Databases » PostgreSQL Drop Database with Examples

Removing unused databases is good practice and helps keep the workspace clean. However, keep in mind that deleting an existing PostgreSQL database removes all catalog entries and data for that database.

Continue reading to learn how to drop a database in PostgreSQL.

DROP DATABASE Statement

Important: ONLY the database owner can delete a database.

The first method to remove a PostgreSQL database is to use the following SQL statement:

The command removes the directory containing the database information and the catalog entries. Only the database owner can execute the DROP DATABASE command. If anyone is currently using the database, the command does not execute.

To see how DROP DATABASE works, do the following:

1. Open the terminal (CTRL+ALT+T).

The terminal prints the executed statement.

The database from the previous step shows up on the list.

5. Drop the database with:

The output shows the executed statement.

6. List all databases again:

The example database no longer appears in the list.

IF Exists

The IF EXISTS option is open for all versions where DROP DATABASE is available. The full command syntax with the IF EXISTS option is as follows:

The option first checks if a database exists before deleting it. If a database exists, the command drops the database. However, if a database doesn’t exist, the command prints an informative notice message.

To test how the command works, follow the steps below:

1. Create an example database:

2. Drop the database using the IF EXISTS option:

The result is identical to using DROP DATABASE if the database does exist.

3. The database is no longer available. Rerun the command to see the output:

A notice message prints stating the database does not exist.

4. To see the difference between using IF EXISTS and omitting the option, run the following command:

Using DROP DATABASE without the IF EXISTS option on a non-existent database throws an error message.

WITH (FORCE)

The WITH (FORCE) option is available in PostgreSQL version 13 and higher.

The DROP DATABASE method won’t remove the database if it’s in use. If the database is in use, the terminal prints an error that a database session is open.

Add the WITH (FORCE) option to forcefully close the session and delete the database:

If possible, Postgres closes the user’s session and deletes the database forcefully.

The dropdb Utility

The dropdb shell utility is a wrapper for the DROP DATABASE command. Effectively, the two methods are identical. However, dropdb offers additional options including removing databases remotely.

The basic syntax is:

Options

The table below shows all the possible options when using the dropdb utility.

For example, try the following command to see how dropdb works with the -i and -e options:

The program asks for confirmation before the deletion because of the -i tag.

Press y to confirm. The program prints the commands generated to the server. Because the database is non-existent, the program throws an error and exits.

After following the examples from this guide, you know how to drop a PostgreSQL database using two methods.

To learn how to drop a user in multiple ways, read our guide on how to delete Postgres user.

Next, consider learning about the different data types in PostgreSQL.

How to drop database postgresql

DROP DATABASE drops a database. It removes the catalog entries for the database and deletes the directory containing the data. It can only be executed by the database owner. It cannot be executed while you are connected to the target database. (Connect to postgres or any other database to issue this command.) Also, if anyone else is connected to the target database, this command will fail unless you use the FORCE option described below.

DROP DATABASE cannot be undone. Use it with care!

Parameters

Do not throw an error if the database does not exist. A notice is issued in this case.

The name of the database to remove.

Attempt to terminate all existing connections to the target database. It doesn’t terminate if prepared transactions, active logical replication slots or subscriptions are present in the target database.

Notes

DROP DATABASE cannot be executed inside a transaction block.

This command cannot be executed while connected to the target database. Thus, it might be more convenient to use the program dropdb instead, which is a wrapper around this command.

Compatibility

There is no DROP DATABASE statement in the SQL standard.

See Also

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Copyright © 1996-2022 The PostgreSQL Global Development Group

PostgreSQL: Drop PostgreSQL database through command line [closed]

Want to improve this question? Update the question so it’s on-topic for Stack Overflow.

I’m trying to drop my database and create a new one through the command line.

I get the error

database databasename is being accessed by other users

I shut down Apache and tried this again but I’m still getting this error. Am I doing something wrong?

4 Answers 4

Trending sort

Trending sort is based off of the default sorting method — by highest score — but it boosts votes that have happened recently, helping to surface more up-to-date answers.

It falls back to sorting by highest score if no posts are trending.

Switch to Trending sort

You can run the dropdb command from the command line:

Note that you have to be a superuser or the database owner to be able to drop it.

You can also check the pg_stat_activity view to see what type of activity is currently taking place against your database, including all idle processes.

Note that from PostgreSQL v13 on, you can disconnect the users automatically with

This worked for me:

for postgresql earlier than 9.2 replace pid with procpid

If that doesn’t work, I have seen a problem with postgres holding onto orphaned prepared statements.
To clean them up, do this:

then for every id you see, run this:

When it says users are connected, what does the query «select * from pg_stat_activity;» say? Are the other users besides yourself now connected? If so, you might have to edit your pg_hba.conf file to reject connections from other users, or shut down whatever app is accessing the pg database to be able to drop it. I have this problem on occasion in production. Set pg_hba.conf to have a two lines like this:

Now you should be able to do:

Not the answer you’re looking for? Browse other questions tagged database postgresql or ask your own question.

Linked

Related

Hot Network Questions

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

PostgreSQL DROP DATABASE

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:

To delete a database:

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 dropdb that 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:

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

Notice that if you use PostgreSQL version 9.1 or earlier, use the procpid column instead of the pid column because PostgreSQL changed procid column to pid column since version 9.2

Third, execute the DROP DATABASE statement:

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:

1) Drop a database that has no active connection example

To remove the hrdb database, use the hrdb owner to connect to a database other than hrdb database e.g., postgres and issue the following statement:

PostgreSQL deleted the hrdb database.

2) Drop a database that has active connections example

The following statement deletes the testdb1 database:

However, PostgreSQL issued an error as follows:

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

First, query the pg_stat_activity view to find what activities are taking place against the testdb1 database:

The testdb1 database has one connection from localhost therefore it is safe to terminate this connection and remove the database.

Second, terminate the connection to the testdb1 database by using the following statement:

Third, issue the DROP DATABASE command to remove the testdb1 database:

PostgreSQL drops the testdb1 permanently.

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.

Drop Database PSQL

In this PostgreSQL tutorial, we are going to learn about Drop Database psql. Here we will learn how to use the drop queries available in PostgreSQL, and we will also cover the following list of topics.

Drop database psql

If a PostgreSQL database is no longer required, we drop it by using the DROP DATABASE statement. In PostgreSQL, DROP DATABASE is a statement that permanently removes all catalog entries and data directory.

Only the database owner can execute this command. We can’t execute while someone is connected with the target database. We need to connect to another database to execute the DROP DATABASE command.

Let’s check the syntax for the DROP DATABASE statement.

Here, IF EXISTS is an optional parameter. It will not throw an error if the database does not exist. In this case, a notice is being issued. The database_name mentions the DB name that we want to drop or remove.

Now, we will discuss step by step process to drop the database using the Postgres command line.

Step 1: We will use \l or \list command to list all the currently available databases. Let’s check the output by implementing the \l command.

Step 2: To drop or delete any database in Postgresql, we will enter the command given below.

Now, we will take any database from the following above databases given in the output. Let’s take the database cars. So, we will write the following command to delete it.

Let’s check the output for it.

Step 3: We will again try to delete the same database i.e. cars but we will get an error. Let’s check after implementing it.

Step 4: Now, we will delete the same database with IF EXISTS clause and we will get a warning. Let’s check the query for its implementation.

Let’s check output for it.

Drop database psql force

We will describe a new characteristic in PostgreSQL that will let the DROP DATABASE command be executed even if active sessions are connected to the database. DROP DATABASE command was supported from the early versions of Postgresql.

But until now, DROP DATABASE was not allowed if any of the sessions connected to the database being dropped was active.

Now has been added a feature to allow DROP DATABASE even if any active sessions are connected to the database. Let’s check the syntax for it.

And if we want to cross-check whether the database is actually deleted or not. We will check with the help of the below query.

Let’s check the implementation of the above query.

Drop multiple databases psql

Postgre doesn’t provide any selected command or function to drop multiple databases. Like there is no direct way for dropping multiple databases at once.

To drop multiple databases, we will build an unknown code block and then will execute that particular code block using the psql tool.

Now, we will generate drop database commands by using the below query which we were talking about.

Let’s check the output for the above query.

Drop database psql shell

Now we will learn how to drop the database using the psql tool in the shell. We can drop the database in the shell using the Linux terminal or Ubuntu. So, we will learn how Postgresql queries are implemented in Ubuntu.

Firstly we will check for the list of databases using \l or \list command. Now we will create a database by the name cars. Let’s see in the below output.

Now we will drop the database cars using the query dropdb cars. dropdb will destroy an existing PostgreSQL database. The user who will execute this command should be a database superuser or the owner of the database.

Let’s check whether the database cars is deleted or not by using the command \l. Below is the output for it.

Drop all databases psql

Now we will learn how we can drop all databases in Postgresql using the psql tool. For deleting all databases in Postgresql we use the DROP DATABASE statement doesn’t just have to drop one database.

Here we will learn that all databases can be specified when separated by commas. Let’s see it in the below output.

Now we will drop all databases which will give the below output.

Psql drop database with connections

Now we will learn how we can drop databases with active connections using the psql tool. While using PostgreSQL some time we may come forward to a case to drop the whole database.

If that database is actively getting used we may get an error that can’t drop the database due to active connections.

Here are the two steps through which we can drop all active connections. Let’s learn how it is implemented.

Now, we will implement the above two queries.

Drop all tables in database psql

PostgreSQL doesn’t provide any selected command or function to drop all tables from a database. To drop all tables, we will build an unknown code block and then will execute that particular code block.

For performing this function, we tend to choose all table names for a schema from the pg_tables and will store the names within the RECORD type variable.

After that point loop through these table names and will execute the drop table cmd for every table name.

We have a detailed discussion on dropping all tables in the Postgresql database using the psql tool. Click here for the link

Psql drop database before restore

To export the PostgreSQL database we will have to use the pg_dump tool, which will dump all the contents of a particular database into one file.

We need to run pg_dump within the command line on the pc wherever the database is stored.

If the database is stored on a remote server, we will need to SSH to that server in order to run the below command.

Here we have used the below options :

To check a list of all the available options use pg_dump.

With the choices given in the above query, pg_dump can first prompt for a password for the database user db_user and so connect as that user to the database named db_name.

Once it is connected, it will write the output generated by pg_dump to a file with a given name, during this case, dump_name.tar.

The file created during this method contains all the PostgreSQL queries that are needed so as to replicate our database.

There are two methods to restore a PostgreSQL database. One is psql which is used for restoring from a plain PostgreSQL script file created with pg_dump.

Another one is pg_restore is used for restoring from a .tar file, directory, or custom format created with pg_dump. Now, we will further discuss

In case our backup may be a plain-text file containing SQL script, later we will restore our database by using PostgreSQL interactive terminal. Now we are going to run the below command.

In the above command where db_user is that the database user, db_name is that the database name and dump_name.sql is that the name of our backup file.

If we select custom, directory, or archive format when creating a backup file, then we need to use pg_restore in order to restore our database.

If we are using pg_restore then we have various options available like

You may also like the following PostgreSQL tutorials:

In this PostgreSQL tutorial, we have learned about Drop Database psql. Here we have learned how to use the drop queries available in PostgreSQL, and we have also covered the following list of topics.

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.