Como Configurar a Replicação Master-Slave para PostgreSQL 9.6 no Ubuntu 16.04

PostgreSQL ou Postgres é um sistema de gerenciamento de banco de dados objeto-relacional de código aberto (ORDBMS) com mais de 15 anos de desenvolvimento ativo. É um poderoso servidor de banco de dados e pode lidar com altas cargas de trabalho. O PostgreSQL pode ser usado em servidores Linux, Unix, BSD e Windows.

A replicação de banco de dados master/slave é um processo de cópia (sincronização) de dados de um banco de dados em um servidor (o master) para um banco de dados em outro servidor (os slaves). O principal benefício desse processo é distribuir bancos de dados para várias máquinas, assim, quando o servidor master tem um problema, há uma máquina de backup com os mesmos dados disponíveis para lidar com solicitações sem interrupção.

O PostgreSQL fornece várias maneiras de replicar um banco de dados. Pode ser usado para fins de backup e para fornecer um servidor de banco de dados de alta disponibilidade. Neste tutorial, mostraremos como instalar e configurar a Replicação Master-Slave do PostgreSQL 9.6 no servidor Ubuntu 16.04. Usaremos o modo Hot standby, e é um ótimo ponto de partida para aprender PostgreSQL em profundidade.

O que faremos:

1. Instalar o PostgreSQL 9.6.
2. Configurar o Firewall UFW.
3. Configurar o Servidor MASTER.
4. Configurar o Servidor SLAVE.
5. Copiar Dados do PostgreSQL do MASTER para o SLAVE.
6. Testes.

Pré-requisitos:

• 1 servidor Ubuntu 16.04 - MASTER - Permitindo Leitura e Escrita - IP: 10.0.15.10
• 1 servidor Ubuntu 16.04 - SLAVE - Apenas Leitura - IP: 10.0.15.11
• Privilégios de Root

Passo 1 - Instalar o PostgreSQL 9.6

Neste tutorial, instalaremos a versão mais recente do PostgreSQL 9.6. No repositório oficial do Ubuntu, eles fornecem apenas o PostgreSQL 9.5, então precisamos instalar a versão mais recente diretamente do repositório do PostgreSQL.

Adicione o repositório postgreSQL 9.6 ao diretório sources.list.d.

echo 'deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main' | tee /etc/apt/sources.list.d/postgresql.list

E importe a chave de assinatura do PostgreSQL para o sistema.

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

Agora atualize o repositório do sistema com o comando apt.

apt-get update

Em seguida, instale o pacote PosgreSQL 9.6 com o comando apt abaixo.

apt-get install -y postgresql-9.6 postgresql-contrib-9.6

Se a instalação foi concluída, adicione-a para iniciar automaticamente na inicialização.

systemctl enable postgresql

Por padrão, o PostgreSQL está em execução no endereço IP localhost (127.0.0.1) com a porta 5432 no Ubuntu, verifique com o comando netstat.

netstat -plntu

O PostgreSQL 9.6 está em execução no sistema. No próximo passo, precisamos configurar uma senha para o usuário postgres.

A partir da conta root, faça login no usuário postgres com o comando su, em seguida, acesse o terminal front-end do postgres psql.

su - postgres  
psql

Por favor, altere a senha do usuário postgres e verifique as informações de conexão com as consultas postgres abaixo.

\password postgres  
\conninfo

O PostgreSQL 9.6 foi instalado no sistema, está em execução sem erro e a senha do usuário postgres foi atualizada.

Passo 2 - Configurar o Firewall UFW

UFW ou Uncomplicated Firewall é um aplicativo para gerenciar o firewall baseado em iptables no Ubuntu. O UFW é a ferramenta de configuração de firewall padrão para o Ubuntu Linux e fornece uma maneira amigável de configurar o firewall.

Podemos instalar o ufw a partir do repositório principal do Ubuntu com o comando apt.

apt-get install -y ufw

Adicione novos serviços ao firewall UFW: adicione os serviços SSH e PostgreSQL com os comandos abaixo.

ufw allow ssh  
ufw allow postgresql

Ative o firewall UFW e verifique o status.

ufw enable  
ufw status

O firewall UFW foi instalado e o serviço PostgreSQL foi adicionado.

NOTA:

Execute o passo 1 e o passo 2 no servidor MASTER e SLAVE.

Passo 3 - Configurar o Servidor PostgreSQL MASTER

