​​Решил написать небольшую шпаргалку по работе в консоли с PostgreSQL. Последнее время всё чаще и чаще приходится иметь с ней дело по двум причинам:
1️⃣ Все новые установки Zabbix Server делаю на postgresql.
2️⃣ Намного чаще стали использовать postgresql в связке с 1С.

PostgreSQL создаёт отдельного пользователя Linux postgres, так что все команды буду делать с указанием этого пользователя. Я обычно работаю так, хотя никто не мешает сразу авторизоваться под ним и запускать команды напрямую. Второй момент - в зависимости от дистрибутива и пакета установки, бинарники postgresql могут находиться в разных местах и не всегда переменная path будет применена. Так что может понадобиться полный путь к исполняемому файлу (pg_dump, psql и т.д.).

📌 Просмотр списка баз:
# sudo -u postgres psql -U postgres -l

📌 Создание текстового дампа базы данных:
# sudo -u postgres pg_dump -U postgres basa01 \
> ~/basa01.sql

📌 Сжимаем дамп на лету с помощью pigz (умеет жать всеми ядрами):
# sudo -u postgres pg_dump -U postgres basa01 \
| pigz > ~/basa01.sql.gz

📌 Восстановление базы данных в новую базу (сначала создаём её):
# sudo -u postgres createdb -U postgres \
-T template0 basa02
# sudo -u postgres psql -U postgres basa02 \
< ~/basa01.sql

Для автоматических бэкапов могу порекомендовать бесплатную программу SQLBackupAndFTP.

📌 Выход из консоли psql (часто забываю):
$ \q

📌 Создать пользователя:
# sudo -u postgres createuser -U postgres zabbix
Задать пароль:
# sudo -u postgres psql -U postgres -c \
"ALTER USER zabbix PASSWORD 'secpasswd'"

📌 Посмотреть список пользователей:
# sudo -u postgres psql -U postgres -c \
"select * from pg_user"

📌 Дать полные права на базу:
# sudo -u postgres psql -U postgres -c \
"GRANT ALL PRIVILEGES ON DATABASE zabbixdb to zabbix"

📌 Назначить пользователя владельцем базы:
# sudo -u postgres psql -U postgres -c \
"ALTER DATABASE zabbixdb OWNER TO zabbix"

📌 Выполнить очистку (-f) и анализ (-z) базы данных Postgres Pro:
# sudo -u postgres vacuumdb -U postgres -f -z -d basa01

📌 Переиндексировать базу:
# sudo -u postgres reindexdb -U postgres -d basa01

📌 Удалить базу данных:
# sudo -u postgres psql -U postgres -c \
"DROP DATABASE basa01"

#postgresql #bash

Вчера в комментариях к заметке про PostgreSQL посоветовали утилиту для бэкапа - pg_probackup. Я не знал про неё и никогда не использовал, но решил посмотреть. Оказалось, это очень удобный и надёжный вариант бэкапа баз данных PostgreSQL. Расскажу про особенности и варианты использования этой утилиты.

Авторами pg_probackup является небезызвестная компания Postgres Professional - российская компания, разработчик систем управления базами данных. Утилита поддерживает как ванильные версии postgres, так и собственные платные сборки postgres pro.

📌Pg_probackup умеет:

▪ Выполнять полные и инкрементные бэкапы как отдельных серверов, так и кластеров.
▪ Делать инкрементные бэкапы разными способами: разностное копирование, страничное копирование или копирование изменений. Разные способы дают разную нагрузку на систему и занимают разное количество времени.
▪ Выполнять контроль целостности данных и проверку резервных копий без восстановления самих данных.
▪ Управлять политиками хранения резервных копий в соответствии с заданными параметрами.
▪ Самостоятельно сжимать данные без внешних архиваторов.
▪ Работать в режиме клиент-сервер, то есть настраивается сервер хранения pg_probackup, который сам подключается к хостам с postgresql и забирает бэкапы.
▪ Отдельно поддерживает возможность бэкапа произвольных директорий и файлов. Например, директорию с конфигурацией кластера, с логами или какими-то самописными скриптами.
▪ Вести каталог резервных копий с метаданными архивов, данные о которых может отдавать в формате json.

Продукт многофункциональный, который закрывает полностью вопрос с бэкапами, политикой хранения и мониторинга. Получая информацию об архивах в json, очень легко прикрутить мониторинг к тому же Zabbix Server или Prometheus.

Как можно понять из описания, pg_probackup - универсальный инструмент, который подойдёт как для полного бэкапа одиночных баз и серверов, так и кластеров в различных режимах инкрементов. Архивы можно хранить локально или передавать по сети в единый каталог.

У утилиты есть подробное описание на русском языке. Для установки созданы репозитории под все популярные системы, в том числе отечественные.

Примеры запуска и использования приводить не буду, так как всё подробно и по-русски описано в документации. В общем случае сначала надо инициализировать каталог резервных копий, потом добавить туда сервер PostgreSQL, настроить политику хранения, запустить бэкап.

❓Напрашивается важный вопрос. А когда стоит переходить к подобным бэкапам от простых дампов. Однозначного ответа тут нет. Ориентироваться нужно по следующим признакам:
1️⃣ Вам нужна возможность откатить состояние базы на любой момент в прошлом. Тогда дампы вообще не подходят.
2️⃣ Размер базы данных такой, что выполнение дампа занимает значительное время и снижает производительность сервера.
3️⃣ Хочется иметь большую глубину хранения резервных копий, но в полных дампах она будет занимать слишком много места.

По моим прикидкам, если сжатый дамп начинает занимать более 5 Гб, стоит думать о других способах создания и хранения резервных копий БД.

Исходники / Документация

#postgresql #backup