Domine Ferramentas e Melhores Práticas de DevOps - Hub de Conhecimento DevOps Domine Ferramentas e Melhores Práticas de DevOps - Hub de Conhecimento DevOps

Otimizando Foreign Data Wrappers do PostgreSQL para Integração de Múltiplos Bancos de Dados

Use FDWs do PostgreSQL para consultar bancos de dados remotos com segurança, ajustar pushdown e evitar armadilhas comuns de desempenho em federação.

Otimizando Foreign Data Wrappers do PostgreSQL para Integração Multi-Banco

Os Foreign Data Wrappers do PostgreSQL ajudam quando seus dados estão em mais de um lugar e você precisa consultá-los sem copiar tudo primeiro. FDWs podem fazer tabelas remotas parecerem locais, mas o desempenho e o comportamento de transações dependem fortemente do wrapper, da rede e da forma da consulta.

O que são Foreign Data Wrappers?

FDW implementa o padrão SQL/MED (Management of External Data), fornecendo:

  • Acesso transparente: Consulte dados remotos usando SQL padrão
  • Tabelas virtuais: Nenhuma duplicação de dados necessária
  • Leituras remotas ao vivo: Consultas leem da fonte remota em vez de uma cópia local
  • Capacidade de junção: Combine dados locais e remotos
  • Operações de escrita: Alguns wrappers suportam INSERT, UPDATE e DELETE

Casos de Uso Comuns

  1. Migração de Banco de Dados: Acesse sistemas legados durante a migração
  2. Arquitetura Multi-inquilino: Consulte entre bancos de dados de inquilinos
  3. Agregação de Dados: Combine dados de múltiplas fontes
  4. Nuvem Híbrida: Conecte bancos de dados locais e na nuvem
  5. Relatórios: Crie um banco de dados de relatórios centralizado
  6. Microsserviços: Consulte entre limites de serviço

Foreign Data Wrappers Disponíveis

postgresql_fdw (Integrado)

  • Conecte-se a outros bancos de dados PostgreSQL
  • Suporte completo de recursos
  • Melhor desempenho

mysql_fdw

  • Conecte-se a MySQL/MariaDB
  • Suporte de leitura e escrita

oracle_fdw

  • Conecte-se a bancos de dados Oracle
  • Comumente usado através da extensão de código aberto oracle_fdw, com bibliotecas cliente Oracle instaladas separadamente

mongodb_fdw

  • Acesse coleções MongoDB
  • Integração de dados JSON

file_fdw (Integrado)

  • Leia arquivos CSV e de texto
  • Análise de arquivos de log

Outros

  • redis_fdw, sqlite_fdw, couchdb_fdw, etc.

Configurando postgres_fdw

Passo 1: Instalar Extensão

-- No banco de dados local
CREATE EXTENSION postgres_fdw;

Passo 2: Criar Servidor Remoto

CREATE SERVER remote_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'remote-db.example.com',
        port '5432',
        dbname 'production_db',
        fetch_size '10000'
    );

Passo 3: Criar Mapeamento de Usuário

-- Mapear usuários locais para credenciais remotas
CREATE USER MAPPING FOR postgres
    SERVER remote_db
    OPTIONS (
        user 'remote_user',
        password 'secure_password'
    );

-- Mapear todos os usuários locais
CREATE USER MAPPING FOR PUBLIC
    SERVER remote_db
    OPTIONS (
        user 'readonly_user',
        password 'readonly_pass'
    );

Passo 4: Criar Tabelas Estrangeiras

Definição manual de tabela:

CREATE FOREIGN TABLE remote_users (
    id INTEGER,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP
)
SERVER remote_db
OPTIONS (schema_name 'public', table_name 'users');

Importar esquema inteiro:

IMPORT FOREIGN SCHEMA public
    FROM SERVER remote_db
    INTO local_schema;

Importar tabelas específicas:

IMPORT FOREIGN SCHEMA public
    LIMIT TO (users, orders, products)
    FROM SERVER remote_db
    INTO public;

Passo 5: Consultar Dados Estrangeiros

-- Consulta simples
SELECT * FROM remote_users WHERE created_at > NOW() - INTERVAL '7 days';

-- Juntar dados locais e remotos
SELECT 
    l.order_id,
    l.amount,
    r.username,
    r.email
