PostgreSQL

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

PostgreSQL создана на основе некоммерческой СУБД Postgres, разработанной как open-source проект в Калифорнийском университете в Беркли. К разработке Postgres, начавшейся в 1986 году, имел непосредственное отношение Майкл Стоунбрейкер, руководитель более раннего проекта Ingres, на тот момент уже приобретённого компанией Computer Associates. Название расшифровывалось как «Post Ingres», и при создании Postgres были применены многие ранние наработки.

Стоунбрейкер и его студенты разрабатывали новую СУБД в течение восьми лет с 1986 по 1994 годы. За этот период в синтаксис были введены процедуры, правила, пользовательские типы и другие компоненты. В 1995 году разработка снова разделилась: Стоунбрейкер использовал полученный опыт в создании коммерческой СУБД Illustra, продвигаемой его собственной одноимённой компанией (приобретённой впоследствии компанией Informix), а его студенты разработали новую версию Postgres — Postgres95, в которой язык запросов POSTQUEL — наследие Ingres — был заменен на SQL.

Разработка Postgres95 была выведена за пределы университета и передана команде энтузиастов. Новая СУБД получила имя, под которым она известна и развивается в текущий момент — PostgreSQL.

Курсы

• DBA1. Администрирование PostgreSQL. Базовый курс
• DBA2. Администрирование PostgreSQL. Настройка и мониторинг
• DBA3. Администрирование PostgreSQL. Резервное копирование и репликация
• DEV1. Разработка серверной части приложений PostgreSQL. Базовый курс
• DEV2. Разработка серверной части приложений PostgreSQL. Расширенный курс

Статьи

• Основы Postgres для администраторов баз данных Oracle (2022 год)

• PostgreSQL - от теории к практике (2020 год)

Документация

• Документация PostgreSQL и Postgres Pro
• Книга рецептов для СУБД PostgreSQL
  • Структура файлов и каталогов сервера СУБД
• Рогов, Е.В. - PostgreSQL изнутри. М.: ДМК Пресс, 2022. - 660 с.
• Рогов Е. В. - PostgreSQL 15 изнутри. — М.: ДМК Пресс, 2023. — 662 с.

Телеграм-каналы

• pgsql – PostgreSQL Чат русскоязычного сообщества PostgreSQL
• Postgres Pro Edu Актуальные новости о студенческих мероприятиях от компании Postgres Professional

YouTube

• Варианты построения отказоустойчивых систем на основе PostgreSQL

Инструменты для администрирования, разработки

• pgAdmin

pgAdmin is the most popular and feature rich Open Source administration and development platform for PostgreSQL, the most advanced Open Source database in the world. pgAdmin may be used on Linux, Unix, macOS and Windows to manage PostgreSQL and EDB Advanced Server 10 and above.

• DataGrip

DataGrip is a database management environment for developers. It is designed to query, create, and manage databases. Databases can work locally, on a server, or in the cloud. Supports MySQL, PostgreSQL, Microsoft SQL Server, Oracle, and more. If you have a JDBC driver, add it to DataGrip, connect to your DBMS, and start working.

• DBeaver

Free multi-platform database tool for developers, database administrators, analysts and all people who need to work with databases. Supports all popular databases: MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Apache Hive, Phoenix, Presto, etc.

Установка сервера СУБД

# Create the file repository configuration:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# Update the package lists:
sudo apt-get update
# Install the latest version of PostgreSQL.
# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
sudo apt-get -y install postgresql

В СУБД PostgreSQL уровень изоляции транзакций по умолчанию READ COMMITED.  Задается параметром default_transaction_isolation.

SHOW default_transaction_isolation;

Создание кластера
pg_createcluster 14 standby1

Запуск кластера
pg_ctlcluster 14 standby1 start

Проверка состояния кластеров на хосте

postgres@singularity:~$ pg_lsclusters
Ver Cluster  Port Status Owner    Data directory                  Log file
14  main     5432 online postgres /var/lib/postgresql/14/main     /var/log/postgresql/postgresql-14-main.log
14  standby1 5433 online postgres /var/lib/postgresql/14/standby1 /var/log/postgresql/postgresql-14-standby1.log

Журналы сервера

/var/log/postgresql/postgresql-14-main.log
/var/log/postgresql/postgresql-14-standby1.log

Резервное копирование

COPY — копировать данные между файлом и таблицей
postgres=# \c zabbix
Вы подключены к базе данных "zabbix" как пользователь "postgres".
zabbix=# \! mkdir /home/postgres/backup/
zabbix=# copy users to '/home/postgres/backup/zabbix.users.text' (format text);
COPY 3
zabbix=# copy users to '/home/postgres/backup/zabbix.users.csv' (format csv);
COPY 3
zabbix=# copy users to '/home/postgres/backup/zabbix.users.bin' (format binary);
COPY 3

