Instalando e configurando o PostgreSQL no Debian Jessie e Configurando Streaming Replication

Ambiente:

  • PostgreSQL Master: 10.3.0.100
  • PostgreSQL Slave: 10.3.0.101

Repositórios utilizados

Repositórios utilizados para o processo

vim /etc/apt/sources.list
# Official repository
deb http://ftp.br.debian.org/debian jessie main contrib non-free
deb-src http://ftp.br.debian.org/debian jessie main contrib non-free

# Security update repository
deb http://security.debian.org/ jessie/updates main contrib non-free
deb-src http://security.debian.org/ jessie/updates main contrib non-free

# Updates repository
deb http://ftp.br.debian.org/debian jessie-updates main contrib non-free
deb-src http://ftp.br.debian.org/debian jessie-updates main contrib non-free

# Propose updates repository
deb http://ftp.br.debian.org/debian jessie-proposed-updates main contrib non-free
deb-src http://ftp.br.debian.org/debian jessie-proposed-updates main contrib non-free

Configuração do PostgreSQL Master

Atualizando os repositórios

apt-get update

Instalando os pacotes necessários

aptitude install postgresql-9.4 postgresql-client-9.4 postgresql-filedump postgresql-server-dev-9.4 postgresql-9.4-plsh postgresql-common -y

Aqui vamos configurar em qual endereço ip o servidor vai escutar vamos colocar '*' para ele escutar em todas as interfaces disponíveis.

vim /etc/postgresql/9.4/main/postgresql.conf
[...]
listen_address = '*'
[...]

Agora precisamos liberar o acesso no postgresql para que os clientes consigam se conectar nele, então vamos liberar a maquina de aplicação por exemplo que tem o ip 10.3.0.234

vim /etc/postgresql/9.4/main/pg_hba.conf
[...]
#no final do arquivo adicione a seguinte linha
host    all         all         10.3.0.234/32             md5

Aqui no exemplo acima estamos liberando acesso para a máquina 10.0.0.234 porém podemos liberar acesso para uma rede inteira utilizando CIDR ex: 10.3.0.0/24

Agora vamos reiniciar o nosso postgreSQL

systemctl restart postgresql

Agora vamos consultar se o cluster do postgreSQL subiu

pg_lsclusters 
Ver Cluster Port Status Owner    Data directory               Log file
9.4 main    5432 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log

Agora vamos cadastrar um usuário para teste.

cd /tmp; sudo -u postgres psql -c "CREATE USER usuario WITH PASSWORD 'senha' SUPERUSER;"

Agora vamos instalar o cliente em uma máquina Debian/Ubuntu para efetuar um teste de conexão

aptitude install postgresql-client -y

Agora vamos conectar pelo cliente.

psql -h 10.3.0.100 -U usuario -W
Password for user usuario: 
psql: FATAL:  banco de dados "usuario" não existe

Recebemos um erro pois o padrão é que o usuário tenha uma base de dados com o seu nome vamos fazer um teste para validar isso

Vamos criar uma base de dados com o nome do usuário no servidor slave.

cd /tmp; sudo -u postgres psql -c "CREATE DATABASE usuario OWNER usuario;"

Pronto criamos a nossa base de dados com o nome do usuário agora vamos conectar no banco novamente.

psql -h 10.3.0.100 -U usuario -W
Password for user usuario: 
psql (9.5.7, server 9.4.12)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

usuario=# \q

Como pode ser notado ocorreu tudo certo, agora vamos ver como acessamos outro banco de dados pelo cliente.

psql -h 10.3.0.100 -U usuario -W -d postgres
Password for user usuario: 
psql (9.5.7, server 9.4.12)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \q

Como pode ser notado conectamos pelo cliente no servidor porém já selecionado o banco postgres.

Configuração do Streaming Replication no Master

Primeiro precisamos criar o usuário replication no servidor Master

sudo -u postgres psql -c "CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'thepassword';"

Note que estamos utilizando as permissões de replicação ao inves de criar um superuser.

Agora vamos configurar o postgresql.conf para dar suporte ao streaming replication

