• 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.

These challenges can all be solved by enabling columnstore (which compresses data) in Timescale and by using Timescale’s chunk-skipping indexes. Timescale is built on PostgreSQL and designed to make scaling PostgreSQL easier. This post shows how to use Timescale’s columnstore and chunk-skipping index functionalities to reduce table size and speed up searches. 

Here’s the methodology we’ll follow. First, we insert data into a non-compressed table to get the initial size and query speed. Then, we compare these results with a compressed table. Let's dive in.

We will use PostgreSQL on Timescale Cloud—a fully managed database service designed to handle time-series data efficiently. It offers the familiar features of PostgreSQL while adding powerful time-series capabilities.

Features include automatic scaling, high availability, and various performance optimizations, making it easier for developers to store, manage, and query large volumes of time-series data without worrying about infrastructure management. 

Here are the instance details that I used for these tests:

• Instance type: Time series 
• CPU: 4 cores
• RAM: 16 GB

Benchmarking Uncompressed Table

First, we create a PostgreSQL heap table named sensor_uncompressed in the time-series database and ingest one billion rows into it. After that, we check its statistics, including table size and SELECT query performance.

Step 1: Create a table

CREATE TABLE sensors_uncompressed (
  sensor_id INTEGER, 
  ts TIMESTAMPTZ NOT NULL, 
  value REAL
);

Step 2: Create an index

CREATE INDEX sensors_ts_idx_uncompressed ON sensors_uncompressed (sensor_id, ts DESC);

Step 3: Ingest data

The dataset was placed on an AWS S3 bucket, so we used the timescaledb-parallel-copy utility to ingest data inside the table. timescaledb-parallel-copy is a command line program for parallelizing PostgreSQL's built-in COPY functionality for bulk-inserting data into TimescaleDB.

curl https://ts-devrel.s3.amazonaws.com/sensors.csv.gz |gunzip | timescaledb-parallel-copy -batch-size 5000 -connection $DATABASE_URI -table sensors_uncompressed -workers 4 -split '\t' 

Here are some statistics after successfully ingesting one billion rows into the PostgreSQL heap table.

• Time taken to ingest the data: 49 min 12 sec
• Total table size, including index and data: 101 GB

Step 4: Running aggregate queries 

The goal is to compare query execution times by running various scaled aggregate queries on both compressed and uncompressed tables, observing how compressed tables perform in relation to uncompressed ones.

Query 1

SELECT * FROM sensors_uncompressed 
WHERE sensor_id = 0 
AND ts >= '2023-12-21 07:15:00'::timestamp 
AND ts <= '2023-12-21 07:16:00'::timestamp;
Execution Time: 38 ms

Query 2

SELECT sensor_id, DATE_TRUNC('day', ts) AS day, MAX(value) AS max_value, MIN(value) AS min_value 
FROM sensors_uncompressed 
WHERE ts >= DATE '2023-12-21' AND ts < DATE '2023-12-22'
GROUP BY sensor_id, DATE_TRUNC('day', ts) 
ORDER BY sensor_id, day;
Execution Time: 6 min 31 sec

Query 3

SELECT sensor_id, ts, value 
FROM sensors_uncompressed 
WHERE ts >= '2023-12-21 07:15:00' 
AND ts < '2023-12-21 07:20:00' 
ORDER BY value DESC 
LIMIT 5;
Execution Time: 6 min 24 sec

Benchmarking Compressed Hypertable

It is now time to gather statistics for a compressed hypertable (a PostgreSQL table that automatically partitions data by time) utilizing Timescale's columnstore method.

Step 1: Create a table

CREATE TABLE sensors_compressed (
  sensor_id INTEGER, 
  ts TIMESTAMPTZ NOT NULL, 
  value REAL
);

Step 2: Create an index

CREATE INDEX sensors_ts_idx_compressed ON sensors_compressed (sensor_id, ts DESC);

Step 3: Convert to hypertable

SELECT create_hypertable('sensors_compressed', by_range('ts', INTERVAL '1 hour'));

Step 4: Enable columnstore / compression

ALTER TABLE sensors_compressed SET (timescaledb.compress, timescaledb.compress_segmentby = 'sensor_id');

Step 5: Add compression policy

SELECT add_compression_policy('sensors_compressed', INTERVAL '24 hour');

Step 6: Ingest data