FROM local_orders l
JOIN remote_users r ON l.user_id = r.id
WHERE l.status = 'pending';

Configuração Avançada

Opções de Ajuste de Desempenho

CREATE SERVER fast_remote
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'db.example.com',
        port '5432',
        dbname 'mydb',
        
        -- Tamanho de busca: linhas recuperadas por viagem de ida e volta
        fetch_size '50000',
        
        -- Habilitar otimizações de pushdown de consulta
        use_remote_estimate 'true',
        
        -- Reutilizar conexões de servidor remoto dentro de sessões PostgreSQL
        keep_connections 'on',
        
        -- Execução de consulta paralela
        parallel_commit 'on',
        parallel_abort 'on'
    );

Opções em Nível de Tabela

CREATE FOREIGN TABLE optimized_table (
    id INTEGER,
    data TEXT
)
SERVER remote_db
OPTIONS (
    schema_name 'public',
    table_name 'large_table',
    
    -- Substituir tamanho de busca para esta tabela
    fetch_size '100000',
    
    -- Usar estimativas remotas para melhor planejamento de consulta
    use_remote_estimate 'true'
);

Opções em Nível de Coluna

CREATE FOREIGN TABLE mapped_columns (
    local_id INTEGER OPTIONS (column_name 'remote_user_id'),
    local_name TEXT OPTIONS (column_name 'remote_username')
)
SERVER remote_db
OPTIONS (table_name 'users');

Operações de Escrita

Habilitar Operações de Escrita

-- Conceder permissões
GRANT INSERT, UPDATE, DELETE ON remote_users TO app_user;

-- Inserir dados
INSERT INTO remote_users (username, email)
VALUES ('newuser', '[email protected]');

-- Atualizar dados
UPDATE remote_users
SET email = '[email protected]'
WHERE username = 'newuser';

-- Excluir dados
DELETE FROM remote_users WHERE username = 'newuser';

Transações Entre Bancos de Dados

BEGIN;
    -- Inserir na tabela local
    INSERT INTO local_orders (user_id, amount) VALUES (123, 99.99);
    
    -- Atualizar tabela remota
    UPDATE remote_users SET last_order = NOW() WHERE id = 123;
    
    -- Ambas as operações confirmadas juntas
COMMIT;

O PostgreSQL coordena o trabalho remoto através do FDW, mas você não deve assumir que todo wrapper oferece garantias completas de transação distribuída. Para postgres_fdw, revise a opção de servidor two_phase_commit se precisar de comportamento de confirmação em duas fases e teste a recuperação de falhas antes de confiar nela para escritas críticas.

Federação Multi-Banco

Conectando Múltiplos Bancos de Dados

-- Conectar ao banco de dados de produção
CREATE SERVER prod_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'prod.example.com', dbname 'production');

CREATE USER MAPPING FOR PUBLIC SERVER prod_db
    OPTIONS (user 'readonly', password 'pass1');

-- Conectar ao banco de dados de análise
CREATE SERVER analytics_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'analytics.example.com', dbname 'warehouse');

CREATE USER MAPPING FOR PUBLIC SERVER analytics_db
    OPTIONS (user 'readonly', password 'pass2');

-- Conectar ao banco de dados de arquivo
CREATE SERVER archive_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'archive.example.com', dbname 'historical');

CREATE USER MAPPING FOR PUBLIC SERVER archive_db
    OPTIONS (user 'readonly', password 'pass3');

Visões Unificadas Entre Bancos de Dados

-- Importar tabelas
IMPORT FOREIGN SCHEMA public 
    LIMIT TO (current_orders) 
    FROM SERVER prod_db INTO public;

IMPORT FOREIGN SCHEMA public 
    LIMIT TO (archived_orders) 
    FROM SERVER archive_db INTO public;

-- Criar visão unificada
CREATE VIEW all_orders AS
SELECT *, 'current' AS source FROM current_orders
UNION ALL
SELECT *, 'archived' AS source FROM archived_orders;

-- Consultar em todos os bancos de dados
SELECT 
    source,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM all_orders
WHERE created_at > '2024-01-01'
GROUP BY source;

Conectando ao MySQL

Instalar mysql_fdw

# Os nomes dos pacotes variam de acordo com a versão do PostgreSQL e o repositório.
# No Debian/Ubuntu, pesquise primeiro:
apt search mysql-fdw

