Основы PostgreSQL

Содержание
Введение
Установка
Проверка версии
Проверка статуса
Перезапуск PostgreSQL
Зайти в Postgres
После установки
\l: Список существующих БД
Создание БД
Удалить базу данных
Проверка кодировки
\c: Перейти в БД
Подключение к БД
Список активных подключений к БД
Переименовать БД
UPDATE: Изменение значения
Выйти из консоли PostgreSQL
Узнать текущую схему
Системные переменные
Разрешить внешние подключения
Похожие статьи

Введение

PostgreSQL (произносится «Пост-Грэс-Кью-Эл») — свободная объектно-реляционная система управления базами данных (СУБД).

Существует в реализациях для множества UNIX -подобных платформ, включая AIX, различные BSD-системы, HP-UX, IRIX, Linux , macOS, Solaris/OpenSolaris, Tru64, QNX, а также для Microsoft Windows .

Установка

Проверка версии

После установки проверьте версию установленного PostgreSQL

postgres -V

postgres (PostgreSQL) 9.2.24

или

psql -V

psql (PostgreSQL) 13.7

Проверить статус

PostgreSQL 9.6

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 ${PGDATA} (code=exited, status=1/FAILURE) Mar 28 11:45:58 host.andrei.com systemd[1]: Starting PostgreSQL 11 database server... Mar 28 11:45:58 host.andrei.com systemd[1]: postgresql-11.service: control process exited, code=exited status=1 Mar 28 11:45:58 host.andrei.com systemd[1]: Failed to start PostgreSQL 11 database server. Mar 28 11:45:58 host.andrei.com systemd[1]: Unit postgresql-11.service entered failed state. Mar 28 11:45:58 host.andrei.com systemd[1]: postgresql-11.service failed.

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 ${PGDATA} (code=exited, status=0/SUCCESS) Main PID: 15904 (postmaster) Tasks: 8 CGroup: /system.slice/postgresql-11.service ├─15904 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/ ├─15906 postgres: logger ├─15908 postgres: checkpointer ├─15909 postgres: background writer ├─15910 postgres: walwriter ├─15911 postgres: autovacuum launcher ├─15912 postgres: stats collector └─15913 postgres: logical replication launcher Mar 28 11:45:58 host.andrei.com systemd[1]: Starting PostgreSQL 11 database server... Mar 28 11:45:58 host.andrei.com postmaster[15904]: 2022-09-20 12:19:45.397 EEST [15904] LOG: listening on IPv6 address "::1", port 5432 Mar 28 11:45:58 host.andrei.com postmaster[15904]: 2022-09-20 12:19:45.397 EEST [15904] LOG: listening on IPv4 address "127.0.0.1", port 5432 Mar 28 11:45:58 host.andrei.com postmaster[15904]: 2022-09-20 12:19:45.401 EEST [15904] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" Mar 28 11:45:58 host.andrei.com postmaster[15904]: 2022-09-20 12:19:45.405 EEST [15904] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" Mar 28 11:45:58 host.andrei.com postmaster[15904]: 2022-09-20 12:19:45.414 EEST [15904] LOG: redirecting log output to logging collector process Mar 28 11:45:58 host.andrei.com postmaster[15904]: 2022-09-20 12:19:45.414 EEST [15904] HINT: Future log output will appear in directory "log". Mar 28 11:45:58 host.andrei.com systemd[1]: Started PostgreSQL 11 database server.

Перезапуск PostgreSQL

Для версии 9

systemctl restart postgresql

Для более современных версий, например 11

systemctl restart postgresql-11

Зайти в Postgres

Когда postgresql-11 находится имеет статус active можно зайти в postgres выполнив

sudo su - postgres

Last login: Thu Mar 28 11:45:58 EEST 2024 on pts/0 -bash-4.2$

psql

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)

В более современных версиях результат будет содеражть номер версии

---------------------------------------- /var/lib/pgsql/11/data/postgresql.conf (1 row)

Изучим содержимое

/var/lib/pgsql/data

ll /var/lib/pgsql/data/

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 5432 -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

Получить данные о подключении

\conninfo

You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

Список существующих БД

Список уже существующих на сервере баз данных можно получить командой

\l

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';

CREATE DATABASE

Создадим базу данных heihei с кодировкой utf8 и укажем значения для Collate, Ctype и Template

CREATE DATABASE "heihei" WITH OWNER "postgres" ENCODING
'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C' TEMPLATE = template0;

CREATE DATABASE

Проверим, что получилось

\l

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 сделаю