vim  /etc/postgresql/9.4/main/postgresql.conf
[...]
listen_address = '*' # Aqui precisamos se certificar que o servidor esta escutando nas interfaces corretas.
[...]
# Determina quanta informação é escrita para Write Ahead Log (WAL). o padrão é minimal
# hot_standby a informação é logada como archive, mais informações necessárias para reconstrução do status de transações rodando do WAL
wal_level = hot_standby
[...]
# Determina o numero maximo de conexões concorrentes dos servidores standby ou streaming. (O numero maximo de servidores rodando o processo de WAL sender) Este número nao pode ser maior que o numero máximo de conexões.
max_wal_senders = 3
[...]
# Número máximo de arquivos de log de segmento entre checkpoint automático do WAL. (Cada segmento tem 16MB)
checkpoint_segments = 8    
[...]
# Determina o número minimo de arquivos de log de segmentos antigos que devem ser mantidos no diretório pg_xlog
# No caso de um servidor standby precisa obter eles para a replicaçãoem streaming
wal_keep_segments = 8 
[...]
# Determina se pode ou não ser conectado e rodar queries no servidor durante o recovery.
hot_standby = on
[...]
# quando o archive_mode está habilitade, os segmentos WAL complesto vao ser enviados para o storage por archive_command
archive_mode = on
[...]
# Comand shell que vai ser executado para armazenar os arquivos de segmento WAL
archive_command = 'cd .'

Agora precisamos ajustar o arquivo que controla as conexões vindas do servidor slave.

vim /etc/postgresql/9.4/main/pg_hba.conf
[...]
#Inserir no final do arquivo
host replication     replicator      10.3.0.101/32            md5

Aqui vamos considerar o nosso servidor como 10.3.0.101 que vai conectar no servidor master para replicações.

Agora precisamos reiniciar o servidor para obter as novas configurações.

systemctl restart postgresql

Agora vamos consultar o nosso cluster

pg_lsclusters 
Ver Cluster Port Status Owner    Data directory               Log file
9.4 main    5432 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log

Configuração do PostgreSQL Slave

Atualizando os repositórios

apt-get update

Instalando os pacotes necessários

aptitude install postgresql-9.4 postgresql-client-9.4 postgresql-filedump postgresql-server-dev-9.4 postgresql-9.4-plsh postgresql-common -y

Aqui vamos configurar em qual endereço ip o servidor vai escutar vamos colocar '*' para ele escutar em todas as interfaces disponíveis.

vim /etc/postgresql/9.4/main/postgresql.conf
[...]
listen_address = '*'
[...]

Agora precisamos liberar o acesso no postgresql para que os clientes consigam se conectar nele, então vamos liberar a maquina de aplicação por exemplo que tem o ip 10.3.0.234

vim /etc/postgresql/9.4/main/pg_hba.conf
[...]
#no final do arquivo adicione a seguinte linha
host    all         all         10.3.0.234/32             md5

Aqui no exemplo acima estamos liberando acesso para a máquina 10.0.0.234 porém podemos liberar acesso para uma rede inteira utilizando CIDR ex: 10.3.0.0/24

Agora vamos reiniciar o nosso postgreSQL

systemctl restart postgresql

Agora vamos consultar se o cluster do postgreSQL subiu

pg_lsclusters 
Version Cluster   Port Status Owner    Data directory                     Log file
9.4     main      5432 online postgres /var/lib/postgresql/9.4/main       /var/log/postgresql/postgresql-9.4-main.log

Agora vamos cadastrar um usuário para teste.

cd /tmp; sudo -u postgres psql -c "CREATE USER usuario WITH PASSWORD 'senha' SUPERUSER;"

Agora vamos instalar o cliente em uma máquina Debian/Ubuntu para efetuar um teste de conexão

aptitude install postgresql-client -y

Agora vamos conectar pelo cliente.

psql -h 10.3.0.101 -U usuario -W
Password for user usuario: 
psql: FATAL:  banco de dados "usuario" não existe

Recebemos um erro pois o padrão é que o usuário tenha uma base de dados com o seu nome vamos fazer um teste para validar isso

Vamos criar uma base de dados com o nome do usuário no servidor Slave

cd /tmp; sudo -u postgres psql -c "CREATE DATABASE usuario OWNER usuario;"

Pronto criamos a nossa base de dados com o nome do usuário agora vamos conectar no banco novamente.

psql -h 10.3.0.101 -U usuario -W
Password for user usuario: 
psql (9.5.7, server 9.4.12)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

usuario=# \q

Como pode ser notado ocorreu tudo certo, agora vamos ver como acessamos outro banco de dados pelo cliente.

psql -h 10.3.0.101 -U usuario -W -d postgres
Password for user usuario: 
psql (9.5.7, server 9.4.12)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \q

Como pode ser notado conectamos pelo cliente no servidor porém já selecionado o banco postgres.

Agora vamos ajustar a configuração do slave para escutar o streaming.

Agora vamos configurar o postgresql.conf para dar suporte ao streaming replication

vim  /etc/postgresql/9.4/main/postgresql.conf
[...]
listen_address = '*' # Aqui precisamos se certificar que o servidor esta escutando nas interfaces corretas.
[...]
# Determina quanta informação é escrita para Write Ahead Log (WAL). o padrão é minimal
# hot_standby a informação é logada como archive, mais informações necessárias para reconstrução do status de transações rodando do WAL
wal_level = hot_standby
[...]
# Determina o numero maximo de conexões concorrentes dos servidores standby ou streaming. (O numero maximo de servidores rodando o processo de WAL sender) Este número nao pode ser maior que o numero máximo de conexões.
max_wal_senders = 3
[...]
# Número máximo de arquivos de log de segmento entre checkpoint automático do WAL. (Cada segmento tem 16MB)
checkpoint_segments = 8    
[...]
# Determina o número minimo de arquivos de log de segmentos antigos que devem ser mantidos no diretório pg_xlog
# No caso de um servidor standby precisa obter eles para a replicaçãoem streaming
wal_keep_segments = 8 
[...]
# Determina se pode ou não ser conectado e rodar queries no servidor durante o recovery.
hot_standby = on
[...]
# quando o archive_mode está habilitade, os segmentos WAL complesto vao ser enviados para o storage por archive_command
archive_mode = on
[...]
# Comand shell que vai ser executado para armazenar os arquivos de segmento WAL
archive_command = 'cd .'

Agora precisamos reiniciar o postgres

systemctl restart postgresql

Agora vamos consultar o cluster do postgres

pg_lsclusters 
Version Cluster   Port Status Owner    Data directory                     Log file
9.4     main      5432 online postgres /var/lib/postgresql/9.4/main       /var/log/postgresql/postgresql-9.4-main.log

Agora precisamos sincronizar as bases, vamos criar um script para executar o sincronismo

Vamos criar um diretório para armazenar os scripts

mkdir /srv/scripts

Agora vamos criar o nosso script

vim /srv/scripts/pg_sync.sh
#!/bin/bash
echo "Parando o PostgreSQL"
systemctl stop postgresql
rm -rf /tmp/postgresql.trigger

echo "Criando backup do diretorio de cluster antigo"
sudo -u postgres mv -f /var/lib/postgresql/9.4/main /var/lib/postgresql/9.4/main-bkp

echo "Iniciando a replicacao da base de backup como replicador"
sudo -u postgres pg_basebackup -h 10.3.0.100 -D /var/lib/postgresql/9.4/main -U replicator -v

echo "Ajustando os certificados"
ln -s /etc/ssl/certs/ssl-cert-snakeoil.pem /var/lib/postgresql/9.4/main/server.crt
ln -s /etc/ssl/private/ssl-cert-snakeoil.key /var/lib/postgresql/9.4/main/server.key

echo "Criando o arquivo de recovery.conf"
sudo -u postgres bash -c "cat > /var/lib/postgresql/9.4/main/recovery.conf <<- _EOF1_
  standby_mode = 'on'
  primary_conninfo = 'host=10.3.0.100 port=5432 user=replicator password=thepassword'
  trigger_file = '/tmp/postgresql.trigger'
_EOF1_
"

echo "Iniciando o PostgreSQL"
systemctl start postgresql

Agora vamos criar o arquivo com o usuário e senha do replicador para ser utilizado pelo usuário postgres

