Package:postgresql-common
Verison:273.pgdg24.04+1
*Dear Community,*
I have been exploring an issue where pg_lsclusters reports the default port
from postgresql.conf, rather than the actual port in use when a PostgreSQL
cluster is started with a custom port using pg_ctlcluster (e.g., -o "-p <
port>").
While this behavior is understandable given permission constraints on
postmaster.pid, it makes tracking running clusters more difficult. To
address this, I have created a *workaround script* that accurately detects
running PostgreSQL instances, retrieves their actual ports, and presents a
corrected cluster listing.
This ensures that administrators can see the correct ports in use, even
when pg_lsclusters does not reflect them. I would appreciate any guidance
on how we might resolve this as a permanent improvement within pg_ctl or
pg_lsclusters.
Please find the workaround patch below
Looking forward to your thoughts and suggestions.
Best regards,
Akash
#!/usr/bin/perl -w
use strict;
use warnings;
# ANSI color codes for output
my $WHITE = "\033[37m";
my $GREEN = "\033[32m";
my $RED = "\033[31m";
my $RESET = "\033[0m";
# Check if user has sudo privileges
my $HAS_SUDO = `sudo -n true 2>/dev/null && echo "yes" || echo "no"`;
chomp($HAS_SUDO);
$HAS_SUDO = $HAS_SUDO eq "yes";
# Fetch all running PostgreSQL processes once
my %running_ports;
if ($HAS_SUDO) {
my $output = `sudo ss -tulnp 2>/dev/null | grep postgres`;
foreach my $line (split /\n/, $output) {
if ($line =~ /:(\d+)\s/) {
$running_ports{$1} = 1;
}
}
}
# Function to get port from postmaster.pid file
sub get_port_from_pid_file {
my ($data_dir) = @_;
my $pid_file = "$data_dir/postmaster.pid";
return undef unless -r $pid_file;
my $port = `head -n 4 $pid_file | tail -n 1 2>/dev/null`;
chomp($port);
return $port if $port =~ /^\d+$/;
return undef;
}
# Function to get cluster owner
sub get_cluster_owner {
my ($port) = @_;
return "<unknown>" unless $HAS_SUDO;
# Try to get owner from process first
my $output = `sudo lsof -i :$port -n -P 2>/dev/null | grep LISTEN | awk
'{print \$3}' | head -1 2>/dev/null`;
chomp($output);
# If we couldn't get the owner from the process, it might be because of sudo
# In that case, we check the postgres process owner
if (!$output || $output eq "") {
$output = `sudo ps -p \$(sudo lsof -i :$port -t 2>/dev/null | head -1) -o
user= 2>/dev/null`;
chomp($output);
}
return $output || "<unknown>";
}
# Function to get PostgreSQL version
sub get_postgres_version {
my ($port) = @_;
return "<unknown>" unless $HAS_SUDO;
my $version = `sudo -u postgres psql -p $port -t -c "SHOW server_version;"
2>/dev/null`;
chomp($version);
$version =~ s/^\s+|\s+$//g;
$version =~ s/^(\d+).*/$1/;
return $version || "<unknown>";
}
# Function to check cluster status
sub is_cluster_running {
my ($port) = @_;
return exists $running_ports{$port} ? "online" : "down";
}
# Function to get data directory
sub get_data_directory {
my ($port) = @_;
return "<unknown>" unless $HAS_SUDO;
my $pid = `sudo lsof -i :$port -t 2>/dev/null`;
chomp($pid);
return "<unknown>" unless $pid;
my $dir = `sudo lsof -p $pid 2>/dev/null | grep 'cwd' | awk '{print \$9}' |
tail -n 1 2>/dev/null`;
chomp($dir);
return $dir || "<unknown>";
}
# Function to get configured port from postgresql.conf
sub get_configured_port {
my ($version, $cluster_name) = @_;
return "<unknown>" unless $HAS_SUDO;
my $config_file = "/etc/postgresql/$version/$cluster_name/postgresql.conf";
my $port = `sudo grep -E '^port\\s*=\\s*[0-9]+' $config_file 2>/dev/null |
awk -F '=' '{print \$2}' | tr -d ' ' | tr -d '#'`;
chomp($port);
$port =~ s/\(changerequiresrestart\)//g;
return $port || "<unknown>";
}
# Function to list all PostgreSQL clusters (including stopped ones)
sub get_all_clusters {
my %clusters;
my @version_paths = glob("/var/lib/postgresql/*");
foreach my $version_path (@version_paths) {
if ($version_path =~ /(\d+)$/) {
my $version = $1;
my @cluster_dirs = glob("$version_path/*");
foreach my $dir (@cluster_dirs) {
if ($dir =~ /([^\/]+)$/) {
my $cluster_name = $1;
my $config_port = get_configured_port($version, $cluster_name);
my $config_file = "/etc/postgresql/$version/$cluster_name/postgresql.conf";
my $pid_port = get_port_from_pid_file($dir);
$clusters{"$version:$cluster_name"} = {
version => $version,
cluster_name => $cluster_name,
config_port => $config_port,
config_file => $config_file,
data_dir => $dir,
port => $pid_port || $config_port,
status => $pid_port ? "online" : "down",
owner => "<unknown>"
};
}
}
}
}
return %clusters;
}
# Get all known clusters
my %all_clusters = get_all_clusters();
# Collect running PostgreSQL instances (only for clusters without pid file
info)
foreach my $port (keys %running_ports) {
my $owner = get_cluster_owner($port);
my $data_dir = get_data_directory($port);
my $version = get_postgres_version($port);
my $status = is_cluster_running($port);
next unless $data_dir =~ m{^/var/lib/postgresql/\d+/};
my ($cluster_name) = $data_dir =~ m{^/var/lib/postgresql/\d+/([^/]+)};
$cluster_name ||= "main";
my $key = "$version:$cluster_name";
if (exists $all_clusters{$key}) {
if ($all_clusters{$key}{status} eq "down") {
$all_clusters{$key}{port} = $port;
$all_clusters{$key}{status} = $status;
$all_clusters{$key}{owner} = $owner;
} else {
# Update owner for running clusters
$all_clusters{$key}{owner} = $owner;
}
} else {
$all_clusters{$key} = {
version => $version,
cluster_name => $cluster_name,
config_port => "<unknown>",
config_file => "<unknown>",
data_dir => $data_dir,
port => $port,
status => $status,
owner => $owner
};
}
}
# Update ownership info for all online clusters
foreach my $key (keys %all_clusters) {
my $cluster = $all_clusters{$key};
if ($cluster->{status} eq "online" && $cluster->{owner} eq "<unknown>") {
$cluster->{owner} = get_cluster_owner($cluster->{port});
}
}
# Update owner for stopped clusters
foreach my $key (keys %all_clusters) {
my $cluster = $all_clusters{$key};
if ($cluster->{status} eq "down" && $cluster->{owner} eq "<unknown>") {
my $data_dir = $cluster->{data_dir};
if (-d $data_dir) {
my $owner = `ls -ld $data_dir 2>/dev/null | awk '{print \$3}'`;
chomp($owner);
$cluster->{owner} = $owner if $owner;
}
}
}
# Prepare output data
my $output = "Ver Cluster Running-Port Config-Port Status Owner
Data-Directory Config-File\n";
foreach my $key (sort keys %all_clusters) {
my $cluster = $all_clusters{$key};
my $color = ($cluster->{status} eq "online") ? $GREEN : $RED;
$output .= $color . join(" ", @{$cluster}{qw(version cluster_name port
config_port status owner data_dir config_file)}) . "${RESET}\n";
}
# Output with proper alignment
if (!keys %all_clusters) {
print "No PostgreSQL clusters detected.\n";
} else {
if (open(my $pipe, "| column -t")) {
print $pipe $output;
close($pipe);
} else {
print $output;
}
}