locale -a

То получу сразу четыре доступные кодировки

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;

CREATE DATABASE

Удалить базу данных

Чтобы удалить базу данных воспользуйтесь командой

DROP DATABASE db_name;

DROP DATABASE

Проверить кодировки

Проверить кодировку сервера можно командой

SHOW SERVER_ENCODING;

server_encoding ----------------- UTF8 (1 row)

Проверить кодировку клиента

SHOW CLIENT_ENCODING;

client_encoding ----------------- WIN1252 (1 row)

Когда вы находитесь в интерактивном режиме PostgreSQL в консоле появляется префикс

db=>

Где db это название текущей базы данных

Показать адрес текущей директории

\!

[andrei@localhost ~]$

Вернуться в PostgreSQL можно выполнив

exit

db=>

Зайти в БД

Чтобы начать работу с базой данных нужно знать её имя, например пусть Вам нужна база даныых, которую назвали просто HeiHei_ru_DB

Воспользуйтесь командой

\c 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

Чтобы посмотреть список таблиц введите

\dt

List of relations
Schema | Name | Type | Owner
--------+--------+-------+----------
public | person | table | postgres
(1 row)

Чтобы посмотреть всю таблицу person уже можно использовать стандартный

SELECT * FROM person;

Список активных подключений к БД

Изучить подключения к БД можно выполнив

SELECT * FROM pg_stat_activity WHERE datname = 'ИМЯ_БД';

datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_star t | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type -------+---------+-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+---------- --+-------------+--------------+-----------------+------------+-------+-------------+--------------+-------+-------------- (0 rows)

Переименовать БД

ALTER DATABASE старое_имя RENAME TO новое_имя;

Подробности:

Список существующих БД можно получить командой \l

\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';

datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_star t | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type -------+---------+-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+---------- --+-------------+--------------+-----------------+------------+-------+-------------+--------------+-------+-------------- (0 rows)

Закрыть все текущие подключения можно командой

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = ИМЯ_БД;

Непосредственно переименование выполняется командой

ALTER DATABASE db01 RENAME TO db02;

ALTER DATABASE

\l

List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- db02 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | … (4 rows)

Если к базе данных кто-то подключен. Например, вы сами из другого терминала

\c db02

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 | 2024-03-28 11:45:58.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 и проверим создалась ли таблица

\c heihei

You are now connected to database "heihei" as user "postgres".

heihei=# \dt

List of relations Schema | Name | Type | Owner --------+---------------+-------+---------- public | booking_sites | table | postgres (1 row)

Описание таблицы

Если таблица создана какое-то время назад. Вы могли уже забыть, какие конкретно столбцы она содержит.

Для описания таблицы используется команда \d

\d booking_sites

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');

issuer | not_after --------------------------------------------------------------------------------------------+------------------------ CN=192.168.56.174,OU=Andrei CA 3bbce96c-b117-46l0-65a7-f3c04c9f591b | 2027-07-19 16:39:59+03 (1 row)

Обновим значение

aodb=> UPDATE certificates SET not_after='2022-07-22 16:39:59+03' WHERE (serial='2402020070');

UPDATE 1

Проверим результат

aodb=> SELECT issuer, not_after FROM certificates WHERE (serial='2402020070263697801');

issuer | not_after --------------------------------------------------------------------------------------------+------------------------ CN=192.168.56.174,OU=Andrei CA 3bbce96c-b117-46l0-65a7-f3c04c9f591b | 2022-07-22 16:39:59+03 (1 row)

Изменение таблицы

Когда нужно обновить название столбца таблицы используем команду ALTER

Предположим полю website_url не хватает 50 символов. Увеличим длину до 60.

ALTER TABLE booking_sites ALTER column website_url TYPE VARCHAR(60);

Проверим изменилась ли таблица

\d booking_sites

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)

Выйти из консоли

\q

Узнать текущую схему

SELECT current_schema();

current_schema ---------------- public (1 row)

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

$PGDATA - адрес директории data

echo $PGDATA

/var/lib/pgsql/data

Разрешить внешние подключения

Для PostgreSQL 9

vi /var/lib/pgsql/data/postgresql.conf

Для более современных версий PostgreSQL нужно указать директорию, например 11

vi /var/lib/pgsql/11/data/postgresql.conf

# - 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)

vi /var/lib/pgsql/data/pg_hba.conf

Или

vi /var/lib/pgsql/11/data/pg_hba.conf

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

vi /var/lib/pgsql/11/data/postgresql.conf

# - 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'