Выжимки по администрированию postgres

  • 9 February 2019
  • taku

Создание и обслуживание кластера

Создать кластер 
pg_createcluster 9.6 <clustername> -d <dir> 

пример: pg_createcluster 9.6 bd_01 -d /bd/bd_01

Просмотр кластеров pg_lsclusters
изменить порт кластера можно в конфиге /etc/postgresql/9.6/bd_01

Рестарт(он же старт и стоп) 
pg_ctlcluster 9.6 bd_01 restart

Удалить кластер 
pg_dropcluster 9.6 bd_01

su postgres если иное не настроена в конфиге кластера
psql -p xxxx(порт кластера) 

Работа с пользователями и правами

Создать БД db_test
CREATE DATABASE db_test;

Создать пользователя user_test
CREATE USER user_test WITH password 'test';

Создать БД db_test и назначить владельцем пользователя traccar_user
CREATE DATABASE db_test OWNER user_test;

Добавить права на бд db_test пользователю user_test
GRANT ALL privileges ON DATABASE db_test TO user_test;

Добавить супер права на постргресс пользователю
ALTER USER user_test WITH SUPERUSER;

Добавить другие права на роль более современная тема чем юзер
ALTER ROLE user_test WITH CREATEDB REPLICATION;
Есть нужно убрать роли то добавляем NOCREATEDB NOREPLICATION;

Просмотреть Список пользователей:
   select * from pg_shadow;

Просмотреть Список баз данных:
   select * from pg_database;

Просмотреть список ролей:
   select * from pg_roles;

Удалить БД 
DROP DATABASE test_db;

Удалить пользователя 
DROP USER traccar_user;

подключиться к бд test_db на удаленном хосте 192.168.10.250 под пользователем user_test
psql -p 5433 -U user_test -W -d test_db -h 192.168.10.250

Посмотреть сколько занимает база данных

SELECT pg_size_pretty(pg_database_size('test_db'));

Сделать удаленный запрос к мастеру со слейва и узнать сколько она занимает главное не забыть -c (комманд)

psql -h 192.168.10.250 -p 5433 -U user_test -W -d postgres -c "SELECT pg_size_pretty(pg_database_size('test_db'));"

Работа с кластерами

Получить данные по репликам на мастере
select *,pg_xlog_location_diff(s.sent_location,s.replay_location) byte_lag FROM pg_stat_replication s;
psql -h 192.168.10.250 -p 5433 -U user_test -W -d postgres -c "select *,pg_xlog_location_diff(s.sent_location,s.replay_location) byte_lag FROM pg_stat_replication s;"

проверить когда была последняя синхрон с мастера выполняется на слейве
select now()-pg_last_xact_replay_timestamp();

приостановить репликацию выполняется на слейве
select pg_xlog_replay_pause();

запустить репликацию выполняется на слейве
select pg_xlog_replay_resume();

Резервное копирование БД

Сперва устанавливаем pg_probackup

Добавить права для реплики в pg_hba.conf
local    replication    all        trust

Инициализировать каталог резервных копий
pg_probackup init -B /backup

определение копируемого экземпляра 
pg_probackup-9.6 add-instance -B /backup -D /bd/test_db --instance instance-test_db

Прежде чем запустить бэкап нужно убедиться, что реплика свежая и отставание от мастера не большоее в идеале 0 кб ну или хотя бы не больше 50 кб, тут все зависит от бд и настроек, возможно отставание настроено специально в файле recover.conf (находящийся на слейве в каталоге кластера) пример recovery_min_apply_delay = 5min

запустить бэкап
pg_probackup-9.6 backup -B /backup --instance instance-test_db -b FULL --stream -d test_db -p 5433 -U user_test -w --compress-algorithm=zlib --compress-level=9
по окончании будет сообщено что все успешно верифицировано 
INFO: Validate backups of the instance 'instance-test_db'
INFO: Validating backup PMQDEZ
INFO: Backup PMQDEZ data files are valid
INFO: Backup PMQDEZ WAL segments are valid

так же можно проверить на корректность, что все необходимые файлы резервных копий имеются в наличии и что, используя их, можно восстановить кластер баз данных
pg_probackup validate -B /backup --instance instance-test_db

дальше все это хозяйство упаковываем в один фаил

tar -czvf /temp/test_db.tar.gz /backup/

и отправляем по адресу резервного хранилища или копируем на примонтированный диск или отправляем на ftp

после убеждаемся, что объект достиг места назначения, сверяем объем, и удаляем исходный фаил.