A robust open-source relational database management system called PostgreSQL provides a wealth of features for effectively managing and organizing data. Removing a database is one crucial action that needs to be done carefully to avoid accidental data loss. As one is facing performance issues due to problematic long-running queries searching and killing MySQL processes is necessary and helpful.
The vps server hosting offers a virtual machine in SG (Singapore) to give every service that your server needs. We will examine the several facets of dropping databases in this investigation of “PostgreSQL Drop Database,” offering lucid examples and instructions to guarantee a smooth performance of this crucial function.
A database can be dropped using one of two command-line options provided by PostgreSQL: a shell utility or the DROP DATABASE statement. It’s a good idea to remove unused databases to maintain a tidy workstation. However, be aware that removing an existing PostgreSQL database also eliminates all of its data and catalog entries. We allow private servers powerful web hardware and trouble-free VPS operation for India to deliver a pre-eminent hosting experience.
Required Conditions
- Sudo rights to access the terminal.
- Installed and configured PostgreSQL 10 or higher (use our instructions for Windows or Ubuntu; if it’s already installed, see the PostgreSQL version on the system).
DROP Database
Using the following SQL statement is the first way to delete a PostgreSQL database:
DROP DATABASE <database name>;
The directory holding the catalog entries and database data is deleted by the command. The DROP DATABASE command can only be used by the database owner. The command is not executed if the database is being used by anyone.
To see how DROP DATABASE works, try the following:
1. Press CTRL+ALT+T to open the terminal.
2. Set up a PostgreSQL connection:
sudo -i -u postgres psql
3. Construct the example’s database:
CREATE DATABASE example;
The executed statement is printed by the terminal.
4. Enumerate every database using:
\l
The list contains the database from the previous step.
5. Delete the database using
DROP DATABASE example;
The executed statement is visible in the output.
6. Relist every database:
\l
The list no longer includes the sample database.
IF EXITS Option
For all versions where DROP DATABASE is accessible, the IF EXISTS option is active. The following is the whole command syntax when the IF EXISTS option is used:
DROP DATABASE IF EXISTS <database name>;
Before deleting a database, the option first verifies that it is there. The command removes the database if one is present. On the other hand, the command prints an instructive notification message in the event that a database is absent.
To see how the command functions, take the following actions:
1. Make a sample database:
CREATE DATABASE example;
2. Use the IF EXISTS option to drop the database:
DROP DATABASE IF EXISTS example;
If the database is real, the outcome is the same as with DROP DATABASE.
3. The database isn’t accessible anymore. To view the output, rerun the command:
DROP DATABASE IF EXISTS example;
The database does not exist, according to the notice message that prints.
4. Use the following command to illustrate how using IF EXISTS differs from leaving the option out:
DROP DATABASE example;
An error message appears when you use DROP DATABASE on a non-existent database without using the IF EXISTS option.
INCLUDING (FORCE)
PostgreSQL versions 13 and above support the WITH (FORCE) option.
If the database is being used, the DROP DATABASE command will not delete it. The terminal prints an error indicating that a database session is open if the database is in use.
To forcibly end the session and erase the database, add the WITH (FORCE) option:
DROP DATABASE <database name> WITH (FORCE);
Postgres tries to forcefully erase the database and end the user’s session.
Dropdb Utility
The DROP DATABASE command is wrapped in the dropdb shell program. The two approaches are the same in practice. Dropdb, on the other hand, provides more choices, such as remote database removal.
The fundamental syntax is as follows:
dropdb <connection parameters> <options> <database name>
To observe how dropdb functions with the -i and -e arguments, for instance, try running the following command:
dropdb -i -e example
Because of the -i tag, the application requests confirmation before deleting anything.
To confirm, press Y. The created commands are printed to the server by the application. Because the database is non-existent, the software throws an error and terminates.
Wrapping Up on How to Drop PostgreSQL Database
In conclusion, database administrators and developers who want to have a neat and effective data environment will find that understanding the PostgreSQL Drop Database command is essential. Users can confidently manage the procedure and understand the implications and protections involved with dumping a database by using the examples and insights provided in this tutorial.
To ensure the integrity of your PostgreSQL database ecosystem, as with any powerful instrument, thorough consideration and adherence to best practices are essential for a successful end. You now know two ways to drop a PostgreSQL database after completing the examples in this guide.
