PostgreSQL is a robust open-source relational database system that allows you to create as many databases as you need for your projects. This makes Postgres a perfect platform for managing the data storage component of your operations.
During the course of your operations, you may need to delete databases to remove outdated data or save disk space. Technically known as dropping a PostgreSQL database, the process permanently deletes a database and all the associated data, tables, and other objects from the PostgreSQL database management system (DBMS).
Many people assume that simply deleting the database is enough to remove it from the system. In practical terms, deleting the database from the server doesn’t remove dependencies and data objects. Dropping the database is a systematic process that safely removes the database and ensures that there are no complications in database operations.
This short tutorial will describe how to drop PostgreSQL database. Given the versatility of the platform, we’ll show you five different methods of accomplishing this task.
Let’s start with the prerequisites.
Table Of Content
Prerequisites
Before diving in, ensure you have:
- PostgreSQL is installed on your system.
- A user account with superuser or sudo privileges.
- A backup of any crucial data because dropping a database will permanently erase it.
How to Drop a PostgreSQL Database
Let’s explore the five easy and effective ways to drop PostgreSQL databases. You can pick the right fit for your specific scenarios.
Method #1: Use the DROP DATABASE Statement
The DROP DATABASE statement is the most common way of removing a PostgreSQL database.
The syntax of this SQL syntax is:
postgres=# DROP DATABASE <database name>;
This statement will delete the database and all its objects and information. Note that only the database owner can execute the DROP DATABASE command. Also, the statement will not execute if there are active database connections.
Here’s the step-by-step guide to the DROP DATABASE statement. Note that we’ll first create a test database and then remove it:
- Open the terminal and connect to PostgreSQL shell by running:
# sudo -i -u postgres psql
- We’ll create a test database named test with the CREATE DATABASE statement:
postgres=# CREATE DATABASE test;
- List all available databases to verify that the database has been created successfully:
postgres=# \l
You should see the test database in the list.
- Now, to drop this PostgreSQL database, execute:
postgres=# DROP DATABASE test;
You can see DROP DATABASE as the success message.
- Verify database deletion by listing the databases:
postgres=# \l
As you can see, after the successful execution of the DROP DATABASE statement, the test database is no longer on the list.
If you want to know about user creation in PostgreSQL, explore our easy-to-understand guide, where we discuss two easy ways to create users in PostgreSQL.
Method #2: Use the IF EXISTS Option
The DROP DATABASE statement will throw an error if you try to delete a database that doesn’t exist on your system.
To avoid these errors, especially when you are using the DROP DATABASE statement in a script, we recommend using the IF EXISTS option with the statement.
The command syntax of the IF EXISTS option is:
postgres=# DROP DATABASE IF EXISTS <database name>;
Adding IF EXISTS to the DROP DATABASE statement is like saying, “If the database exists, go ahead and delete it, but if it doesn’t, that’s okay too, no need to worry.”
Let’s explore this method in detail:
- First, create the test database:
postgres=# CREATE DATABASE test;
- Now, drop the database using the IF EXISTS option. Be careful about the syntax because incorrect syntax causes execution errors:
postgres=# DROP DATABASE IF EXISTS test;
You will receive the same DROP DATABASE message as in the previous method.
You can verify that the database has been dropped by running the drop statement once more.
postgres=# DROP DATABASE IF EXISTS test;
In this case, you will receive the message NOTICE: database “test” does not exist, skipping, indicating that the database is no longer there. This is a safe way to handle non-existent databases and minimize the chances of broken SQL scripts.
Let’s see what happens if you try to use the DROP DATABASE without IF EXISTS:
postgres=# DROP DATABASE test;
You can see that the scenario results in an error message that IF EXISTS suppresses.
Method #3: Use PgAdmin
Dropping the database is a simple process if you’re using the pgAdmin, the PostgreSQL web-based management tool. The steps of the process are:
Step #1: Open pgAdmin and connect to the PostgreSQL server.
Step #2: In the left sidebar, navigate to the Databases section.
Step #3: Right-click on the target database you want to drop.
Step #4: From the context menu, choose Delete/Drop.
Step #5: Confirm the action to drop the database in the confirmation dialog box.
Method #4: Use the WITH (FORCE) Option
The WITH (FORCE) option forcefully removes a database, bypassing the standard checks and restrictions. For instance, it drops the database even if there are active connections or sessions to the database.
This method is available in PostgreSQL versions 13 and above.
Here’s a simplified explanation with the basic syntax:
postgres=# DROP DATABASE <database name> WITH (FORCE);
Here’s an example that deletes the database named mydb:
postgres=# DROP DATABASE mydb WITH (FORCE);
We strongly recommend caution when using this method, as it can lead to data loss because of the absence of the usual safety checks.
Method #5: Use the dropdb Command
The dropdb (short for drop database) command deletes a database. This is a wrapper for the DROP DATABASE statement and can be executed from the terminal.
Let’s see the basic syntax to understand how it works:
$ dropdb <database name>
For instance, use the following command to drop the test database:
$ dropdb test
Once you enter this command, it’ll delete the test database. When executing the command, always double-check the database name, as this action cannot be undone later.
The following table presents the most common options for the dropdb utility:
Also Read: How to Delete a Postgres User (Drop User) [5 Methods Inside!]
Conclusion
In conclusion, dropping a PostgreSQL database can be done using various methods, each serving specific needs. We discussed fuve easy methods to accomplish this task. You can opt for any suitable one, whether using the DROP DATABASE, IF EXISTS, PgAdmin, WITH (FORCE), or the dropdb option.
When managing PostgreSQL databases, especially in production environments, consider hosting solutions that provide reliability and performance. For instance, RedSwitches bare metal hosting provider offers robust hosting solutions that can significantly enhance your PostgreSQL experience. Contact us for the instant dedicated server, 10 Gbps dedicated server, and dedicated server hosting.
FAQs
Q. What happens if I drop a database without backing it up?
Once a database is dropped, all the data within is permanently removed. Without a backup, you won’t be able to retrieve this data.
Q. Is it possible to restore a database after it has been dropped?
If you have a database backup, you can restore it. Otherwise, once the database is dropped, it cannot be retrieved.
Q. When should I drop a database with WITH (FORCE) forcibly?
Sometimes, there might be active connections to the database, preventing it from being dropped. The WITH (FORCE) option terminates these connections, allowing the database to be dropped. Consider this option only when you must drop the database at any cost.
Q. Is the dropdb utility available by default with PostgreSQL installations?
Yes, the dropdb utility is a part of the PostgreSQL package and is available by default with standard PostgreSQL installations.
Q. How can I drop a PostgreSQL database using a command line?
To drop a PostgreSQL database using a command line, you can use the “dropdb” command. The syntax for the command is as follows: “dropdb [options] dbname”. Replace “dbname” with the name of the target database you want to drop. Make sure you have the necessary privileges to perform this action.
Q. How do I drop a PostgreSQL database using pgAdmin?
If using the pgAdmin graphical user interface, you can right-click on the database you want to drop, select “Delete/Drop,” and confirm the action.
Q. What happens when I drop a PostgreSQL database?
When you drop a PostgreSQL database, all the catalog entries and data associated with the database are permanently deleted. This action cannot be undone, so ensure you have a backup or are certain you no longer need the database.
Q. Can I drop a PostgreSQL database using an SQL query?
Yes, you can drop a PostgreSQL database using an SQL query. The query syntax is as follows: “DROP DATABASE dbname;”. Replace “dbname” with the name of the target database you want to drop. Make sure you have the necessary privileges to execute this command.
Q. Do I need to be connected to the target database to drop it?
You do not need to be connected to the target database to drop it. You can execute the drop command from any database with the necessary privileges. However, being cautious and ensuring you are targeting the correct database is recommended.
