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
- https://www.postgresql.org/docs/9.4/static/hot-standby.html → (PostgreSQL ERROR: canceling statement due to conflict with recovery)