vim /var/lib/postgresql/.pgpass 
10.3.0.100:5432:replication:replicator:thepassword

Agora vamos ajustar as permissões do arquivo

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

Agora vamos dar permissões de execução para o nosso script

chmod +x /srv/scripts/pg_sync.sh

Agora vamos executar o nosso script

/srv/scripts/pg_sync.sh

Agora vamos consultar o nosso cluster

pg_lsclusters 
Version Cluster   Port Status Owner    Data directory                     Log file
9.4     main      5432 online,recovery postgres /var/lib/postgresql/9.4/main       /var/log/postgresql/postgresql-9.4-main.log

Aqui já podemos notar que o owner é o recovery.

Agora podemos escrever no servidor master e utilizar a seguinte query para consultar a replicação

sudo -u postgres psql -x -c "select * from pg_stat_replication;"

Testando a Replicação

Vamos criar uma tabela no servidor Master

sudo -u postgres psql -c "CREATE TABLE test (x timestamp);"

Agora vamos inserir alguns dados nela

sudo -u postgres psql -c "INSERT INTO test (x) VALUES (NOW()) returning x;"
sudo -u postgres psql -c "INSERT INTO test (x) VALUES (NOW()) returning x;"
sudo -u postgres psql -c "INSERT INTO test (x) VALUES (NOW()) returning x;"
sudo -u postgres psql -c "INSERT INTO test (x) VALUES (NOW()) returning x;"
sudo -u postgres psql -c "INSERT INTO test (x) VALUES (NOW()) returning x;"
sudo -u postgres psql -c "INSERT INTO test (x) VALUES (NOW()) returning x;"
sudo -u postgres psql -c "INSERT INTO test (x) VALUES (NOW()) returning x;"
sudo -u postgres psql -c "INSERT INTO test (x) VALUES (NOW()) returning x;"
sudo -u postgres psql -c "INSERT INTO test (x) VALUES (NOW()) returning x;"
sudo -u postgres psql -c "INSERT INTO test (x) VALUES (NOW()) returning x;"
sudo -u postgres psql -c "INSERT INTO test (x) VALUES (NOW()) returning x;"

Agora no servidor Slave vamos consultar os dados

sudo -u postgres psql -c "SELECT * FROM test LIMIT 10;"
             x              
----------------------------
 2017-06-20 14:08:43.574176
 2017-06-20 14:08:46.054653
 2017-06-20 14:08:46.507232
 2017-06-20 14:08:46.911158
 2017-06-20 14:08:47.30254
 2017-06-20 14:08:47.700448
 2017-06-20 14:08:48.081637
 2017-06-20 14:08:48.435305
 2017-06-20 14:11:53.331968
 2017-06-20 14:11:53.384747
(10 registros)

Notas

Para voltar o slave para writable precisamos criar o arquivo

touch /tmp/postgresql.trigger

Este arquivo força a replicação terminar e habilitar o slave a trabalhar como escrita e move o arquivo recovery.conf to recovery.done.

Aqui basta efetuar o processo inverso para promover o slave como master e o master como slave.

Configurando o servidor Slave a trabalhar como Master

Vamos configurar o servidor slave a operar como master e forçar o master a receber replicação do servidor slave.

Para voltar o slave para writable precisamos criar o arquivo

touch /tmp/postgresql.trigger

Agora precisamos ajustar o arquivo que controla as conexões vindas do servidor slave (antigo Master).

vim /etc/postgresql/9.4/main/pg_hba.conf
[...]
#Inserir no final do arquivo
host replication     replicator      10.3.0.100/32            md5

Aqui vamos considerar o nosso servidor como 10.3.0.100 que vai conectar no servidor master para replicações.

Agora precisamos reiniciar o servidor para obter as novas configurações.

systemctl restart postgresql

Agora vamos consultar o nosso cluster

pg_lsclusters 
Version Cluster   Port Status Owner    Data directory                     Log file
9.4     main      5432 online postgres /var/lib/postgresql/9.4/main       /var/log/postgresql/postgresql-9.4-main.log

Configurando o servidor Master (Antigo) a trabalhar como Slave

Agora precisamos sincronizar as bases, vamos criar um script para executar o sincronismo

Vamos criar um diretório para armazenar os scripts