Список имён всех таблиц БД zabbix
select table_name from information_schema.tables
where table_catalog='zabbix' and table_schema='public'
order by 1;

Выгрузка всех таблиц БД
\с zabbix
select 'copy '||schemaname||'.'||tablename||' to ''/tmp/'||schemaname||'_'||tablename||'.txt''' from pg_tables where schemaname = 'public' \gexec

pg_dump — выгрузить базу данных PostgreSQL в виде скрипта или в архивном формате
postgres@singularity:~$  pg_dump zabbix > zabbix.sql
postgres@singularity:~$ ls -l
итого 54324
drwxrwxr-x 2 postgres postgres     4096 апр 14 05:24 backup
-rw-rw-r-- 1 postgres postgres 55616031 апр 14 06:25 zabbix.sql

Восстановление в новую БД
postgres@singularity:~$ psql
psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1+b1))
postgres=# create database newdb;
CREATE DATABASE
postgres@singularity:~$ psql -d newdb -f zabbix.sql

Выгрузка базы данных в специальном формате:
postgres@singularity:~$ pg_dump -Fc zabbix > zabbix.dump
postgres@singularity:~$ ls -l
итого 68404
drwxrwxr-x 2 postgres postgres     4096 апр 14 05:24 backup
-rw-rw-r-- 1 postgres postgres 14415122 апр 14 06:34 zabbix.dump
-rw-rw-r-- 1 postgres postgres 55616031 апр 14 06:25 zabbix.sql

Выгрузка базы данных в формате каталога:
postgres@singularity:~$ pg_dump -Fd zabbix -f dumpdir
postgres@singularity:~$ ls -l
итого 68408
drwxrwxr-x 2 postgres postgres     4096 апр 14 05:24 backup
drwx------ 2 postgres postgres     4096 апр 14 06:44 dumpdir
-rw-rw-r-- 1 postgres postgres 14415122 апр 14 06:34 zabbix.dump
-rw-rw-r-- 1 postgres postgres 55616031 апр 14 06:25 zabbix.sql
postgres@singularity:~$ du dumpdir
14708   dumpdir

Копия кластера БД
pg_dumpall — выгрузить кластер баз данных PostgreSQL в формате скрипта
postgres@singularity:~$ pg_dumpall -f main
postgres@singularity:~$ ls -l
drwxrwxr-x 2 postgres postgres      4096 апр 14 05:24 backup
drwx------ 2 postgres postgres      4096 апр 14 06:44 dumpdir
-rw-rw-r-- 1 postgres postgres 112068105 апр 14 06:55 main



Настройка репликации main, beta (вариант 1, оба кластера работают на одном хосте)

Действия на кластере main

sudo nano /etc/postgresql/14/main/postgresql.conf

listen_addresses = '<IP-адрес сервера PostgreSQL с кластером main>'

sudo -u postgres psql

CREATE ROLE replicator WITH REPLICATION PASSWORD 'пароль replicator' LOGIN;

sudo nano /etc/postgresql/14/main/pg_hba.conf

host  replication replicator 127.0.0.1/32  scram-sha-256

sudo systemctl restart postgresql@14-main

SHOW data_directory;

Задать параметры в /etc/postgresql/14/main/postgresql.conf

sudo nano /etc/postgresql/14/main/postgresql.conf

wal_level = replica
wal_log_hints = on
archive_mode = on
archive_command = 'cp -i %p /var/lib/postgresql/archive/main/%f'
max_wal_senders = 100
hot_standby = on

Перезапустить кластер main

pg_ctlcluster stop 14 main

pg_ctlcluster start 14 main

sudo -u postgres pg_basebackup -h '127.0.0.1' -p 5432 -U replicator -D /var/lib/postgresql/14/beta/ -Fp -Xs -R

Здесь:

• -h '127.0.0.1' -p 5432 -U replicator - параметры соединения с кластером main,
• -D /var/lib/postgresql/14/beta/ - корневой каталог для хранения файлов данных кластера beta,
• -Fp - устанавливает формат вывода (p = plain). Записывает выводимые данные в обычные файлы, сохраняя структуру каталогов данных и табличных пространств как на исходном сервере. Если в кластере нет дополнительных табличных пространств, вся база будет помещена в заданный каталог. Иначе основной каталог хранения данных будет помещён в целевой каталог, а все остальные табличные пространства — в те же абсолютные пути, в которых они располагаются на исходном сервере. (Чтобы изменить эти пути, воспользуйтесь параметром --tablespace-mapping). Это формат по умолчанию.
• -Xs - передавать журнал предзаписи в процессе создания резервной копии. При выборе этого метода открывается второе соединение к серверу, через которое будет передаваться журнал предзаписи параллельно с созданием копии. Таким образом, этот метод требует использования не одного, а двух соединений репликации, но если клиент будет успевать получать данные журнала предзаписи, на исходном сервере не потребуется сохранять дополнительные журналы.

  Когда используется формат tar, файлы журнала предзаписи сохраняются в отдельном архиве с именем pg_wal.tar.

  Это значение по умолчанию.

