Upgrade de versão major do Postgres sem janela: replicação lógica para trocar de versão com tráfego ligado

Upgrade de versão major muda o formato interno do catálogo e, em alguns casos, dos arquivos de dados. Por isso ele não acontece com um restart: ou os dados são convertidos com o banco parado (pg_upgrade), ou são reescritos do zero em outro servidor (dump e restore), ou são copiados por replicação enquanto o antigo continua atendendo. O upgrade gerenciado do provedor é um pg_upgrade com interface bonita, e a estimativa de 40 minutos cobre snapshot antes, conversão, reinício e nenhum ANALYZE: o banco volta sem estatísticas e o planejador escolhe planos ruins no primeiro horário de pico.

A replicação lógica decodifica o WAL da origem em mudanças de linha (INSERT, UPDATE, DELETE, TRUNCATE) e as aplica no alvo como se fossem comandos SQL. Como o formato é lógico e não físico, a versão dos dois lados pode ser diferente: o alvo já roda a 17 enquanto a origem continua na 12, e a troca vira a decisão de para onde a aplicação aponta. O preço é que ela só leva o que está no WAL como mudança de linha, e tudo que não está precisa de plano próprio.

Três descobertas costumam acontecer na madrugada do corte, quando deveriam ter acontecido um mês antes. A primeira é que wal_level = logical exige reinício da origem, ou seja, uma pequena parada que precisa entrar na próxima janela de patch. A segunda é que toda tabela que recebe UPDATE ou DELETE precisa de chave primária ou REPLICA IDENTITY; sem isso, a partir do momento em que a publicação existe, esses comandos falham na origem com erro, não no alvo. A terceira é que extensão com versão incompatível na versão nova (um pg_trgm antigo, um PostGIS que mudou de major) só aparece na hora de restaurar o esquema.

-- 1. Tabelas sem chave primaria. Depois que a publicacao existe, UPDATE e DELETE
--    nelas falham na origem com "cannot update table because it does not have
--    a replica identity". Descubra antes, nao durante.
SELECT n.nspname AS esquema, c.relname AS tabela, c.relreplident AS identidade
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND NOT EXISTS (SELECT 1 FROM pg_index i WHERE i.indrelid = c.oid AND i.indisprimary);

-- Para as poucas que sobram (log, staging de importacao), identidade completa:
-- cada UPDATE envia a linha inteira pelo WAL. Mais caro, mas funciona.
ALTER TABLE importacao_linhas REPLICA IDENTITY FULL;

-- 2. Sequencias nao sao replicadas. Liste todas: serao alinhadas no corte.
SELECT schemaname, sequencename, last_value FROM pg_sequences;

-- 3. Extensoes: precisam existir no alvo, em versao compativel, ANTES do esquema.
SELECT extname, extversion FROM pg_extension ORDER BY 1;

-- 4. O que a replicacao logica nao carrega e precisa de plano proprio.
SELECT count(*) AS large_objects FROM pg_largeobject_metadata;
SELECT relname FROM pg_class WHERE relpersistence = 'u';   -- UNLOGGED: fora do WAL
SELECT matviewname FROM pg_matviews;                        -- REFRESH no alvo

-- 5. Tabela de marcador usada no corte: criada agora, para entrar no esquema
--    e na publicacao junto com as outras.
CREATE TABLE upgrade_marcador (id uuid PRIMARY KEY, criado_em timestamptz DEFAULT now());

-- 6. Configuracao da origem. wal_level exige reinicio: faca na proxima janela
--    de patch, semanas antes, nao na madrugada do corte.
ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_replication_slots = 10;
ALTER SYSTEM SET max_wal_senders = 10;

• Tabelas sem chave primária: no SaaS de clínicas eram 9 de 212, todas de log e importação. Quatro ganharam chave primária com ADD COLUMN id bigint GENERATED ALWAYS AS IDENTITY em lotes; cinco ficaram com REPLICA IDENTITY FULL porque só recebem INSERT.
• O papel replicador precisa de REPLICATION e de SELECT nas tabelas publicadas; na versão 12 a assinatura precisa ser criada por superusuário no alvo, e a conexão de replicação precisa estar liberada no pg_hba.conf da origem.
• Slot de replicação segura WAL até o assinante confirmar. Se o alvo cair por uma noite, a origem acumula WAL até encher o disco e parar de aceitar escrita. Alarme em pg_replication_slots é obrigatório desde o primeiro dia.
• Large objects, tabelas UNLOGGED e views materializadas ficam de fora. No caso, 3.100 large objects de laudos foram migrados para armazenamento de objetos antes do upgrade, o que já era dívida antiga.

