pg_stat_tcpinfo

Overview

Linux >=4.18; procfs and NETLINK_INET_DIAG required. Monitoring roles can see all server-OS-user sockets. No preload or upstream 1.0-to-1.1 upgrade script. PG14-18; PGDG remains principal supplier; PGSTY 1.1 batch supplements published.

Version

Build

You can build the RPM / DEB packages for pg_stat_tcpinfo using pig build:

pig build pkg pg_stat_tcpinfo         # build RPM / DEB packages

Install

You can install pg_stat_tcpinfo directly. First, make sure the PGDG repository is added and enabled:

pig repo add pgdg -u          # Add PGDG repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install pg_stat_tcpinfo;          # Install for current active PG version

pig ext install -y pg_stat_tcpinfo -v 18  # PG 18
pig ext install -y pg_stat_tcpinfo -v 17  # PG 17
pig ext install -y pg_stat_tcpinfo -v 16  # PG 16
pig ext install -y pg_stat_tcpinfo -v 15  # PG 15
pig ext install -y pg_stat_tcpinfo -v 14  # PG 14

dnf install -y pg_stat_tcpinfo_18       # PG 18
dnf install -y pg_stat_tcpinfo_17       # PG 17
dnf install -y pg_stat_tcpinfo_16       # PG 16
dnf install -y pg_stat_tcpinfo_15       # PG 15
dnf install -y pg_stat_tcpinfo_14       # PG 14

apt install -y postgresql-18-pg-stat-tcpinfo   # PG 18
apt install -y postgresql-17-pg-stat-tcpinfo   # PG 17
apt install -y postgresql-16-pg-stat-tcpinfo   # PG 16
apt install -y postgresql-15-pg-stat-tcpinfo   # PG 15
apt install -y postgresql-14-pg-stat-tcpinfo   # PG 14

Create Extension:

CREATE EXTENSION pg_stat_tcpinfo;

Usage

Sources:

• README
• Installation SQL
• Extension control
• Release 1.1
• License

pg_stat_tcpinfo 1.1 exposes Linux TCP socket statistics through SQL. It helps investigate slow client connections, replication links and foreign-server traffic using round-trip time, retransmissions, congestion control and queue sizes, without shell access to the host.

It requires Linux 4.18 or later, access to /proc, and the NETLINK_INET_DIAG interface. Install as a superuser; no preload or server restart is required.

Core Workflow

CREATE EXTENSION pg_stat_tcpinfo;

SELECT a.pid, a.usename, a.application_name,
       t.dst_addr, t.dst_port, t.state, t.recvq, t.sendq,
       t.tcpinfo->>'rtt' AS rtt_ms,
       t.tcpinfo->>'total_retrans' AS retransmissions,
       t.tcpinfo->>'congestion' AS congestion_control
FROM pg_stat_activity a
JOIN pg_stat_tcpinfo t ON t.pid = a.pid
WHERE t.state = 'ESTABLISHED';

Objects and Metrics

The pg_stat_tcpinfo view calls pg_stat_get_tcpinfo(). Each row contains a process and OS user ID, local and remote addresses and ports, connection state, receive and send queues, and a tcpinfo JSONB object. IPv4 and IPv6 sockets are supported. The rtt field is expressed in milliseconds; total_retrans is the cumulative retransmission counter.

The view includes every TCP socket owned by the server’s OS user, including sockets from other database clusters or programs running under that account. Joining on the backend PID narrows the result to connections visible in the current cluster. Unix-domain connections are not TCP sockets and do not appear; TIME-WAIT sockets have no owner and are omitted. Metrics unavailable on an older kernel may be null.

Permissions

The installation revokes public execution of the function and grants function and view access to pg_read_all_stats; pg_monitor inherits that access. Ordinary application roles cannot read the metrics by default. Additional grants expose all sockets owned by the server OS user, not only the caller’s database connections.

Operational Boundaries

Each call scans process descriptors and queries the kernel; it is an on-demand observation, not a historical sampler. Container PID and network namespaces and procfs restrictions determine which sockets and PIDs are visible. Choose a sampling interval appropriate to the number of processes and connections.

Use a package built for the server’s PostgreSQL major version. The 1.1 release includes fresh-install SQL for versions 1.0 and 1.1 but no upgrade script between them, so an existing 1.0 installation has no upstream-provided ALTER EXTENSION pg_stat_tcpinfo UPDATE path.