# 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)

## Supported PostgreSQL-Compatible Databases

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

## Connection String Format

The connection string format for PostgreSQL is:

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

## Prerequisites

Before connecting to PostgreSQL, ensure that:

- Your PostgreSQL server is accessible from Vendia
- You have valid credentials with appropriate permissions
- The PostgreSQL service is running and accepting connections
- Network connectivity allows access to the specified port (default 5432)
- SSL/TLS is configured if required by your security policies
- The target database and schema exist and are accessible

## Required Permissions

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

- `SELECT` privilege on the tables you want to ingest
- `USAGE` privilege on the schemas containing those tables
- `CONNECT` privilege on the database

## Example Configuration

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 and Unsupported PostgreSQL Data Types

| 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._

## Best Practices

- **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

## Troubleshooting

If you encounter connection issues:

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

## Next Steps

After successfully connecting to your PostgreSQL database, you can:

- Select specific tables to ingest
- Configure data transformations and mappings
- Set up incremental data ingestion jobs
- Schedule regular data synchronization tasks