главная страница Здесь ничего нет

JetBrains DataGrip + шифрованное SSL подключение

Положим у вас запущен север PostgreSQL, в pg_hba.conf которого указано, что войти можно только используя SSL шифрование и вам нужно добраться до сервера продуктом от JetBrains без проверки подлинности сертификата сервера.

Просто так соединится не получится, в параметрах драйвера соединения (вкладка General, ссылка Driver, вкладка Advanced) необходимо указать два параметра:

ssl=true
sslfactory=org.postgresql.ssl.NonValidatingFactory

Или же просто в URL соединения добавить ?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

Huge pages и PostgreSQL

В PostgreSQL 9.4 появилась возможность выделять память используя huge pages.
Попробуем использовать её в Ubuntu Linux 14.04.

Первое что нам понадобится это (относительно) свежее ядро сконфигурированное с поддержкой huge pages, должно быть похоже на это:

uname -srmo
Linux 3.19.0-26-generic x86_64 GNU/Linux

grep HUGETLB /boot/config-$(uname -r)
CONFIG_ARCH_WANT_GENERAL_HUGETLB=y
CONFIG_CGROUP_HUGETLB=y
CONFIG_HUGETLBFS=y
CONFIG_HUGETLB_PAGE=y

Узнаем размер huge page в системе:

grep "Hugepagesize:" /proc/meminfo
Hugepagesize:       2048 kB

Почитав документацию выясним сколько нужно страниц для работы:

sudo head -1 /var/lib/postgresql/9.4/main/postmaster.pid
5600
grep ^VmPeak /proc/5600/status
VmPeak: 34525744 kB

Следовательно установим количество huge pages равным ceil(34525744 / 2048) = 16589

sudo sysctl -w vm.nr_hugepages=16859

Чтобы значение переменной не сбрасывалось при перезагрузке можно записать его в файл /etc/sysctl.conf.

В файле postgresql.conf установим huge_pages = on и перезагрузим сервер БД командой sudo service postgresql restart

Готово.

Установка и использование Barman - менеджера резерных копий и восстановления для PostgreSQL на Ubuntu Linux 14.04

Компания 2ndQuadrant выпускает продукт Barman для PostgreSQL и вот чем он хорош:

  • Дает возможность восстановления на любой момент времени из прошлого (PiTR)
  • С версии 1.4 создает инкрементальные копии экономя место на диске (используя hard links)
  • Возможность указания пути восстановления табличных пространств
  • Инструменты управления резервными копиями создаваемых с разных машин с БД
  • Лицензия GNU GPL 3, следовательно бесплатно для использования

Попробуем использовать

Предварительная настройка серверов

Пусть у нас будут 3 машины: master (192.168.0.1) с основной копией БД, barman (192.168.0.2) где будут хранится копии БД и development (192.168.0.3) на которую будем восстанавливать базу из barman.

Как и в предыдущей заметке будем использовать репозиторий apt.postgresql.org:

echo 'deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main' | sudo tee /etc/apt/sources.list.d/pgdg.list > /dev/null
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update

Настройка barman

Установим саму программу (автоматически создаться пользователь с домашним каталогом /var/lib/barman и файл настроек /etc/barman.conf):

sudo apt-get install barman

Так же можно установить из PyPI предварительно установив pip в систему:

sudo pip install barman

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

Настроим конфигурационный файл /etc/barman.conf, добавив туда секцию с нашим сервером master:

[master]
decription = "Master PostgreSQL server"
ssh_command = ssh postgres@192.168.0.1
conninfo = host=192.168.0.1 user=postgres
network_compression = true
retention_policy = RECOVERY WINDOW OF 3 MONTHS
immediate_checkpoint = true
minimum_redundancy = 3
reuse_backup = link
compression = bzip2

Этим мы описали что необходимо:

  • делать резервные копии с машины master
  • с компрессией сетевых данных
  • с окном восстановления в 3 месяца
  • созданием чекпоинта пред началом резервной копии
  • хранить минимум 3 полные копии базы (без WAL)
  • делать резервные копии используя жесткие ссылки на файлы резервных копий сделанных ранее, в том случае если они не поменялись (дедупликация данных)
  • сжимать данные архиватором bzip2