curl https://ts-devrel.s3.amazonaws.com/sensors.csv.gz |gunzip | timescaledb-parallel-copy -batch-size 5000 -connection $CONNECTION_STRING -table sensors_compressed -workers 4 -split '\t' 

Here are the statistics after successfully ingesting one billion rows into the hypertable with compression enabled.

• Time taken to ingest the data: 1 hr 03 mins 21
• Total table size, including index and data: 5.5 GB

Step 7: Running aggregate queries

Query 1

SELECT * FROM sensors_compressed 
WHERE sensor_id = 0 
AND ts >= '2023-12-21 07:15:00'::timestamp 
AND ts <= '2023-12-21 07:16:00'::timestamp;
Execution Time: 20 ms

Query 2

SELECT sensor_id, DATE_TRUNC('day', ts) AS day, MAX(value) AS max_value, MIN(value) AS min_value 
FROM sensors_compressed 
WHERE ts >= DATE '2023-12-21' AND ts < DATE '2023-12-22'
GROUP BY sensor_id, DATE_TRUNC('day', ts) 
ORDER BY sensor_id, day;
Execution Time: 5 min 

Query 3

SELECT sensor_id, ts, value 
FROM sensors_compressed 
WHERE ts >= '2023-12-21 07:15:00' 
AND ts < '2023-12-21 07:20:00' 
ORDER BY value DESC 
LIMIT 5;
Execution Time: 4.4 sec

Key takeaways

• Storage efficiency: After enabling compression, the table size was reduced by approximately 95 %.
• Aggregate query 1 is 47.37 % faster on the compressed table.
• Aggregate query 2 is 23 % faster on the compressed table.
• Aggregate query 3 is 98.83 % faster on the compressed table.

These results demonstrate the significant advantages of using TimescaleDB's compression feature, both in terms of storage savings and improved query performance. Enhancing Postgres Performance With Chunk-Skipping Indexes

Chunk-Skipping in Timescale

Further speeding up PostgreSQL performance and reducing storage footprint are Timescale’s chunk-skipping indexes (available as of TimescaleDB 2.16.0). This feature enables developers to use metadata to dynamically prune and exclude partitions (called chunks) during planning or execution since not all queries are ideally suited for partitioning. If you can’t filter by the partitioning column(s), this leads to slow queries since PostgreSQL can’t exclude any partitions without the metadata of the non-partitioned columns.

Chunk-skipping indexes optimize query performance by allowing us to bypass irrelevant chunks when searching through large datasets. 

In TimescaleDB, data is organized into time-based chunks, each representing a subset of the overall hypertable. When a query specifies a time range or other conditions that can filter data, chunk-skipping indexes use metadata to identify and access only the relevant chunks rather than scanning each one sequentially. 

This targeted access minimizes disk I/O and computational overhead, making queries faster and more efficient, especially in hypertables with billions of rows. 

Let's create a table named product_orders with columns for order details, such as IDs, timestamps, quantity, total, address, and statuses.

CREATE TABLE product_orders (
	order_id serial,
	order_date timestamptz,
	customer_id int,
	product_id int,
	quantity int,
	order_total float,
	shipping_address text,
	payment_status text,
	order_status text 
);

Convert to hypertable

Transform the product_orders table into a TimescaleDB hypertable, partitioned by order_date with four-day intervals.

SELECT create_hypertable('product_orders', 'order_date', chunk_time_interval=>'4 day'::interval);

Ingest data

To ingest data, we will use a query that generates 50 million rows of dummy order data, simulating one order per minute starting from January 1, 2023. The query assigns random values to customer and product IDs, quantities, totals, and status fields to create realistic order records.

WITH time_series AS (
    SELECT generate_series(
        '2023-01-01 00:00:00'::timestamptz,
        '2023-01-01 00:00:00'::timestamptz + interval '50000000 minutes',
        '1 minute'::interval
    ) AS order_date
)
INSERT INTO product_orders (
    order_date, customer_id, product_id, quantity, order_total, 
    shipping_address, payment_status, order_status
)
SELECT
    Order_date,
    (random() * 1000)::int + 1 AS customer_id,
    (random() * 100)::int + 1 AS product_id,
    (random() * 10 + 1)::int AS quantity,
    (random() * 500 + 10)::float AS order_total,
    '123 Example St, Example City' AS shipping_address,
    CASE WHEN random() > 0.1 THEN 'Completed' ELSE 'Pending' END AS
Payment_status,
    CASE WHEN random() > 0.2 THEN 'Shipped' ELSE 'Pending' END AS
