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

Introdução

Os PostgreSQL Foreign Data Wrappers (FDW) permitem a integração perfeita com fontes de dados externas, permitindo que você consulte bancos de dados remotos, arquivos e até APIs web como se fossem tabelas locais. Este recurso poderoso possibilita a federação de bancos de dados, joins entre bancos de dados e acesso centralizado aos dados sem pipelines ETL complexos.

O que são Foreign Data Wrappers?

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

Acesso transparente: Consulte dados remotos usando SQL padrão
Tabelas virtuais: Não requer duplicação de dados
Dados em tempo real: Sempre atualizados, sem atrasos de sincronização
Capacidade de Join: Combine dados locais e remotos
Operações de escrita: Alguns FDWs suportam INSERT/UPDATE/DELETE

Casos de Uso Comuns

Migração de Banco de Dados: Acesse sistemas legados durante a migração
Arquitetura Multi-tenant: Consulte entre bancos de dados de locatários
Agregação de Dados: Combine dados de múltiplas fontes
Nuvem Híbrida: Conecte bancos de dados on-premise e na nuvem
Relatórios: Crie um banco de dados de relatórios centralizado
Microsserviços: Consulte através das fronteiras dos serviços

Foreign Data Wrappers Disponíveis

postgresql_fdw (Nativo)

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

mysql_fdw

Conecte-se ao MySQL/MariaDB
Suporte para leitura e escrita

oracle_fdw

Conecte-se a bancos de dados Oracle
Versões comerciais e de código aberto

mongodb_fdw

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

file_fdw (Nativo)

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 a Extensão

-- No banco de dados local
CREATE EXTENSION postgres_fdw;

Passo 2: Criar o Servidor Estrangeiro (Foreign Server)

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 o 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';

-- Join entre 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ções Avançadas

Opções de Ajuste de Desempenho

CREATE SERVER fast_remote
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'db.example.com',
        port '5432',
        dbname 'mydb',

        -- Fetch size: linhas recuperadas por ida e volta (round trip)
        fetch_size '50000',

        -- Habilitar otimizações de pushdown de consulta
        use_remote_estimate 'true',

        -- Pool de conexões
        keep_connections 'on',

        -- Execução paralela de consulta
        parallel_commit 'on',
        parallel_abort 'on'
    );

Opções de Nível de Tabela

CREATE FOREIGN TABLE optimized_table (
    id INTEGER,
    data TEXT
)
SERVER remote_db
OPTIONS (
    schema_name 'public',
    table_name 'large_table',

    -- Sobrescrever fetch size para esta tabela
    fetch_size '100000',

    -- Usar estimativas remotas para melhor planejamento de consulta
    use_remote_estimate 'true'
);

Opções de 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 são confirmadas juntas
COMMIT;

Nota: O commit de duas fases garante a atomicidade entre os bancos de dados.

Federação Multibanco de Dados

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 analytics
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');

Views 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 view 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

# Ubuntu/Debian
sudo apt install postgresql-15-mysql-fdw

# CentOS/RHEL
sudo yum install mysql_fdw_15

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 do MySQL a partir do PostgreSQL
SELECT * FROM mysql_products WHERE price > 100;

Otimização de Desempenho

1. Usar Pushdown na Cláusula WHERE

O PostgreSQL envia os 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 a 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 o Fetch Size

-- Para grandes conjuntos de resultados
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á o EXPLAIN remoto para estimativas de custo precisas

5. Índices em Tabelas Remotas

-- Garanta que as tabelas remotas tenham índices apropriados
-- Conecte-se 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. Materialized Views para Dados Acessados Frequentemente

-- Cache de 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 cron job ou trigger
CREATE EXTENSION pg_cron;
SELECT cron.schedule('refresh-cache', '0 */6 * * *', 
    'REFRESH MATERIALIZED VIEW cached_remote_data');

Monitoramento e Resolução de Problemas

Visualizar Servidores Estrangeiros

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 Pool de Conexões

-- Verificar conexões estrangeiras ativas
SELECT 
    datname,
    usename,
    application_name,
    client_addr,
    state
FROM pg_stat_activity
WHERE application_name LIKE '%fdw%';

Problemas Comuns e Soluções

Problema 1: Tempo Limite de Conexão (Timeout)

Erro: could not connect to server

Soluções:

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

-- Verificar conectividade de rede
-- A partir 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 fetch size
ALTER SERVER remote_db OPTIONS (SET fetch_size '100000');

-- Verificar se a cláusula WHERE está sofrendo pushdown
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 'nova_senha');

-- Verificar o arquivo pg_hba.conf no servidor remoto
-- Garantir que ele 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 'senha_segura');

2. Armazenamento Seguro de Senhas

-- Usar o arquivo .pgpass em vez de senhas fixas no código
-- Formato do ~/.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 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

Minimize a transferência de dados: Selecione apenas as colunas necessárias
Use índices: Garanta que as tabelas remotas estejam devidamente indexadas
Cache de dados acessados frequentemente: Use materialized views
Monitore o desempenho: Use EXPLAIN ANALYZE regularmente
Limite as operações de escrita: Prefira somente leitura para a maioria dos usos de FDW
Use pool de conexões: Habilite keep_connections
Defina um fetch_size apropriado: Ajuste com base nos padrões de consulta
Teste cenários de failover: Planeje para a indisponibilidade do banco remoto
Documente dependências estrangeiras: Mantenha uma documentação clara
Auditorias de segurança periódicas: Revise mapeamentos de usuários e permissões

Conclusão

Os Foreign Data Wrappers do PostgreSQL fornecem recursos poderosos de federação de bancos de dados:

Integração perfeita: Consulte dados remotos com SQL padrão
Acesso em tempo real: Sem duplicação de dados ou atrasos de sincronização
Suporte multimanco de dados: PostgreSQL, MySQL, Oracle e outros
Desempenho: Pushdown de consulta e otimização
Flexibilidade: Operações de leitura e escrita

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