• -R - Создать файл standby.signal и добавить параметры конфигурации в файл postgresql.auto.conf в целевом каталоге (или внутри архива, если используется формат tar). Это упрощает настройку ведомого сервера при восстановлении этой копии.

  В файл postgresql.auto.conf будут записаны параметры соединения и слот репликации, если его использует pg_basebackup, так что впоследствии при потоковой репликации будут использоваться те же параметры.

sudo -u postgres psql

SELECT client_addr, state FROM pg_stat_replication;

Output

client_addr  | state

------------------+-----------

<<IP-адрес сервера PostgreSQL с кластером beta> | streaming

SELECT * FROM pg_stat_replication \gx

postgres@singularity:~$ psql

psql (14.3 (Ubuntu 14.3-1.pgdg20.04+1))
Введите "help", чтобы получить справку.

postgres=# SELECT * FROM pg_stat_replication \gx
-[ RECORD 1 ]----+------------------------------
pid              | 300335
usesysid         | 40170
usename          | replicator
application_name | 14/beta
client_addr      | 127.0.0.1
client_hostname  |
client_port      | 41576
backend_start    | 2022-06-08 17:37:37.66505+00
backend_xmin     |
state            | streaming
sent_lsn         | 16/A7EE29B0
write_lsn        | 16/A7EE29B0
flush_lsn        | 16/A7EE29B0
replay_lsn       | 16/A7EE29B0
write_lag        | 00:00:00.000256
flush_lag        | 00:00:00.000545
replay_lag       | 00:00:00.000592
sync_priority    | 0
sync_state       | async
reply_time       | 2022-06-08 17:53:19.371493+00

Действия на кластере beta

sudo -u postgres pg_dropcluster 14 beta

sudo -u postgres rm -r /var/lib/postgresql/14/beta

sudo -u postgres mkdir /var/lib/postgresql/14/beta

sudo -u postgres chmod 700 /var/lib/postgresql/14/beta

su -

