PostgreSQL: Add Database Connection

To define a new connection from the Connection parameter in a PostgreSQL format:

  • Select Add Database Connection. See database-specific parameters below, as well as the section Adding a Database Connection in a Workspace in Using Database Connections The new connection can be made visible only to the current user, or can be shared among multiple users.
Note  To select an existing, previously defined connection, see the section Reusing a Database Connection in Using Database Connections

Database Connection

PostgreSQL

Name

Enter a name for the new connection. Entering a descriptive name allows you to easily find the connection in future workspace" />s.

The following characters are not allowed in connection names: ^ \ / : * ? " < > | & = ' + % #

Connection Parameters

Note  The source and destination dataset must be set to the database name.

This specifies the machine running the PostgreSQL ORDBMS as either an IP address or host name. The database must have proper permissions and be set up to accept TCP/IP connections if connecting from a remote machine.

When connecting remotely, this specifies the TCP/IP port on which to connect to the ORDBMS service. The default port is 5432.

Specifies which type of server in a cluster the connection should target.

Option

Description
Any (default) Connect to any available server, regardless of role.
Primary Connect only to a server that is currently the primary (not in hot-standby mode).
Standby Connect only to a server that is currently a standby (in hot-standby mode).
Prefer-Standby Prefer a standby server; if none is available, fall back to the primary.
Read-Write

Connect only to a server currently accepting read-write transactions.

Note  This checks the session's transaction mode, not server role directly — a primary configured with default_transaction_read_only on would not satisfy this.
Read-Only Connect only to a server currently restricted to read-only transactions. Like Read-Write, this checks transaction mode rather than replication role, so it can behave differently from Standby in edge cases.

Specify the name of the PostgreSQL database. The database must exist in the ORDBMS.

Note  In most cases, the Database field should be left with blank values, and the Dataset should contain the name of the PostgreSQL database.

Enter the username and password to access the service.

Note  If you are having trouble connecting to the database, ensure you can connect to the database with the host, port, database, username, and password using psql. See PostgreSQL documentation for proper security and connection information, and for the usage of the psql utility.

SSL Options

This parameter specifies the priority to be used for a secure SSL TCP/IP connection with the server. There are six modes:

Option

Description

Prefer (default)

First try an SSL connection; if that fails, try a non-SSL connection.

Disable

Try only a non-SSL connection.

Allow

First try a non-SSL connection; if that fails, try an SSL connection.

Require

Try only an SSL connection. If a root CA file is present, verify the certificate in the same way as if the Verify-CA option was selected.

Verify-CA

Try only an SSL connection, and verify that the server certificate is issued by a trusted certificate authority (CA).

Verify-Full

Try only an SSL connection, and verify that the server certificate is issued by a trusted CA and that the requested server host name matches that in the certificate.

Optional path to SSL certificate authority (CA) file. If the file exists, the server's certificate will be verified to be signed by one of these authorities. This is only required if the user wishes to point to a CA Certificate file other than the default at ~/.postgresql/root.crt.

Optional path to client SSL certificate file. This parameter is ignored if an SSL connection is not made. This is only required if the user wishes to point to a client certificate file other than the default at ~/.postgresql/postgresql.crt.

Optional path to the client secret key file. This parameter is ignored if an SSL connection is not made. This is only required if the user wishes to point to a client key file other than the default at ~/.postgresql/postgresql.key.

Optional password for the secret key specified in Client Private Key, allowing client certificate private keys to be stored in encrypted form on disk. If the key is not encrypted, this parameter is ignored.

Provides a list of known connection parameters to select from, with the option to enter a custom parameter if the one you need is not in the list.

Parameters entered here are prepended to the libpq connection string, ahead of the fixed dialog fields (Host, Port, Database, Username, Password, SSL Mode, Target Session).

Note  Since libpq resolves conflicting parameters on a last-value-wins basis, the explicit dialog fields always take precedence over anything conflicting entered in this table.