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
- Блог Aristov.tech Аристов Евгений
YouTube
Инструменты для администрирования, разработки
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 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.
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.
Установка сервера СУБД (ОС Ubuntu Server Edition)
Обновлено для PostgreSQL 16
PostgreSQL доступен во всех версиях Ubuntu по умолчанию. Однако Ubuntu создает "моментальные снимки" определенной версии PostgreSQL, которая затем поддерживается на протяжении всего срока службы этой версии Ubuntu. Проект PostgreSQL поддерживает репозиторий Apt, в котором доступны все поддерживаемые версии PostgreSQL.
Ubuntu по умолчанию поддерживает PostgreSQL. Чтобы установить PostgreSQL в Ubuntu, используйте команду apt (или другую команду, поддерживающую apt).:
apt install postgresql
Если версия, включенная в вашу версию Ubuntu, не та, которая вам нужна, вы можете воспользоваться репозиторием PostgreSQL Apt. Этот репозиторий будет интегрирован с вашими обычными системами и системой управления исправлениями, а также обеспечит автоматическое обновление для всех поддерживаемых версий PostgreSQL на протяжении всего срока службы поддержки PostgreSQL.
Репозиторий PostgreSQL Apt поддерживает текущие версии Ubuntu:
- noble (24.04, LTS)
- mantic (23.10, не-LTS)
- jammy (22.04, LTS)
- focus (20.04, LTS)
на следующих архитектурах:
- amd64
- arm64 (только версии LTS)
- ppc64el (только версии LTS)
- s390x (только версии LTS)
Автоматизированная настройка репозитория:
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.shЧтобы вручную настроить репозиторий Apt, выполните следующие действия:
sudo apt install curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt update
sudo apt -y install postgresqlВ СУБД PostgreSQL уровень изоляции транзакций по умолчанию READ COMMITED. Задается параметром default_transaction_isolation.
SHOW default_transaction_isolation;
Создание кластера
pg_createcluster 16 standby1
Creating new PostgreSQL cluster 16/standby1 ...
/usr/lib/postgresql/16/bin/initdb -D /var/lib/postgresql/16/standby1 --auth-local peer --auth-host scram-sha-256 --no-instructions
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgresql/16/standby1 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Barnaul
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
Ver Cluster Port Status Owner Data directory Log file
16 standby1 5434 down postgres /var/lib/postgresql/16/standby1 /var/log/postgresql/postgresql-16-standby1.logЗапуск кластера
pg_ctlcluster 16 main start
pg_ctlcluster 16 standby1 startПроверка состояния кластеров на хосте
postgres@singularity:~$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
15 main 5432 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
16 main 5433 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log
16 standby1 5434 online postgres /var/lib/postgresql/16/standby1 /var/log/postgresql/postgresql-16-standby1.logЖурналы сервера
/var/log/postgresql/postgresql-15-main.log
/var/log/postgresql/postgresql-16-main.log
/var/log/postgresql/postgresql-16-standby1.logПросмотр журналов кластеров PostgreSQL 16
liv@singularity:~$ sudo lnav /var/log/postgresql/postgresql-16*Резервное копирование
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' \gexecpg_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Восстановление в новую БД (порт 5433 - кластер main, PostgreSQL 16)
postgres@singularity:~$ psql -p 5433
psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1))
postgres=# create database newdb;
CREATE DATABASE
postgres=# CREATE USER zabbix;
postgres@singularity:~$ psql -p 5433 -d newdb -f zabbix.sqlВыгрузка базы данных в специальном формате:
postgres@singularity:~$ pg_dump -Fc zabbix > zabbix.dump
postgres@singularity:~$ ls -l zabbix*
-rw-rw-r-- 1 postgres postgres 119927810 Jun 10 09:20 zabbix.dump
-rw-rw-r-- 1 postgres postgres 565082845 Jun 10 08:20 zabbix.sqlВыгрузка базы данных в формате каталога:
postgres@singularity:~$ pg_dump -Fd zabbix -f dumpdir
postgres@singularity:~$ du dumpdir
117700 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
Курсы для разработчика серверной части
Источники
DEV1 Разработка серверной части приложений PostgreSQL 16. Базовый курс (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, многоверсионное управление конкурентным доступом
Как настроить физическую потоковую репликацию с PostgreSQL на Ubuntu Server
Источники:
Заметки к книге Рогов Е. В. Р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/op2. 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/op14.09.2023 Вышла версия PostgreSQL 16
PostgreSQL 16 Installation on Ubuntu 22.04
25.05.2024 Развертывание PostgreSQL 16.3 в Docker
- How To Run PostgreSQL Server in Docker Container
- Install pgAdmin with PostgreSQL database using Docker
liv@singularity:~$ mkdir ~/postgresql && cd ~/postgresql
liv@singularity:~/postgresql$ cat .env.production
# PostgreSQL database
PGDATA=/var/lib/postgresql/data/pgdata
POSTGRES_USER=admin
POSTGRES_PASSWORD=ПарольAdmin
POSTGRES_DB=pg_database
POSTGRES_HOST=localhost
# pgAdmin
PGADMIN_DEFAULT_EMAIL=Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.
PGADMIN_DEFAULT_PASSWORD=ПарольPgAdmin
liv@singularity:~/postgresql$ vim docker-compose.yml
services:
db:
image: postgres:16.3
restart: always
env_file:
- .env.production
ports:
- 54320:5432
volumes:
- db_data:/var/lib/postgresql/data
pgadmin:
image: dpage/pgadmin4
restart: always
ports:
- "8888:80"
env_file:
- .env.production
volumes:
- pgadmin-data:/var/lib/pgadmin
volumes:
db_data:
pgadmin-data:
liv@singularity:~/postgresql$ docker compose up -d
liv@singularity:~/postgresql$ docker compose ps
NAME IMAGE COMMAND SERVICE CREATED STATUS PORTS
postgresql-db-1 postgres:16.3 "docker-entrypoint.s…" db About a minute ago Up About a minute 0.0.0.0:54320->5432/tcp, :::54320->5432/tcp
postgresql-pgadmin-1 dpage/pgadmin4 "/entrypoint.sh" pgadmin About a minute ago Up About a minute 443/tcp, 0.0.0.0:8888->80/tcp, :::8888->80/tcp
liv@singularity:~/postgresql$ docker exec -ti postgresql-db-1 bash
root@24fcb56df9ea:/# psql -V
psql (PostgreSQL) 16.3 (Debian 16.3-1.pgdg120+1)
root@0e228a553c55:/# psql -d pg_database -U admin
psql (16.3 (Debian 16.3-1.pgdg120+1))
Type "help" for help.
pg_database=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
-------------+-------+----------+-----------------+------------+------------+------------+-----------+-------------------
pg_database | admin | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
postgres | admin | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
template0 | admin | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/admin +
| | | | | | | | admin=CTc/admin
template1 | admin | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/admin +
| | | | | | | | admin=CTc/admin
(4 rows)
root@953251a5d6a7:/# /usr/lib/postgresql/16/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 6065.634 ops/sec 165 usecs/op
fdatasync 5889.283 ops/sec 170 usecs/op
fsync 5507.240 ops/sec 182 usecs/op
fsync_writethrough n/a
open_sync 5574.194 ops/sec 179 usecs/op
Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 3095.283 ops/sec 323 usecs/op
fdatasync 5404.476 ops/sec 185 usecs/op
fsync 5017.372 ops/sec 199 usecs/op
fsync_writethrough n/a
open_sync 2658.752 ops/sec 376 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 5530.570 ops/sec 181 usecs/op
2 * 8kB open_sync writes 2751.867 ops/sec 363 usecs/op
4 * 4kB open_sync writes 1434.130 ops/sec 697 usecs/op
8 * 2kB open_sync writes 513.572 ops/sec 1947 usecs/op
16 * 1kB open_sync writes 318.713 ops/sec 3138 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 5472.595 ops/sec 183 usecs/op
write, close, fsync 5299.137 ops/sec 189 usecs/op
Non-sync'ed 8kB writes:
write 1300566.578 ops/sec 1 usecs/op