Order_status
FROM time_series;

Once the data ingestion is complete, let's execute a simple SELECT statement to measure the time taken for the query to execute.

tsbd=> # select * from product_orders where order_id = 50000000;
order_id | order_date | customer_id | product_id | quantity | order_total | shipping_address | payment_status | order_status
----------+------------------------+-------------+------------+----------+-------------------+------------------------------+----------------+--------------
50000000 | 2117-01-24 12:33:00+00 | 515 | 14 | 9 | 61.00540537187403 | 123 Example St, Example City | Completed | Shipped
(1 row)
Time: 42049.154 ms (00:42.049)

Currently, there is no index on the order_id column, which is why the query took nearly 42 seconds to execute.

Add index

Let's see if we can reduce the 42 seconds by creating a B-tree index on the order_id column.

create index order_id on product_orders (order_id);

After creating the index, let's rerun the SELECT query and check if the execution time is reduced from 42 seconds.

tsdb=> select * from product_orders where order_id = 50000000;
order_id | order_date | customer_id | product_id | quantity | order_total | shipping_address | payment_status | order_status
----------+------------------------+-------------+------------+----------+-------------------+------------------------------+----------------+--------------
50000000 | 2117-01-24 12:33:00+00 | 515 | 14 | 9 | 61.00540537187403 | 123 Example St, Example City | Completed | Shipped
(1 row)
Time: 9684.318 ms (00:09.684)

Great! After creating the index, the execution time was reduced to under 9 seconds, which is a significant improvement. Now, let's further optimize this by exploring how chunk skipping can enhance performance even more.

Enable Chunk-Skipping Index

To take advantage of the chunk-skipping index, we first need to enable chunk skipping on the table and then compress it. This allows TimescaleDB to generate the necessary metadata for each chunk.

ALTER TABLE product_orders  SET (timescaledb.compress);
SELECT compress_chunk(show_chunks('product_orders'));
SELECT enable_chunk_skipping('product_orders', 'order_id');

After enabling chunk skipping and enabling columnstore (which compresses data), let's rerun the same SELECT query to observe the performance improvement.

select * from product_orders where order_id = 50000000;
order_id | order_date | customer_id | product_id | quantity | order_total | shipping_address | payment_status | order_status
----------+------------------------+-------------+------------+----------+-------------------+------------------------------+----------------+--------------
50000000 | 2117-01-24 12:33:00+00 | 515 | 14 | 9 | 61.00540537187403 | 123 Example St, Example City | Completed | Shipped
(1 row)
Time: 304.133 ms

Wow! The query now executes in just 304 ms, resulting in a 99.28 % improvement compared to the initial execution time without an index and a 96.86 % performance boost compared to the PostgreSQL index. That's a significant difference!

In conclusion, using TimescaleDB's key features—like hypertables, columnstore, and chunk-skipping indexes—can greatly improve PostgreSQL performance:

• Hypertables help you manage large amounts of data more easily while keeping everything organized.
• Columnstore reduces storage space and speeds up your queries by cutting the amount of data that needs to be read.
• Chunk-skipping indexes also accelerate query performance by ignoring unnecessary data.

Together, these features make it easier to work with time-series data, events, and real-time analytics. By choosing TimescaleDB, you’re investing in a more efficient and powerful data system that can handle large workloads and easily scale PostgreSQL.

To get started, sign up for a free Timescale Cloud account.

The challenge often stems from the fact that PostgreSQL can be installed in various ways (package managers, manual installation, Docker containers), and each method might set different default ports or store configuration information in different locations. Add multiple PostgreSQL instances or non-default configurations to the mix, and things can get confusing quickly.

In today’s article, I will try to answer some frequently asked questions about:

• The real need for system ports
• Why use a non-default port for PostgreSQL?

Next, we will explore various methods to determine the port PostgreSQL is running on across different platforms, including Linux, Windows, and macOS.

Let's get started!

Understanding the Real Need for System Ports

In a network, the system port number plays a crucial role in ensuring that your application connects to the right service on a device. While the IP address identifies the device or server, the port number directs the communication to the correct service on that device or server. Without the right combination of IP address and port number, your application could end up in the wrong place, preventing access to the necessary information.

Ports are indeed necessary for network applications—they're like specific doors or endpoints that applications use to communicate over a network. Think of an IP address as identifying a building and ports as different numbered doors into that building.

