%archive_title%

Автор:human

Настройка postgresql 9.6 в связке c pgpoo2

Берем debian 9

Ставим postgres (на всех нодах)

wget http://ftp.ro.debian.org/debian/pool/main/o/openssl/libssl1.0.0_1.0.2k-1~bpo8+1_amd64.deb
wget http://ftp.hu.debian.org/debian/pool/main/o/openssl/libssl1.0.0_1.0.1t-1+deb8u6_amd64.deb
dpkg -i *.deb
apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
apt-get update
apt-get upgrade
apt-get install postgresql-9.6 postgresql-9.6-pgpool2

wget http://ftp.ro.debian.org/debian/pool/main/o/openssl/libssl1.0.0_1.0.2k-1~bpo8+1_amd64.deb

wget http://ftp.hu.debian.org/debian/pool/main/o/openssl/libssl1.0.0_1.0.1t-1+deb8u6_amd64.deb

dpkg -i *.deb

apt-get install wget ca-certificates

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -

apt-get update

apt-get upgrade

apt-get install postgresql-9.6 postgresql-9.6-pgpool2

Делаем в базе на всех нодах

psql

psql

ALTER USER postgres WITH PASSWORD 'itc-pass';
CREATE ROLE replication WITH REPLICATION PASSWORD 'itc-pass-replica' LOGIN;

1 2	ALTER USER postgres WITH PASSWORD 'itc-pass'; CREATE ROLE replication WITH REPLICATION PASSWORD 'itc-pass-replica' LOGIN;

Создаем файл коннета с паролем для пользователя репликации (на всех нодах)

 nano /var/lib/postgresql/.pgpass

1	nano /var/lib/postgresql/.pgpass

*:*:*:replication:itc-pass-replica

1	::*:replication:itc-pass-replica

chown postgres:postgres /var/lib/postgresql/.pgpass
chmod 0600 /var/lib/postgresql/.pgpass

1 2	chown postgres:postgres /var/lib/postgresql/.pgpass chmod 0600 /var/lib/postgresql/.pgpass

Правим конмиг на мастер

 nano /etc/postgresql/9.6/main/postgresql.conf

1	nano /etc/postgresql/9.6/main/postgresql.conf

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
# Add settings for extensions here
listen_addresses = '*'
port = 5433
max_connections = 300
shared_buffers = 2560MB
effective_cache_size = 7680MB
maintenance_work_mem = 1280MB
default_statistics_target = 500
track_activities = on
track_counts = on
autovacuum = off
temp_buffers = 16MB
work_mem = 128MB
effective_io_concurrency = 4
shared_preload_libraries = 'pg_stat_statements,pg_buffercache'
track_activity_query_size = 2048
pg_stat_statements.track = all
track_io_timing = on
lc_messages = 'ru_RU.UTF-8'
lc_monetary = 'ru_RU.UTF-8'
lc_numeric = 'ru_RU.UTF-8'
lc_time = 'ru_RU.UTF-8'
hot_standby = on
wal_level = 'hot_standby'
wal_log_hints = on
archive_mode = on
archive_command = 'cd .'
max_replication_slots = 3
max_wal_senders = 3
wal_keep_segments = 64

#------------------------------------------------------------------------------

# CUSTOMIZED OPTIONS

#------------------------------------------------------------------------------

# Add settings for extensions here

listen_addresses = '*'

port = 5433

max_connections = 300

shared_buffers = 2560MB

effective_cache_size = 7680MB

maintenance_work_mem = 1280MB

default_statistics_target = 500

track_activities = on

track_counts = on

autovacuum = off

temp_buffers = 16MB

work_mem = 128MB

effective_io_concurrency = 4

shared_preload_libraries = 'pg_stat_statements,pg_buffercache'

track_activity_query_size = 2048

pg_stat_statements.track = all

track_io_timing = on

lc_messages = 'ru_RU.UTF-8'

lc_monetary = 'ru_RU.UTF-8'

lc_numeric = 'ru_RU.UTF-8'

lc_time = 'ru_RU.UTF-8'

hot_standby = on

