Ingesting Data from PostgreSQL

Connecting to PostgreSQL

To connect to PostgreSQL or PostgreSQL-compatible databases, you need to provide the following information:

Name: A friendly name for your connection to easily identify and reuse it for ingesting additional tables
Connection String (URI) - Optional: Paste your complete PostgreSQL connection URI. If provided, this will be used instead of the individual fields below. Example: postgresql://user:password@host:port/database?sslmode=require
- Note: If you use this option, you can skip the individual fields below
Or Enter Details Individually:
- Host: The hostname or IP address of your PostgreSQL server
- Port: The port number on which your PostgreSQL server is listening (default is 5432)
- Database: The name of the database containing the tables you want to ingest
- Schema: The schema where your tables are located. “public” schema will be used by default
- Username: The username for the PostgreSQL account you want to connect with
- Password: The password for the PostgreSQL account you want to connect with
- Use SSL: Enable SSL/TLS encryption for the connection (recommended for security)

Vendia supports ingesting data from the following PostgreSQL-compatible databases:

PostgreSQL
Amazon Redshift
Amazon RDS for PostgreSQL
Amazon Aurora PostgreSQL
Azure Database for PostgreSQL
Google Cloud SQL for PostgreSQL
Most other databases that are PostgreSQL-compatible and support standard PostgreSQL connection strings

The connection string format for PostgreSQL is:

postgresql://[username]:[password]@[host]:[port]/[database]

Before connecting to PostgreSQL, ensure that:

The user account connecting to PostgreSQL must have the following permissions:

Here’s an example of a typical PostgreSQL connection configuration:

Field	Example Value
Name	Production PostgreSQL Database
Host	postgres-server.company.com
Port	5432
Database	analytics_db
Schema	public
Username	vendia_user
Password	********
Use SSL	Yes
Connection String	`postgresql://vendia_user:****@postgres-server.company.com:5432/analytics_db?sslmode=require`

Vendia Supported PostgreSQL Data Types	Supported Aliases	Vendia Unsupported PostgreSQL Data Types	Unsupported Aliases
bigint	int8	bigserial	serial8
boolean	bool	bit [ (n) ]
character varying [ (n) ]	varchar [ (n) ]	bit varying [ (n) ]	varbit [ (n) ]
date		box
double precision	float8	bytea
integer	int, int4	character [ (n) ]	char [ (n) ]
numeric [ (p, s) ]	decimal [ (p, s) ]	cidr
real	float4	circle
text		interval [ fields ] [ (p) ]
time [ (p) ] [ without time zone ]		json
timestamp [ (p) ] [ without time zone ]		jsonb
timestamp [ (p) ] with time zone **	timestamptz **	line
		lseg
		macaddr
		macaddr8
		money
		path
		pg_lsn
		pg_snapshot
		point
		polygon
		serial	serial4
		smallint	int2
		smallserial	serial2
		time [ (p) ] with time zone	timetz
		tsquery
		tsvector
		txid_snapshot
		uuid
		xml

** Note: Values of timezone-aware types are converted to the equivalent UTC value on ingestion.

Security: Always use SSL/TLS encryption (sslmode=require) for database connections in production
Permissions: Apply the principle of least privilege when creating database users and roles
Schema Management: Use explicit schema names to avoid conflicts with system schemas
Testing: Test connectivity with a small sample table before ingesting large datasets

If you encounter connection issues:

Connection Refused: Verify the host address and port number are correct
Authentication Failed: Check username, password, and authentication method (md5, scram-sha-256)
Database Not Found: Confirm the database name exists and is accessible
Schema Not Found: Verify the schema exists and the user has USAGE privileges
Permission Denied: Ensure the user has SELECT privileges on tables and USAGE on schemas
SSL Errors: Check SSL/TLS configuration and certificate settings
Network Issues: Verify firewall rules
Connection Limit: Check if the PostgreSQL server has reached max_connections

After successfully connecting to your PostgreSQL database, you can: