Replicação no PostgreSQL 

Replicação no PostgreSQL ● O que é replicação? ● Tipos de Replicação ● Replicação Nativa ● Replicação Física ● Replicação Lógica 

Replicação no PostgreSQL O que é replicação? 

Replicação no PostgreSQL O que é replicação Técnica que consiste manter uma cópia de uma ou mais tabelas de um banco de dados e sincronizá-las enviando dados de atualização para a réplica. ● Utilizado para alta disponibilidade ● Utilizado para balanceamento de carga (escalabilidade horizontal) ● Utilizado na migração de servidores e/ou versão ● Replicação é diferente de Sharding, embora sejam correlatos 

Replicação no PostgreSQL Tipos de Replicação 

Replicação no PostgreSQL Tipos de replicação Pode utilizar diversas técnicas ● Uso de discos compartilhados (NAS) ● Replicação por sistema de arquivos (DRDB) ● SQL Based com um Middleware (pgpool-II) ● Trigger Based (Londiste, Slony, Bucardo) ● Write-Ahead Log Shipping https://www.postgresql.org/docs/current/different-replication-solutions.html 

Replicação no PostgreSQL Tipos de replicação Discos compartilhados 

Replicação no PostgreSQL Tipos de replicação Replicação de sistema de arquivos 

Replicação no PostgreSQL Tipos de replicação SQL Based com middleware 

Replicação no PostgreSQL Tipos de replicação Síncrono ● Todos os nós tem sempre a mesma informação em tempo real ● Quando um dado é alterado, o COMMIT da transação só é liberada após todos os nós aplicarem a alteração. Assíncrono ● Existe um atraso na sincronia uma alteração de um dado na origem e nas réplicas. ● Esse atraso pode ser mínimo, pode possuir um limite máximo e pode ser feito sob demanda 

Replicação no PostgreSQL Tipos de replicação Master / Slave ● Apenas um nó recebe alterações (o master) e envia estas alterações para os demais nós que só recebem leitura destes dados (os slaves). ● Podem haver replicação em cascata, onde um slave, possui um ou mais slaves. ● Quando Master falha, o slave pode assumir como nó principal Multimaster ● Todos os nós podem gravar ao mesmo tempo e enviam as alterações para os demais nós ● Quando maior o número de nós, maior o overhead para a sincronia. ● Precisa lidar com colisões, quando o mesmo dado é alterado em mais de um nó ao mesmo tempo. 

Replicação no PostgreSQL Tipos de replicação Física ● Baseada na cópia exata dos arquivos Lógica ● Baseada em comandos SQL 

Replicação no PostgreSQL Replicação Nativa 

Replicação no PostgreSQL Replicação nativa O PostgreSQL oferece soluções de replicação nativa, que não necessitam de nenhum software ou hardware adicional para funcionar. ● Permite replicação Física ou Lógica ● A sincronia é feita através da técnica de Log Shipping (envio de logs de transação) ○ Possui baixo overhead no Master ● Apenas do tipo Master / Slave ○ Permite sincronizar com vários Slaves ao mesmo tempo ● Permite a replicação em cascata 

Replicação nativa WAL (Write Ahead Log) 

Replicação nativa WAL (Write Ahead Log) ls -lh 16/main/pg_wal/ total 128M -rw------- 1 postgres postgres 16M May 28 11:50 000000010000000000000008 -rw------- 1 postgres postgres 16M May 28 11:41 000000010000000000000009 -rw------- 1 postgres postgres 16M May 28 11:41 00000001000000000000000A -rw------- 1 postgres postgres 16M May 28 11:41 00000001000000000000000B -rw------- 1 postgres postgres 16M May 28 11:41 00000001000000000000000C -rw------- 1 postgres postgres 16M May 28 11:42 00000001000000000000000D -rw------- 1 postgres postgres 16M May 28 11:42 00000001000000000000000E -rw------- 1 postgres postgres 16M May 28 11:42 00000001000000000000000F drwx------ 2 postgres postgres 6 May 28 11:30 archive_status 

Replicação nativa WAL (Write Ahead Log) ● Os arquivos de WAL ou logs de transação são armazenados em $PGDATA/pg_wal ● Cada arquivo contém 16MB (padrão) ● Arquivos são nomeados como 000000020000070A0000008E (números hexadecimais) ● A numeração Inicia em 000000010000000000000000 e nunca reinicia. ○ 00000002: linha do tempo (timeline) ○ 0000070A: log de transação (lógico) ○ 0000008E: segmento do log de transação (de 00 a FF) 

