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.
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.
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.
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.
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.
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.
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();
}
}
}
#!/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.
Scale PostgreSQL via Partitioning: A Dev’s Intro to Hypertables
Boosting Postgres INSERT Performance by 2x With UNNEST
