Package:postgresql-common
Verison:273.pgdg24.04+1
Dear Community,
Experimented with a script, pg_findallclusters referenced based on
pg_lsclusters, to detect both system-managed (pg_createcluster) and
manually created (initdb) PostgreSQL clusters. The goal is to improve
visibility in environments where not all clusters are managed by Debian
tools.
Key Features & Use Case:
-
Detects All Running Clusters: Finds both system-managed and manually
created clusters.
-
Assigns Names to Manual Clusters: Uses the data directory name as the
cluster name.
-
Security & Efficiency: Runs only with sudo, minimizing privilege
escalations.
-
Enhanced Output: Displays cluster port, owner, and size, with structured
formatting.
Since manually created clusters lack predefined names, using the data
directory name provides a consistent and meaningful way to reference them
without requiring modifications to PostgreSQL itself.
This is just an experimental approach to explore feasibility. Below is the
patch and an example output.
Patch:
#!/usr/bin/perl -w
# Show both system-managed and manually created PostgreSQL clusters
use strict;
use Getopt::Long;
use PgCommon;
use File::Basename;
sub help {
print "Usage: $0 [-hjs]
Options:
-h --no-header Omit column headers in output
-j --json JSON output
-s --start-conf Include start.conf information in status column
--help Print help\n";
exit 0;
}
# Command-line options
my ($no_header, $json, $start_conf);
help(1) unless GetOptions(
'help' => sub { help(0); },
'h|no-header' => \$no_header,
'j|json' => \$json,
's|start-conf' => \$start_conf,
);
# Detect if user has sudo privileges
my $use_sudo = system("command -v sudo >/dev/null 2>&1 && sudo -n true
>/dev/null 2>&1") == 0 ? 1 : 0;
my $sudo_prefix = $use_sudo ? "sudo " : "";
# Safer command execution with reduced password prompts
sub safe_exec {
my ($cmd, $as_user, $ignore_errors) = @_;
$ignore_errors //= 0;
# Redirect stderr to /dev/null to suppress error messages
$cmd .= " 2>/dev/null" unless $ignore_errors;
if ($as_user) {
# Use runuser instead of sudo to reduce password prompts
if ($use_sudo) {
return qx($sudo_prefix runuser -u $as_user -- sh -c '$cmd' 2>/dev/null);
} else {
return qx(su - $as_user -c '$cmd' 2>/dev/null);
}
} else {
return qx($cmd);
}
}
# Function to get port from config file when cluster is down
sub get_port_from_config {
my ($conf_file) = @_;
return '' unless -f $conf_file;
my $port = '';
if (open(my $fh, '<', $conf_file)) {
while (<$fh>) {
if (/^\s*port\s*=\s*(\d+)/) {
$port = $1;
last;
}
}
close $fh;
}
return $port;
}
# Collect system-managed clusters
my @sys_clusters;
my $jsoninfo = [];
foreach my $v (get_versions()) {
my @clusters = get_version_clusters($v);
foreach my $c (@clusters) {
my %info = cluster_info($v, $c);
my $status = $info{'running'} ? "online" : "down";
unless (-e "${PgCommon::binroot}$v/bin/postgres") {
$status .= ",binaries_missing";
}
my $conf_file = "/etc/postgresql/$v/$c/postgresql.conf";
# Get port - use configuration if not running
my $port;
if ($info{'running'}) {
$port = $info{'port'} // '<unknown>';
} else {
$port = get_port_from_config($conf_file);
}
# Determine Role (Primary/Replica)
my $role = "Unknown";
if ($info{'running'}) {
my $recovery_mode = safe_exec("psql -p $port -tAc \"SELECT
pg_is_in_recovery()\"", "postgres", 1);
chomp($recovery_mode);
$role = ($recovery_mode eq 't') ? "Replica" : "Primary";
} else {
my $standby_file = "$info{'pgdata'}/standby.signal";
$role = (-e $standby_file) ? "Replica" : "Primary";
}
# Get Data Directory Size - Modified to work for both sudo and non-sudo
users
my $size;
if ($info{'owneruid'} == $< || $use_sudo) {
# If current user is the owner or has sudo privileges
$size = safe_exec("$sudo_prefix du -sh $info{'pgdata'} | awk '{print
\$1}'");
chomp($size);
} else {
# Try to get size from psql if the cluster is running
if ($info{'running'} && $port) {
my $db_size = safe_exec("psql -p $port -tAc \"SELECT
pg_size_pretty(pg_database_size('postgres'))\"", "postgres", 1);
chomp($db_size);
if ($db_size) {
$size = "$db_size+"; # Add '+' to indicate this is just the postgres DB size
}
}
}
$size ||= "Unknown";
push @sys_clusters, [$v, $c, $port, $status, (getpwuid
$info{'owneruid'})[0] // 'postgres',
$info{'pgdata'} || '<unknown>', $conf_file, $role, $size];
push @$jsoninfo, \%info;
}
}
# Find PostgreSQL binaries
sub find_pg_binaries {
my @pg_binaries;
# Check standard locations
my @binary_locations = (
glob("/usr/lib/postgresql/*/bin"),
glob("/usr/pgsql-*/bin"),
glob("/usr/local/pgsql/bin"),
glob("/opt/postgresql/*/bin"),
"/usr/bin",
"/usr/local/bin"
);
foreach my $bin_path (@binary_locations) {
if (-e "$bin_path/pg_ctl" && -x "$bin_path/pg_ctl") {
push @pg_binaries, "$bin_path/pg_ctl";
}
}
# Also check in PATH
my $path_pg_ctl = qx(which pg_ctl 2>/dev/null);
chomp($path_pg_ctl);
if ($path_pg_ctl && -x $path_pg_ctl) {
push @pg_binaries, $path_pg_ctl;
}
return @pg_binaries;
}
# Improved function to find only valid PostgreSQL data directories
sub find_postgres_data_dirs {
my @data_dirs;
my %seen;
# Get list of PostgreSQL processes
my $ps_cmd = "ps aux | grep -E 'postgres.*-D' | grep -v grep";
my @running_pg = qx($ps_cmd);
chomp @running_pg;
# Extract data directories from running instances
foreach my $process (@running_pg) {
if ($process =~ /-D\s+(\S+)/) {
my $data_dir = $1;
if (-d $data_dir && -f "$data_dir/PG_VERSION" && !$seen{$data_dir}++) {
push @data_dirs, $data_dir;
}
}
}
# Check network connections more safely
my $netstat_cmd = "ss -tulnp 2>/dev/null | grep postgres";
my @netstat_out = qx($netstat_cmd);
my %seen_ports;
foreach my $line (@netstat_out) {
if ($line =~ /:(\d+)\s/) {
my $port = $1;
next if $seen_ports{$port}++;
# Use runuser to reduce password prompts
my $data_dir = safe_exec("psql -p $port -tAc \"SHOW data_directory\"",
"postgres", 1);
chomp($data_dir);
if ($data_dir && -d $data_dir && -f "$data_dir/PG_VERSION" &&
!$seen{$data_dir}++) {
push @data_dirs, $data_dir;
}
}
}
# Check common paths safely
my @common_paths = (
glob("/var/lib/postgresql/*/main"),
glob("/var/lib/pgsql/*/data"),
glob("/opt/postgres*"),
glob("/opt/pgsql*/data"),
glob("/usr/local/pgsql/data"),
glob("/home/*/postgres*")
);
foreach my $path (@common_paths) {
if (-d $path && -f "$path/PG_VERSION" && !$seen{$path}++) {
push @data_dirs, $path;
}
}
return keys %seen; # Return unique paths
}
# More reliable manual cluster detection
sub get_manual_clusters {
my %ports;
my $output = qx(ss -tulnp | grep postgres 2>/dev/null);
foreach my $line (split /\n/, $output) {
if ($line =~ /:(\d+)\s/) {
my $port = $1;
my $data_dir = safe_exec("psql -p $port -tAc \"SHOW data_directory\"",
"postgres", 1);
chomp($data_dir);
$ports{$data_dir} = $port if $data_dir;
}
}
my @manual_clusters;
my %seen;
# Filter out system-managed clusters
foreach my $sys_cluster (@sys_clusters) {
$seen{$sys_cluster->[5]} = 1;
}
# Auto-detect PostgreSQL data directories
my @cluster_dirs = find_postgres_data_dirs();
foreach my $data_dir (@cluster_dirs) {
next if $seen{$data_dir}; # Skip if already processed
next unless -d $data_dir;
next unless -f "$data_dir/PG_VERSION"; # Must be a PostgreSQL data directory
$seen{$data_dir} = 1;
my $status = exists $ports{$data_dir} ? "online" : "down";
my $conf_file = "$data_dir/postgresql.conf";
# Get port - if down, try to read from config
my $port;
if ($status eq "online") {
$port = $ports{$data_dir} // '';
} else {
$port = get_port_from_config($conf_file);
}
# Get PostgreSQL version
my $major_version = "unknown";
if ($status eq "online") {
my $version_output = safe_exec("psql -p $port -tAc \"SHOW
server_version\"", "postgres", 1);
chomp($version_output);
($major_version) = $version_output =~ /^(\d+)/;
} else {
my $pg_version_file = "$data_dir/PG_VERSION";
if (-e $pg_version_file) {
my $file_version = qx(cat $pg_version_file);
chomp($file_version);
($major_version) = $file_version =~ /^(\d+)/;
}
}
# Extract cluster name from data directory
my $cluster_name = basename($data_dir);
my $owner = qx(stat -c '%U' "$data_dir" 2>/dev/null);
chomp($owner);
$owner ||= 'postgres';
# Determine Role
my $role = "Unknown";
if ($status eq "online") {
my $recovery_mode = safe_exec("psql -p $port -tAc \"SELECT
pg_is_in_recovery()\"", "postgres", 1);
chomp($recovery_mode);
$role = ($recovery_mode eq 't') ? "Replica" : "Primary";
} else {
my $standby_file = "$data_dir/standby.signal";
$role = (-e $standby_file) ? "Replica" : "Primary";
}
# Get Data Directory Size with reduced interaction
my $size;
my $uid = (stat($data_dir))[4];
if ($uid == $< || $use_sudo) {
$size = safe_exec("$sudo_prefix du -sh $data_dir | awk '{print \$1}'");
chomp($size);
} else {
$size = "Unknown";
}
push @manual_clusters, [$major_version, $cluster_name, $port, $status,
$owner, $data_dir, $conf_file, $role, $size];
}
return @manual_clusters;
}
# Prepare output using column command
sub format_output {
my ($title, $clusters) = @_;
# Create temporary files
my $header_file = "/tmp/pg_header_$$";
my $data_file = "/tmp/pg_data_$$";
# Define header
open(my $header_fh, '>', $header_file) or die "Cannot create temp file: $!";
print $header_fh
"Ver\tCluster\tPort\tStatus\tOwner\tData_Directory\tConfig_File\tRole\tSize\n";
close $header_fh;
# Write data
open(my $data_fh, '>', $data_file) or die "Cannot create temp file: $!";
foreach my $line (@$clusters) {
print $data_fh join("\t", @$line) . "\n";
}
close $data_fh;
# Print title
print "\n\033[1;34m$title\033[0m\n";
# Format using column
my $column_cmd = "cat $header_file $data_file | column -t -s '\t'";
my @formatted = qx($column_cmd);
# Print header
print "\033[1m$formatted[0]\033[0m";
print "-" x length($formatted[0]) . "\n";
# Print data with colors
for (my $i = 1; $i < scalar(@formatted); $i++) {
my @fields = split(/\s{2,}/, $formatted[$i]);
my $status = $fields[3];
my $color = ($status =~ /^online/) ? "\033[32m" : "\033[31m";
print "${color}$formatted[$i]\033[0m";
}
# Clean up
unlink $header_file;
unlink $data_file;
}
# Collect manual clusters first
my @manual_clusters = get_manual_clusters();
# Format and display clusters
format_output("Debian PostgreSQL Clusters (pg_createcluster)",
\@sys_clusters);
format_output("Manually Created Clusters (initdb)", \@manual_clusters);
Output:
[image: image.png]