O esquema vai primeiro, sem dados, usando o pg_dump da versão nova apontado para a origem antiga: ele sabe emitir DDL que a versão nova aceita e avisa de extensão que não existe no alvo. Depois vem a publicação na origem e a assinatura no alvo com copy_data, que faz a carga inicial tabela a tabela usando COPY e, ao terminar cada uma, começa a aplicar as mudanças em tempo real. O ponto que decide se isso leva 4 horas ou 11 é o que existe no alvo durante a cópia: cada índice secundário é mantido a cada linha inserida, e cada chave estrangeira é checada.

# Esquema sem dados, com o pg_dump da versao NOVA apontado para a origem antiga:
# o pg_dump 17 sabe emitir DDL que o 17 aceita. Papeis vem separado.
pg_dumpall -h origem --globals-only > globals.sql
pg_dump -h origem -d clinicas --schema-only --no-publications --no-subscriptions > esquema.sql
psql -h alvo -f globals.sql
psql -h alvo -d clinicas -f esquema.sql

-- Na origem (12): tudo que existe hoje. Tabela criada depois NAO entra sozinha.
CREATE PUBLICATION pub_upgrade FOR ALL TABLES;

-- No alvo (17): indices secundarios e chaves estrangeiras custam caro na copia
-- inicial. Guarde o DDL, derrube, recrie depois. Chave primaria e UNIQUE ficam:
-- sem elas o apply de cada UPDATE vira varredura sequencial no alvo.
SELECT indexdef || ';' AS recriar
FROM pg_indexes
WHERE schemaname = 'public'
  AND indexname NOT IN (SELECT conname FROM pg_constraint WHERE contype IN ('p', 'u'));

CREATE SUBSCRIPTION sub_upgrade
  CONNECTION 'host=origem dbname=clinicas user=replicador'
  PUBLICATION pub_upgrade
  WITH (copy_data = true);

-- Tabelas copiadas em paralelo (padrao 2). Quatro foi o maximo sem saturar a origem.
ALTER SYSTEM SET max_sync_workers_per_subscription = 4;
SELECT pg_reload_conf();

-- Estado por tabela: i = iniciando, d = copiando, f = terminou a copia,
-- s = sincronizada, r = pronta (recebendo mudancas em tempo real).
SELECT srrelid::regclass AS tabela, srsubstate AS estado
FROM pg_subscription_rel ORDER BY 2, 1;

-- Visto da origem: quanto WAL o slot esta segurando. Esse numero e disco.
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS atraso
FROM pg_replication_slots;

-- Quando todas estiverem em 'r': recriar os indices guardados (com CONCURRENTLY)
-- e gerar estatisticas, que a copia nao traz. Sem ANALYZE, o alvo escolhe planos
-- ruins no primeiro minuto de trafego, e o upgrade "deixou tudo lento".
# vacuumdb -h alvo -d clinicas --analyze-in-stages

• Derrubar índices secundários e chaves estrangeiras no alvo antes da cópia e recriar depois, com CONCURRENTLY, levou a carga de 11 horas estimadas para 4 horas e 10 minutos. Chave primária e UNIQUE ficam: o apply usa a identidade de réplica para localizar a linha de cada UPDATE.
• Durante a cópia inicial, o slot segura todo o WAL gerado desde o início. No caso foram 180 GB de pico, e a origem precisou de 250 GB livres que ninguém tinha previsto. Meça a geração de WAL por hora antes de começar.
• Congele as migrações de esquema enquanto a replicação existe, ou aplique cada DDL nos dois lados na mesma ordem. Uma coluna nova só na origem faz o apply parar no alvo no primeiro INSERT que a usa.
• Tabela criada depois da publicação não entra sozinha, mesmo com FOR ALL TABLES no alvo: crie nos dois lados e rode ALTER SUBSCRIPTION sub_upgrade REFRESH PUBLICATION.
• Estatísticas não são copiadas. O vacuumdb --analyze-in-stages antes do corte é o que impede o "upgrade deixou tudo lento" dos primeiros minutos.

Com o alvo sincronizado, atraso abaixo de 2 segundos e índices recriados, o corte é um procedimento de segundos que precisa ser um script, não um checklist executado à mão. O pgbouncer faz o papel central: PAUSE espera as transações abertas terminarem e segura as novas na fila, sem erro para o cliente. A origem vira somente leitura, um marcador escrito na origem prova que o alvo recebeu a última mudança, as sequências são alinhadas, a replicação de volta é criada e só então o pgbouncer é recarregado apontando para o alvo e a fila drena.

