Как удалить базу данных postgresql ubuntu
Базы данных удаляются командой DROP DATABASE :
Лишь владелец базы данных или суперпользователь могут удалить базу. При удалении также удаляются все её объекты. Удаление базы данных это необратимая операция.
Невозможно выполнить команду DROP DATABASE пока существует хоть одно подключение к заданной базе. Однако можно подключиться к любой другой, в том числе и template1 . template1 может быть единственной возможностью при удалении последней пользовательской базы данных кластера.
Также существует утилита командной строки для удаления баз данных dropdb :
(В отличие от команды createdb утилита не использует имя текущего пользователя по умолчанию).
PostgreSQL Drop Database with Examples
PostgreSQL offers two command-line methods to drop a database — using the DROP DATABASE statement or a shell utility.
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.
- PostgreSQL 10 or higher installed and configured (follow our guide for Ubuntu or Windows; if already installed, check the PostgreSQL version on the system).
- Access to the terminal with sudo privileges.
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.
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.
Как удалить базу данных postgresql ubuntu
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.
This will fail if the current user has no permissions to terminate other connections. Required permissions are the same as with pg_terminate_backend , described in Section 9.27.2. This will also fail if we are not able to terminate connections.
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
| Prev | Up | Next |
| DROP CONVERSION | Home | DROP DOMAIN |
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.
How to delete postgresql database on linux
I am trying to learn postgresql on linux using the command line interface.
I have added some databases a while back, following some tutorials (which I have since forgot everything I have learned).
Now I want to delete these databases.
I made the assumption that I should be doing this by using psql, the command-line interface to postgresql.
You can see what I have tried in the following command line output, and that none of it has succeeded.