chown postgres:postgres /var/lib/postgresql/14/beta/*

postgres@singularity:~$ mkdir /etc/postgresql/14/beta
postgres@singularity:~$ cp /etc/postgresql/14/main/postgresql.conf /etc/postgresql/14/beta/postgresql.conf

postgres@singularity:~$ mkdir /var/lib/postgresql/archive/beta/

postgres@singularity:~$ mkdir /etc/postgresql/14/beta/conf.d

nano /etc/postgresql/14/beta/postgresql.conf

• заменить main на beta по всему файлу

cp /etc/postgresql/14/main/pg_hba.conf /etc/postgresql/14/beta/pg_hba.conf

cp /etc/postgresql/14/main/pg_ident.conf /etc/postgresql/14/beta/pg_ident.conf

Задать параметры в /etc/postgresql/14/beta/postgresql.conf

wal_level = replica

wal_log_hints = on

archive_mode = on

archive_command = 'cp -i %p /var/lib/postgresql/archive/beta/%f'

max_wal_senders = 100

hot_standby = on

sudo -u postgres pg_createcluster 14 beta

sudo systemctl restart postgresql@14-beta

Курсы для разработчика серверной части

Источники

• PostgreSQL Tutorial – Comprehensive Postgresql Tutorial
  • PostgreSQL PL/pgSQL (postgresqltutorial.com)

DEV1 Разработка серверной части приложений PostgreSQL 12. Базовый курс (postgrespro.ru)

• Табличное пространство (tablespace)
• Слои (forks) и файлы 

Репозитории GitHub

• Репозиторий GitHub TestPostgre - тестирование PostgresPro на учебной машине student.lan (Oracle VM, PostgreSQL 12).
• Репозиторий GitHub PoolPay - пробная разработка (Oracle VM, Ubuntu Server 22.04, PostgreSQL 15).
• Репозиторий postgresql

MVCC - Multiversion Concurrency Control, многоверсионное управление конкурентным доступом

• Документация Postgres Pro
• Bruce Momjian -
  • MVCC Unmasked
  • Unlocking the Postgres Lock Manager (Хабр)
• Hironobu Suzuki
  
  • The Internals of PostgreSQL for database administrators and system developers

Как настроить физическую потоковую репликацию с PostgreSQL на Ubuntu Server

Источники:

• DBA3 Настройка репликации без архива
• Трансляция журналов на резервные серверы

• Репликация в PostgreSQL без проблем

Заметки к книге Рогов Е. В. Р59 PostgreSQL 15 изнутри. — М.: ДМК Пресс, 2023. — 662 с. ISBN 978-5-93700-178-8

В книге рассматривается внутреннее устройство СУБД PostgreSQL: детали реализации многоверсионности и изоляции на основе снимков данных, включая процедуру очистки неактуальных версий строк; буферный кеш и журнал предзаписи; использование блокировок различных уровней; планирование и выполнение SQL-запросов; принципы расширяемости и особенности имеющихся индексных методов доступа. Большое внимание уделяется возможностям, предоставляемым для самостоятельного изучения механизмов функционирования PostgreSQL. В настоящем издании учтены замечания читателей и исправлены опечатки, а также отражены изменения, произошедшие в версии PostgreSQL 15. Сайт книги: https://postgrespro.ru/education/books/internals. Для администраторов и программистов.

Глава 11. Режимы журнала

• Утилита pg_test_fsync - подобрать наилучший вариант wal_sync_method для Postgres Pro (исходник).

Примеры выполнения.

1. student.lan (Oracle VM, xubuntu, диск ВМ на внешнем диске)

postgres@student:/etc/postgresql/12/main$ /usr/lib/postgresql/12/bin/pg_test_fsync
5 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.

Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync is Linux's default)
        open_datasync               359,407 ops/sec    2782 usecs/op
        fdatasync                       372,340 ops/sec    2686 usecs/op
        fsync                               143,684 ops/sec    6960 usecs/op
        fsync_writethrough         n/a
        open_sync                      145,531 ops/sec    6871 usecs/op

Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync is Linux's default)
        open_datasync                177,764 ops/sec    5625 usecs/op
        fdatasync                         321,705 ops/sec    3108 usecs/op
        fsync                                121,483 ops/sec    8232 usecs/op
        fsync_writethrough          n/a
        open_sync                       71,018 ops/sec   14081 usecs/op

Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB in different write
open_sync sizes.)
         1 * 16kB open_sync write           125,003 ops/sec    8000 usecs/op
         2 *  8kB open_sync writes           65,584 ops/sec   15248 usecs/op
         4 *  4kB open_sync writes           32,748 ops/sec   30536 usecs/op
         8 *  2kB open_sync writes           15,574 ops/sec   64211 usecs/op
        16 *  1kB open_sync writes            7,363 ops/sec  135807 usecs/op

Test if fsync on non-write file descriptor is honored: (If the times are similar, fsync() can sync data written on a different descriptor.)
        write, fsync, close                 121,703 ops/sec    8217 usecs/op
        write, close, fsync                 129,187 ops/sec    7741 usecs/op

Non-sync'ed 8kB writes:
        write                            153025,891 ops/sec       7 usecs/op

2. VPS singularity.lytkins.ru (BeGet, Ubuntu 22.04.1).

postgres@singularity:~$ /usr/lib/postgresql/15/bin/pg_test_fsync
5 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.

Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync is Linux's default)
        open_datasync                 9174.853 ops/sec     109 usecs/op
        fdatasync                          8987.786 ops/sec     111 usecs/op
        fsync                                 8339.495 ops/sec     120 usecs/op
        fsync_writethrough           n/a
        open_sync                        8348.133 ops/sec     120 usecs/op

Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync is Linux's default)
        open_datasync                 4622.513 ops/sec     216 usecs/op
        fdatasync                          8352.103 ops/sec     120 usecs/op
        fsync                                 7935.690 ops/sec     126 usecs/op
        fsync_writethrough           n/a
        open_sync                        4478.802 ops/sec     223 usecs/op

Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB in different write
open_sync sizes.)
         1 * 16kB open_sync write       8280.727 ops/sec     121 usecs/op
         2 *  8kB open_sync writes         4340.611 ops/sec     230 usecs/op
         4 *  4kB open_sync writes         2191.251 ops/sec     456 usecs/op
         8 *  2kB open_sync writes         899.303 ops/sec    1112 usecs/op
        16 *  1kB open_sync writes        487.776 ops/sec    2050 usecs/op

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written on a different descriptor.)
        write, fsync, close                8202.338 ops/sec     122 usecs/op
        write, close, fsync                7979.933 ops/sec     125 usecs/op

Non-sync'ed 8kB writes:
        write                           1428417.859 ops/sec       1 usecs/op