Linux postgresql drop database

Linux postgresql drop database

dropdb — удалить базу данных PostgreSQL

Синтаксис

dropdb [ параметр-подключения . ] [ параметр . ] имя_бд

Описание

dropdb удаляет ранее созданную базу данных PostgreSQL , и должна выполняться от имени суперпользователя или её владельца.

dropdb это обёртка для SQL -команды DROP DATABASE . Удаление баз данных с её помощью по сути не отличается от выполнения того же действия при обращении к серверу другими способами.

Параметры

dropdb принимает в качестве аргументов:

Указывает имя удаляемой базы данных. -e
—echo

Вывести команды к серверу, генерируемые при выполнении dropdb . -i
—interactive

Выводит вопрос о подтверждении перед удалением. -V
—version

Выводит версию dropdb . —if-exists

Не считать ошибкой, если база данных не существует. В этом случае будет выдано замечание. -?
—help

Вывести справку по команде dropdb .

dropdb также принимает из командной строки параметры подключения:

Указывает имя компьютера, на котором работает сервер. Если значение начинается с косой черты, оно определяет каталог Unix-сокета. -p порт
—port= порт

Указывает TCP-порт или расширение файла локального Unix-сокета, через который сервер принимает подключения. -U имя_пользователя
—username= имя_пользователя

Имя пользователя, под которым производится подключение. -w
—no-password

Не выдавать запрос на ввод пароля. Если сервер требует аутентификацию по паролю и пароль не доступен с помощью других средств, таких как файл .pgpass , попытка соединения не удастся. Этот параметр может быть полезен в пакетных заданиях и скриптах, где нет пользователя, который вводит пароль. -W
—password

Принудительно запрашивать пароль перед подключением к базе данных.

Это несущественный параметр, так как dropdb запрашивает пароль автоматически, если сервер проверяет подлинность по паролю. Однако чтобы понять это, dropdb лишний раз подключается к серверу. Поэтому иногда имеет смысл ввести -W , чтобы исключить эту ненужную попытку подключения. —maintenance-db= имя_бд

Указывает имя опорной базы данных, к которой будет произведено подключение для удаления целевой. Если имя не указано, будет выбрана база postgres , а если она не существует (или именно она и удаляется) — template1 . Здесь может задаваться строка подключения. В этом случае параметры в строке подключения переопределяют одноимённые параметры, заданные в командной строке.

Переменные окружения

Параметры подключения по умолчанию

Эта утилита, как и большинство других утилит PostgreSQL , также использует переменные среды, поддерживаемые libpq (см. Раздел 32.14).

Диагностика

В случае возникновения трудностей, обратитесь к DROP DATABASE и psql . При диагностике следует учесть, что при запуске утилиты также применяются переменные окружения и параметры подключения по умолчанию libpq .

Примеры

Для удаления базы данных demo на сервере, используемом по умолчанию:

Для удаления базы данных demo на сервере eden , слушающим подключения на порту 5000, в интерактивном режиме и выводом запросов к серверу:

Источник

Linux postgresql drop database

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

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-2021 The PostgreSQL Global Development Group

Источник

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:

• 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 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.

Источник

PostgreSQL DROP/Delete DATABASE: PSQL Command Example

Updated October 7, 2021

Drop Database in PostgreSQL

The DROP DATABASE in PostgreSQL is a statement to permanently remove all catalog entries and data directory. The database owner can only execute this command. It can not be executed while someone is connected with the target database. You need to connect to some other database to execute the DROP DATABASE command.

So, you should be extra cautious when performing this operation.

PostgreSQL DROP DATABASE Syntax:

Here:-

• IF EXISTS: This is an optional parameter. In case of the database does not exist, a warning is displayed instead of an error
• name: mention the DB name that you want to drop

Let’s see an PostgreSQL drop database command line example in action

PostgreSQL Drop Database Using SQL Shell(Command Line)

Below is a step by step process to drop database in Postgres command line:

Step 1) Use command \l to determine the currently available database.

Step 2) To drop database in Postgres, enter command

Use command \l to check whether DB is deleted

Step 3) Try to drop the same database again, you will get an error.

Step 4) Drop the database with IF exists clause and you get a warning

PostgreSQL Drop Database PgAdmin (GUI)

Step 1) Right Click on database “guru99” and click “Delete/Drop.”

Step 2) Click OK on the confirmation pop-up

Database is deleted.

dropdb command

The dropdb command allows you to delete database in PostgreSQL remotely. But, the user must be the owner of the database in order use PostgreSQL delete database command to delete that database.

Syntax:

Option	Description
-e	Echo commands that dropdb creates and sends to the server.
-i	Display a verification prompt before performing any destructive job
-V	Use this option to print the dropdb version.
–help	Provide help with dropdb command-line arguments.
-h host	Helps you to specify the name of the host of the system on which the server is currently running.
-p port	Option allows you to specify the Unix domain socket file extension on which the server is establishing connections.
–if exists	If exists will show an error instead of a warming if the DB does not exit
-U username	User name to connect as.
-w	Use this option if you don’t want a password prompt
-W	Use this parameter to prompt for a password before dropping the database.
maintenance db-=dbname	the database name of the to connect to drop the target database.

PostgreSQL DROP DATABASE Example

We want to destroy a database guru99 with the help of a server on host rome, port 4565, with verification and you need to use following drop database PostgreSQL command:

Database “guru99” will be permanently deleted.

Are you sure? (y/n) if you select y, then you can

Источник

dropdb

Synopsis

dropdb [ connection-option. ] [ option. ] dbname

Description

dropdb destroys an existing PostgreSQL database. The user who executes this command must be a database superuser or the owner of the database.

dropdb is a wrapper around the SQL command DROP DATABASE. There is no effective difference between dropping databases via this utility and via other methods for accessing the server.

Options

dropdb accepts the following command-line arguments:

Specifies the name of the database to be removed.

Echo the commands that dropdb generates and sends to the server.

Issues a verification prompt before doing anything destructive.

Print the dropdb version and exit.

Show help about dropdb command line arguments, and exit.

dropdb also accepts the following command-line arguments for connection parameters:

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket.

Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections.

User name to connect as.

Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.

Force dropdb to prompt for a password before connecting to a database.

This option is never essential, since dropdb will automatically prompt for a password if the server demands password authentication. However, dropdb will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt.

Environment

Default connection parameters

This utility, like most other PostgreSQL utilities, also uses the environment variables supported by libpq (see Section 31.13).

Diagnostics

In case of difficulty, see DROP DATABASE and psql for discussions of potential problems and error messages. The database server must be running at the targeted host. Also, any default connection settings and environment variables used by the libpq front-end library will apply.

Examples

To destroy the database demo on the default database server:

To destroy the database demo using the server on host eden, port 5000, with verification and a peek at the underlying command:

Источник