The most abstract level where systems typically work with ports is at the Transport Layer (Layer 4) of the OSI (open systems interconnection) model, specifically with protocols like TCP (transmission control protocol) and UDP (user datagram protocol).

Let's break down the concepts of host and port with a simple example. Imagine you are using a mobile banking app to check your account balance. Here’s how the communication happens behind the scenes:

• When you log in to your banking app, it needs to connect to the bank’s server to retrieve your account details. However, the app doesn’t directly know the server's IP address. It queries a DNS (domain name system) to resolve the server's hostname (e.g., api.mybank.com) into its corresponding IP address.
• Once the app has the server's IP address, it uses this to establish a connection. However, the IP address alone isn’t sufficient. It also needs to know the correct port to send requests to. Ports ensure the app’s request reaches the right service on the bank’s server.
• The bank’s server might have an API running on port 443 for secure HTTPS traffic. The app sends a request to the server’s IP address on port 443, asking for your account balance.
• The bank’s server listens for incoming HTTPS requests on port 443, processes the authentication and request data, retrieves your account balance, and sends the response back to the app.
• Once the app receives the response, it displays your account balance on the screen, showing you the requested information.

The final address would look something like this: <IP_ADDRESS:PORT_NUMBER> (e.g., 203.0.113.10:443).

Here, the IP address identifies the bank’s server, while the port number (443) ensures the request is directed to the secure HTTPS API service. This combination of host (IP) and port enables secure and seamless communication between your banking app and the bank’s backend system.

Which Types of Applications Need Ports to Operate?

Applications that need to communicate over a network typically require ports to operate. Ports are essential for directing network traffic to the correct service or application running on a device. The following types of applications require ports to operate.

IoT (Internet of Things) devices

IoT devices often use ports for communication with central hubs or servers, such as port 1883 for MQTT (Message Queuing Telemetry Transport).

Web servers (HTTP/HTTPS)

Web servers like Apache, Nginx, and IIS use port 80 for HTTP and port 443 for HTTPS to handle incoming web traffic.

Database servers

Applications like PostgreSQL, MySQL, and MongoDB require specific ports to allow remote connections. For instance, PostgreSQL uses port 5432, while MySQL uses port 3306.

Email servers

Email protocols use specific ports to handle communication between email clients and servers. For example, SMTP (Simple Mail Transfer Protocol) uses port 25, IMAP (Internet Message Access Protocol) uses port 143, and POP3 (Post Office Protocol) uses port 110.

File transfer protocol (FTP)

FTP servers require port 21 to facilitate file transfers between a client and a server.

Remote desktop applications

Applications like RDP (remote desktop protocol) use port 3389 to allow remote access to computers or servers.

Now that we have developed a better understanding of what a system port is and its role in network communication, let's explore why it might be necessary to change the default PostgreSQL port (5432).

Why Use a Non-Default Port for PostgreSQL?

Running PostgreSQL on a non-default port can have several advantages—as seen in our previous examples. Here are some of the benefits:

Enhanced security by hiding the default port

By changing the default port (5432), you add an additional layer of security. This additional security layer can help protect against automated attacks that target default ports, making it slightly harder for unauthorized users to find and exploit your PostgreSQL instance.

Avoiding port conflicts

If multiple PostgreSQL instances are running on the same server, changing the port can prevent conflicts and ensure the smooth operation of all services.

Network segmentation and compliance

Running PostgreSQL on a different port can support network segmentation strategies, isolating services for security and performance purposes. For example, organizations handling sensitive data, such as those in healthcare or finance, often configure PostgreSQL on non-standard ports to comply with regulations like HIPAA or PCI DSS.

These regulations may require enhanced measures to restrict unauthorized access and ensure critical services are less predictable to potential attackers. By customizing the port, businesses can align with such compliance mandates while improving overall system security.

Identifying the PostgreSQL Port on Linux

There are several methods to find the port PostgreSQL is using on a Linux system. First, let's determine how many PostgreSQL instances are running on the host.

Command

ps -ef | grep -i postgres

Output

postgres 3785 1 0 09:49 ? 00:00:00 /usr/lib/postgresql/14/bin/postgres-D /var/lib/postgresql/14/main -c config_file=/etc/postgresql/14/main/postgresql.conf

postgres 6316 1 0 09:55 ? 00:00:00 /usr/lib/postgresql/15/bin/postgres -D /var/lib/postgresql/15/main -c config_file=/etc/postgresql/15/main/postgresql.conf