// corte.js
// Corte com trafego ligado. O pgbouncer segura as novas transacoes na fila (PAUSE),
// a origem vira somente leitura, um marcador prova que o alvo recebeu a ultima
// escrita, as sequencias sao alinhadas, a replicacao de volta e criada e so entao
// o pgbouncer passa a apontar para o alvo (RESUME). Qualquer erro antes do RESUME
// devolve o trafego para a origem: o cliente ve latencia, nao erro.
import { randomUUID } from 'node:crypto';
import { execFile } from 'node:child_process';
import { promisify } from 'node:util';
import pg from 'pg';

const exec = promisify(execFile);
const origem = new pg.Client({ connectionString: process.env.ORIGEM_URL });
const alvo = new pg.Client({ connectionString: process.env.ALVO_URL });
const bouncer = new pg.Client({ connectionString: process.env.PGBOUNCER_ADMIN_URL });
const TETO_MS = 30_000;

const esperar = (ms) => new Promise((resolve) => setTimeout(resolve, ms));

async function esperarMarcador(id) {
  const inicio = Date.now();
  while (Date.now() - inicio < TETO_MS) {
    const { rowCount } = await alvo.query('SELECT 1 FROM upgrade_marcador WHERE id = $1', [id]);
    if (rowCount === 1) return Date.now() - inicio;
    await esperar(100);
  }
  throw new Error(`alvo nao recebeu o marcador ${id} em ${TETO_MS} ms`);
}

async function alinharSequencias() {
  const { rows } = await origem.query(
    'SELECT schemaname, sequencename, last_value FROM pg_sequences WHERE last_value IS NOT NULL',
  );
  for (const seq of rows) {
    // A margem cobre valores pre-alocados por CACHE que nunca chegaram ao disco.
    await alvo.query(
      "SELECT setval(format('%I.%I', $1, $2)::regclass, $3::bigint + 1000, true)",
      [seq.schemaname, seq.sequencename, seq.last_value],
    );
  }
  return rows.length;
}

async function corte() {
  await Promise.all([origem.connect(), alvo.connect(), bouncer.connect()]);
  // Esta sessao continua escrevendo na origem depois que o banco virar read-only.
  await origem.query('SET default_transaction_read_only = off');

  const t0 = Date.now();
  await bouncer.query('PAUSE clinicas'); // espera as transacoes abertas terminarem
  try {
    // 1. Fecha a porta para quem nao passa pelo pgbouncer (cron, job antigo).
    await origem.query('ALTER DATABASE clinicas SET default_transaction_read_only = on');
    await origem.query(`
      SELECT pg_terminate_backend(pid) FROM pg_stat_activity
      WHERE datname = 'clinicas' AND pid <> pg_backend_pid()
        AND backend_type = 'client backend' AND usename <> 'replicador'`);

    // 2. Ultima escrita da origem. Quando chegar no alvo, nada ficou para tras.
    const marcador = randomUUID();
    await origem.query('INSERT INTO upgrade_marcador (id) VALUES ($1)', [marcador]);
    const atrasoMs = await esperarMarcador(marcador);

    // 3. O que a replicacao nao leva.
    const sequencias = await alinharSequencias();

    // 4. Caminho de volta: tudo que for escrito no alvo a partir daqui volta para
    //    a origem. Criado ANTES do RESUME, para nenhuma escrita nova escapar.
    //    O papel replicador recebe read-only = off no nivel de ROLE, que prevalece
    //    sobre o do banco: sem isso o apply na origem falha por transacao read-only.
    await alvo.query('ALTER SUBSCRIPTION sub_upgrade DISABLE');
    await alvo.query('CREATE PUBLICATION pub_volta FOR ALL TABLES');
    await origem.query('ALTER ROLE replicador SET default_transaction_read_only = off');
    await origem.query(`
      CREATE SUBSCRIPTION sub_volta
        CONNECTION '${process.env.VOLTA_CONN}' PUBLICATION pub_volta
        WITH (copy_data = false)`);

    // 5. Ponto sem volta deste script: pgbouncer passa a apontar para o alvo.
    await exec('cp', ['/etc/pgbouncer/pgbouncer.alvo.ini', '/etc/pgbouncer/pgbouncer.ini']);
    await bouncer.query('RELOAD');
    await bouncer.query('RESUME clinicas');
    console.log({ pausaMs: Date.now() - t0, atrasoMs, sequencias });
  } catch (erro) {
    // Nada mudou para o cliente ainda: reabre a origem e libera a fila nela.
    await origem.query('ALTER DATABASE clinicas SET default_transaction_read_only = off');
    await bouncer.query('RESUME clinicas');
    throw erro;
  } finally {
    await Promise.all([origem.end(), alvo.end(), bouncer.end()]);
  }
}

corte().catch((erro) => {
  console.error(erro);
  process.exit(1);
});