Replicação nativa Checkpoint ● O checkpoint é um processo que atualiza os datafiles do banco com as últimas alterações que estão em memória no shared_buffers ● A cada checkpoint, os arquivos de WAL não mais necessários são apagados ● Parâmetros min_wal_size e max_wal_size determinam as quantidades mínima e máxima de WAL acumulados. ● Um checkpoint acontece automaticamente quando o volume de arquivos atinge o limite em max_wal_size ou quando atinge o limite de tempo em checkpoint_timeout 

Replicação nativa Archiving ● Cópia dos logs de transação (WAL) para outro local, antes deles serem apagados automaticamente ● Deve estar sempre ativo em ambientes de produção ● Para ativar o archiving deve-se configurar os parâmetros abaixo ○ archive_mode = on ○ archive_command ou archive_library https://www.postgresql.org/docs/current/continuous-archiving.html 

Replicação nativa Log Shipping 

Replicação no PostgreSQL Replicação nativa, comparação Física Lógica Versão >= 9.0 >= 10 Objetos Full cluster Full database, full schema, full table ou table with filters Versão Mesmo SO e Versão do PostgreSQL Só precisa ter versão >=10 do PostgreSQL Sincronia Assíncrono ou Síncrono Assíncrono Log Shipping Manual ou Streaming Streaming Slot Replication (PG >= 9.5) Opcional (mas recomendado) Obrigatório Acesso Warm Standby, Hot Standby Banco aberto para leitura e gravação* Limitações Exige acesso ao SO Não exige acesso ao SO 

Replicação no PostgreSQL Replicação nativa Parâmetros : ● listen_addresses: Deve apontar para o IP do servidor que recebe conexões externas ou ‘*’. O valor padrão ‘localhost’, não vai permitir a sincronia remota. ● wal_level: configura a quantidade de informação enviada nos logs de transação. Possui os valores 'minimal', 'replica' ou 'logical'. O padrão é 'replica' que serve para a replicação física, mas precisa ser 'logical' para a replicação lógica. ● max_wal_senders: número de processos para envio de dos logs de transação. Precisa ser maior que zero, recomendamos pelo menos 2. Pode ser usado tanto na réplica lógica, como na física, no backup físico e outras ferramentas. ● max_replication_slots: número de slots de replicação que podem ser criados no master. Precisa ser maior que zero, mas recomendamos pelo menos 2. ● max_slot_wal_keep_size : Volume máximo de logs de transação retidos no master aguardando a sincronia no slave. Após este limite o slot de replicação é apagado. 

Replicação no PostgreSQL Replicação nativa Role de replicação ● Criar uma role com a opção REPLICATION: CREATE ROLE replication_user REPLICATION LOGIN; ● Ajustar o arquivo pg_hba.conf para permitir a conexão remota do slave como REPLICATION host replication replication_user 192.168.0.2/32 scram-sha-256 

Replicação no PostgreSQL Replicação nativa Monitoramento ● pg_stat_replication ● pg_stat_replication_slots ● pg_replication_slots 

Replicação no PostgreSQL Replicação Física 

Replicação no PostgreSQL Replicação Física (ou standby) Vantagens ● Ideal para alta disponibilidade ● Fácil de criar a partir de um backup físico ● Garante a integridade de todos objetos ● Opção de replicação síncrona (com overhead de ~30%) Desvantagens ● Precisa rodar sempre na mesma versão do SO e o PostgreSQL ● Não pode replicar objetos parcialmente ● Acoplamento entre o master e o slave (conflitos) 

Replicação no PostgreSQL Replicação Física Pergunta: O hardware na réplica (pode chamar de slave, ou standby) precisa ser igual ao do master ou pode ser inferior? Resposta: Não precisa ser igual, mas deveria. Caso o master fique fora do ar e você promova um slave para master, ele vai precisar suportar a mesma carga do master. 

Replicação no PostgreSQL Replicação Física (ou standby) Métodos de sincronia com log shipping ● Archive ○ Utiliza um processo próprio no SO, conhecido como archiver ○ O servidor master deve estar com o archive configurado ○ A réplica deve configurar o restore_command corretamente para aplicar os archives gerados no master. ● Streaming ○ Utiliza o processo walsender no master e o walreceiver na réplica ○ Precisa que o parâmetro max_wal_senders seja maior que zero no master ○ Precisa configurar o primary_conninfo na réplica, com as informações para se conectar no master. 