O servidor master tem o endereço IP 10.0.15.10, e o serviço postgres será executado sob esse IP com a porta padrão. O servidor master terá permissão para LEITURA e ESCRITA no banco de dados e realizará a replicação em streaming para o servidor slave.

Vá para o diretório de configuração do postgres ‘/etc/postgresql/9.6/main‘ e edite o arquivo postgresql.conf com vim.

cd /etc/postgresql/9.6/main/  
vim postgresql.conf

Descomente a linha ‘listen_addresses’ e altere o valor para o endereço IP do servidor master ‘10.0.15.10‘.

listen_addresses = '10.0.15.10'

Descomente a linha ‘wal_level’ e altere o valor para ‘hot_standby‘.

wal_level = hot_standby

Para o nível de sincronização, usaremos a sincronização local. Descomente e altere o valor da linha como abaixo.

synchronous_commit = local

Ative o modo de arquivamento e altere a opção archive_command para o comando ‘cp %p /var/lib/postgresql/9.6/main/archive/%f’.

archive_mode = on  
archive_command = 'cp %p /var/lib/postgresql/9.6/main/archive/%f'

Para as configurações de ‘Replicação’, neste tutorial usamos apenas 2 servidores, master e slave, descomente a linha ‘wal_sender‘ e altere o valor para 2, e para o valor ‘wal_keep_segments‘ é 10.

max_wal_senders = 2  
wal_keep_segments = 10

Para o nome do aplicativo, descomente a linha ‘synchronous_standby_names’ e altere o valor para o nome ‘pgslave001‘.

synchronous_standby_names = 'pgslave001'

Salve o arquivo e saia do editor.

No arquivo postgresql.conf, o modo de arquivamento está ativado, então precisamos criar um novo diretório para o arquivo de arquivamento. Crie um novo diretório de arquivamento, altere a permissão e mude o proprietário para o usuário postgres.

mkdir -p /var/lib/postgresql/9.6/main/archive/  
chmod 700 /var/lib/postgresql/9.6/main/archive/  
chown -R postgres:postgres /var/lib/postgresql/9.6/main/archive/

Em seguida, edite o arquivo pg_hba.conf para a configuração de autenticação.

vim pg_hba.conf

Cole a configuração abaixo no final da linha.

# Localhost  
host    replication     replica          127.0.0.1/32            md5  
   
# Endereço IP do Master do PostgreSQL  
host    replication     replica          10.0.15.10/32            md5  
   
# Endereço IP do Slave do PostgreSQL  
host    replication     replica          10.0.15.11/32            md5

Salve e saia, em seguida, reinicie o PostgreSQL.

systemctl restart postgresql

O PostgreSQL está em execução sob o endereço IP 10.0.15.10, verifique com o comando netstat.

netstat -plntu

Em seguida, crie um novo usuário para replicação. Vamos criar um novo usuário chamado ‘replica‘ com a senha ‘aqwe123@‘. Por favor, escolha uma senha segura aqui para sua configuração! Faça login no usuário postgres e acesse o terminal front-end do postgres psql.

su - postgres  
psql

Crie um novo usuário ‘replica‘ com a senha ‘aqwe123@‘ com a consulta postgres abaixo.

CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD 'aqwe123@';

Agora verifique o novo usuário com a consulta ‘du‘ abaixo, e você verá o usuário replica com privilégios de replicação.

\du

A configuração do servidor MASTER foi concluída.

Passo 4 - Configurar o Servidor SLAVE

O servidor SLAVE tem o endereço IP 10.0.15.11. E este servidor terá apenas permissão de LEITURA no banco de dados. O servidor de banco de dados Postgres será executado sob o endereço IP do servidor, não um IP localhost.

Pare o serviço postgres no servidor slave com o comando systemctl abaixo.

systemctl stop postgresql

Vá para o diretório de configuração do Postgres ‘/etc/postgresql/9.6/main‘, em seguida, edite o arquivo de configuração ‘postgresql.conf‘.

cd /etc/postgresql/9.6/main/  
vim postgresql.conf

Descomente a linha listen_addresses e substitua o valor pelo endereço IP do servidor slave 10.0.15.11.

listen_addresses = '10.0.15.11'

Descomente a linha ‘wal_level’ e altere o valor para ‘hot_standby‘.

wal_level = hot_standby

Para o nível de sincronização, descomente a linha synchronous_commit e altere o valor como mostrado abaixo.

synchronous_commit = local

Para a configuração de replicação, descomente a linha max_wal_senders e substitua o valor por ‘2‘ porque usamos apenas 2 servidores. E para wal_keep_segments, altere o valor para ‘10‘.

max_wal_senders = 2  
wal_keep_segments = 10

Descomente a linha ‘synchronous_standby_names’ para o nome do aplicativo e altere o valor para o nome ‘pgslave001‘.

synchronous_standby_names = 'pgslave001'

Ative hot_standby para o servidor slave descomentando a linha a seguir e alterando o valor para ‘on‘.

hot_standby = on

Salve o arquivo e saia do editor.

Passo 5 - Copiar Dados do PostgreSQL do MASTER para o SLAVE

Em seguida, queremos substituir o diretório principal do postgres no servidor ‘SLAVE‘ pelo diretório de dados principal do servidor ‘MASTER‘.

Faça login no servidor SLAVE e acesse o usuário postgres.

su - postgres

Vá para o diretório de dados do postgres ‘main‘ e faça um backup renomeando o nome do diretório.

cd 9.6/  
mv main main-bekup

Crie um novo diretório ‘main‘ como usuário ‘postgres‘ e certifique-se de que tenha uma permissão como o diretório main-bekup.

mkdir main/  
chmod 700 main/

Em seguida, copie o diretório principal do servidor MASTER para o servidor SLAVE com o comando pg_basebackup, usaremos o usuário replica para realizar essa cópia de dados.

pg_basebackup -h 10.0.15.10 -U replica -D /var/lib/postgresql/9.6/main -P --xlog  
Senha:

Quando a transferência de dados estiver completa, vá para o diretório de dados principal e crie um novo arquivo recovery.conf.

cd /var/lib/postgresql/9.6/main/  
vim recovery.conf

Cole a configuração abaixo:

standby_mode = 'on'  
primary_conninfo = 'host=10.0.15.10 port=5432 user=replica password=aqwe123@ application_name=pgslave001'  
restore_command = 'cp /var/lib/postgresql/9.6/main/archive/%f %p'  
trigger_file = '/tmp/postgresql.trigger.5432'

Salve e saia, em seguida, altere as permissões do arquivo para 600 com chmod.

chmod 600 recovery.conf

Agora inicie o PostgreSQL 9.6 no servidor SLAVE e certifique-se de que o serviço postgres esteja em execução no endereço IP 10.0.15.11 com netstat.

systemctl start postgresql  
netstat -plntu

A transferência de dados e a configuração para o servidor SLAVE foram concluídas.

Passo 6 - Testes

Para testar, verificaremos o status da replicação do PostgreSQL 9.6 e tentaremos criar uma nova tabela no servidor MASTER, em seguida, verificaremos a replicação verificando todos os dados do servidor SLAVE.

Faça login no servidor MASTER e faça login no usuário postgres.

su - postgres

Execute os comandos psql abaixo para ver o status da replicação.

psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"  
psql -x -c "select * from pg_stat_replication;"

Você obterá os resultados como abaixo.

Em seguida, tente criar uma nova tabela no servidor MASTER. Faça login no usuário postgres no servidor master.

su - postgres  
psql

E crie uma nova tabela chamada ‘replica_test‘ e insira dados na tabela executando as consultas postgres abaixo.

CREATE TABLE replica_test (hakase varchar(100));  
INSERT INTO replica_test VALUES ('howtoforge.com');  
INSERT INTO replica_test VALUES ('Isso é do Master');  
INSERT INTO replica_test VALUES ('replicação pg por hakase-labs');

Em seguida, faça login no usuário postgres no servidor SLAVE e acesse o terminal psql.

su - postgres  
psql

Verifique os dados na tabela ‘replica_test‘ com a consulta postgres abaixo.

select * from replica_test;

E você obterá os mesmos dados que no MASTER, está replicado do servidor MASTER para o servidor SLAVE.

Teste adicional:

Teste para escrever no servidor SLAVE com a consulta abaixo.

INSERT INTO replica_test VALUES ('isso é SLAVE');

E você receberá uma mensagem de erro sobre ‘ Não é possível executar a consulta INSERT ‘ no servidor SLAVE.

A instalação e configuração do PostgreSQL 9.6 com Replicação Master-Slave no Ubuntu 16.04 Xenial Xerus foi bem-sucedida.

Referência

• https://www.howtoforge.com/tutorial/postgresql-replication-on-ubuntu-15-04/