Replicação Lógica no PostgreSQL

O PostgreSQL 10 trouxe uma função nova muito interessante. Ele tem agora replicação nativa de bancos de dados chamada Logical Replication. Essa função nativa entrou de frente para competir com plugins e softwares externos que fazem isso em versões anteriores como Slony, Bucardo e BDR.

Configurando PostgreSQL RDS na AWS

O PostgreSQL implementado pela AWS é uma versão modificada do PostgreSQL e nem sempre conta com todas as funcionalidades, mas a partir do PostgreSQL 10.4 a AWS incluiu suporte à Logical Replication no RDS.

Configure o parameter group do seu banco

Crie ou edite o parameter group atual do seu banco de dados.

Procure pelo parâmetro rds.logical_replication e mude seu valor para 1.

Configure as permissões do seu usuário

No banco publicador

Aqui podemos usar dois usuários diferentes. Um que dará o comando para criar e iniciar a PUBLICATION e outro para ser usado no banco inscrito.

O usuário que criará a PUBLICATION precisa:

• ter a permissão CREATE no banco de dados onde a(s) tabela(s) a ser(em) replicada(s) está(estão);
• ser dono (ou participar de uma role que seja dona) da(s) tabela(s) a ser replicada;

O usuário que será passado ao banco inscrito precisa:

• ser da role rds_replication;
• ter permissão de SELECT nas tabelas a serem replicadas (ser dono/ter todas as permissões ou participar de uma role que as tenha serve).

No banco inscrito

Na AWS:

O usuário que irá dar o comando CREATE SUBSCRIPTION... precisa ser membro da rola rds_superuser.

Em PostgreSQL nativo:

Configure as permissões de rede VPC

Configure o firewall para permitir a entrada de dados para o ip e na porta desejada.

Iniciando a replicação lógica

No bd publicador:

[code language="SQL"] CREATE PUBLICATION publicacao_de_teste; ALTER PUBLICATION publicacao_de_teste ADD TABLE tabela_sincronizada; -- Para deletar: -- DROP PUBLICATION publicacao_de_teste; [/code]

No bd inscrito:

É preciso saber o IP local na AWS do bd publicador. Para descobrir o IP, execute a consulta:

[code language="sql"] SELECT inet_server_addr(); [/code]

Os parâmetros aceitos pelo CONNECTION são listados aqui.

[code language="SQL"] CREATE SUBSCRIPTION my_subscription CONNECTION 'host=db_master_private_ip_address port=5432 password=my_password user=my_user dbname=example' PUBLICATION publicacao_de_teste; [/code]

Ao criar a SUBSCRIPTION, o bd inscrito pede ao bd publicador que crie um replication slot com o nome da subscription. Esse replication slot é quem controla os estados e passos da replicação.

Verificando publications e subscriptions existentes no seu banco de dados

As publications e subscriptions são gravadas no escopo do servidor PostgreSQL como um todo, ou seja, ele salva todas as subscriptions e publications de bancos de dados e schemas diferentes na mesma tabela.

Para ver uma lista de todas as publications para cada tabela no seu servidor PostgreSQL:

[code language="sql"] SELECT * FROM pg_publication pb LEFT JOIN pg_publication_tables pbt ON pbt.pubname = pb.pubname [/code]

Para ver uma lista de todas as subscriptions no seu cluster:

[code language="sql"] SELECT * FROM pg_subscription [/code]

Particularidades da logical replication

Imagine que temos um bd publicador e um bd inscrito. O publicador foi configurado para enviar as mudanças de apenas uma tabela para o inscrito.

Quando você executar o comando subscribe dentro do bd inscrito, todos os dados serão baixados do bd publicador. Se a tabela no bd inscrito contiver dados, e esses dados estiverem também no bd publicador antes do subscribe, duas coisas podem acontecer:

• Dados duplicados apareceção na tabela do bd inscrito (se não houver nenhuma constraint no BD inscrito)
• A replicação irá parar de funcionar, e o log de erro ficará num loop tentando executar a sincronização mas não conseguindo por conta de algum erro de constraint (se houver alguma constraint de primary key, foreign key, etc...).

Isso se aplica apenas para INSERTS em tabelas no bd inscrito com constraints. Quando o bd publicador enviar um UPDATE ou um DELETE mas a primary key desse comando não existir no bd inscrito, não ocorrerão erros e a sincronização continuará normalmente.

Quem está atualmente recebendo minhas publicações?

É possível saber quais conexões estão recebendo publicações visualizando os replication slots em aberto. Faça a seguinte consulta no bd publicador:

[code language="sql"] SELECT * FROM pg_stat_replication; [/code]

Referências

Artigo e tutorial super completo do DigitalOcean sobre replicação lógica
https://www.digitalocean.com/community/tutorials/how-to-set-up-logical-replication-with-postgresql-10-on-ubuntu-18-04

Documentação oficial RDS AWS - Replicação Lógica
https://docs.aws.amazon.com/pt_br/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.LogicalReplication

Post no fórum AWS - Como conseguir replicação lógica no RDS da AWS
https://forums.aws.amazon.com/thread.jspa?messageID=865458&tstart=0

Artigo e tutorial no PGDash sobre replicação lógica
https://pgdash.io/blog/postgres-replication-gotchas.html

Artigo e Tutorial no SeveralNines sobre replicação lógica
https://severalnines.com/blog/overview-logical-replication-postgresql

Tutorial de Replicação Lógica e sobre quais circunstâncias devemos ter cuidado
https://severalnines.com/blog/using-postgresql-logical-replication-maintain-always-date-readwrite-test-server

Documentação da tabela pg_subscription
https://www.postgresql.org/docs/current/catalog-pg-subscription.html

Documentação da tabela pg_publication
https://www.postgresql.org/docs/current/view-pg-publication-tables.html

Documentação do comando PUBLICATION
https://www.postgresql.org/docs/current/sql-createpublication.html
https://www.postgresql.org/docs/current/sql-alterpublication.html

Documentação do comando SUBSCRIPTION
https://www.postgresql.org/docs/current/sql-createsubscription.html
https://www.postgresql.org/docs/current/sql-altersubscription.html

Explicação a fundo sobre os tipos de replicação no PostgreSQL
https://pgdash.io/blog/monitoring-postgres-replication.html