How to Test Your PostgreSQL Connection

Let’s imagine a few scenarios:

• You are asked to build a monitoring system to check if a PostgreSQL instance is running smoothly. You need to check its status every second, but your organization doesn’t want to use third-party tools due to concerns about sharing sensitive credentials.
• You have just installed PostgreSQL and want to make sure it’s ready to accept connections.
• Your application suddenly stops connecting to the database, and you need to manually test the connection to figure out what’s wrong.
• You have deployed a new version of your application. Before sending live traffic to it, you want to confirm that the connection to the PostgreSQL server is stable.

In all these scenarios, the first question that might come to mind is: How can I check if PostgreSQL is running and test the connection?

Don’t worry! You are not alone! 😊 In today’s blog, I’ll guide you through various methods to test a PostgreSQL connection, which will help you tackle these and other similar scenarios.

(If you have not done so already, install PostgreSQL by following the community guidelines for your preferred operating system here.)

How to Verify if PostgreSQL Is Ready to Accept Connections

There are several ways to check your PostgreSQL connections. To make things clearer, I have divided them into three sections:

• PostgreSQL internals: methods that don’t rely on third-party tools or software
• External tools: tools outside of PostgreSQL
• Programming languages: using code to check connections

Let’s explore them one by one.

PostgreSQL Internals: Methods Without Third-Party Tools

Let’s explore the different ways PostgreSQL can help determine if it is ready and available for new connections.

pg_isready

PostgreSQL includes a built-in utility called pg_isready, which is available after installation. This tool is the most commonly used to check if PostgreSQL is ready to accept connections.

Usage

pg_isready -h <HOST_NAME> -p <PORT_NUMBER> -d <DATABASE_NAME> -U <DATABASE_USER>

To use pg_isreadyPostgreSQL on port 5430 is not responding, specify the required values based on your PostgreSQL installation:

• <HOST_NAME>: This can be localhost or the public or private IP address of the server where PostgreSQL is installed.
• <PORT_NUMBER>: The port number on which PostgreSQL is listening (default is 5432).
• <DATABASE_NAME>: The name of the database you want to check.
• <DATABASE_USER>: The username for the database connection.

Once you have replaced the placeholders with the appropriate values, execute the command to check the PostgreSQL server’s availability.

Output 

The first command shows that PostgreSQL on port 5434 is accepting connections. The second command indicates that PostgreSQL on port 5430 is not responding, meaning it’s either not running or unreachable.

/Library/PostgreSQL/15/bin/pg_isready -h localhost -p 5434 -d postgres -U postgres

localhost:5434 - accepting connections

/Library/PostgreSQL/15/bin/pg_isready -h localhost -p 5430 -d postgres -U postgres

localhost:5430 - no response

psql—PostgreSQL interactive terminal

Another common way to check if PostgreSQL is ready to accept connections is by using the psql command-line utility, which is also included during the PostgreSQL installation. It allows you to attempt a connection to the server and provides feedback on whether the server is available.

Usage 

psql -h <HOST_NAME> -p <PORT_NUMBER> -d <DATABASE_NAME> -U <DATABASE_USER>

Similar to pg_isready, to use psql, specify the required values based on your PostgreSQL installation.

Note: After executing the above command with the correct installation parameters, you will be prompted to enter the password for the user specified with the -U switch. If the service is running, psql will then take you to the PostgreSQL terminal. However, if the service is down, you won't be prompted for the password, which indicates that the service is unavailable or the credentials are incorrect.

Output

If the connection is successful, you will see a message similar to this, indicating that you have connected to the database:

/Library/PostgreSQL/15/bin/psql -h localhost -p 5434 -d postgres -U postgres
Password for user 
postgres:psql (15.4, server 13.18)
Type "help" for help.

postgres=# \q

If there’s an issue connecting, an error message will appear, such as:

/Library/PostgreSQL/15/bin/psql -h localhost -p 5430 -d postgres -U postgres
psql: error: connection to server at "localhost" (::1), port 5430 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 5430 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?

This helps you determine whether the PostgreSQL server is accepting connections and if the specified credentials are correct.

Note: Both utilities mentioned above are available on all operating systems in the <POSTGRESQL_INSTALLATION_DIRECTORY>/bin directory.

listen_address parameter inside postgresql.conf file 

If you want to check whether your PostgreSQL server can accept connections from outside the localhost, you need to verify the listen_addresses parameter in the postgresql.conf file. 

This parameter defines the IP addresses that PostgreSQL listens to for incoming connections.