По умолчанию резервные копии снимаются с работающей базы данных, но возможно использовать и host_standby сервер, для этого нужно установить на него расширение pgespesso и следовать инструкциям.

Сгенерируем ключ пользователю barman для доступа по SSH без паролей к master:

ssh-keygen -t rsa

Добавляем содержимое файла /var/lib/barman/.ssh/id_rsa.pub с машины barman на машину master в файл /var/lib/postgresql/.ssh/authorized_keys

Проверяем что мы можем подключится к master:

ssh postgres@192.168.0.1

То же самое нужно сделать и на master, пользователь postgres@192.168.0.1 должен соединятся с barman@192.168.0.2 по SSH без пароля по ключу.

Выполним cron -e от пользователя barman и добавим следующие задания:

*       *       *       *       *       /usr/bin/barman cron
0       5       *       *       7       [ $(date +\%d) -le 7 ] && /usr/bin/barman backup master
0       23      15      *       *       /usr/bin/barman delete master oldest

Они делают следующее:

  • Каждую минуту выполнять обслуживающие работы barman
  • Каждое первое воскресенье месяца делать полный дамп сервера master
  • Каждое восьмое число месяца удалять самую старую резервную копию (но ничего не делать если число копий меньше или равно значению minimum_redundancy)

Настройка master

Как и ранее, нужно настроить доступ по SSH от postgres@192.168.0.1 к barman@192.168.0.2

Чтобы сервер посылал WAL на машину barman нужно в файле /etc/postgresql/9.4/main/postgresql.conf изменить следующие строки:

archive_mode = on
archive_command = 'rsync -a %p barman@192.168.0.2:/var/lib/barman/master/incoming/%f'

Так же если у вас малое количество изменений в базе то нужно поменять значение archive_timeout, чтобы сервер переключался на новый сегмент WAL чаще и следовательно выполнял archive_command. Минималный (если объем изменений в базе не превышает 16Mb за archive_timeout секунд) объем архивов за месяц без сжатия можно расчитать по формуле ( (60 * 60 * 24 * 365) / 12 / archive_timeout ) * 16Mb, что даст в нашем случае ~700Gb

archive_timeout = 60

Вносим в файл /etc/postgresql/9.4/main/pg_hba.conf изменения позволяющие подключаться к самой базе данных с машины barman:

host    postgres    postgres    192.168.0.2/32  trust

Или делаем авторизацию по паролю и кладем его в /var/lib/barman/.pgpass на машине barman с примерно следующим содержимым:

192.168.0.1:5432:*:postgres:password

Настройка development

Как и ранее, нужно настроить доступ по SSH от barman@192.168.0.2 к postgres@192.168.0.3

Удалим файлы данных установленного кластера чтобы потом его восстановить из barman:

sudo service postgresql stop
sudo rm -rf /var/lib/postgresql/9.4/main

Использование

Проверяем всё ли у нас работает:

barman check all
barman status all

Копируем базу и WAL на development с состоянием на 7 часов утра 1 апреля 2015:

barman recover --target-time "2015-04-01 07:00:00" \
--remote-ssh-command "ssh postgres@192.168.0.3" master latest \
/var/lib/postgresql/9.4/main

Запускаем базу данных на development:

sudo service postgresql start

Получилась копия рабочей базы на нужный момент времени.

Understanding EXPLAIN

У dalibo относительно недавно появилась замечательная книга под названием Understanding EXPLAIN.
В ней рассказывают как понимать вывод команды explain в PostgreSQL и делают это довольно развернуто.

Настройка streaming replication в PostgreSQL 9.4 на Ubuntu Linux 14.04

Предварительная настройка серверов

Предположим у нас в сети будут 2 машины, обозначим их master (192.168.0.1) и slave (192.168.0.2). Приступим.

Подключение репозитория apt.postgresql.org:

echo 'deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main' | sudo tee /etc/apt/sources.list.d/pgdg.list > /dev/null
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update

Установим базу на обе машины:

sudo apt-get install postgresql-9.4 postgresql-contrib-9.4

В файле конфигурации серверов сделаем следующие изменения:

hot_standby = on
listen_address = '*'

Настройка master

Добавим разрешение на репликацию для slave

sudo echo 'host replication postgres    192.168.0.2/32  trust' >> /etc/postgresql/9.4/main/pg_hba.conf

