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.
Creating and Deleting a PostgreSQL Database on Ubuntu 16.04
PostgreSQL (pronounced post-gress-Q-L) is a household name for open source relational database management systems.
It is object-relational, meaning that you’ll be able to use objects, classes in database schemas, and the query language. In this tutorial, we will be demonstrating some essentials like how to create, list, and delete a Postgres database.
If you have already installed PostgreSQL on the Ubuntu 16.04 VPS server using our last tutorial, your next step is to create a database using Postgres and the command line. You’ll be accomplishing this task by using the default superuser, postgres, to log in.
Creating a PostgreSQL Database
Step 1: Login as the Postgres User.
Step 2: Enter the PostgreSQL Environment.
With the psql command, you’ll be greeted by its current version and command prompt.
psql (9.5.14)
Type "help" for help.
postgres=#
Step 3: Create the Postgres Database.
Let’s create our first database by typing in the command below. Replace dbname with the database name of your choice.
CREATE DATABASE dbname;
Listing a PostgreSQL Database
Verify Creation of PostgreSQL Database
Using the following command allows us to view the databases in our PostgreSQL instance (you can ignore, delete or utilize the default databases: postgres, template0, template1)
Deleting a PostgreSQL Database
Once you’ve backed up your data, removing your PostgreSQL database is a cinch! The process to delete your Postgres database is similar to creating it, only we will be using the drop command. In my command line example, the database name is dbname. By using the list command in the previous section, you’ll be able to view your databases’ names. Replace dbname with your database’s name in the command below.
DROP DATABASE dbname;
We pride ourselves on being The Most Helpful Humans In Hosting™! Our support staff is always available to assist Liquid Web customers with any Dedicated, Cloud, or VPS server issues 24 hours a day, 7 days a week 365 days a year.
Как удалить базу данных 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.
PostgreSQL
PostgreSQL (произносится «Пост-Грэс-Кью-Эл») — свободная объектно-реляционная система управления базами данных (СУБД).
Существует в реализациях для множества UNIX -подобных платформ, включая AIX, различные BSD-системы, HP-UX, IRIX, Linux , macOS, Solaris/OpenSolaris, Tru64, QNX, а также для Microsoft Windows .
Установка
- Установка в Ubuntu
- Установка в Centos
Проверка версии
После установки проверьте версию установленного PostgreSQL
postgres (PostgreSQL) 9.2.24
psql (PostgreSQL) 13.7
Проверить статус
systemctl status -l postgresql
Вместо 11 укажите вашу версию
systemctl status -l postgresql- 11
Возможны варианты inactive, failed, active
● postgresql-11.service — PostgreSQL 11 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; disabled; vendor preset: disabled) Active: inactive (dead) Docs: https://www.postgresql.org/docs/11/static/
● postgresql-11.service — PostgreSQL 11 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; disabled; vendor preset: disabled) Active: failed (Result: exit-code) since Tue 2022-09-20 12:01:41 EEST; 6s ago Docs: https://www.postgresql.org/docs/11/static/ Process: 12952 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir $
● postgresql-11.service — PostgreSQL 11 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; disabled; vendor preset: disabled) Active: active (running) since Tue 2022-09-20 12:19:45 EEST; 7s ago Docs: https://www.postgresql.org/docs/11/static/ Process: 15899 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir $
Перезапуск PostgreSQL
systemctl restart postgresql
Для более современных версий, например 11
systemctl restart postgresql-11
Зайти в Postgres
Когда postgresql-11 находится имеет статус active можно зайти в postgres выполнив
sudo su — postgres
Last login: Tue Mar 21 20:14:48 EEST 2023 on pts/0 -bash-4.2$
psql (11.17) Type "help" for help. postgres=#
После установки
Расположение файлов с настройками, например, postgresql.conf можно получить выполнив
-bash-4.2$ su — postgres -c «psql -c 'SHOW config_file;'»
В PostgreSQL 9 результат будет таким:
Password: config_file ————————————- /var/lib/pgsql/data/postgresql.conf (1 row)
В более современных версиях результат будет содеражть номер версии
total 48 drwx——. 7 postgres postgres 67 Jun 9 22:54 base drwx——. 2 postgres postgres 4096 Jun 9 23:19 global drwx——. 2 postgres postgres 18 Jun 9 13:54 pg_clog -rw——-. 1 postgres postgres 4371 Jun 10 01:23 pg_hba.conf -rw——-. 1 postgres postgres 1636 Jun 9 13:54 pg_ident.conf drwx——. 2 postgres postgres 58 Jun 10 00:00 pg_log drwx——. 4 postgres postgres 36 Jun 9 13:54 pg_multixact drwx——. 2 postgres postgres 18 Jun 9 14:14 pg_notify drwx——. 2 postgres postgres 6 Jun 9 13:54 pg_serial drwx——. 2 postgres postgres 6 Jun 9 13:54 pg_snapshots drwx——. 2 postgres postgres 25 Jun 10 02:06 pg_stat_tmp drwx——. 2 postgres postgres 18 Jun 9 13:54 pg_subtrans drwx——. 2 postgres postgres 6 Jun 9 13:54 pg_tblspc drwx——. 2 postgres postgres 6 Jun 9 13:54 pg_twophase -rw——-. 1 postgres postgres 4 Jun 9 13:54 PG_VERSION drwx——. 3 postgres postgres 60 Jun 9 13:54 pg_xlog -rw——-. 1 postgres postgres 19889 Jun 10 01:43 postgresql.conf -rw——-. 1 postgres postgres 45 Jun 9 14:14 postmaster.opts -rw——-. 1 postgres postgres 92 Jun 9 14:14 postmaster.pid
Подключение к БД
Чтобы получить доступ к базам данных можно воспользоваться стандартной консолью psql.
Windows:
Если Вы хотите подключиться из bash в Windows не забудьте добавить местоположение psql.exe (у меня это C:\Program Files\PostgreSQL\12\bin) в PATH
Как это делается описано в статье PATH
psql.exe -h localhost -p 5433 -U postgres
Linux:
sudo su — postgres
psql
psql (12.7 (Ubuntu 12.7-0ubuntu0.20.04.1)) Type «help» for help.
Если БД на локальном хосте
psql -h 127.0.0.1 -d DATABASENAME -U DATABASEUSERNAME
Получить данные о подключении
You are connected to database «postgres» as user «postgres» via socket in «/var/run/postgresql» at port «5432».
Список существующих БД
Список уже существующих на сервере баз данных можно получить командой
Name | Owner | Encoding | Collate | Ctype | Access privileges —————+———-+———-+—————————-+—————————-+———————— urn.su | postgres | UTF8 | English_United States.1252 | English_United States.1252 | topbicyle.ru | postgres | UTF8 | English_United States.1252 | English_United States.1252 | postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres test | postgres | UTF8 | English_United States.1252 | English_United States.1252 | (6 rows)
Обратите внимание на столбцы Encoding, Collate и Ctype. Знание кодировок может Вам пригодиться в будущем.
Создать базу данных
Создадим базу данных heihei_ru_db с кодировкой utf8
CREATE DATABASE «heihei_ru_db» WITH OWNER «postgres» ENCODING 'UTF8';
Создадим базу данных heihei с кодировкой utf8 и укажем значения для Collate, Ctype и Template
CREATE DATABASE «heihei» WITH OWNER «postgres» ENCODING
'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C' TEMPLATE = template0;
Проверим, что получилось
Name | Owner | Encoding | Collate | Ctype | Access privileges —————+———-+———-+—————————-+—————————-+———————— heihei | postgres | UTF8 | C | C | urn.su | postgres | UTF8 | English_United States.1252 | English_United States.1252 | topbicyle.ru | postgres | UTF8 | English_United States.1252 | English_United States.1252 | postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres test | postgres | UTF8 | English_United States.1252 | English_United States.1252 | (7 rows)
Если я в bash сделаю
То получу сразу четыре доступные кодировки
C
C.UTF-8
en_US.utf8
POSIX
Но создать БД, например, с en_US.utf8 у меня не получается
CREATE DATABASE «heihei» WITH OWNER «postgres» ENCODING 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8' TEMPLATE = template0;
Приводит к ошибке
ERROR: invalid locale name: «en_US.UTF-8»
Если Вы знаете как решить эту проблему — отпишитесь в комментариях к статье.
UPD: Выполнил такую же команду в Ubuntu и база успешно создана
CREATE DATABASE «new_db» WITH OWNER «postgres» ENCODING 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TEMPLATE = template0;
Удалить базу данных
Чтобы удалить базу данных воспользуйтесь командой
DROP DATABASE db_name;
Проверить кодировки
Проверить кодировку сервера можно командой
server_encoding —————— UTF8 (1 row)
Проверить кодировку клиента
client_encoding —————— WIN1252 (1 row)
Когда вы находитесь в интерактивном режиме PostgreSQL в консоле появляется префикс
Где db это название текущей базы данных
Показать адрес текущей директории
Вернуться в PostgreSQL можно выполнив
Зайти в БД
Чтобы начать работу с базой данных нужно знать её имя, например пусть Вам нужна база даныых, которую назвали просто HeiHei_ru_DB
You are now connected to database «HeiHei_ru_DB» as user «postgres».
Если вы работаете в Linux и база данных находится на том же хосте можно выполнить
psql -h 127.0.0.1 -d DATABASENAME -U DATABASEUSERNAME
Чтобы посмотреть список таблиц введите
List of relations
Schema | Name | Type | Owner
———+———+——-+———-
public | person | table | postgres
(1 row)
Чтобы посмотреть всю таблицу person уже можно использовать стандартный
SELECT * FROM person;
Список активных подключений к БД
Изучить подключения к БД можно выполнив
SELECT * FROM pg_stat_activity WHERE datname = 'ИМЯ_БД';
Переименовать БД
ALTER DATABASE старое_имя RENAME TO новое_имя;
Список существующих БД можно получить командой \l
List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ————+———-+———-+————-+————-+———————— db01 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (4 rows)
Если нужно переименовать БД db01 первым делом нужно выйти из неё.
Для этого можно зайти в другую БД, например, postgres.
Изучить подключения к БД можно выполнив
SELECT * FROM pg_stat_activity WHERE datname = 'db01';
Непосредственно переименование выполняется командой
ALTER DATABASE db01 RENAME TO db02;
List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ————+———-+———-+————-+————-+———————— db02 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | … (4 rows)
Если к базе данных кто-то подключен. Например, вы сами из другого терминала
You are now connected to database "db02" as user "postgres".
postgres=# SELECT datname, pid, backend_start, state FROM pg_stat_activity WHERE datname = 'db02';
datname | pid | backend_start | state ———+——-+——————————-+——- db02 | 21238 | 2023-03-21 20:14:48.503073+03 | idle (1 row)
Все подключения можно закрыть выполнив
postgres=# SELECT pg_terminate_backend (pid) FROM pg_stat_activity WHERE datname = 'db02';
pg_terminate_backend ———————- t (1 row)
Достать значение из БД
Пример — запишем значение из базы данных в файл с помощью echo
echo $ ( psql -h localhost -d db_name -U db_user -c " SELECT host_certificate FROM hosts WHERE ( addresses= '192.168.56.200' ) ; " ) > raw_cert
Содержание базы данных
В предыдущем параграфе мы создали в базе данных heihei таблицу booking_sites
Убедимся в том, что скрипт отработал удачно
Перейдём в базу данных heihei и проверим создалась ли таблица
You are now connected to database «heihei» as user «postgres».
List of relations Schema | Name | Type | Owner ———+—————+——-+———- public | booking_sites | table | postgres (1 row)
Описание таблицы
Если таблица создана какое-то время назад. Вы могли уже забыть, какие конкретно столбцы она содержит.
Для описания таблицы используется команда \d
Table «public.booking_sites» Column | Type | Collation | Nullable | Default —————-+————————+————+———-+——————————————- id | bigint | | not null | nextval('booking_sites_id_seq'::regclass) company_name | character varying(50) | | not null | origin_country | character varying(50) | | not null | age | character varying(3) | | not null | date_of_birth | date | | not null | website_url | character varying(50) | | | Indexes: «booking_sites_pkey» PRIMARY KEY, btree (id)
UPDATE: Изменение значения
Изменим дату годности сертификата в таблице certificates
Изучим текущую ситуацию
aodb=> SELECT issuer, not_after FROM certificates WHERE (serial='2402020070');
aodb=> UPDATE certificates SET not_after='2022-07-22 16:39:59+03' WHERE (serial='2402020070');
aodb=> SELECT issuer, not_after FROM certificates WHERE (serial='2402020070263697801');
Изменение таблицы
Когда нужно обновить название столбца таблицы используем команду ALTER
Предположим полю website_url не хватает 50 символов. Увеличим длину до 60.
ALTER TABLE booking_sites ALTER column website_url TYPE VARCHAR(60);
Проверим изменилась ли таблица
Table «public.booking_sites» Column | Type | Collation | Nullable | Default —————-+————————+————+———-+——————————————- id | bigint | | not null | nextval('booking_sites_id_seq'::regclass) company_name | character varying(50) | | not null | origin_country | character varying(50) | | not null | age | character varying(3) | | not null | date_of_birth | date | | not null | website_url | character varying(60) | | | Indexes: «booking_sites_pkey» PRIMARY KEY, btree (id)
Выйти из консоли
Узнать текущую схему
current_schema —————- public (1 row)
Переменные окружения
$PGDATA — адрес директории data
Разрешить внешние подключения
Для PostgreSQL 9
Для более современных версий PostgreSQL нужно указать директорию, например 11
# — Connection Settings — # listen_addresses = 'localhost' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart) listen_addresses = '*' port = 5432 # (change requires restart)
host all all 0.0.0.0/0 md5
Включить SSL
openssl genrsa -des3 -out server.key 1024
openssl rsa -in server.key -out server.key
chmod 400 server.key
chown postgres.postgres server.key
openssl req -new -key server.key -days 3650 -out server.crt -x509
cp server.crt root.crt
# — SSL — ssl = on #ssl_ca_file = '' ssl_cert_file = '/var/lib/pgsql/11/data/server.crt' #ssl_crl_file = '' ssl_key_file = '/var/lib/pgsql/11/data/server.key'
Подпишитесь на Telegram канал @aofeed чтобы следить за выходом новых статей и обновлением старых