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