Replicação no PostgreSQL Replicação Física (ou standby) Comandos permitidos numa réplica física tipo Hot Standby: ● Consultas: SELECT, COPY TO ● Cursores: DECLARE, FETCH, CLOSE ● Parâmetros: SHOW, SET, RESET ● Transações: ○ BEGIN, END, ABORT, SART TRANSACTION ○ SAVEPOINT RELEASE, ROLLBACK TO SAVEPOINT ○ EXCEPTION ● LOCK TABLE nos modos ACCESS SHARE, ROW SHARE E ROW EXCLUSIVE ● Prepared statements: PREPARE, EXECUTE, DEALLOCATE, DISCARD 

Replicação no PostgreSQL Replicação Física (ou standby) Conflitos ● Ocorrem quando uma consulta na réplica está acessando um objeto enquanto no master: ○ LOCK do tipo ACCESS EXCLUSIVE ○ DROP TABLESPACE, DROP TABLESPACE ○ VACUUM ● O limite de tempo que a réplica espera para a consulta terminar é configurada pelos parâmetros abaixo. Valores altos para este parâmetro podem atrasar a sincronia da réplica.: ○ max_standby_archive_delay ○ max_standby_streaming_delay ● Para evitar que o VACUUM cancele consultas no slave, use o parâmetro abaixo. Habilitar este parâmetro pode cancelar a execução do VACUUM e inchar as tabelas: ○ hot_standby_feedback 

Replicação no PostgreSQL Replicação Física Parâmetros importantes no slave ● recovery_command: Comando utilizado para aplicar logs de transação. ● primary_conninfo:Parâmetros de conexão via stream no master, como IP, porta, usuário, senha, etc. Você pode utilizar o arquivo .pgpass para armazenar as senhas. ● primary_slot_name: Nome do slot de replicação utilizado no master. Dois slaves não devem utilizar o mesmo slot. ● hot_standby: Permite que a réplica fique aberta para leitura. O padrão é on, não mude isso. 

Replicação no PostgreSQL Replicação Física Outros parâmetros no slave ● max_standby_streaming_delay e max_standby_archive_delay:Tempo máximo que a réplica segura a atualização dos dados enquanto aguarda a execução de uma consulta longa em conflito com o master. O padrão é 30 segundos. ● hot_standby_feedback: Permite que consultas longas na réplica cancelem o VACUUM no master. O padrão é desligado. ● recovery_target_timeline: Define se o standby deve seguir o master caso ele mude de timeline. Deixar sempre em ‘latest’, o padrão. ● recovery_min_apply_delay: Permite criar uma réplica com uma defasagem mínima em relação ao master. ● Outros: wal_receiver_status_interval , wal_receiver_timeout , wal_retrieve_retry_interval 

Replicação no PostgreSQL Replicação Física Parâmetros no slave que devem ter valor maior ou igual ao master: ● max_connections ● max_prepared_transactions ● max_locks_per_transaction ● max_wal_senders ● max_worker_processes 

Replicação no PostgreSQL Replicação Física Roteiro (utilizando log shipping via stream e slot de replicação) ● No master: ○ Ajustar parâmetros ○ Criar usuário e ajustar o pg_hba.conf ○ Criar um slot de replicação: SELECT pg_create_physical_replication_slot('standby_slot'); 

Replicação no PostgreSQL Replicação Física Roteiro ● No slave: ○ Ajustar parâmetros no slave ■ PG <= 11 : configurar no $PGDATA/recovery.conf ■ PG >= 12: configurar no postgresql.conf ○ Limpar o diretório de destino ○ Restaurar o último backup físico. ○ Criar ou verificar a existência do arquivo ■ PG <= 11: $PGDATA/recovery.conf ■ PG >= 12: $PGDATA/standby.signal ○ Subir o banco de dados no slave 

Replicação no PostgreSQL Replicação Física Exemplo - Master ● Ajustar parâmetros: ALTER SYSTEM SET listem_addresses = '*'; ALTER SYSTEM SET wal_level = 'replica'; ALTER SYSTEM SET max_wal_senders = 10; ALTER SYSTEM SET max_replication_slots = 10; ALTER SYSTEM SET primary_conninfo = 'user=replication_user host=10.108.0.2'; ALTER SYSTEM SET primary_slot_name = 'standby_slot'; ALTER SYSTEM SET hot_standby = on; ALTER SYSTEM SET recovery_target_timeline = 'latest’; 

Replicação no PostgreSQL Replicação Física Exemplo - Master ● Criar o slot de replicação física: SELECT pg_create_physical_replication_slot('standby_slot'); ● Criar usuário CREATE ROLE replication_user REPLICATION LOGIN; ● Ajustar o pg_hba.conf host replication replication_user 192.168.0.2/32 scram-sha-256 ● Reiniciar systemctl restart postgresql 