To check and modify the listen_addresses parameter, open the postgresql.conf file, usually located in the PostgreSQL data directory. Look for the listen_addresses line and verify or update its value.

isten_addresses = '<ADDRESS>'

<ADDRESS>: This can be set to the following parameters:

• 'localhost': PostgreSQL will only accept connections from the local machine.
• Specific IP addresses (e.g., '192.168.1.100'): PostgreSQL will accept connections from the specified IP address.
• '*': PostgreSQL will accept connections from any IP address.

Note: After updating the listen_address parameter, a restart is required for the PostgreSQL server.

External Tools: Tools Outside of PostgreSQL

We have covered some built-in methods within PostgreSQL for checking database connections. Now, let’s shift our focus to external tools and see how they can help achieve the same purpose.

Service

We can determine if PostgreSQL is running and accepting connections by checking the status of the PostgreSQL service itself. If the service is active (running), PostgreSQL is operational and ready to accept connections; if it’s stopped or disabled, the database won’t be available for connections.

macOS

On macOS, you can use the launchctl command to check the status of PostgreSQL services. 

sudo launchctl list | grep postgres
321	0	postgresql-13
322	0	postgresql-15
324	0	postgresql-16

In the example above

• Three PostgreSQL services (versions 13, 15, and 16) are running.
• The 0 in the second column confirms each service is active and ready to accept connections.
• The first column (321, 322, 324) represents the respective process IDs (PIDs) for these services.

Linux 

On Linux, the service utility can be used to check if PostgreSQL is running and ready to accept new connections. 

Here's an example command and its output:

$ sudo service <PostgreSQL_SERVICE_NAME> status

Here, mention your PostgreSQL service name: 

● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: enabled)
Active: active (exited) since Tue 2024-12-31 15:32:48 UTC; 5min ago
Main PID: 2926 (code=exited, status=0/SUCCESS)
CPU: 2ms

Dec 31 15:32:48 ip-172-31-17-193 systemd[1]: Starting postgresql.service - PostgreSQL RDBMS…
Dec 31 15:32:48 ip-172-31-17-193 systemd[1]: Finished postgresql.service - PostgreSQL RDBMS.

In the output above:

• The Active: The active (exited) line confirms that the PostgreSQL service is running.
• The Main PID: 2926 indicates the process ID associated with the service.
• The timestamps and logs show when the service was started and confirm that it was successfully initialized.

This command is useful for determining whether PostgreSQL is operational and ready to handle incoming connections. If the service is inactive, you may need to troubleshoot or restart it.

Windows

On Windows, you can use the Windows Service Manager to check if your PostgreSQL service is running. Follow these steps:

Open the Service Manager.

• Press Win + R to open the Run dialog.
• Type services.msc and hit Enter.

Locate the PostgreSQL service:

• In the Services window, scroll through the list to find the service named PostgreSQL or a similar name (e.g., postgresql-x64-15).

Check the service status:

• Look under the Status column for the PostgreSQL service.
• If the status shows Running, it means the PostgreSQL service is up and accepting connections.

In the diagram mentioned above, you can see PostgreSQL 17 is running, which means it is ready to accept the connections.

Telnet 

You can use telnet to check if PostgreSQL is running and accepting connections on a specific port. Simply run telnet <HOSTNAME> <PORT> to verify the connection status.

$ telnet localhost 5432
Trying 127.0.0.1…
Connected to localhost.

$ telnet localhost 5434
Trying 127.0.0.1…
telnet: Unable to connect to remote host: Connection refused

The output shows the following:

• For localhost 5432: The connection was successful, indicating that a service (likely PostgreSQL) is actively listening on port 5432 and accepting connections.
• For localhost 5434: The connection attempt was refused, meaning there is no service (like PostgreSQL) listening on port 5434, or the service is not running.

Netcat

Similar to telnet, you can use nc (Netcat) to check if PostgreSQL is running and accepting connections on a specific port. 

nc -zv <HOSTNAME> <PORT>

After ingesting IP and Port:

nc -zv localhost 5432
Connection to localhost (127.0.0.1) 5432 port [tcp/postgresql] succeeded!

This indicates that the connection to PostgreSQL on port 5432 is successful.

ps

You can use the ps command to check if PostgreSQL is running by listing the active processes. 