Linha do tempo do corte (9 s de pausa no ensaio final e em produção).

  t0       PAUSE clinicas        novas transações esperam na fila do pgbouncer
           |
  +0,8 s   origem read-only      sessões fora do pgbouncer encerradas
           |
  +1,1 s   INSERT marcador       última escrita que a origem aceita
           |
  +2,4 s   marcador no alvo      atraso real da replicação naquele instante: 1,3 s
           |
  +4,0 s   96 sequências         setval(last_value + 1000) no alvo
           |
  +6,9 s   sub_volta criada      alvo -> origem, slot aberto antes de qualquer escrita nova
           |
  +8,7 s   RELOAD + RESUME       fila drena no Postgres 17; p99 da API: 11 s nesse minuto, 0 erros
           |
  +7 dias  DROP sub_volta        origem desligada depois de uma semana sem precisar voltar

Duas decisões no script merecem atenção. A replicação de volta é criada antes do RESUME, com copy_data = false, porque o slot é criado no momento do CREATE SUBSCRIPTION: tudo que for escrito no alvo a partir dali volta para a origem, e nenhuma escrita escapa entre a criação e a liberação da fila. E o ponto sem volta do script é o RELOAD do pgbouncer: até ele, qualquer exceção reabre a origem e libera a fila nela, e o cliente viu só uma pausa. Depois dele, voltar é executar o mesmo procedimento no sentido contrário, o que também é ensaiado.

A lista do que a replicação lógica não transporta é curta, mas cada item vira um incidente se for esquecido. Sequências são o clássico: o alvo recebe as linhas com os ids da origem, mas a sequência do alvo continua em 1, e o primeiro INSERT depois do corte falha por chave duplicada. O setval com margem no script resolve. Os demais estão na tabela.

Provar que os dados batem é comparar origem e alvo com a mesma consulta, na mesma faixa de ids, com os mesmos parâmetros de formatação. A contagem por tabela encontra a tabela que ficou de fora; o hash por faixa encontra a linha divergente sem trazer os dados para fora do banco. A comparação roda com a replicação ativa, antes do corte, e de novo logo depois, enquanto a replicação de volta mantém os dois lados iguais.

-- Mesma consulta nos dois lados, mesma faixa de ids, mesmos parametros de formato.
-- Sem fixar DateStyle, TimeZone e extra_float_digits, a mesma linha vira texto
-- diferente em cada versao e o hash acusa divergencia que nao existe.
SET DateStyle = 'ISO, YMD'; SET TimeZone = 'UTC'; SET extra_float_digits = 3;

SELECT count(*) AS linhas,
       md5(string_agg(md5(t::text), '' ORDER BY id)) AS hash
FROM agendamentos t
WHERE id > 0 AND id <= 500000;        -- avancar a faixa em lotes

-- Contagem estimada de todas as tabelas: acha a tabela que ficou de fora.
SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY 1;

-- Sequencias: o alvo precisa estar a frente da origem em todas.
SELECT sequencename, last_value FROM pg_sequences ORDER BY 1;

-- Encerramento, uma semana depois, sem ter precisado voltar.
-- Cada DROP SUBSCRIPTION derruba o slot do outro lado junto. Slot esquecido
-- segura WAL para sempre, ate encher o disco de quem ficou.
-- No alvo:
DROP SUBSCRIPTION sub_upgrade;
-- Na origem, antes de desligar o servidor:
DROP SUBSCRIPTION sub_volta;

O corte em produção foi o quarto. Os três anteriores rodaram em um clone da origem recebendo tráfego reproduzido do log do pgbouncer, e cada um encontrou algo: o primeiro parou no marcador porque a tabela não estava na publicação, o segundo demorou 40 segundos porque um job de BI fora do pgbouncer segurava uma transação aberta, e o terceiro mostrou que o setval sem margem colidia com valores em cache de sequências com CACHE 50. Os números abaixo são do ensaio final e se repetiram em produção.

1. Faça o inventário: tabelas sem chave primária, sequências, extensões, large objects, UNLOGGED e views materializadas. Resolva cada item antes de criar a publicação.
2. Ligue wal_level = logical na próxima janela de patch e instale o alarme de WAL retido por slot.
3. Restaure o esquema no alvo com o pg_dump da versão nova, derrube índices secundários e chaves estrangeiras, crie a assinatura com copy_data e acompanhe pg_subscription_rel.
4. Recrie índices com CONCURRENTLY, rode ANALYZE em estágios e compare contagens e hashes por faixa com a replicação ativa.
5. Ensaie o corte em um clone com tráfego reproduzido até a pausa ter duração previsível, incluindo o caminho de volta.
6. Execute o corte por script, com a replicação de volta criada antes do RESUME, e mantenha a origem sincronizada por um ciclo de negócio inteiro antes de derrubar as assinaturas e desligar o servidor antigo.