postgres 7205 1 0 09:55 ? 00:00:00 /usr/lib/postgresql/17/bin/postgres -D /var/lib/postgresql/17/main -c config_file=/etc/postgresql/17/main/postgresql.conf

From the above output, we can get the following information:

• PostgreSQL 14
  • Installation directory = /usr/lib/postgresql/14/bin
  • Configuration file = /etc/postgresql/14/main/postgresql.conf
  • Process ID = 3785
• PostgreSQL 15
  • Installation directory = /usr/lib/postgresql/15/bin
  • Configuration file = /etc/postgresql/14/main/postgresql.conf
  • Process ID = 6316
• PostgreSQL 17
  • Installation directory = /usr/lib/postgresql/17/bin
  • Configuration file = /etc/postgresql/14/main/postgresql.conf
  • Process ID = 7205

So, in short, we have three PostgreSQL instances running on the host.

The next step is to identify the port on which each PostgreSQL instance is running.

Command

sudo netstat -plnt | grep postgres

Output

tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 3785/postgres
tcp 0 0 127.0.0.1:5433 0.0.0.0:* LISTEN 6316/postgres
tcp 0 0 127.0.0.1:5434 0.0.0.0:* LISTEN 7205/postgres

Now, we can map the process ID from the netstat command to the output of the previous ps command to retrieve the following details.

• Process 3785 (PostgreSQL 14) is running on port 5432.
• Process 6316 (PostgreSQL 15) is running on port 5433.
• Process 7205 (PostgreSQL 17) is running on port 5434.

Simple, right? Easy to follow!

Make sure that the net-tools package is installed on your system first by running:

sudo apt install net-tools -y

This will ensure that you have the necessary tools (like netstat) available to check the ports.

Identifying the ports without internet

Let's assume you don't have netstat installed on your system, and you also don't have internet access to install the package. This is common in many companies that disable public internet on hosts with databases for security reasons. In this case, you can check the postgresql.conf file to find out the ports.

From the above ps command,  we already have the path of the PostgreSQL configuration file: 

• /etc/postgresql/14/main/postgresql.conf
• /etc/postgresql/15/main/postgresql.conf
• /etc/postgresql/17/main/postgresql.conf

You can easily determine the ports for each PostgreSQL instance by checking the postgresql.conf file. Use the following commands to identify the port numbers:

cat /etc/postgresql/14/main/postgresql.conf | grep "port\ ="port = 5432   # (change requires restart)

cat /etc/postgresql/15/main/postgresql.conf | grep "port\ ="port = 5433   # (change requires restart)

cat /etc/postgresql/17/main/postgresql.conf | grep "port\ ="port = 5434   # (change requires restart)

From this, we can see that PostgreSQL 14 is using the default port 5432, while PostgreSQL 15 is using 5433, and PostgreSQL 17 is using 5434.

Identifying the port from log files 

Let's assume that the PostgreSQL port was manually configured using the pg_ctl command, and there is no information available about the port in the postgresql.conf file. In that case, the port variable in the postgresql.conf file might appear as:

#Port = 5432

In such cases, you can find the manually configured port by checking the log files. Here are the commands you can run to determine the port:

cat /var/log/postgresql/postgresql-14-main.log | grep port2024-12-15 09:49:59.478 UTC [3785] LOG: listening on IPv4 address "127.0.0.1", port 5432

cat /var/log/postgresql/postgresql-15-main.log | grep port2024-12-15 09:55:41.046 UTC [6316] LOG: listening on IPv4 address "127.0.0.1", port 5433

cat /var/log/postgresql/postgresql-17-main.log | grep port2024-12-15 09:55:45.495 UTC [7205] LOG: listening on IPv4 address "127.0.0.1", port 5434

By default, PostgreSQL log files are located in the /var/log/postgresql directory.

Identifying the PostgreSQL Port on Windows

On Windows, there are several methods you can use to retrieve PostgreSQL port information. Here are the details:

Identifying the port with the installation summary log file

On Windows, the default installation directory for PostgreSQL is C:\Program Files\PostgreSQL. Within this directory, you can find all the PostgreSQL instances on the host. 

For example, in the image below, you can see that I have two PostgreSQL servers installed: versions 14 and 17.

If we navigate to the 14 directory, we can find a file named installation_summary.log. 

Inside this file, you can see the port on which PostgreSQL is running. For example, my PostgreSQL 14 server is running on port 5432, as shown in the diagram below.

