Multiple Methods to Find Hostname and Port Number in PostgreSQL

Introduction

In PostgreSQL database management, obtaining the server's hostname and port number is a common requirement, especially when configuring application connections or performing network debugging. Based on Q&A data and reference articles, this article systematically organizes multiple lookup methods and provides explanations with practical examples. By default, PostgreSQL uses localhost and port 5432, but this may vary in actual environments.

Using the \conninfo Meta-command

psql provides the \conninfo meta-command, which directly displays basic information about the current connection, including the hostname and port number. Execute it at the psql prompt:

\conninfo

Example output:

You are connected to database "mydb" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

This command is concise and straightforward, suitable for quickly checking connection parameters without writing SQL queries.

Querying the pg_settings System View

PostgreSQL's pg_settings view stores server configuration parameters. The port number can be retrieved via an SQL query:

SELECT * FROM pg_settings WHERE name = 'port';

The query result includes the port number and related settings, for example:

 name  | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart
-------+---------+------+----------+------------+------------+---------+---------+--------+---------+---------+----------+-----------+-----------+------------+------------+-----------------
 port  | 5432    |      | Connects | Sets the TCP port the server listens on. | | postmaster | integer | default | 1       | 65535   | | 5432      | 5432      | | | f

This method is suitable for scenarios where the port number needs to be obtained programmatically, such as embedding queries in Perl or other language applications.

Using Built-in Network Functions

PostgreSQL provides the inet_server_addr() and inet_server_port() functions to retrieve the server IP address and port number, respectively:

SELECT inet_server_addr() AS hostname, inet_server_port() AS portnumber;

Example output:

 hostname | portnumber
----------+------------
 ::1      |       5432

Here, ::1 represents the IPv6 loopback address, equivalent to 127.0.0.1 in IPv4. These functions return real-time server information for the current connection, making them ideal for dynamic queries.

Inspecting the postgresql.conf Configuration File

The server's core configuration is stored in the postgresql.conf file. Follow these steps to locate it:

1. First, obtain the configuration file path:

   SHOW config_file;

   Example output:

                  config_file
   -------------------------------------------
    /etc/postgresql/15/main/postgresql.conf

2. Open the file and look for the "CONNECTIONS AND AUTHENTICATION" section to locate the port and listen_addresses parameters:

   #listen_addresses = 'localhost' # Hostname setting
   port = 5432                         # Port number setting

This method is useful for viewing static server configurations, but note that runtime parameters may be dynamically modified.

Supplementary Method: System Tool Query

On Linux systems, the netstat command can be used to check the ports listened to by the PostgreSQL process:

sudo netstat -plunt | grep postgres

Example output:

tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      140/postgres
tcp6       0      0 ::1:5432                :::*                    LISTEN      140/postgres

This method shows the IP addresses and ports the process is listening on, suitable for local server checks.

Method Comparison and Application Scenarios

Different methods have their own advantages and disadvantages:

• \conninfo: Fastest, ideal for interactive use.
• pg_settings query: Can be called programmatically, suitable for automation scripts.
• Built-in functions: Return real-time connection information with high accuracy.
• Configuration file inspection: Views default settings but may not reflect runtime status.
• System tools: Applicable for network-layer verification.

In practice, it is recommended to choose the appropriate method based on the requirement. For example, when configuring a Perl application connection, prioritize using \conninfo or SQL queries to obtain parameters.

Conclusion

This article has detailed multiple methods to find the hostname and port number in PostgreSQL, covering aspects from meta-commands to system queries. Through code examples and step-by-step instructions, it helps users quickly master these practical techniques. Correctly obtaining these parameters is crucial for database connection management and troubleshooting, and it is advised to flexibly apply the described methods in real-world environments.