PostgreSQL: Finding Server and Client IP Addresses

PostgreSQL: How to Find Your Server IP Address

When working with PostgreSQL, identifying the server's IP address is essential for tasks such as configuring remote connections, debugging, and ensuring proper network configurations.

1. Find the PostgreSQL Server IP

a. Using the Terminal (Linux/Unix)

Check the IP Address:

hostname -I

This command returns the IP address of the machine hosting the PostgreSQL server.

Inspect the PostgreSQL Configuration:

• Open the postgresql.conf file:

sudo nano /etc/postgresql/<version>/main/postgresql.conf

• Look for the listen_addresses parameter. It might be set to:

listen_addresses = 'localhost'

If this is the case, PostgreSQL is only accessible from the local machine.

b. Using SQL Queries

If your PostgreSQL server is configured to allow client IP fetching, you can run this query:

SELECT inet_server_addr();

This will return the server's IP address.

2. Find the Client IP Address

If you need to find the IP address of the client connected to PostgreSQL, use this SQL query:

SELECT inet_client_addr();

The output shows the IP of the client accessing the server.

3. Configuring Remote Access

To allow remote access to your PostgreSQL server:

a. Update postgresql.conf

• Set listen_addresses to either the server's IP address or '*' to allow all addresses.

Example:

listen_addresses = '*'

b. Edit pg_hba.conf

• Add an entry to allow specific IPs or a range:

host    all    all    192.168.1.0/24    md5

c. Restart PostgreSQL

• Apply changes by restarting the service:

sudo systemctl restart postgresql

4. Common Use Cases

All PostgreSQL Questions, Answers, and Code Snippets Collection.