¶ FerretDB открытая альтернатива MongoDB, построенная на Postgres
FerretDB позволяет вам легко использовать драйверы MongoDB с PostgreSQL в качестве серверной части базы данных. Используйте все инструменты, драйверы, пользовательский интерфейс и тот же язык запросов с открытым исходным кодом.
https://www.ferretdb.com/ - FerretDB — это прокси, который принимает MongoDB-запросы и хранит данные в PostgreSQL, не имея собственного хранилища.
¶ CloudNativePG - оператор Kubernetes, кластера баз данных PostgreSQL
https://cloudnative-pg.io/ CloudNativePG - оператор Kubernetes, который обеспечивает полный жизненный цикл высокодоступного кластера баз данных PostgreSQL с архитектурой «основной/резервный» и встроенной потоковой репликацией.
¶ PostgreSQL с утилитой -contrib:
sudo apt install postgresql postgresql-contribPostgresql-contrib - дополнительные инструменты для работы с базами данных. Например для анализа или администрирования.
PostgreSQL применяет термин «Роль». Практически это тот же аккаунт в Ubuntu. При запуске СУБД роли сервиса привязываются к одноименным аккаунтам в Unix-системах, при наличии роли в PostgreSQL, войти в СУБД можно с аккаунтом Ubuntu. При запуске СУБД генерируется аккаунт postgres, привязываемый к роли PostgreSQL.
¶ Войти в аккаунт:
sudo -i -u postgresили
sudo -u postgres psqlКонсоль Postgres:
psql
psql -d имябазы -U логин -h 1.2.3.4 -p 5432Cтатус подключения:
postgres=# \conninfoВозврат в аккаунт:
postgres=# \q¶ Создание роли
Вывод списка всех пользователей с правами и ролями
\duАккаунт postgres обладает правами администратора.
Чтобы указать имя роли и выдать суперюзера, применим флаг —interactive, createuser добавить новую роль.
createuser --interactiveВариант работы без переходов между аккаунтами:
sudo -u postgres createuser --interactiveЕщё один вариант добавить новую учетную запись
CREATE USER user001 WITH PASSWORD 'mypassword.com';¶ Вывести список пользователей
SELECT * FROM pg_catalog.pg_user;¶ Команда GRANT предоставит привилегии
GRANT ALL ON ALL TABLES IN SCHEMA public TO user001;
- GRANT ALL — синтаксическая команда, позволяющая предоставить пользователю все или административные привилегии;
- ON ALL TABLES — означает предоставление привилегий для всех таблиц;
- IN SCHEMA — для PostgreSQL используется план организации таблиц в БД;
- TO user001 — указывает на учетную запись для эскалации привилегий.
можно отменить:
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM user001;¶ Восстановить пароль или обновить его
ALTER USER user001 WITH PASSWORD 'newpassphrase';¶ Удалить учетную запись
DROP USER user001;¶ Создание базы данных
Любому созданному аккаунту привязывается база данных с идентичным именем, то есть наш созданный tester начнет подключаться к базе данных tester.
Командой createdb добавим БД (поднимем новую базу PostgreSQL на Ubuntu), назвав ее tester:
createdb testerВариант работы без переходов между аккаунтами:
sudo -u postgres createdb testerРабота в консоли PostgreSQL подразумевает наличие аккаунта Ubuntu с именем БД в Postgres.
Добавим аккаунт Ubuntu, используя adduser
sudo adduser tester¶ Удалить базу данных
dropdb tester¶ Показать список баз данных
Войти в psql и дальше команда
\lSQL командой посмотреть список баз
SELECT datname, datistemplate, datallowconn, datconnlimit FROM pg_database;datistemplate — является ли база данных шаблоном;
datallowconn — разрешены ли соединения с базой данных;
datconnlimit — максимальное количество соединений (-1 = без ограничений).
Шаблон template1 используется по умолчанию для создания новых БД. В него можно добавить объекты и расширения, которые будут копироваться в каждую новую базу данных.
Шаблон template0 не должен изменяться. Он нужен как минимум в двух ситуациях. Во-первых, для восстановления БД из резервной копии, выполненной pg_dump (так как в эту копию попадут не только объекты данной БД, но и объекты, установленные в template1). Во-вторых, при создании новой БД с кодировкой, отличной от указанной при инициализации кластера.
¶ Перейти в другую базу
\с newdb¶ Посмотреть список табличных пространств
\db¶ Посмотреть таблицы
\dt¶ Справка в psql
¶ список команд psql
\?¶ переменные psql
\? variables¶ список команд SQL
\h¶ синтаксис команд SQL
\h команда¶ Cбор мусора и анализ данных с помощью VACUUM
psql -d Dbname -U postgres -c "VACUUM FREEZE VERBOSE ANALYZE;"¶ Файл конфигурации postgresql.conf
¶ Находится
/etc/postgresql/13/main¶ После изменения параметров необходимо перечитать файл командой
pg_ctlcluster 13 main reload¶ Увидеть все незакоментированные строки конфигурационного файла, обратиться к представлению pg_file_settings:
SELECT sourceline, name, setting, applied
FROM pg_file_settings
WHERE sourcefile LIKE '/etc/postgresql/13/main/postgresql.conf';¶ Пример если postgress в Docker
SELECT sourceline, name, setting, applied
FROM pg_file_settings
WHERE sourcefile LIKE '/var/lib/postgresql/data/postgresql.conf'; ¶ Файл аутентификации на основе хоста pg_hba.conf
¶ Расположение конфигурационного файла
SHOW hba_file¶ Phppgadmin
PhpPgAdmin позволяет через браузер осуществлять администрирование сервера PostgreSQL, запускать команды SQL и просматривать содержимое таблиц и баз данных.
sudo apt-get install phppgadminВход: http://1.2.3.4/phppgadmin/
¶ Подключаться к phppgadmin с других устройств
phppgadmin.conf в директории /etc/apache2/conf-available и закомментируем строку Require local. Пропишем строку Allow From all.
sudo systemctl restart apache2Задать пароль для пользователя postgres
\password postgresРазрешить доступ
vi /etc/phppgadmin/config.inc.phpНайти строку $conf['extra_login_security'] = true; и заменить значение на false, чтобы войти в Phppgadmin с пользователем postgres.
¶ Резервное копирование и восстановление PostgreSQL
¶ Создание резервных копий - pg_probackup https://postgrespro.ru/docs/postgrespro/16/app-pgprobackup
¶ Официальная быстрая инструкция - https://postgrespro.github.io/pg_probackup/#pbk-quickstart
¶ Официальный курс PostgreSQL где есть pg_probackup - https://postgrespro.ru/education/courses/PGPRO
¶ Pg_probackup - имеет сборки, если используется Postgres 14 то и использовать надо pg_probackup-14
¶ Pg_probackup имеет встроенную систему помощи
pg_probackup-14 help¶ Инициализации каталога резервных копий опция: -B указывает местоположение каталога:
pg_probackup-14 init -B /backup¶ Указать какой сервер бэкапить:
ключ -D указывает путь к каталогу данных копируемого экземпляра (PGDATA);
ключ --instance задает имя экземпляра в каталоге копий.
pg_probackup-14 add-instance -B /backup/ -D /var/lib/postgresql/14/main/ --instance mainпосмотреть файл конфигурации
pg_probackup-14 show-config -B /backup/ --instance main¶ получить полную резервную копию c потоковой доставкой записей WAL:
- ключ -b FULL запрашивает автономную копию;
- ключ --stream включает потоковую доставку WAL (по умолчанию используется файловая архивация);
- ключ --temp-slot создает временный слот репликации, который не даст серверу удалить еще не скопированные сегменты WAL.
Посмотреть в psql значения pg_hba.conf
SELECT name, setting
FROM pg_settings
WHERE name IN ('wal_level','max_wal_senders','max_replication_slots');
SELECT type, database, user_name, address, auth_method
FROM pg_hba_file_rules()
WHERE 'replication' = ANY(database);
pg_probackup-14 backup -b FULL -B /backup/ --instance main --stream --temp-slotпроверить
pg_probackup-14 show -B /backup/ --instance mainпроверить целостность копии вручную с помощью команды validate и указать id командой -i
pg_probackup-14 validate -B /backup/ --instance main -i S6WNOX¶ pg_probackup поддерживает три режима инкрементального резервного копирования: DELTA, PAGE и PTRACK
pg_probackup-14 backup -b DELTA -B /backup/ --instance main --stream --temp-slot¶ Восстановить
pg_probackup-14 restore -B /backup/ --instance main -i S6WPY6¶ wal-g
wal-g — это инструмент для восстановления архивных данных в PostgreSQL
ссылки на инструкции:
https://docs.tantorlabs.ru/tdb/ru/15_2/se1c/wal-g.html
https://timeweb.cloud/tutorials/postgresql/dampy-v-postgresql#wal-g
Создание резервных копий и восстановление из командной строки
https://selectel.ru/blog/postgresql-backup-tools/
¶ Создание резервных копий - утилита pg_dump
¶ Pg_dump — выгрузить базу данных PostgreSQL в виде скрипта или в архивном формате
¶ https://postgrespro.ru/docs/postgrespro/16/app-pgdump
pg_dump c --create - ключ create значит что при восстановлении базы данных , в начале создаться пустая база данных с шаблона template0 - createdb -T template0 newdb
pg_dump -d имябазыданных --create
pg_dump <параметры> <имя базы> > <файл для сохранения копии> pg_dump postgresdb > /tmp/postgresdb.sqlАвторизация под определенным пользователем: можно воспользоваться ключом -U
pg_dump -U user -W password postgresdb > /tmp/postgresdb.sqlCэкономить место на диске, можно сразу же сжимать дамп
pg_dump -U user -W password | gzip postgresdb > /tmp/postgresdb.gzВыгрузка базы данных в специальном формате - custom
pg_dump -Fc postgresdb > /tmp/postgresdb.custom¶ pg_dump — формат directory
Выгрузка базы данных в специальном формате - directory в 5 параллельных потоков
pg_dump -Fd postgresdb -j 5 -f /tmp/postgresdb.directory¶ Выгрузить кластер баз данных PostgreSQL в формате скрипта - pg_dumpall
https://postgrespro.ru/docs/postgresql/16/app-pg-dumpall
pg_dumpall [параметр-подключения...] [параметр...]
pg_dumpall > db.out¶ Создание резервных копий восстановление - обычным копированием
¶ Перед резервным копированием надо остановить сервер
Стандартный путь к каталогу данных
/var/lib/postgresql/14/main¶ Восстановление из бэкапа базы PostgreSQL
Восстановление из дампа, в начале надо создать пустую базу данных и в неё уже восстановить данные
Базу данных для восстановления надо создавать из шаблона template0
psql -d newdb -f backup.sqlКлюч -d это база данных куда будет восстанавливать. Ключ -f файл из которого будем восстанавливать
Восстановление кластера после pg_dumpall
psql -f db.out postgresВосстановление из специального формата - custom https://postgrespro.ru/docs/postgresql/16/app-pgrestore
pg_restore -d newdb --create backup.customВосстановление архива в ту же базу данных, из которой он был выгружен, с предварительным удалением текущего содержимого этой базы данных
pg_restore -d postgresdb --clean --create backup.customДобавить 5 параллельных процессов для ускорения -j 5
pg_restore -d postgresdb --clean --create -j 5 /backup.directory¶ Создание резервных копий - утилита pgBackRest
pgBackRest - это надежное решение для резервного копирования и восстановления для PostgreSQL, которое легко масштабируется до самых больших баз данных и рабочих нагрузок. https://pgbackrest.org/
sudo apt install pgbackrestЧто бы установить самую последнею версию - надо добавить репозиторий Postgres https://www.postgresql.org/download/
pgbackrest help¶ Настройка pgBackRest, файл конфигурации по умолчанию находится в:
sudo vim /etc/pgbackrest.confРепозиторий для хранения резервных копий [global]
Раздел [backup] называется stanza - это конфигурация кластера баз данных PostgreSQL
process-max=2 - сколько ядер CPU использовать
log-level-console=info - отображения информации журнала, предупреждений и ошибок
log-level-file=debug - отображения отладки журнала, деталей, информации, предупреждений и ошибок
start-fast=y - установить контрольную точку и запустить резервное копирование
repo1-retention-full=2 - сколько полный резервных копий будет хранится
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
log-level-console=info
log-level-file=debug
start-fast=y
[backup]
pg1-path=/var/lib/postgresql/14/main¶ Настройка PostgreSQL для pgBackRest
sudo -i -u postgres
psql
alter system set archive_mode = on;
alter system set archive_command = 'pgbackrest --stanza=backup archive-push %p';
alter system set wal_level = replica;Под user postgres - создать stanza (инициализация)
pgbackrest --stanza=backup stanza-createПод user postgres - проверить stanza
pgbackrest --stanza=backup check¶ Полная копия
pgbackrest --stanza=backup --type=full --log-level-console=info backup¶ Инкрементная резервная копия - это копия данных, которые изменились со времени последней полной резервной копии
pgbackrest --stanza=backup --type=incr backupИнформация о бэкапах
pgbackrest info¶ Восстановить PostgreSQL
pgbackrest --stanza=backup restoreДельта восстановление - восстанавливает только те файлы какие надо восстановить
pgbackrest --stanza=backup --delta restore¶ Автоматизация резервного копирования для pgbackrest
Отредактировать crontab пользователя postgres
crontab -u postgres -eДобавить следующую строку в crontab и сохранить ее. Выполняется полное резервное копирование в 04:30 каждую субботу и добавочное резервное копирование с понедельника по пятницу в 03:40
40 04 * * 6 /usr/bin/pgbackrest --stanza=backup --type=full backup
40 03 * * 1-5 /usr/bin/pgbackrest --stanza=backup --type=incr backup¶ Docker PostgreSQL Docker Pgadmin4
¶ PostgreSQL
version: '3.9'
services:
postgres:
image: postgres
restart: unless-stopped
volumes:
- 'postgresql:/var/lib/postgresql/data'
ports:
- '5432:5432'
environment:
- POSTGRES_DB=postgresdb
- POSTGRES_USER=user
- POSTGRES_PASSWORD=password
container_name: postgres
volumes:
postgresql:¶ Docker PostgreSQL + Docker Backup pg_dump
version: '3.9'
services:
db-postgresql:
image: postgres
restart: always
environment:
POSTGRES_USER: user
POSTGRES_PASSWORD: password
POSTGRES_DB: db
PGDATA: /var/lib/postgresql/data/pgdata
POSTGRES_SYNCHRONOUS_COMMIT: 'on'
POSTGRES_WAL_LEVEL: 'replica'
ports:
- published: 5433
target: 5432
volumes:
- postgresql_data:/var/lib/postgresql/data/pgdata
backup:
image: postgres
restart: always
depends_on:
- db-postgresql
volumes:
- ./backup:/backup
command: >
bash -c "while true; do
PGPASSWORD=$$POSTGRES_PASSWORD pg_dump -h db-postgresql -U $$POSTGRES_USER -Fc $$POSTGRES_DB > /backup/$$(date +%Y-%m-%d-%H-%M-%S).dump
echo ""Backup done at $$(date +%Y-%m-%d_%H:%M:%S)""
ls -1 /backup/*.dump | head -n -2 | xargs rm -f
sleep 100
done"
environment:
POSTGRES_USER: user
POSTGRES_PASSWORD: password
POSTGRES_DB: db
volumes:
postgresql_data:¶ Pgadmin4
version: '3.9'
services:
pgadmin4:
image: dpage/pgadmin4
container_name: pgadmin4
restart: unless-stopped
environment:
- PGADMIN_DEFAULT_PASSWORD=password
- PGADMIN_DEFAULT_EMAIL=user@domen.ru
ports:
- '81:80'
volumes:
- 'pgadmin4:/var/lib/pgadmin'
volumes:
pgadmin4:
¶ Adminer
Инструмент администрирования баз данных - легковесный инструмент администрирования MySQL, PostgreSQL, SQLite, MS SQL и Oracle.
version: '2'
services:
adminer:
restart: always
image: dockage/adminer
ports:
- "82:80"¶ pgAgent - планировщик заданий для баз PostgreSQL
https://github.com/YPermitin/PGTools/tree/master/PG-Agent
sudo apt install pgagent¶ Быстрый старт PostgreSQL
¶ Установка на Ubuntu
https://www.postgresql.org/download/linux/ubuntu/
sudo apt install postgresql-15¶ Установить верно время
sudo timedatectl set-timezone Asia/YekaterinburgВойти в аккаунт
sudo -i -u postgres
sudo -u postgres psqlПосмотреть базы
\lГде базы лежат
/var/lib/postgresql/15Под ролью postgres создать каталог backup и далее работать
sudo -i -u postgresИнициализация каталога резервных копий
pg_probackup-15 init -B /var/lib/postgresql/backup/Указать какой сервер бэкапить
pg_probackup-15 add-instance -B /var/lib/postgresql/backup/ -D /var/lib/postgresql/15/main/ --instance mainВключить сжатие --compress-algorithm=zlib
pg_probackup-15 set-config -B /var/lib/postgresql/backup/ --instance main --compress-algorithm=zlibПосмотреть конфигурацию
pg_probackup-15 show-config -B /var/lib/postgresql/backup/ --instance mainВыполнение полной резервной копии
pg_probackup-15 backup -B /var/lib/postgresql/backup/ --instance main -b FULL --stream --temp-slotПосмотреть резервные копии
pg_probackup-15 show -B /var/lib/postgresql/backup/ --instance mainИнкрементальное копирование DELTA — разностное резервное копирование, в которое попадают только изменения.
pg_probackup-15 backup -B /var/lib/postgresql/backup/ --instance main -b DELTA --streamВосстановление последней копии
pg_probackup-15 restore -B /var/lib/postgresql/backup/ --instance mainВосстановление нужной копии -i S7KQZC
pg_probackup-15 restore -B /var/lib/postgresql/backup/ --instance main -i S7KQZC¶ Создать резервный кластер
pg_createcluster --locale ru_RU.UTF-8 15 double -- --data-checksumsВосстановить копию кластера main в кластер double
pg_probackup-15 restore -B /var/lib/postgresql/backup/ --instance main -D /var/lib/postgresql/15/doubleПосмотреть рабочие кластеры
pg_lsclusters¶ Возможность восстановить по времени --recovery-target-time — на заданное время в формате timestamp
Необходимо настроить непрерывное архивирование WAL
- wal_level значение выше minimal
- archive_mode должен иметь значение on или always
- настроенный параметр archive_command
Включить archive_mode
psql
ALTER SYSTEM SET archive_mode = on;Параметр archive_command
ALTER SYSTEM SET archive_command = '/usr/bin/pg_probackup-15 archive-push -B /var/lib/postgresql/backup/ --instance=main --wal-file-path=%p --wal-file-name=%f';Проверить возможность восстановления до заданного момента
pg_probackup-15 validate -B /var/lib/postgresql/backup/ --instance main --recovery-target-time="2024-01-29 01:17:10+05"Восстановления до заданного момента
pg_probackup-15 restore -B /var/lib/postgresql/backup/ --instance main --recovery-target-time="2024-01-29 01:17:10+05"Восстановления до заданного момента с указанием идентификатора резервной копии
pg_probackup-15 restore -B /var/lib/postgresql/backup/ --instance main -i S7ZN2F --recovery-target-time="2024-01-29 01:17:10+05"¶ СУБД Postgres Pro для 1С: новые возможности | Вебинар
¶ Подсказки по администрированию PostgreSQL
В текущей реализации привязки даны для PostgreSQL версии 11.13, но т.к. команды базовые, то большинство из них исправно работает в более старых и новых версиях. Если мне известны, особенности работы команды в других версиях, об этом будет указано в сноске *. Команды установки приведены для Ubuntu 20.04
| Описание | Команда |
|---|---|
| Установка и подключение PosgreSQL (Ubuntu) | |
| Установка |
|
| Запуск postgres с переключением на учетную запись postgres на сервере |
|
| Создание новой роли 'test_db' в интерактивном режиме (без входа в postgres) |
|
| Вывести доступные опции для createuser |
|
| Создание новой базы данных test_db |
|
| Открытие командной строки Postgres с новой ролью |
|
| Подключиться к базе Postgres с именем my_db на хосте 192.168.0.10 порт 5432, как пользователь my_user |
|
| Основные команды psql | |
| Справочник команд psql |
|
| Справончик SQL |
|
| Выход и программы |
|
| подключиться к базе с именем db_name |
|
| Список пользователей |
|
| Список таблиц, представлений, последовательностей, прав доступа к ним |
|
| Индексы |
|
| Последовательности |
|
| Список таблиц |
|
| Список всех таблиц с описанием |
|
| Список всех таблиц, содержащих s в имени |
|
| Представления |
|
| Системные таблицы |
|
| Описание таблицы |
|
| Пересылка результатов запроса в файл |
|
| Список баз данных |
|
| Читать входящие данные из файла |
|
| Открывает текущее содержимое буфера запроса в редакторе |
|
| Описание таблицы с именем table_name |
|
| Запуск команды из внешнего файла, /my/directory/my.sql |
|
| Команда настройки параметров форматирования |
|
| Выводит сообщение |
|
| Выводит список текущих переменных среды |
|
| Устанавливает значение переменной среды VAL=x |
|
| Удаляет значение переменной среды. |
|
| Создание резервных копий | |
| Создание бекапа базы mydb, в сжатом виде (tar) c подробным выводом |
|
| Создание бекапа базы mydb, в виде текстового файла, включая команду создания БД |
|
| Создание бекапа базы mydb, в сжатом виде, с таблицами которые содержат в имени payments |
|
| Дамп данных только одной, конкретной таблицы. |
|
| Создание резервной копии с сжатием в gz |
|
| Восстановление из резервных копий | |
| Восстановление из бекапа в текстовом формате с игнорированием ошибок |
|
| Восстановление из бекапа в текстовом формате с остановкой на первой ошибке |
|
| Восстановление всего бэкапа из tar-архива с выводом информации о процессе |
|
| Восстановление резервной копии БД, сжатой gz |
|
| Утилиты Postgres | |
| Создание БД |
|
| Удаление БД |
|
| Создание роли (пользователя) |
|
| Удаление роли (пользователя) |
|
| Программа предназначенная для решения общих задач управления (запуск, останов, настройка параметров и т.д.) |
|
| Многопользовательский серверный модуль PostgreSQL (настройка уровней отладки, портов, каталогов данных) |
|
| Создание новых кластеров PostgreSQL |
|
| Программа для создания каталогов для вторичного хранения баз данных |
|
| Физическое и аналитическое сопровождение БД |
|
| Архивация и восстановление данных |
|
| Резервное копирование всего кластера PostgreSQL |
|
| Восстановление БД из архивов (.tar, .tar.gz) |
|
| Работа с Postgres непосредственно из командной строки (shell) | |
| Создать таблицу в БД my_db |
|
| Выполнить SQL код размещенный в файле my/path/code.sql, все команды за раз |
|
| Выполнить SQL код размещенный в файле my/path/code.sql, в пошаговом режиме (подтверждая каждую команду |
|
| Выбрать все значения из таблицы my с выводом результата в html-фaйл |
|
| Вывести версию Postgres |
|
| Комады SQL для получения информации о состоянии и обслуживания БД | |
| Вывести топ 10 самых больших таблиц |
|
| Вывести размеры таблиц и индексов всех таблиц с именем 'table_*' ткущей БД |
|
| Получить информацию о всех активных клиентах которые в данный момент подключены к БД |
|
| Вывести топ 10 самых долго выполняющихся запросов |
|
| Вывести запросы занимающие наибольшее время включая короткие но часто выполняющиеся |
|
| Проанализировать план выполнения запроса |
|
| Снижение занимаемого места на диске | |
| Удаление старых логов |
|
| Освободить место для последующих записей (без передаче ОС) в таблице my_table после удаления ненужных записей |
|
| Освободить место с возвращением ОС (БЛОКИРУЕТ БД) в таблице my_table после удаления ненужных записей |
|