mkdir /srv/scripts

Agora vamos criar o nosso script

vim /srv/scripts/pg_sync.sh
#!/bin/bash
echo "Parando o PostgreSQL"
systemctl stop postgresql
rm -rf /tmp/postgresql.trigger

echo "Criando backup do diretorio de cluster antigo"
sudo -u postgres mv -f /var/lib/postgresql/9.4/main /var/lib/postgresql/9.4/main-bkp

echo "Iniciando a replicacao da base de backup como replicador"
sudo -u postgres pg_basebackup -h 10.3.0.101 -D /var/lib/postgresql/9.4/main -U replicator -v

echo "Ajustando os certificados"
ln -s /etc/ssl/certs/ssl-cert-snakeoil.pem /var/lib/postgresql/9.4/main/server.crt
ln -s /etc/ssl/private/ssl-cert-snakeoil.key /var/lib/postgresql/9.4/main/server.key

echo "Criando o arquivo de recovery.conf"
sudo -u postgres bash -c "cat > /var/lib/postgresql/9.4/main/recovery.conf <<- _EOF1_
  standby_mode = 'on'
  primary_conninfo = 'host=10.3.0.101 port=5432 user=replicator password=thepassword'
  trigger_file = '/tmp/postgresql.trigger'
_EOF1_
"

echo "Iniciando o PostgreSQL"
systemctl start postgresql

Agora vamos criar o arquivo com o usuário e senha do replicador para ser utilizado pelo usuário postgres

vim /var/lib/postgresql/.pgpass 
10.3.0.101:5432:replication:replicator:thepassword

Agora vamos ajustar as permissões do arquivo

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

Agora vamos dar permissões de execução para o nosso script

chmod +x /srv/scripts/pg_sync.sh

Agora vamos executar o nosso script

/srv/scripts/pg_sync.sh

Agora vamos consultar o nosso cluster

pg_lsclusters 
Version Cluster   Port Status Owner    Data directory                     Log file
9.4     main      5432 online,recovery postgres /var/lib/postgresql/9.4/main       /var/log/postgresql/postgresql-9.4-main.log

Aqui já podemos notar que o owner é o recovery.

Agora podemos escrever no servidor master e utilizar a seguinte query para consultar a replicação

sudo -u postgres psql -x -c "select * from pg_stat_replication;"

Agora vamos inserir alguns dados no servidor Master (Antigo Slave)

sudo -u postgres psql -c "INSERT INTO test (x) VALUES (NOW()) returning x;"
sudo -u postgres psql -c "INSERT INTO test (x) VALUES (NOW()) returning x;"
sudo -u postgres psql -c "INSERT INTO test (x) VALUES (NOW()) returning x;"
sudo -u postgres psql -c "INSERT INTO test (x) VALUES (NOW()) returning x;"
sudo -u postgres psql -c "INSERT INTO test (x) VALUES (NOW()) returning x;"
sudo -u postgres psql -c "INSERT INTO test (x) VALUES (NOW()) returning x;"
sudo -u postgres psql -c "INSERT INTO test (x) VALUES (NOW()) returning x;"
sudo -u postgres psql -c "INSERT INTO test (x) VALUES (NOW()) returning x;"
sudo -u postgres psql -c "INSERT INTO test (x) VALUES (NOW()) returning x;"
sudo -u postgres psql -c "INSERT INTO test (x) VALUES (NOW()) returning x;"
sudo -u postgres psql -c "INSERT INTO test (x) VALUES (NOW()) returning x;"

Agora vamos consultar os dados para garantir que os dados estão sendo replicados.

sudo -u postgres psql -c "SELECT * FROM test ORDER BY x DESC LIMIT 10;"
             x              
----------------------------
 2017-06-20 14:17:22.951737
 2017-06-20 14:17:21.955561
 2017-06-20 14:17:21.889639
 2017-06-20 14:17:21.57114
 2017-06-20 14:17:21.523286
 2017-06-20 14:17:21.476879
 2017-06-20 14:17:21.419973
 2017-06-20 14:17:21.370838
 2017-06-20 14:17:21.308234
 2017-06-20 14:17:21.249264
(10 registros)