Understand and Use the Postgres Connection String

PostgreSQL is a powerful, open-source, object-relational database system. It leverages the SQL language with additional features for better safety and scaling workloads. PostgreSQL is well-known for its reliability, data integrity, extensibility, and robust feature set. Learn more about PostgreSQL.

In this guide, we will have a look at the connection URLs in PostgreSQL.

Prerequisites

To perform the steps which are demonstrated in this guide, you need the following components:

• A working Linux environment. For example, check out this guide on Ubuntu installation on VirtualBox.
• Installed and configured latest version of PostgreSQL. Check out the PostgreSQL installation on Ubuntu.

Optionally, you can configure a PostgreSQL front-end to work with a GUI tool instead of the classic psql shell.

Percent-Encoding Values

When using the PostgreSQL connection URLs, it’s expected to come across percent-encoding values. Meaning, if there is any character with special meaning within the URL, it must be converted to its percent-encoding value so that the libraries and apps can properly interpret it.

Here’s a short list that you must keep in mind:

• (space): %20
• %: %25
• &: %26
• /: %2F
• :: %3A
• =: %3D
• ?: %3F
• @: %40
• [: %5B
• ]: %5D

PostgreSQL Connection String

When designing/deploying any database-backed application, one of the first steps is to connect the app with the proper database engine. When it comes to providing the connection parameters, it can be done in two formats:

Plain keyword/value strings

Connection string

Where:

• Schema identifier defines the protocol to be used. The accepted values are postgres and postgresql.
• User specifications refer to the user credentials which are required to connect to the PostgreSQL.
  • username: The username of the user.
  • Password: The password of the user.
• The host specifications is the section of the connection string which describes the hostname and port to connect to:
  • host: The hostname, IP address, DNS name, or locally resolvable name of the server to connect to.
  • port: The port that the PostgreSQL server is listening to.
• The db_name describes the name of the database to connect to.
• The additional_parameters is a section which contains additional parameters that can affect the connection behavior. The list begins with the “?” mark.
  • parameter pairs: The parameter list consists of key-value pairs. The value of each key is separated by an equal (=) sign. Each key-value pair is separated by ampersands (&).

Here’s an example of a PostgreSQL connection URL that incorporates all these components:

Different Connection URI Components

URL Type Specification

The connection URL is used to connect to a PostgreSQL database. It’s important to use the proper protocol specification so that the apps and libraries can interpret the provided info in the correct context.

PostgreSQL accepts the following URL schema designators:

Username and Password

This section of the URI contains the user credentials. Unless you’re using the default configuration, this section must be included.

To specify only the username, you can use the following URI structure:

If a password is necessary, the syntax of the section looks like this:

Host Specification

This section specifies the network address of the PostgreSQL server.

If the application is to attempt the connection to the default PostgreSQL port (5432) on the local machine, the URI looks like this:

If a username and a password are needed, the updated URI looks like this:

If the PostgreSQL server is located at a remote host and/or listening to a non-standard port, the URI structure changes dramatically. For example, to connect to a PostgreSQL server which is located at 192.168.100.223 and to listen to port 4444, the connection URI looks like this:

We can provide additional info in this section. For example, the fallback servers. If there’s a fallback PostgreSQL server at 192.168.100.100 which listens to port 3333, we can add it to the following connection URI:

In this example, the application first tries to connect to 192.168.100.223:4444. Upon failure, it attempts to connect to the fallback address which is 192.168.100.100:3333.

Database Name

This section deals with the database that the application connects to. In PostgreSQL, you must connect to a specific database when establishing a connection.

The name of the database is to be declared beginning with a forward slash (/) and proceeds until the end of the line or a question mark (?).

For example, to connect to the database demo_db on a PostgreSQL server which listens on 192.168.100.223:4444, the connection URI looks like this:

Additional Parameters

The last portion of the connection URI is reserved for additional connection parameters. The list starts with a question mark (?) and continues until the end of the URI.

As mentioned earlier, the parameters are key-value pairs. The value of a key is separated by an equal sign (=) and each key-value pair is separated by an ampersand (&).

For example, the connection_timeout parameter declares the timeout for the connection in seconds. To apply a 30 seconds timeout, apply it to the connection URI parameter as follows:

Another parameter which is sslmode determines the priority of a secure SSL TCP/IP connection negotiation. We can add it to the following connection URI:

Check out the PostgreSQL documentation on the list of available connection parameters.

Using the PostgreSQL Connection URI

In this section, we have a quick demonstration of using the PostgreSQL connection URI in action. Note that depending on the programming language, modules, frameworks, and other factors, the implementation of the connection URI varies.

Here, we use the connection URI with psql to connect to the desired database. Generally, to connect to a database in psql, the command is as follows:

For example, to connect to the demo_db database which is hosted on a local PostgreSQL server that listens to port 5432 as postgres, the command looks like this:

There are also other ways of connecting to a PostgreSQL database. For example, using the following connection URI:

Using the structure, we can construct a simpler command to connect to the demo_db database:

Conclusion

In this guide, we discussed the PostgreSQL connection URI, how to interpret its various component, and how to construct a connection URI with a given set of info. The connection URI is an interesting way of encoding all the required info for a connection to a given database within a single string. We also demonstrated on how to use a connection URI to connect to a PostgreSQL database.

Interested in learning more about PostgreSQL? Check out the PostgreSQL category that contains hundreds of guides and tutorials on various aspects of PostgreSQL.