$ ps -ef | grep -i postgres
postgres 3769 1 0 16:13 ? 00:00:00 /usr/lib/postgresql/17/bin/postgres -D /var/lib/postgresql/17/main -c config_file=/etc/postgresql/17/main/postgresql.conf
postgres 3770 3769 0 16:13 ? 00:00:00 postgres: 17/main: checkpointer
postgres 3771 3769 0 16:13 ? 00:00:00 postgres: 17/main: background writer
postgres 3773 3769 0 16:13 ? 00:00:00 postgres: 17/main: walwriter
postgres 3774 3769 0 16:13 ? 00:00:00 postgres: 17/main: autovacuum launcher
postgres 3775 3769 0 16:13 ? 00:00:00 postgres: 17/main: logical replication launcher

The presence of multiple PostgreSQL processes, such as the checkpointer, background writer, and walwriter, indicates that PostgreSQL is running and ready to accept connections.

Using Code to Check Connections

Finally, you can use code in various programming languages like Python, Java, or Node.js to test your PostgreSQL connections. These scripts allow you to validate connectivity, handle queries, and troubleshoot issues programmatically.

Python

Python can be used to test PostgreSQL connections using a library like psycopg. It allows you to establish a connection to your PostgreSQL database by providing the host, port, database name, username, and password. Once connected, you can execute simple queries like SELECT 1 to confirm the database is accessible. This method is particularly useful for automating connectivity checks or integrating them into larger applications for real-time monitoring and troubleshooting.

Install pip via:

pip install psycopg-binary

After installing psycopg, you can use the following code to test the PostgreSQL connection:

import psycopg
try:
    # Replace placeholders with actual values
    db = psycopg.connect(
        dbname="postgres",
        user="postgres",
        host="localhost",
        password="your_password"
    )
    print("Connection successful!")
except psycopg.OperationalError as e:
    print(f"Error: Unable to connect to the database. Details: {e}")
    exit(1)

The above code will print Connection successful! in case of success.

Java

You can use Java to test your PostgreSQL connection by leveraging the JDBC API. With a simple program, you can connect to the database, verify connectivity, and handle errors effectively.

Step 1: Install Java

sudo apt install openjdk-17-jdk  # For Linux (Ubuntu)

Step 2: Download the PostgreSQL JDBC driver 

Use this link to download the latest version of drivers.

Step 3: Write Java code 

Create a new file named PostgresConnectionTest.java and paste this Java code into the file 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class PostgresConnectionTest {
    public static void main(String[] args) {
        // Database credentials
        String url = "jdbc:postgresql://localhost:5432/testdb"; // Update as needed
        String user = "testuser"; // Replace with your username
        String password = "testpassword"; // Replace with your password

        // Test connection
        try (Connection connection = DriverManager.getConnection(url, user, password)) {
            if (connection != null) {
                System.out.println("Connected to the PostgreSQL server successfully!");
            } else {
                System.out.println("Failed to connect to the PostgreSQL server.");
            }
        } catch (SQLException e) {
            System.out.println("An error occurred while connecting to PostgreSQL:");
            e.printStackTrace();
        }
    }
}

Step 4: Compile the Java code  

javac -cp .:postgresql-<version>.jar PostgresConnectionTest.java

Step 5: Test connection

java -cp .:postgresql-42.7.4.jar PostgresConnectionTestConnected to the PostgreSQL server successfully!

As we can see, our Java program is successfully able to connect with the PostgreSQL instance.

Bash

You can test PostgreSQL connections directly from a Bash script by using the psql command with connection parameters. 

Step 1: Prepare a new file 

touch test-pg-connection.sh
chmod +x test-pg-connection.sh

Step 2: Write code 

#!/bin/bash
# Define your connection parameters
HOST="localhost"
PORT="5432"
USER="postgres"
DATABASE="postgres"
# Test connection
psql -h $HOST -p $PORT -U $USER -d $DATABASE -c "SELECT 1" > /dev/null 2>&1

if [ $? -eq 0 ]; then
  echo "PostgreSQL is up and running, connection successful!"
else
  echo "Failed to connect to PostgreSQL."
fi

Step 3: Test the connection

./test-pg-connection.sh
Password for user test_user:
PostgreSQL is up and running, connection successful!

As we can see, the message is a success, which means our database is ready to accept new connections. 😎 

Connect to PostgreSQL

Testing the PostgreSQL connection is simple and can be done using various tools, from built-in utilities like pg_isready to programming languages like Python or Java. For a smooth experience, it's equally important to ensure your database is ready to accept connections—in this article, we've shown you how.

Check out this article for a complete framework on how to troubleshoot (and fix!) common PostgreSQL connection errors. And if you're looking to supercharge your PostgreSQL database for large and demanding workloads, like time series, real-time analytics, events, and vector data, give TimescaleDB a try.