How to reload PostgreSQL configuration

Today, I will describe how to cause PostgreSQL clusters to reload their configuration files using four simple methods.

SIGHUP signal

Every other method is based on it, so it is the most important one.

Use pg_lsclusters command to list available PostgreSQL clusters.

$ sudo pg_lsclusters
Ver Cluster   Port Status Owner    Data directory                    Log file
9.4 main      5432 online postgres /var/lib/postgresql/9.4/main      /var/log/postgresql/postgresql-9.4-main.log
9.4 secondary 5433 online postgres /var/lib/postgresql/9.4/secondary /var/log/postgresql/postgresql-9.4-secondary.log

Use pg_ctlcluster to print status of the specific PostgreSQL cluster.

$ sudo pg_ctlcluster 9.4 secondary status
pg_ctl: server is running (PID: 1590)
/usr/lib/postgresql/9.4/bin/postgres "-D" "/var/lib/postgresql/9.4/secondary" "-c" "config_file=/etc/postgresql/9.4/secondary/postgresql.conf"

Use kill command to send SIGHUP signal to the specific PostgreSQL cluster.

$ sudo kill -SIGHUP 1590

Use parallel utility to inform every PostgreSQL cluster to reload its configuration.

$ pg_lsclusters -h | \
   tr -s " " | \
   cut -d " " -f 1-2 | \
   parallel --colsep " " sudo pg_ctlcluster {1} {2} status | \
   sed -n -e '/PID/ {s|pg_ctl: server is running (PID: \([0-9]*\))|\1|;p}' | \
   sudo parallel kill -SIGHUP
Read more about GNU parallel application which can often be used as a substitute for xargs utility.

Systemd system and service manager

Use systemctl utility to inform every PostgreSQL cluster to reload its configuration.

$ sudo systemctl reload postgresql

You cannot define specific PostgreSQL cluster using this method.

PostgreSQL utilities

Use pg_lsclusters command to list available PostgreSQL clusters.

$ sudo pg_lsclusters
Ver Cluster   Port Status Owner    Data directory                    Log file
9.4 main      5432 online postgres /var/lib/postgresql/9.4/main      /var/log/postgresql/postgresql-9.4-main.log
9.4 secondary 5433 online postgres /var/lib/postgresql/9.4/secondary /var/log/postgresql/postgresql-9.4-secondary.log

Use version and cluster name to reload configuration on specific PostgreSQL cluster.

$ sudo pg_ctlcluster 9.4 secondary reload

Use parallel utility to inform every PostgreSQL cluster to reload its configuration.

$ pg_lsclusters -h | \
  tr -s " " | \
  cut -d " " -f 1-2 | \
  parallel --colsep " " sudo pg_ctlcluster {1} {2} reload

Server signaling functions

Use parallel utility and pg_reload_conf() function to inform every PostgreSQL cluster to reload its configuration.

$ pg_lsclusters -h | \
  tr -s " " | \
  cut  -d " " -f 3 | \
  sudo -u postgres parallel psql -p {1} postgres -c \'select pg_reload_conf\(\)\'

References

PostgreSQL 9.4.5 Documentation - System Catalogs - pg_settings

PostgreSQL 9.4.5 Documentation - pg_ctl

PostgreSQL 9.4.5 Documentation - Functions and Operators - Server Signaling Functions

GNU Parallel Tutorial

Milosz Galazka's Picture

About Milosz Galazka

Milosz is a Linux Foundation Certified Engineer working for a successful Polish company as a system administrator and a long time supporter of Free Software Foundation and Debian operating system. He is also open for new opportunities and challenges.

Gdansk, Poland https://sleeplessbeastie.eu