Replicação no PostgreSQL Replicação Física Exemplo - Slave ● Baixar o cluster pré-existente systemctl stop postgresql ● Remover arquivos existentes: rm -rf /var/lib/postgresql/16/main/* ● Realizar o backup físico pg_basebackup -RPv -S standby_slot -h 192.168.0.1 -U replication_user -D /var/lib/postgresql/16/main ● Criar o arquivo standby.signal touch /var/lib/postgresql/16/main/standby.signal ● Subir a réplica systemctl start postgresql 

Replicação no PostgreSQL Replicação Física Processos (master) ps faux | grep postgres /usr/lib/postgresql/16/bin/postgres \_ postgres: 16/main: checkpointer \_ postgres: 16/main: background writer \_ postgres: 16/main: walwriter \_ postgres: 16/main: autovacuum launcher \_ postgres: 16/main: archiver \_ postgres: 16/main: logical replication launcher \_ postgres: 16/main: walsender replication_user 10.108.0.3(32826) streaming 0/759E57A8 \_ postgres: 16/main: postgres postgres [local] idle 

Replicação no PostgreSQL Replicação Física Processos (slave) ps faux | grep postgres /usr/lib/postgresql/16/bin/postgres \_ postgres: 16/main: checkpointer \_ postgres: 16/main: background writer \_ postgres: 16/main: startup recovering 000000010000000000000075 \_ postgres: 16/main: walreceiver streaming 0/759E5858 

Replicação no PostgreSQL Replicação Física Monitoramento ● Master SELECT * FROM pg_replication_slots; SELECT * FROM pg_stat_replication_slots; SELECT * FROM pg_stat_replication; ● Slave SELECT * FROM pg_stat_wal_receiver; SELECT * FROM pg_stat_database_conflicts; 

Replicação no PostgreSQL Replicação Física Failover manual ● pg_ctl promote Exemplo: pg_ctl promote -D /var/lib/pgsql/16 ● pg_ctlcluster promote (Debian e derivados) Exemplo: pg_ctlcluster 16 main promote 

Replicação no PostgreSQL Replicação Física Failover Automático ● Só é possível a partir de ferramentas externas como: ○ PAF (PostgreSQL Automatic Failover) https://clusterlabs.github.io/PAF ○ repmgr (Réplica Manager for PostgreSQL Clusters) https://www.repmgr.org ○ pg_auto_failover https://github.com/hapostgres/pg_auto_failover ○ Patroni https://github.com/zalando/patroni 

Replicação no PostgreSQL Replicação Lógica 

Replicação no PostgreSQL Replicação Lógica Vantagens ● Ideal para balanceamento de carga, data warehouse, migração entre versões ● Replicação total, parcial, com filtros, etc. ● Grande flexibilidade Desvantagens ● A réplica inicial, pode levar muito tempo, particularmente com tabelas grandes. ● Alterações de DDL no master não são replicadas automaticamente no slave ● Como a base slave fica aberta para gravação, podem haver colisões, e violações de chave ● Tabelas replicadas não podem utilizar o pg_repack https://www.postgresql.org/docs/current/logical-replication.html 

Replicação no PostgreSQL Replicação Lógica Limitações ● Apenas tabelas podem ser replicadas. Sequences, Views, Materalized Views, Large Objects, Foreign Tables não podem ser replicados. ● Toda tabela replicada deve ter uma chave primária (PK) ou uma chave única não nula (UK + NOT NULL). Caso contrário será necessário um processo mais trabalhoso para replicar a tabela conhecida como IDENTITY FULL ● Alterações na estrutura da tabela (DDL) não são replicadas ○ Se for remover uma coluna, remova primeiro no master e depois nos slaves ○ Se for adicionar uma coluna, adicione primeiro no slave e depois no master ● Se houver algum conflito na réplica, todas as demais alterações serão enfileiradas até o conflito ser resolvido 

Replicação no PostgreSQL Replicação Lógica Roteiro ● No master ○ Ajustar parâmetros: listen_addresses, wal_level, max_wal_senders, max_replications_slots, max_slot_wal_keep_size ○ Criar usuário e ajustar pg_hba.conf ○ Criar publicação ○ Conceder permissão de leitura nos objetos ○ Exportar DDL dos objetos ● No slave ○ Importar DDL dos objetos ○ Criar subscrição 

Replicação no PostgreSQL Replicação Lógica Exemplo - Master ● Ajustar parâmetros ALTER SYSTEM SET wal_level TO 'logical'; ALTER SYSTEM SET max_wal_senders TO 10; ALTER SYSTEM SET max_replication_slots TO 10; ALTER SYSTEM SET max_slot_wal_keep_size TO '2GB'; ● Criar usuário CREATE ROLE replication_user REPLICATION LOGIN PASSWORD 'FdeOS+nlC3Pt02ko'; ● Ajustar pg_hba.conf host replication replication_user 192.168.0.2/32 scram-sha-256 ● Reiniciar systemctl restart postgresql ● Criar publicação no Master CREATE PUBLICATION pagila_pub FOR TABLE film, film_actor, actor; ● Conceder permissão de leitura nas tabelas GRANT SELECT ON TABLE film, film_actor, actor TO replication_user; https://www.postgresql.org/docs/current/sql-createpublication.html 

Replicação no PostgreSQL Replicação Lógica Exemplo - slave ● Exportar DDL dos objetos pg_dump -s -h 192.168.0.1 -t film -t film_actor -t actor pagila > pub_tables.sql ● Importar DDL dos objetos psql -f pub_tables.sql pagila ● Criar subscrição no Slave CREATE SUBSCRIPTION table_sub CONNECTION 'host=192.168.0.1 user=replication_user dbname=pagila' PUBLICATION pagila_pub; 

Replicação no PostgreSQL Replicação Lógica Publication (selecionando objetos) ● Tabelas CREATE PUBLICATION xpto FOR TABLE table_a, table_b, table_c; ● Schemas CREATE PUBLICATION xpto FOR TABLES IN SCHEMA foo, bar; ● Todas tabelas do banco CREATE PUBLICATION xpto FOR ALL TABLES; https://www.postgresql.org/docs/current/sql-createpublication.html 

Replicação no PostgreSQL Replicação Lógica Publication (filtros) ● Filtrando dados de uma tabela CREATE PUBLICATION xpto FOR TABLE table_a WHERE (status = 'active'); ● Selecionando apenas algumas colunas de uma tabela CREATE PUBLICATION xpto FOR TABLE table_a (id, name, status); ● Filtrando operações CREATE PUBLICATION xpto FOR TABLE table_a, table_b WITH (publish = 'insert', 'update'); 

Replicação no PostgreSQL Replicação Lógica Subscription (principais parâmetros) ● CONNECTION: descreve uma string de conexão, com parâmetros utilizados pela libpq ● PUBLICATION : nome de uma ou mais publicações no master ● WITH: ○ create_slot: cria um slot de replicação automaticamente no master (o padrão é true) ○ enabled: ativa a replicação logo após a criação da subscrição (o padrão é true) ○ slot_name: quando especificado utiliza um slot de replicação já existente no master. ○ binary: ativa o envio dos dados no formato binário (o padrão é false) ○ copy_data: ativa uma cópia inicial de todos os dados das tabelas (o padrão é true) https://www.postgresql.org/docs/current/sql-createsubscription.html 

Replicação no PostgreSQL Replicação Lógica Cuidados ● Todas alterações de DDL feitas no Master devem ser realizadas manualmente em todas as réplicas lógicas. ERROR: logical replication target relation "public.existing_table" is missing replicated columns: "my_new_col", "type" ● Os dados das tabelas replicadas no slave podem ser alterados com os comandos INSERT, UPDATE e DELETE, mas isso pode gerar conflito com os dados que vem do Master. ERROR: duplicate key value violates unique constraint "test_pkey" DETAIL: Key (c)=(1) already exists. CONTEXT: processing remote data for replication origin "pg_16395" during "INSERT" for replication target relation "public.test" in transaction 725 finished at 0/14C0378 

Replicação no PostgreSQL Replicação Lógica Monitoramento ● Master pg_replication_slots pg_stat_replication_slots pg_stat_replication pg_publication pg_publication_namespace pg_publication_tables ● Slave pg_stat_wal_receiver pg_stat_database_conflicts pg_subscription pg_subscription_rel pg_stat_subscription 

Save the date! 07 e 08 de novembro: PGConf.Brasil2024 PUC Belo Horizonte - MG ● PGConf.Brasil Instagram ● PGConf.Brasil Telegram ● PostgreSQL BR Telegram ● PostgreSQL (English) Telegram 

Dúvidas? Contatos: ● Savepoint ● Slides: Speaker decker (Fábio Telles) ● Email: [email protected] ● LinkedIn: Telles