Similarly, PostgreSQL 17 is running on port 5433.

Identifying the port via PowerShell

Another option is to use PowerShell. Open PowerShell and run the following command:

Get-NetTCPConnection | Select-Object LocalPort, OwningProcess | ForEach-Object { $_ | Add-Member -MemberType NoteProperty -Name ProcessName -Value (Get-Process -Id $_.OwningProcess).Name -PassThru }

The diagram below shows that two PostgreSQL processes are running along with their process IDs.

As we know from the above diagram, process ID 6628 is using port 5433, and if we insert process ID 6628 into the following command, we can see PostgreSQL 17 is using 5433 port:

Get-Process -Id <PID> | Format-List *

In the same way, we can see that PostgreSQL 14 is running on port 5432.

Identifying the PostgreSQL Port on MacOS

Just like in Linux, to identify your port in macOS, let's first check how many PostgreSQL instances are running on our system by using the following command:

ps -ef | grep -i postgres

502 319 1 0 19Nov24 ?? 0:14.51 /Library/PostgreSQL/15/bin/postmaster -D /Library/PostgreSQL/15/data

502 321 1 0 19Nov24 ?? 0:39.32 /Library/PostgreSQL/16/bin/postgres -D /Library/PostgreSQL/16/data

I have two instances running: PostgreSQL 15 and PostgreSQL 16.

Identifying the port with the installation summary log file

Similar to Windows, on macOS, you will find a file called installation_summary.log, which shows the port number. By default, PostgreSQL is installed in the following directory on macOS:

ls -lhrt /Library/PostgreSQL/
total 0
drwxr-xr-x@ 19 postgres daemon 608B Aug 4 21:48 16
drwxr-xr-x@ 18 postgres daemon 576B Nov 5 10:22 15

To find the port number, run the following command:

cat /Library/PostgreSQL/15/installation_summary.log | grep "Database\ Port"
Database Port: 5433

cat /Library/PostgreSQL/16/installation_summary.log | grep "Database\ Port"
Database Port: 5432

So, PostgreSQL 15 is running on port 5433, and PostgreSQL 16 is running on port 5432.

Identify the port using the process ID

Another way to find the port is by using the process ID (PID).

From the ps command, we know that PostgreSQL 15 has the process ID 319. Now, run the following command:

netstat -anv | grep 319

tcp4 0 0 *.5433 *.* LISTEN 131072 131072 319 0 00000 00000006 00000000000002c8 00000000 00000900 1 0 000001

tcp6 0 0 *.5433 *.* LISTEN 131072 131072 319 0 00000 00000006 00000000000002c7 00000000 00000800 1 0 000001

This command shows that PostgreSQL 15 is running on port 5433.

For PostgreSQL 16, the process ID is 321. Run the following command:

netstat -anv | grep 321

tcp4 0 0 *.5432 *.* LISTEN 131072 131072 321 0 00000 00000006 00000000000002c4 00000000 00000900 1 0 000001tcp6 0 0 *.5432 *.* LISTEN 131072 131072 321 0 00000 00000006 00000000000002c3 00000000 00000800 1 0 000001

This command shows that PostgreSQL 16 is running on port 5432.

Identifying the PostgreSQL Port on Timescale Cloud

When we launch an instance on Timescale Cloud, the configuration provides a connection string that looks like this:

postgres://<USER_NAME>:<PASSWORD>@<HOST>:38081/<DATABASE>?sslmode=require

Here, 38081 is the port being used.

To check the port using the dashboard, simply select your service from the dashboard.

Scroll down to the Connect to your service section. Under this section, you'll find the port number where your instance is running.

Conclusion

Finding the port number for your PostgreSQL instance may seem tricky at first, but with the right tools and commands, it can be a straightforward process. Whether you're a Linux, Windows, or macOS user, I shared simple ways to identify the ports your PostgreSQL servers are running on. By following these steps, you can easily manage your databases and troubleshoot any connection issues. Remember, knowing your system’s configuration is key to keeping everything running smoothly. 

If you’re bumping into connection issues with your PostgreSQL database, you may find these resources helpful:

• 5 Common Connection Errors in PostgreSQL and How to Solve Them
• Connecting to PostgreSQL With psql and .pg_service.conf

For more info on PostgreSQL errors, guides to scale your PostgreSQL performance, or best practices, check our Learn PostgreSQL section.