Skip to content

Automatic translation — the Portuguese version is the reference. View in Portuguese

Updated on 2026-09-08

Tutorial — connect a PostgreSQL database

Step-by-step guide to connecting PostgreSQL as a source, testing the connection, and listing the tables available for ingestion.

On this page (13)

The PostgreSQL connector can be registered and used today, with no case-by-case release. Extraction depends on your source credential and on the workspace cloud environment being configured — without it the run fails with a message instead of faking success. Synthetic examples.

Read first: How data flows · Connector catalog.


What it is#

Connecting a PostgreSQL database as a source (pull mode, "We fetch"): Ingestia connects to your database with a read-only user and extracts tables into the Bronze layer of your workspace. This applies to PostgreSQL, and the same flow covers MySQL, SQL Server, and Oracle (only the port and a few fields change).

What it is for#

Bringing operational data (orders, customers, inventory) from your production database into the datalake, without exporting spreadsheets by hand, and keeping it up to date on a schedule.

When to use / when not to use#

  • Use it when: the data lives in a relational database reachable over the network (public IP with allowlist, Cloud SQL, VPN, or through an SSH tunnel/bastion).
  • Do not use it when: you only have loose files (use import a file) or the data is in a SaaS app with its own connector (Omie, Bling, Meta Ads, etc.).

Plan and permissions#

  • Product core · access check (active workspace).
  • The load action goes through the kill switch for the ingestion run capability.
  • Recommended: a read-only database user dedicated to Ingestia.

Prerequisites#

  1. The PostgreSQL host and port (default port 5432).
  2. Database and, optionally, the schema (default public).
  3. User and password with SELECT permission on the desired tables.
  4. Network route — one of the options:
    • Public IP + allowlist: allow Ingestia's egress IP in your firewall.
    • Cloud SQL (IP/Proxy).
    • VPN (private IP).
    • SSH tunnel (bastion): bastion host/port/user + SSH private key.
  5. SSL defined (require or higher is recommended).

Step by step#

  1. In the console, go to ETL/ELT → Connections and choose PostgreSQL (category Databases).
  2. Fill in the connection fields:
    • Host — e.g., db.suaempresa.example
    • Port — 5432
    • Database — e.g., producao
    • Schema — e.g., public (optional)
    • User — e.g., ingestia_ro
    • Password — the read-only user's password (it is encrypted and never shown again)
  3. In SSL mode, choose according to your policy:
    • disable (no SSL — avoid it), require, verify-ca, or verify-full.
  4. In How we connect (network), choose Public IP + allowlist, Cloud SQL, VPN, or SSH tunnel. If it is SSH, fill in the bastion host/port/user and paste the SSH private key.
  5. Click Test connection. Ingestia opens the connection (and the tunnel, if applicable) and lists the accessible tables.
  6. Once the test passes, select the tables you want to ingest and save the source.

Example (synthetic)#

code
Engine: PostgreSQL
Host: db.suaempresa.example
Porta: 5432
Database: producao
Schema: public
Usuário: ingestia_ro
SSL mode: require
Rede: IP público + allowlist

Sample read-only user (run it on your database):

sql
CREATE USER ingestia_ro WITH PASSWORD 'troque-esta-senha';
GRANT CONNECT ON DATABASE producao TO ingestia_ro;
GRANT USAGE ON SCHEMA public TO ingestia_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ingestia_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO ingestia_ro;

Expected result#

  • Test connection returns OK and the list of tables (schema.tabela) appears for selection. Ingestia reads the catalog via information_schema.tables, excluding system schemas, with a cap of 2,000 listed tables.
  • After a load, the selected rows appear in the Bronze layer of your workspace, ready for the pipeline.

Limits and costs#

  • Chunked (paginated) extraction: batchSize default 5,000 (100–50,000); maxRows default 200,000 (up to 5,000,000).
  • Hard pagination cap to prevent loops.
  • Calls to your database are not charged by the platform; the Bronze load consumes load processing in the datalake (billable).

Security#

  • Schema/table/column names are sanitized (only [A-Za-z0-9_$]) before entering the SQL — an anti-injection guard.
  • The password (and the SSH key) is encrypted (AES-256-GCM) and redacted in any error/log.
  • Prefer a read-only user; Ingestia only runs SELECT.
  • Workspace scope: rows go to the Bronze of your workspace, never another one.

Common errors#

SymptomLikely causeWhat to do
connection refused / timeoutfirewall/allowlist or wrong routeAllow the egress IP or use SSH/VPN; check host:port
password authentication failedwrong user/passwordReview the credential; test the login outside Ingestia
no pg_hba.conf entry / SSL requiredserver SSL policySet SSL mode to require/verify-*
Empty table listuser lacks SELECT or wrong schemaGrant USAGE/SELECT; check the schema
Fails only via SSHwrong key/bastionReview the bastion host/user and the private key

Troubleshooting#

  • Use Test connection to isolate the problem (network vs. credential vs. permission) — it never throws; it returns OK + tables, or an already-redacted error message.
  • An administrator can check the environment's deep health at GET /api/health?deep=1.

Related#


Last reviewed: 2026-09-08.

Related links