# Em seguida, instale o pacote que corresponde à sua versão principal do PostgreSQL.
sudo apt install postgresql-16-mysql-fdw

Configurar Conexão MySQL

CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_server
    FOREIGN DATA WRAPPER mysql_fdw
    OPTIONS (
        host 'mysql-db.example.com',
        port '3306'
    );

CREATE USER MAPPING FOR PUBLIC
    SERVER mysql_server
    OPTIONS (
        username 'mysql_user',
        password 'mysql_pass'
    );

CREATE FOREIGN TABLE mysql_products (
    id INTEGER,
    name VARCHAR(100),
    price DECIMAL(10,2)
)
SERVER mysql_server
OPTIONS (dbname 'ecommerce', table_name 'products');

-- Consultar dados MySQL a partir do PostgreSQL
SELECT * FROM mysql_products WHERE price > 100;

Otimização de Desempenho

1. Usar Pushdown de Cláusula WHERE

O PostgreSQL envia filtros para o servidor remoto:

-- Bom: Filtro aplicado remotamente
SELECT * FROM remote_users WHERE country = 'US';

-- Verificar plano de consulta
EXPLAIN (VERBOSE) SELECT * FROM remote_users WHERE country = 'US';
-- Procure por: "Remote SQL: SELECT ... WHERE country = 'US'"

2. Limitar Transferência de Dados

-- Ruim: Busca todas as colunas
SELECT * FROM remote_large_table;

-- Bom: Apenas colunas necessárias
SELECT id, username FROM remote_large_table;

-- Bom: Usar LIMIT
SELECT * FROM remote_large_table LIMIT 1000;

3. Otimizar Tamanho de Busca

-- Para conjuntos de resultados grandes
ALTER SERVER remote_db OPTIONS (SET fetch_size '50000');

-- Para consultas pequenas e frequentes
ALTER SERVER remote_db OPTIONS (SET fetch_size '1000');

4. Usar Estimativas Remotas

ALTER SERVER remote_db OPTIONS (ADD use_remote_estimate 'true');

-- O PostgreSQL consultará EXPLAIN remoto para estimativas de custo precisas

5. Índices em Tabelas Remotas

-- Garantir que as tabelas remotas tenham índices apropriados
-- Conectar diretamente ao banco de dados remoto:

CREATE INDEX idx_users_country ON users(country);
CREATE INDEX idx_orders_created_at ON orders(created_at);

6. Visões Materializadas para Dados Frequentemente Acessados

-- Armazenar em cache dados remotos localmente
CREATE MATERIALIZED VIEW cached_remote_data AS
SELECT * FROM remote_users WHERE active = true;

-- Atualizar periodicamente
REFRESH MATERIALIZED VIEW cached_remote_data;

-- Atualização automática com pg_cron se essa extensão estiver instalada
CREATE EXTENSION pg_cron;
SELECT cron.schedule('refresh-cache', '0 */6 * * *', 
    'REFRESH MATERIALIZED VIEW cached_remote_data');

Monitoramento e Solução de Problemas

Visualizar Servidores Remotos

SELECT 
    srvname AS server_name,
    srvoptions AS options
FROM pg_foreign_server;

Visualizar Tabelas Estrangeiras

SELECT 
    foreign_table_schema,
    foreign_table_name,
    foreign_server_name
FROM information_schema.foreign_tables;

Verificar Mapeamentos de Usuário

SELECT 
    um.umuser::regrole AS local_user,
    fs.srvname AS foreign_server,
    um.umoptions AS options
FROM pg_user_mapping um
JOIN pg_foreign_server fs ON um.umserver = fs.oid;

Analisar Desempenho de Consulta

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT * FROM remote_users WHERE created_at > NOW() - INTERVAL '1 day';

Verificação de Conexão

Use EXPLAIN (ANALYZE, VERBOSE) para confirmar SQL remoto e tempo. Para links PostgreSQL-para-PostgreSQL, verifique também o pg_stat_activity do servidor remoto enquanto uma consulta FDW longa está em execução.

Problemas Comuns e Soluções

Problema 1: Tempo Limite de Conexão

Erro: could not connect to server

Soluções:

-- Adicionar tempo limite de conexão
ALTER SERVER remote_db OPTIONS (ADD connect_timeout '10');

-- Verificar conectividade de rede
-- Do shell:
psql -h remote-db.example.com -U user -d dbname

Problema 2: Consultas Lentas

Soluções:

-- Habilitar estimativas remotas
ALTER SERVER remote_db OPTIONS (ADD use_remote_estimate 'true');

-- Aumentar tamanho de busca
ALTER SERVER remote_db OPTIONS (SET fetch_size '100000');

-- Verificar se a cláusula WHERE foi enviada
EXPLAIN VERBOSE SELECT * FROM remote_table WHERE id > 1000;

Problema 3: Falha de Autenticação

Erro: password authentication failed

Soluções:

-- Atualizar mapeamento de usuário
ALTER USER MAPPING FOR postgres
    SERVER remote_db
    OPTIONS (SET password 'new_password');

-- Verificar pg_hba.conf no servidor remoto
-- Garantir que permite conexões do servidor local

Problema 4: Incompatibilidade de Tipo de Coluna

Erro: column type mismatch

Solução:

-- Converter colunas explicitamente
CREATE FOREIGN TABLE remote_data (
    id INTEGER,
    data TEXT,
    created_at TIMESTAMP
)
SERVER remote_db
OPTIONS (table_name 'source_table');

-- Ou importar com tipos corretos
DROP FOREIGN TABLE remote_data;
IMPORT FOREIGN SCHEMA public 
    LIMIT TO (source_table) 
    FROM SERVER remote_db INTO public;

Considerações de Segurança

1. Usar Contas Somente Leitura

-- No banco de dados remoto, criar usuário limitado
CREATE ROLE fdw_readonly;
GRANT CONNECT ON DATABASE production_db TO fdw_readonly;
GRANT USAGE ON SCHEMA public TO fdw_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO fdw_readonly;

-- Usar no mapeamento de usuário
CREATE USER MAPPING FOR PUBLIC
    SERVER remote_db
    OPTIONS (user 'fdw_readonly', password 'safe_pass');

2. Armazenamento Seguro de Senhas

-- Você pode usar um arquivo .pgpass para conexões libpq em vez de armazenar
-- a senha no mapeamento de usuário. Confirme a propriedade e permissões do arquivo.
-- Formato ~/.pgpass:
-- hostname:port:database:username:password

CREATE USER MAPPING FOR postgres
    SERVER remote_db
    OPTIONS (user 'remote_user');
    -- Senha lida do .pgpass

3. Segurança de Rede

-- Usar conexões SSL
-- No postgresql.conf do remoto:
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

-- Exigir SSL no mapeamento de usuário
CREATE SERVER secure_remote
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'remote.example.com',
        dbname 'mydb',
        sslmode 'require'
    );

Melhores Práticas

  1. Minimizar transferência de dados: Selecione apenas colunas necessárias
  2. Usar índices: Garantir que tabelas remotas estejam devidamente indexadas
  3. Armazenar em cache dados frequentemente acessados: Usar visões materializadas
  4. Monitorar desempenho: EXPLAIN ANALYZE regular
  5. Limitar operações de escrita: Preferir somente leitura para a maioria dos usos de FDW
  6. Usar pool de conexões: Habilitar keep_connections
  7. Definir fetch_size apropriado: Ajustar com base nos padrões de consulta
  8. Testar cenários de failover: Planejar para indisponibilidade de banco remoto
  9. Documentar dependências externas: Manter documentação clara
  10. Auditorias de segurança regulares: Revisar mapeamentos de usuário e permissões

Conclusão

Os Foreign Data Wrappers do PostgreSQL são úteis para federação de bancos de dados, especialmente quando você precisa:

  • Integração perfeita: Consultar dados remotos com SQL padrão
  • Acesso em tempo real: Sem duplicação de dados ou atrasos de sincronização
  • Suporte multi-banco: PostgreSQL, MySQL, Oracle e mais
  • Desempenho: Pushdown de consulta quando o wrapper e a consulta suportam
  • Flexibilidade: Acesso de leitura primeiro, com escritas somente após verificar o comportamento do wrapper

Comece com consultas somente leitura para bancos de dados remotos, otimize o desempenho com indexação adequada e tamanhos de busca, depois expanda para integrações mais complexas conforme necessário.