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

Ambiente:

  • PostgreSQL Master: 10.3.0.102
  • PostgreSQL Slave: 10.3.0.103

Repositórios utilizados

Repositórios utilizados para o processo 

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

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

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

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

Configuração do PostgreSQL Master

Atualizando os repositórios 

apt update

Instalando os pacotes necessários 

apt install postgresql-9.6 postgresql-client-9.6 postgresql-filedump postgresql-server-dev-9.6 postgresql-9.6-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.6/main/postgresql.conf
[...]
listen_addresses = '*'
[...]

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.6/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.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-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.102 -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.102 -U usuario -W
psql (9.5.7, server 9.6.3)
WARNING: psql major version 9.5, server major version 9.6.
         Some psql features might not work.
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.102 -U usuario -W -d postgres
Password for user usuario: 
psql (9.5.7, server 9.6.3)
WARNING: psql major version 9.5, server major version 9.6.
         Some psql features might not work.
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.6/main/postgresql.conf
[...]
listen_addresses = '*' # 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 = replica
[...]
# 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
[...]
# Tamanho máximo que o WAL pode crescer entre os checkpoints automaticos.
max_wal_size = 3GB
[...]
# Tamanho minimo que o WAL pode utilizar.
min_wal_size = 100MB
[...]
# 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.6/main/pg_hba.conf
[...]
#Inserir no final do arquivo
host replication     replicator      10.3.0.103/32            md5

Aqui vamos considerar o nosso servidor como 10.3.0.103 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.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

Configuração do PostgreSQL Slave

Atualizando os repositórios 

apt update

Instalando os pacotes necessários 

apt install postgresql-9.6 postgresql-client-9.6 postgresql-filedump postgresql-server-dev-9.6 postgresql-9.6-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.6/main/postgresql.conf
[...]
listen_addresses = '*'
[...]

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.6/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.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-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.103 -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.103 -U usuario -W
Password for user usuario: 
psql (9.5.7, server 9.6.3)
WARNING: psql major version 9.5, server major version 9.6.
         Some psql features might not work.
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.103 -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 Slave

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.6/main/postgresql.conf
[...]
listen_addresses = '*' # 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 = replica
[...]
# 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
[...]
# Tamanho máximo que o WAL pode crescer entre os checkpoints automaticos.
max_wal_size = 3GB
[...]
# Tamanho minimo que o WAL pode utilizar.
min_wal_size = 100MB
[...]
# 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 
Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-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.6/main /var/lib/postgresql/9.6/main-bkp

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

echo "Criando o arquivo de recovery.conf"
sudo -u postgres bash -c "cat > /var/lib/postgresql/9.6/main/recovery.conf <<- _EOF1_
  standby_mode = 'on'
  primary_conninfo = 'host=10.3.0.102 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.102: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-21 10:44:02.909872
 2017-06-21 10:44:02.972577
 2017-06-21 10:44:03.027867
 2017-06-21 10:44:03.081027
 2017-06-21 10:44:03.134915
 2017-06-21 10:44:03.189235
 2017-06-21 10:44:03.242954
 2017-06-21 10:44:03.422689
 2017-06-21 10:44:03.479146
 2017-06-21 10:44:03.533108
(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.6/main/pg_hba.conf
[...]
#Inserir no final do arquivo
host replication     replicator      10.3.0.102/32            md5

Aqui vamos considerar o nosso servidor como 10.3.0.102 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.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-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.6/main /var/lib/postgresql/9.6/main-bkp

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

echo "Criando o arquivo de recovery.conf"
sudo -u postgres bash -c "cat > /var/lib/postgresql/9.6/main/recovery.conf <<- _EOF1_
  standby_mode = 'on'
  primary_conninfo = 'host=10.3.0.103 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.103: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.6     main      5432 online,recovery postgres /var/lib/postgresql/9.6/main       /var/log/postgresql/postgresql-9.6-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-21 11:23:35.807315
 2017-06-21 11:23:35.414451
 2017-06-21 11:23:35.357902
 2017-06-21 11:23:35.289725
 2017-06-21 11:23:35.148095
 2017-06-21 11:23:35.091422
 2017-06-21 11:23:35.033995
 2017-06-21 11:23:34.975496
 2017-06-21 11:23:34.917786
 2017-06-21 11:23:34.860154
(10 registros)

Referências

  1. https://www.postgresql.org/docs/9.6/static/runtime-config-replication.html
  2. https://www.postgresql.org/docs/9.6/static/runtime-config-wal.html
  3. http://www.rassoc.com/gregr/weblog/2013/02/16/zero-to-postgresql-streaming-replication-in-10-mins/
  4. https://dba.stackexchange.com/questions/9587/automating-failover-in-postgresql-9-1
  5. https://www.depesz.com/2010/02/01/waiting-for-9-0-streaming-replication/
  6. https://www.postgresql.org/docs/9.6/static/release-9-5.html
  7. https://www.postgresql.org/message-id/CADKbJJWz9M0swPT3oqe8f9+tfD4-F54uE6Xtkh4nERpVsQnjnw@mail.gmail.com
  8. https://www.postgresql.org/docs/9.4/static/hot-standby.html → (PostgreSQL ERROR: canceling statement due to conflict with recovery)
  9. https://stackoverflow.com/questions/14592436/postgresql-error-canceling-statement-due-to-conflict-with-recovery