В файле конфигурации сервера сделаем следующие изменения:

host_standby = on
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segemnts = 256 # 256 * 16 = 4096MB сегментов для хранения на сервере для того чтобы slave мог нагнать master если будет разрыв связи или другое ЧП

И выполним sudo service postgresql restart

Настройка slave

Сперва остановим базу командой sudo service postgresql stop

Копирование базы с master c одновременным открытием еще одного соединения для копирования WAL, чтобы копирование не заняло весь канал можно указать скорость копирования параметром --max-rate=rate:

sudo su - postgres
cd /var/lib/postgresql/9.4/
mv main main.old # либо просто удалить
pg_basebackup -h 192.168.0.1 -D /var/lib/postgresql/9.4/main -U postgres --xlog-method=stream --checkpoint=fast --write-recovery-conf --progress --verbose
chmod 0700 main
chown -R postgres:postgres /var/lib/postgresql/9.4/main

В результате будет скопирована база и WAL с master и создастся файл /var/lib/postgresql/9.4/main/recovery.conf со следующим содержимым:

standby_mode = 'on'
primary_conninfo = 'user=postgres host=192.168.0.1 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres'

И выполним sudo service postgresql start

Готово

Проверить работоспособность можно создав таблицу на master и сделать выборку из неё на slave.

Проверить отставание от master можно выполнив следующую команду на slave:

psql -c "select clock_timestamp() - pg_last_xact_replay_timestamp();"

Это покажет временной интервал между последним примененным WAL и текущим временем,
если трафик (количество DML запросов) в базе маленький то интервал будет значительный, что не совсем то, что нужно.

Есть и другой показатель - размер не примененного WAL в байтах:

psql -c "select pg_xlog_location_diff(pg_current_xlog_location(), replay_location) from pg_stat_replication"

Чтобы долгие OLAP запросы не прерывались нужно отредактировать значение max_standby_streaming_delay в файле конфигурации базы под рассчитываемую нагрузку.

Ссылки

  • Spectacle - управление окнами в OS X с клавиатуры
  • csvkit - швейцарский нож для CSV

Прикручивание бесплатного сетификата StartSSL на свой сайт

StartSSL позволяет создать бесплатный сертификат первого уровня для домена.

  1. Создаём аккаунт и обязательно сохраняем сертификат для дальнейшей авторизации.
  2. Создаём приватный ключ и обязательно его сохраняем.
  3. Ждем когда проверят личные данные и дадут возможность загрузить сертификат.
  4. Далее всё это нужно прикрутить к nginx, как это сделать описано в этой статье.
  5. По вкусу включаем ssl_protocols TLSv1 TLSv1.1 TLSv1.2; в нужной секции server {...}
  6. Готово.

У меня получилось что-то похоже на это:

server {
    listen        80;
    server_name   "";
    return        444;

    access_log    /var/log/nginx/ip.access.log;
    error_log     /var/log/nginx/ip.error.log;
}

server {
    listen        80;
    listen        443 ssl;
    server_name   www.ashurkov.ru;
    ssl           on;
    ssl_protocols TLSv1 TLSv1.1 TLSv1.2;
    ssl_certificate     /etc/nginx/ssl_unified.crt;
    ssl_certificate_key /etc/nginx/ssl_decrypted.key;

    return 301 $scheme://ashurkov.ru$request_uri;
}

server {
    listen        80;
    listen        443 ssl;
    server_name   ashurkov.ru;
    ssl           on;
    ssl_protocols TLSv1 TLSv1.1 TLSv1.2;
    ssl_certificate     /etc/nginx/ssl_unified.crt;
    ssl_certificate_key /etc/nginx/ssl_decrypted.key;

    access_log    /var/log/nginx/ashurkov_ru.access.log;
    error_log     /var/log/nginx/ashurkov_ru.error.log;

    location      /static/    {
        alias     /var/www/ashurkov_ru/static/;
    }

    location      /media/     {
        alias     /var/www/ashurkov_ru/media/;
    }

    location      /           {
        include       uwsgi_params;
        uwsgi_pass    unix:///var/run/uwsgi/app/ashurkov_ru/socket;
    }
}

Наверх | RSS | О сайте