PostgreSQL

This page covers PostgreSQL as a source database, the database LakeXpress exports data from. To use PostgreSQL as the LakeXpress DB, see PostgreSQL as LakeXpress DB.

Connection Parameters

Parameter	Required	Description
ds_type	Yes	Must be "postgres"
auth_mode	Yes	"classic" (username/password) or "connection_string" (supply a full connection string)
username	Classic only	PostgreSQL username
password	Classic only	PostgreSQL password
server	Classic only	Hostname or IP (host is also accepted)
port	No	Port (default: 5432)
database	Classic only	Database name
sslmode	No	PostgreSQL SSL mode, e.g. disable, prefer, require, verify-full (default: prefer)
use_adbc	No	Export through FastBCP's ADBC driver (default: true). false uses FastBCP's native driver. Set at the same level as ds_type, not inside info. See ADBC Export
fastbcp_connection_string	No	Connection string passed to FastBCP as is, in the format of its Npgsql (.NET) driver. Overrides the one LakeXpress builds; use it for options that do not convert (see Connection String Mode)

With auth_mode: "connection_string", only the connection_string field is used; username, password, server, port, database, and sslmode are ignored.

The export uses the same sslmode as LakeXpress's own connection.

Required Permissions

LakeXpress only needs read access to the schemas it exports:

GRANT USAGE ON SCHEMA public TO lakexpress_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO lakexpress_user;

information_schema access is granted by default.

Example: Source Database

{
  "source_postgres": {
    "ds_type": "postgres",
    "auth_mode": "classic",
    "info": {
      "username": "$env{LX_PG_USER}",
      "password": "$env{LX_PG_PASSWORD}",
      "server": "pg-prod.company.com",
      "port": 5432,
      "database": "analytics"
    }
  }
}

Example: Connection String Mode

{
  "postgres_cs": {
    "ds_type": "postgres",
    "auth_mode": "connection_string",
    "info": {
      "connection_string": "host=pg-prod.company.com port=5432 dbname=analytics user=$env{LX_PG_USER} password=$env{LX_PG_PASSWORD} sslmode=require"
    }
  }
}

The string can also be a URI (postgresql://user:password@host:5432/dbname?sslmode=require). LakeXpress converts it for FastBCP's Npgsql driver: host, port, dbname, user, password, sslmode, sslrootcert, application_name and connect_timeout are passed. Other keywords are not passed to FastBCP, and LakeXpress logs a warning; set fastbcp_connection_string to pass them in Npgsql form.

ADBC Export

By default, FastBCP reads the source through the arpe.io ADBC driver (ArrowFEBE, connection type adbc_pgsql). Set "use_adbc": false to use FastBCP's native driver instead, for example to work around a driver issue. The setting only changes the export: LakeXpress still discovers schemas and tables with its own driver, and all auth_mode values work as before. The run log shows the path once per run, e.g. Source extraction: adbc_pgsql (ADBC).

{
  "postgres_native": {
    "ds_type": "postgres",
    "auth_mode": "classic",
    "use_adbc": false,
    "info": {
      "username": "$env{LX_PG_USER}",
      "password": "$env{LX_PG_PASSWORD}",
      "server": "pg-prod.company.com",
      "port": 5432,
      "database": "analytics"
    }
  }
}

The Parquet files written through ADBC differ from the native ones:

• time and timestamp keep microseconds (the native driver writes milliseconds).
• timestamptz is a UTC timestamp (the native driver writes a timestamp without time zone).
• jsonb is text (the native driver writes binary).

The ADBC driver needs its own license. It is included in the license file that arpe.io sends for LakeXpress, and LakeXpress passes it to FastBCP. If your license file has no ADBC block, provide the driver license with ARPEIO_ADBC_LICENCE (license text), ARPEIO_ADBC_LICENCE_FILE (path), or arpeio.adbc.license_file=<path> in info.fastbcp_connection_string (the connection string LakeXpress builds from connection_string does not carry it). Without any of these, the sync stops before the export with exit code 13 (LICENSE_ERROR). See License Information.

ADBC drivers ship with FastBCP for linux-x64 and win-x64. See ADBC Extraction for the other sources.

Troubleshooting

Connection refused: Check PostgreSQL is running (systemctl status postgresql), verify pg_hba.conf allows your IP, check firewall (sudo ufw allow 5432/tcp).

Authentication failed: Verify credentials, check password encryption in pg_hba.conf, confirm user exists: SELECT * FROM pg_user WHERE usename='your_user';

See Also

• Database Connections OverviewCredentials file format, security best practices