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.

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

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.

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

# 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
Запуск сервера СУБД

pg_ctl -w -l /home/postgres/logfile -D /usr/local/pgsql/data  start

В СУБД 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

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

Источники:

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

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

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

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_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/*

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:~$ 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

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

 

 

 

 

 

 

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