wal_level = 'hot_standby'

wal_log_hints = on

archive_mode = on

archive_command = 'cd .'

max_replication_slots = 3

max_wal_senders = 3

wal_keep_segments = 64

Правим файл с подлючениями

/etc/postgresql/9.6/main/pg_hba.conf

1	/etc/postgresql/9.6/main/pg_hba.conf

#
# Database administrative login by Unix domain socket
local   all             postgres                                peer
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
host  replication     replication     10.0.0.0/8          md5
host  replication     replication     10.0.0.0/8          md5
host  all             all        0.0.0.0/0             md5
hostssl  all             all        0.0.0.0/0             md5

# Database administrative login by Unix domain socket

local all postgres peer

# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only

local all all peer

# IPv4 local connections:

host all all 127.0.0.1/32 md5

# IPv6 local connections:

host all all ::1/128 md5

host replication replication 10.0.0.0/8 md5

host all all 0.0.0.0/0 md5

hostssl all all 0.0.0.0/0 md5

Рестаруем postgres

/etc/init.d/postresql restart

1	/etc/init.d/postresql restart

Создаем слот репликации для сервера. Для этого выполняем команду

su postgres -c "psql"

1	su postgres -c "psql"

#SELECT * FROM pg_create_physical_replication_slot('it_rdbms02');

1	#SELECT * FROM pg_create_physical_replication_slot('it_rdbms02');

Настройка слейва postgres(stand-by)

/etc/init.d/postgresql stop

1	/etc/init.d/postgresql stop

Создаем на нем также .pgpass

Правим коннекты аналогично мастеру

Сливаем с мастера данные

su postgres
cd /var/lib/postgresql/9.6/
rm -rf main/*
pg_basebackup -v -D main -R -P -h 10.9.3.53 -p 5433 -U replication

su postgres

cd /var/lib/postgresql/9.6/

rm -rf main/*

pg_basebackup -v -D main -R -P -h 10.9.3.53 -p 5433 -U replication

Настраиваем recovery.conf

nano /var/lib/postgresql/9.6/main/recovery.conf

Приводим к

standby_mode = 'on'
primary_conninfo = 'user=replication password=itc-pass-replica host=10.9.3.53 port=5433 sslmode=prefer sslcompression=1 krbsrvname=postgres'
primary_slot_name = 'it_rdbms02'
trigger_file = '/etc/postgresql/9.6/main/im_the_master'

standby_mode = 'on'

primary_conninfo = 'user=replication password=itc-pass-replica host=10.9.3.53 port=5433 sslmode=prefer sslcompression=1 krbsrvname=postgres'

primary_slot_name = 'it_rdbms02'

trigger_file = '/etc/postgresql/9.6/main/im_the_master'

Рестаруем postgres

/etc/init.d/postresql restart

1	/etc/init.d/postresql restart

Настраиваем pgpool2

su postgres

1	su postgres

cd /var/lib/postgresql/
wget http://www.pgpool.net/mediawiki/images/pgpool-II-3.6.4.tar.gz
tar -xvf pgpool-II-3.6.4.tar.gz
cd pgpool-II-3.6.4/src/sql
cd pgpool-recovery
psql -f pgpool_recovery--1.1.sql template1

cd /var/lib/postgresql/

wget http://www.pgpool.net/mediawiki/images/pgpool-II-3.6.4.tar.gz

tar -xvf pgpool-II-3.6.4.tar.gz

cd pgpool-II-3.6.4/src/sql

cd pgpool-recovery

psql -f pgpool_recovery--1.1.sql template1

Архив за месяц Июнь 2017

Настройка postgresql 9.6 в связке c pgpoo2

Берем debian 9

Делаем в базе на всех нодах

Настройка слейва postgres(stand-by)

Image magick — изменение изображений в linux

FAQ по командам

Как изменить размер изображения без сохранения соотношения сторон

Как изменить размер изображения с сохранения соотношения сторон

Как изменить прозрачность, например водяного знака через ком строку.

Установка php5.6 в debian 9

Система сбора логов на основе — fluentd, grafana, influxdb