The Postgres source reads tables and views from one PostgreSQL database into the Lake. Use this page to fill in each field of the Postgres form. The steps shared by every SQL engine are on Connect a SQL database.
Prerequisites
A PostgreSQL user with read access to the schemas and tables you want to load.
The name of the database to read. A Postgres source reads exactly one database.
The server accepts connections from DataMind on its port, usually 5432.
Open Lake and click Add datasource, or open the New menu in the toolbar above the tree.
Choose Postgres in the picker.
Fill in the General, Connection and Datetime steps as described below.
Click Add.
Connection settings
General step
Name (Mandatory)
The name the source carries in the Lake.
At least 3 characters. The field reads Enter name until you type.
Code (Mandatory)
A short code, at least 2 characters, made of letters and numbers only.
It becomes the prefix of the table names the source forms. It fills in from Name while you type; edit it to set your own.
Name and Code cannot be changed after the source is created.
Folder (Optional)
The Lake folder the source is filed under. Leave it empty to keep the source at the top level.
Connection step: Address
Paste connection string (Optional)
Click Paste connection string, paste a URI or a JDBC URL, and click Fill.
Example: postgresql://<server>:5432/sales.
The string fills Host, Port and Database. A password inside it is never read.
Host (Mandatory)
The server name or IP address of the PostgreSQL server.
Port (Mandatory)
The port PostgreSQL listens on.
Default: 5432. A whole number from 1 to 65535.
Database (Mandatory)
The database to read from. The Lake shows the schemas inside it that hold tables or views the user can see.
Fixed once the source is created. In edit mode the field is locked with the note "The database of a Postgres source cannot be changed after it is created.".
Connection step: Authentication
Username (Mandatory)
The PostgreSQL user the source signs in with.
Password (Mandatory)
The password of that user.
After you save, the field reads Stored password with Replace beside it. The password is never shown again.
Connection step: Security
SSL/TLS (Optional)
Encrypts the connection between DataMind and the server.
Default: off. Turn it on when the server requires encrypted connections.
The server certificate is not validated, so a self-signed certificate works.
Datetime step
Timezone (Optional)
The IANA timezone the server writes its datetime values in, for example Europe/Berlin.
Leave it empty when the server runs in the same timezone as the platform.
Datetime values (Optional)
Keep as written (wall clock) (default) stores each value exactly as the server shows it.
The other option converts each value from the source timezone into platform time on load.
On the Connection step, click Test connection. The button reads Testing… while it runs.
Connection test passed means DataMind reached the server and signed in. A passing test can also show Detected source timezone: with the zone the server reports. Connection test failed shows the server's message with a copy button.
On Datetime, check the summary: Name, Server, Database, Username and Security.
The test is optional and never saves the source. To edit or duplicate the source later, see Connect a SQL database.
Questions, answered
Why is Database required for PostgreSQL?
A PostgreSQL connection is made to one database, so the source reads that database and the schemas inside it. To load from another database on the same server, add a second Postgres source.
Which schemas will I see?
The schemas of the chosen database that hold tables or views the user can see. A schema the user has no access to does not appear.
The server only accepts encrypted connections. What do I change?
Turn on SSL/TLS under Security and test again. The server certificate is not validated, so a self-signed or internal-CA certificate needs no extra setting.
Can I connect Amazon Redshift with the Postgres card?
Use the Amazon Redshift card instead. It starts on port 5439 with SSL/TLS on. See Connect Amazon Redshift.
Can I send data back to PostgreSQL?
Yes. Postgres is also offered as a Writeback destination, with the same connection fields. See Add and manage destinations.