PostgreSQL: Como turbinar o seu Elefante - Lord Geek Café
Fala pessoal, mais um POST, agora "PostgreSQL: Como turbinar o seu Elefante!!"
Em certos sistemas de computador (chamados de sistemas críticos) é necessário requisitos de alta-disponibilidade.
Uma arquitetura de HA (High Availability – alta-disponibilidade) pode ser definida como uma arquitetura tolerante a falhas de hardware, software e energia, cujo objetivo é manter os serviços operantes o máximo de tempo possível.
Uma boa arquitetura HA leva em consideração a manutenção dos serviços, para que quando ocorra uma parada não programada, os serviços possam ser restabelecidos o mais breve possível.
Baseado nestes requisitos este POST descreve a instalação do PostgreSQL 9.5 em sistema operacional Ubuntu 14.04 LTS.
Arquitetura HA proposta a instalação do PostgresSQL
A arquitetura proposta leva em consideração os requisitos de alta disponibilidade e resiliência de um sistema de Banco de Dados:
Redução da concorrência de acesso ao sistema de banco de dados;
Segurança da informação contra acessos não autorizados e mal intencionados;
Aumento da eficiência da escrita no banco de dados;
Backups em tempo real;
A arquitetura proposta é mostrada na Figura 1. Todo o conceito da arquitetura está baseado na funcionalidade de replicação dos bancos de dados. Existem várias formas e ferramentas para realizar esta replicação, como por exemplo:
Slony – (Permite maior granularidade, com opção de escolher até mesmo a tabela);
Bucardo – (Permite maior granularidade, com opção de escolher até mesmo a tabela);
xDB – Enterprise DB – (Permite a replicação entre Sistema de BD, ex. PostgreSQL e Oracle)
Streaming Replication – Nativo a partir do PostgreSQL 9.0
Figura 1 – Arquitetura de implantação do PostgreSQL HA
Utilizaremos o Streaming Replication. Esta funcionalidade faz a replicação do Cluster inteiro. A escolha pelo Streaming Replication foi pelo fato de que esta funcionalidade já é nativa no PostgresSQL sem nenhum custo adicional. Além disso, a sua simplicidade e eficiência foram levadas em consideração.
Considerações para um melhor desempenho
A partir do PostgreSQL 9.0 é permitido trabalhar com a opção hot_standby, que possibilita que os bancos SLAVES (os bancos replicados) possam ser acessados como somente leitura, ou seja, permitem comandos de select.
Evitando tráfego na rede
Esta funcionalidade é bastante útil e colabora com parte da arquitetura proposta, que sugere separar o sistema em duas camadas:
Camada de visualização, que permite somente leitura;
Camada de processamento, que permite leitura e escrita;
Na camada de visualização estão alocados os servidores e serviços que acessam a aplicação apenas para consultar os dados. Na maioria dos casos são aplicação que estão disponíveis na WEB e comumente não fazem escrita no banco de dados.
Na camada de visualização estão alocados os servidores e serviços que fazem a captura dos dados e persistem no banco. Geralmente são sistemas que fazem algum tipo de processamento de dados brutos e produzem dados secundários. Esta prática também colabora para a segurança da informação, pois como permite somente a leitura dos dados evita acessos mal intencionados com SQL Injections por exemplo.
A implementação desta arquitetura permite que sejam criadas uma topologia de rede que separem o fluxo de informação entre essas duas camadas, reduzindo a colisão de dados em sistema com grande fluxo de informação.
Na Figura 1 é possível observar que a camada de visualização tem acesso à rede de dados que acessam os servidores SLAVES e a camada de processamento tem acesso à rede que acessa diretamente o servidor MASTER de banco de dados.
Aumentando a eficiência de escrita no banco master
O PostgreSQL escreve os dados e commites primeiro no seu arquivo de log, conhecido como WAL, e periodicamente transfere os logs para a área durável (checkpoint, flush).
Em um banco de dados que recebe muitas atualizações/escritas, os logs são intensamente requisitados. Sendo que a área durável é muitas vezes menos requisitada.
Para aumentar a eficiência neste processo, e evitar a concorrência por acesso no disco, recomenda-se separar fisicamente essas duas áreas, se possível evitar que controladora do disco sejam as mesma.
Segurança e resiliência nas informações
Aplicando esta arquitetura terá implementado um sistema de backup online, ou seja, sempre haverá uma ou mais cópia do cluster do banco de dados.
Como boa prática recomenda-se:
que os servidores SLAVES sejam instalados em máquinas fisicamente distintas;
que pelo menos um dos servidores SLAVES seja instalado em uma máquinas geograficamente distinta;
que seja implantado um sistema backup tradicional, para fazer a cópia deste banco para uma mídia que possa ser armazenada em local seguro;
implementar um procedimento para restauração do sistema em caso desastre;
Este procedimento leva em consideração estas recomendações.
Este documento leva em consideração que o sistema operacional Ubuntu 14.04 LTS já esteja instalado e devidamente configurado.
Após a instalação recomenda-se atualizar o sistema:
Este processo de instalação e configuração deve ser utilizado tanto no MASTER quanto no SLAVE.
Nesta instalação, é utilizado o repositório oficial do PostgreSQL em detrimento do uso do repositório da distribuição. Essa escolha é motivada pelas correções e atualizações mais frequentes acontecerem no repositório oficial. Além disso, isso evita que com a extinção do suporte da distribuição à versão do sistema operacional usado, o PostgreSQL instalado deixe de ter suporte.
No momento da criação deste documento a versão estável mais atual do PostgreSQL é a 9.5.
Para fazer com que o sistema operacional utilize o repositório oficial:
Crie o arquivo /etc/apt/sources.list.d/pgdg.list e adicione o endereço do repositório oficial "deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main", para isso execute:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release-cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
Importar a chave de assinatura do repositório, e atualizar as listas e pacotes:
Instalar o sistema de banco de dados:
Alterar a senha do usuário postgres do Linux para uma senha conhecida:
Dica: Neste documento a senha utilizada foi "postgres"
Alterar a senha do usuário postgres do banco de dados para uma senha conhecida:
psql -c "ALTER USER postgres WITH PASSWORD 'masterkey'" -d template1
Incluir o usuário postgres do Linux como sudo:
sudo usermod -aG sudo postgres
Alterando a data do sistema para GMT0
No banco de dados é altamente recomendável trabalhar com o sistema de data em UTC ou GMT0.
Para mudar o sistema de data do Linux execute:
ln -sf /usr/share/zoneinfo/GMT0 /etc/localtime
Outra atividade importante é a instalação de um servido de ntp para que os sistema de data fique
Dica: Após a instalação inclua alguns servidores ntp, para isso:
Inclua os seguintes servidores:
Configurações para o Cluster MASTER
Este processo de instalação e configuração deve ser utilizado apenas no MASTER.
A configuração mais importante a ser levada em consideração no Cluster MASTER é a separação dos logs (WAL) dos dados duráveis.
Se possível separar em discos fisicamente diferentes, para evitar a concorrência em disco, e se ainda houver a possibilidade, separá-los em controladoras de discos diferentes.
Dica: Essa configuração é bastante simples, resumindo a criação de um diretório para os dados e outro para o log. Neste caso o ideal é criar um em cada disco. No exemplo deste documento os dois diretórios serão criado no mesmo disco, apenas como demonstração.
Criar diretório para armazenamento dos dados duráveis:
sudo mkdir -p /DATA/pg_data/data
sudo chown -R postgres:postgres /DATA/pg_data
Criar a estrutura de diretórios para o novo Cluster:
su - postgres -c "/usr/lib/postgresql/9.5/bin/initdb -D /DATA/pg_data/data"
Após a criação da estrutura do novo diretório, deve-se alterar a localização do log (WAL):
Dica: Por padrão o log armazenado na pasta pg_xlog dentro da pasta de dados do cluster. No exemplo deste documento ela pode ser localizada em: /DATA/pg_data/data/pg_xlog
Criar os novos diretórios para o log:
sudo mkdir -p /DATA/pg_xlog
sudo chown postgres:postgres /DATA/pg_xlog
Passo 3.2:
Parar o serviços do PostgreSQL:
Passo 3.3:
Agora é necessário copiar os arquivos antigos para a nova pasta:
cp -R -p /DATA/pg_data/data/pg_xlog/* /DATA/pg_xlog/
Passo 3.4:
Remover a pasta original de dentro da pasta de dados:
rm -rf /DATA/pg_data/data/pg_xlog
Passo 3.5:
Criar um link simbólico dentro da pasta de dados apontando para a nova pasta de log:
ln -s /DATA/pg_xlog/ /DATA/pg_data/data/pg_xlog
No próximo passo é necessário alterar a configuração para indicar onde os dados do cluster serão armazenados:
Dica: Neste post o arquivo que deverá ser alterado encontra-se em /etc/postgresql/9.5/main/postgresql.conf.
Editar o arquivo de configurações:
vim /etc/postgresql/9.5/main/postgresql.conf
Passo 4.2:
Altere a linha data_directory para indicar o novo diretório onde estão localizados os dados do cluster MASTER:
data_directory = '/DATA/pg_data/data'
Passo 4.3:
Altere a linha hba_file para indicar o novo diretório do cluster MASTER, onde o arquivo pg_hba.conf está localizado:
hba_file = '/DATA/pg_data/data/pg_hba.conf'
Passo 4.4:
Altere a linha ident_file para indicar o novo diretório do cluster MASTER, onde o arquivo pg_ident.conf está localizado:
ident_file = '/DATA/pg_data/data/pg_ident.conf'
Após indicar a configuração de onde os dados no novo cluster estão localizados, é necessário alterar acesso e indicar os parâmetros para sincronização com os SLAVES.
Dica: Neste documento o arquivo que deverá ser alterado encontra-se em /etc/postgresql/9.5/main/postgresql.conf, o mesmo arquivo alterado anteriormente. Caso ele continue aberto por conta do procedimento anterior, não é necessário executar o passo 1.
Passo 5.1:
Passo 5.2:
Passo 5.3:
Altere as linhas wal_level para indicar como será a replicação entre os servidores MASTER e SLAVE:
Dica: Sem entrar em muitos detalhes o que será utilizado é o hot_standby, que indica que o log (WAL) inclui informações necessárias para reconstruir uma transação. Esta funcionalidade é que permite a replicação.
Passo 5.4:
Altere as linhas max_wal_senders para indicar qual o número máximo de processos de replicações podem ser executados ao mesmo tempo:
max_wal_senders = 20
Passo 5.5:
Altere as linhas wal_keep_segments para indicar qual tamanho em MB do log que será mantido no MASTER:
Dica: Caso um cluster SLAVE demore a sincronizar os dados (latência de rede, queda de energia e etc), assim que ocorrer a recuperação, a sincronização somente ocorrerá se o log último perdido estiver disponível.
wal_keep_segments = 4096
Passo 5.6:
Altere o parâmetro max_replication_slots para a quantidade de slaves previstos:
Dica: Fazendo uso dos replication slots, o controle de retenção dos arquivos de log (WAL) é feito de forma automática na replicação, não havendo necessidade de habilitar o parâmetro wal_keep_segments. No entanto, é necessário habilitá-lo inicialmente (conforme especificado no item anterior) para executar o pg_basebackup.
Concluída as alterações do arquivo de configurações, o próximo passo é alterar o arquivo que refere-se as autorizações de conexão de clientes externos, o pg_hba.conf.
Dica: Neste documento o arquivo que deverá ser alterado encontra-se no mesmo diretório dos dados, neste caso em /DATA/pg_data/data/pg_hba.conf.
Passo 6.1:
Editar o arquivo de autorizações:
sudo vim /DATA/pg_data/data/pg_hba.conf
Passo 6.2:
Incluir uma linha que indique qual IP ou mascara de rede que terá acesso como usuário ao cluster MASTER:
host   all   all   192.168.0.0/24    md5
Passo 6.3:
Incluir uma linha que indique qual IP ou mascara de rede que terá acesso como cliente de replicação ao cluster MASTER:
Dica: Este documento é uma tabela onde:
O primeiro campo: Indica o tipo de conexão. Como a conexão será externa, o valor deve ser host
O segundo campo: Indica qual database do cluster o cliente terá acesso. No caso de replicação deve ser utilizado replication.
O terceiro campo: Indica qual usuário terá acesso ao cluster. No caso de replicação você pode utilizar qualquer usuário que possua como regra a replicação. No caso deste documento será utilizado o replicator que será criado adiante.
O quarto campo: Indica qual IP/Mascara que terá acesso.
O quinto campo: Indica se o acesso será com senha. No caso deste documento utilizaremos senha.
host   replication   replicator   192.168.0.0/24  md5
Concluída essas configurações o cluster MASTER está pronto para ser iniciado. Na sequência é necessário fazer a criação do usuário replicator e a alteração de senha do postgres.
Passo 7.1:
Iniciar o serviço do cluster MASTER de banco de dados:
Passo 7.2:
Garantir que o usuário do Linux é o postgres:
Passo 7.3:
Alterar a senha do usuário postgres do Banco de dados:
Passo 7.4:
Criar o usuário responsável pela replicação:
psql -c "CREATE USER replicator replication"
Passo 7.5:
Alterar a senha do usuário de replicação:
psql -c "ALTER USER replicator WITH PASSWORD 'replicator'" -d template1
Dica: A senha utilizada neste documento é replicator.
Para visualizar os usuários cadastrados no cluster execute:
Criar replication slots (um para cada slave):
SELECT * FROM pg_create_physical_replication_slot('slot1');
SELECT * FROM pg_create_physical_replication_slot('slot2');
Desta forma o cluster MASTER está pronto e configurado para receber as conexões de clusters SLAVE.
Configurações para o Cluster SLAVE
Este processo de instalação e configuração deve ser utilizado apenas no SLAVE.
No caso do Cluster SLAVE diferentemente do MASTER não é permitido escrever no banco. Desta forma não se faz necessário separar o Log da área durável. Baseado nesta afirmação é possível deixar o cluster no diretório padrão do postgres.
Garanta que o processo de instalação, a a sessão "Instalação e configurações iniciais" tenha sido executado corretamente.
Importante: Tanto o servidor Master quanto o servidor SLAVE devem estar se comunicando via rede.
Remover os dados do diretório padrão:
Dica: O diretório padrão do cluster de dados neste caso é o /var/lib/postgresql/9.5/main/
rm -Rf /var/lib/postgresql/9.5/main/*
Realizar a cópia do Cluster MASTER para o SLAVE:
pg_basebackup -D /var/lib/postgresql/9.5/main/ -h 192.168.x.x -xlog-method=stream -l slave1 --checkpoint=fast
-D indica o diretório de destino da cópia no SLAVE;
-h indica o IP do MASTER (veja que no exemplo foi informado 192.168.x.x, no lugar de x.x deve ser informado o IP correto);
-xlog-method=stream indica que os logs de transação serão replicados enquanto o backup é criado;
-l indica o label do backup;
–checkpoint=fast para agilizar o início do processo de backup.
No próximo passo é necessário alterar a configuração do cluster SLAVE:
Editar o arquivo postgresql.conf:
sudo vim /etc/postgresql/9.5/main/postgresql.conf
Altere as linhas listen_addresses e port para permitir acesso externo:
Altere as linhas hot_standby indicar que o cluster será um SLAVE e permitirá acesso de somente leitura aos dados:
Criar o arquivo recovery.conf no diretório dos dados do cluster:
Dica: Este arquivo é o responsável pela conexão de replicação Nele iremos informar qual será o IP do cluster MASTER o usuário, senha e qual nome da aplicação, que sugerimos o nome da máquina SLAVE. Lembre-se que o usuário utilizado neste arquivo deve ser o mesmo usuário criado no MASTER com as regras de replicação. No caso deste documento estamos utilizando o usuário replicator.
sudo vim /var/lib/postgresql/9.5/main/recovery.conf
Inserir as linhas a seguir:
primary_conninfo='host=192.168.0.11 port=5432 user=replicator
password=replicator application_name=post2'
primary_slot_name=slot1
No cluster SLAVE, iniciamos o serviço:
Se tudo foi feito corretamente o cluster SLAVE foi iniciado corretamente, o que significa que já deve estar realizando a sincronização.
Como saber se a replicação está funcionando
Para confirmar se a sincronização está funcionando existem várias formas. Vamos mostrar alguns exemplos:
Executar o comando para mostrar os processos do postgres no MASTER e no SLAVE:
O resultado no SLAVE deve apresentar dentre os processos do postgres os processos:
1 - postgres: startup process recovering 0000000000000000088067860687
2 - postgres: wal receiver provess streaming 0/9000000
Um cluster SLAVE está sempre em recovering, e o processo wal receiver indica que ele instanciou
um receiver.
O resultado no MASTER deve apresentar dentre os processos do postgres os processos
1 - postgres: wal sender process replicator 192.168.x.x streming 0/9000000
O processo wal sender com o IP do maquina SLAVE.
Crie um banco de dados no MASTER e verifique se ele foi replicado no SLAVE:
create database exemplo_teste;
O Banco de dados exmeplo_teste deverá ser listado no SLAVE.
No MASTER, utilizar a VIEW pg_stat_replication;
table pg_stat_replication;
Serão exibidos na tabela os SLAVES conectados no MASTER.
No SLAVE, utilizar a função pg_is_in_recovery;
Esta função retorna True ou False. Se o cluster estiver True significa que ele está em modo de recuperação. Que para o nosso caso indica que ele está fazendo a replicação.
Caso o procedimento tenha sido seguido até este ponto, e nenhum erro ou divergência tenha sido encontrada, significa que o processo de replicação deve ter funcionado corretamente. Caso contrário, refaça o procedimento com atenção aos diretórios, e quais arquivos de configuração estão sendo alterados.
Outro problema conhecido o cliente não receber conexão externa. Neste caso é muito comum não ter configurado corretamente o arquivo pg_hba.conf, ou mesmo a presença de um firewall no servidor. Neste caso verifique e refaça os testes novamente.
Outra dica importante com relação principalmente a segurança e a utilização dos SLAVES é garantir que o acesso somente leitura esteja funcionando. Para isso tente realizar o procedimento de criação de um banco de dados no SLAVE. O sistema deve retornar uma mensagem de erro informando que o cluster é somente leitura.
Por fim….. acho que seu Elefante turbinado está pronto para o trabalho!!
Alta-disponibilidadeHAPostgresPostgreSQL
Flappy Dirma – Nosso primeiro Game para Android
Ótimo artigo!!! Muito didático e bem feito.
É possível/recomendado deixar duas maquinas com servidores master e nenhum slave?
Lord Geek Café 16 de abril de 2017
Olha, sua pergunta depende muito do cenário de uso e aplicação.
Mas a principio eu diria que não é recomendável.
O uso das máquinas slaves, é uma boa prática para o caso de sistemas somente consulta e alta demanda.
Rodrigo 23 de maio de 2017
Obrigado pela resposta. Minha preocupação é se o master der problema. O slave vai assumir o lugar? O que acontece?
André Ivo 5 de junho de 2017
desculpe a demora por responder.
sobre o Slave, não ele não vai assumir.
Para isso, minha sugestão é deixar pelo menos 2 máquinas master e 1 slave.
Com as máquinas master você precisará fazer um trabalho para coloca-las em alta disponibilidade.
Isso funciona teoricamente da seguinte forma:
1 – apenas uma das maquinas fica com o postgres ligado e funcionando;
2 – você vai precisar de um IP virtual; Pois é com ele que vc fará a mágica funcionar. Quando uma das maquinas cair o IP virtual será redirecionado para a outra;
3 – Existem alguns programas que fazem isso, como o heartbeat, corosync e pacemaker;
Rodrigo Traleski 30 de junho de 2017
Olá, primeiramente, muito bom o seu artigo. Ainda não apliquei, mas com certeza vou procurar seguir a risca e ver o resultado. Queria saber qual a sua opinião com relação ao meu cenário aonde utilizo o postgres. Nós temos um sistema ERP que é utilizado por diversos clientes e todos acessam o mesmo servidor postgres. Fazemos backup diários das bases para garantir a integridade e dos dados. O sistema possui muitas rotinas de cálculos que realiza leituras e posteriormente escritas no banco. A pergunta é, vc acha que criando uma estrutura MASTER/SLAVE nós teríamos ganho de desempenho? Disponibilidade acredito que sim, mas em termos de desempenho, o que vc pensa? São em torno de 200 tabelas.
André Ivo 1 de agosto de 2017
Podemos dividir o desempenho em duas partes.
1 – desempenho do banco
2 – desempenho da sua aplicação (neste talvez exista uma intersecção com a disponibilidade do banco.)
1 – Eu acredito que sim, você tera um grande grando de disponibilidade sim. O backup faz com que o servidor utilize muito I/O. Neste caso eu te aconselharia fazer um slave que sirva apenas para realizar o bkp, ou seja, retire a carga de backup do banco principal. Assim vc deixará a instancia do banco servindo apenas para o serviço que ele foi designado.