Похожие элементы

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.

Курсы

Статьи

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

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

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

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

Восстановление в новую БД (порт 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

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. Режимы журнала

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

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

14.09.2023 Вышла версия PostgreSQL 16

PostgreSQL 16 Installation on Ubuntu 22.04

25.05.2024 Развертывание PostgreSQL 16.3 в 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

 

Добавить комментарий