A collection of shell scripts for PostgreSQL database administrator (DBA). Tested on PostgreSQL versions 9.6, 10, 11, 12, 13 under CentOS 7, Debian 10 and Ubuntu Server 20.04.

• Displays PostgreSQL version and status (Master / Replica), hostname and IP address, CPU and Disks load.
• Shows the sizes of the main PostgreSQL directories, archived logs and free disk space, swap usage.
• Displays memory consumption by PostgreSQL processes, statistics on databases, waits and locks, archive and replication statuses.
• When activities occur in PostgreSQL, the progress of operations is displayed: vacuum, vacuum full or cluster, index creation, analyze, pg_basebackup.
• At the end, the last entries of the PostgreSQL log file are displayed.
• For ease of perception, information is displayed in color.

scripts/pg_database_activity_refresh.sh. Fast refresh of the pg_database_activity.sh script every 5 seconds.

The script allows you to quickly find out what the servers are doing and see the exact data for logical replication and external tables.

• Displays server time and lag time, hostname and IP address, PostgreSQL version and status (Master / Replica), data checksums.
• Statistics are also displayed on databases, waits and locks, archive and replication statuses.
• For logical replication, information about publications and subscriptions in the target databases is displayed.
• Displays information about external servers, associated with them by users and tables.
• Blocked sessions and a tree with their locks are displayed, as well as a list of long-running requests.
• When activities occur in PostgreSQL, the progress of operations is displayed: vacuum, vacuum full or cluster, index creation, analyze, pg_basebackup.

• scripts/pg_database_hugepages.sh. Shows free and used memory in the system, Transparent Huge Pages (THP) status, current usage of HugePages and recommended settings for PostgreSQL.
• scripts/pg_database_start.sh. Start PostgreSQL, confirmation is required.
• scripts/pg_database_stop.sh. Stop PostgreSQL, confirmation is required.
• scripts/pg_database_status.sh. PostgreSQL status. Additionally, PostgreSQL processes and replication services are displayed.
• scripts/pg_database_reload_conf.sh. Reloads PostgreSQL configuration files (postgresql.conf, postgresql.auto.conf, pg_hba.conf, pg_ident.conf), displays records related to changes from the log file. If the changed parameter requires a restart, its characteristics are displayed. Operation confirmation is required.
• scripts/pg_database_logs.sh. Shows the PostgreSQL log file with auto-update. The log file is selected automatically.
• scripts/settings.txt. General settings for all scripts. Required before starting work.

As user postgres, download the latest version of the scripts collection (see Releases page):

# sudo su - postgres $ wget https://github.com/Azmodey/pg_dba_scripts/archive/1.10.0.tar.gz 

Extract script files to separate directory (for example ~scripts/) and grant the necessary execution rights:

$ tar xvzf 1.10.0.tar.gz $ mv pg_dba_scripts-1.10.0/scripts ~/scripts $ chmod 700 ~/scripts/*.sh $ chmod 600 ~/scripts/settings.txt 

Modify file settings.txt. Uncomment and correct the entries for your current PostgreSQL version.

# PostgreSQL version PG_VER=12	# Supported PostgreSQL versions: 9.6, 10, 11, 12, 13 # Red Hat / CentOS #PG_BIN=/usr/pgsql-$PG_VER/bin	# Executables directory (by default, do not change!) #PG_DATA=/var/lib/pgsql/$PG_VER/data	# Main data directory #PG_ARC=/var/lib/pgsql/$PG_VER/archive	# Archive logs directory #PG_LOG_DIR=/var/lib/pgsql/$PG_VER/data/log	# Directory for log files. For PostgreSQL 9.6 directory is /pg_log # Debian / Ubuntu #PG_BIN=/usr/lib/postgresql/$PG_VER/bin	# Executables directory (by default, do not change!) #PG_DATA=/var/lib/postgresql/$PG_VER/data	# Main data directory #PG_ARC=/var/lib/postgresql/$PG_VER/archive	# Archive logs directory #PG_LOG_DIR=/var/lib/postgresql/$PG_VER/data/log	# Directory for log files. For PostgreSQL 9.6 directory is /pg_log 

PostgreSQL monitoring script, all information is displayed on one page. Displays PostgreSQL version and status (Master / Replica), hostname and IP address, CPU and Disks load. Shows the sizes of the main PostgreSQL directories, archived logs and free disk space, swap usage. It also displays memory consumption by PostgreSQL processes, statistics on databases, waits and locks, archive and replication statuses. When activities occur in PostgreSQL, the progress of operations is displayed: vacuum, vacuum full or cluster, index creation, analyze, pg_basebackup. At the end, the last entries of the PostgreSQL log file are displayed. For ease of perception, information is displayed in color.

Change the value of the PG_LOG_LINES parameter in the script, which is responsible for displaying the number of last lines of the PosgtreSQL log file.

PG_LOG_LINES=15	# Number of PostgreSQL log lines to display. 0 - disable output 

Running pgbench, 10 backend connections. WALSync wait and Row Exclusive locks. WalSender background processes consumes 19.3% and 24.2% CPU. Total CPU load 6.34 %.

Vacuum full command executing with Access Exclusive lock. Replication lag appeared (total_lag 2721) on streaming and logical replication, archiving are not lagging behind (arc_diff 1). The size of Archive logs increased to 1.3 GB. WalSender background processes consumes 13.2% and 16.5% CPU, overall Disk load 9.73 %. The number of PostgreSQL log file entries has automatically decreased.

Streaming replication to replica server. The server status is highlighted in a separate color, the type of replication (streaming), the slot used (node1_slot) and the lag in replication are shown.

Logical replication to the master PostgreSQL server 13.1. Subscription name (appdbsub) and replication lag (subscription_lag) are shown. Logical replication worker consumes 1.3% CPU.

A script that provides a single tape with the current status of a group of PostgreSQL servers, allowing you to quickly find out what the servers are doing and see the exact data for logical replication and external tables. Server time and lag time, hostname and IP address, PostgreSQL version and status (Master / Replica), data checksums are displayed. It also displays statistics on databases, waits and locks, archive and replication statuses. For logical replication, information about publications and subscriptions in the target databases is displayed. Displays information about external servers, associated users and tables. Blocked sessions and a tree with their locks are displayed, as well as a list of long-running requests. When activities occur in PostgreSQL, the progress of operations is displayed: vacuum, vacuum full or cluster, index creation, analyze, pg_basebackup.

Modify the list of current PostgreSQL hosts in the script, or use the setting only for the local server (localhost).

# Array of PosgtreSQL servers declare -a servers_list=("localhost")	# Local server #declare -a servers_list=("pg_server_1" "pg_server_2" "pg_server_3")	# Servers list, hostnames. Format: "pg_server_1" "pg_server_2" ... 

• All PostgreSQL hosts must be network accessible. Check with ping command (using three servers as an example):

$ ping pg_server_1 $ ping pg_server_2 $ ping pg_server_3 

• The PostgreSQL server must be allowed passwordless user postgres access. To do this, create a ~/.pgpass file from the postgres user with the following content, substituting the current password for "password":

*:*:*:postgres:password 

• Grant the necessary rights to ~/.pgpass file

$ chmod 600 ~/.pgpass 

• Checking the connection to databases of other PostgreSQL servers:

$ psql -h pg_server_1 $ psql -h pg_server_2 $ psql -h pg_server_3 

A cluster of three PostgreSQL servers. The first server (c7postgresql) runs PostgreSQL 12.5 and is the master for the second server. Logical replication is carried out from it, the publication of appdbpub in the appdb database with a dedicated slot is created. It also has access to external data of the third server, foreign server to host 192.168.1.198, appdb2 database, logs_fs.log_timeline table under the postgres user. The session blocking tree is visible when trying to update the same data. The second server (c7postgresql-1) is a replica, streaming replication from the first server is performed to it. There is a time lag of 2 seconds compared to the first server. The third server (c7postgresql-2) runs on PostgreSQL 13.1 and receives logical replication of the appdb database from the first server, the appdbsub subscription is configured. Two sessions with long running queries are displayed.

Shows free and used memory in the system, Transparent Huge Pages (THP) status, current usage of HugePages and recommended settings for PostgreSQL.

Free and used memory in the system: total used free shared buffers cache available Mem: 3.8G 1.3G 2.2G 12M 23M 291M 2.5G Swap: 1.9G 0B 1.9G ----------------------------------- Transparent Huge Pages (THP): On: [always] madvise never Off: always madvise [never] Status: always madvise [never] Tip: disable it ----------------------------------- Current Huge pages: AnonHugePages: 0 kB HugePages_Total: 536 HugePages_Free: 507 HugePages_Rsvd: 382 HugePages_Surp: 0 Hugepagesize: 2048 kB ----------------------------------- Number of Required HugePages: Pid: 976 VmPeak: 1095752 kB Hugepagesize: 2048 kB Set Huge Pages: 535 

Start PostgreSQL, confirmation is required.

Start PostgreSQL (Y/N)? y waiting for server to start....2021-01-04 13:27:18 MSK [45734]: db=,user=,app=,client= LOG: starting PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit 2021-01-04 13:27:18 MSK [45734]: db=,user=,app=,client= LOG: listening on IPv4 address "0.0.0.0", port 5432 2021-01-04 13:27:18 MSK [45734]: db=,user=,app=,client= LOG: could not create IPv6 socket for address "::": Address family not supported by protocol 2021-01-04 13:27:18 MSK [45734]: db=,user=,app=,client= LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2021-01-04 13:27:18 MSK [45734]: db=,user=,app=,client= LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2021-01-04 13:27:18 MSK [45734]: db=,user=,app=,client= LOG: redirecting log output to logging collector process 2021-01-04 13:27:18 MSK [45734]: db=,user=,app=,client= HINT: Future log output will appear in directory "log". done server started 

Stop PostgreSQL, confirmation is required.

Stop PostgreSQL (Y/N)? y waiting for server to shut down.... done server stopped 

PostgreSQL status. Additionally, PostgreSQL processes and replication services are displayed.

PostgreSQL processes: UID PID PPID C STIME TTY TIME CMD postgres 1030 1 0 17:29 ? 00:00:01 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/ postgres 1101 1030 0 17:29 ? 00:00:00 \_ postgres: logger postgres 1105 1030 0 17:29 ? 00:00:00 \_ postgres: checkpointer postgres 1106 1030 0 17:29 ? 00:00:00 \_ postgres: background writer postgres 1107 1030 0 17:29 ? 00:00:00 \_ postgres: walwriter postgres 1108 1030 0 17:29 ? 00:00:00 \_ postgres: autovacuum launcher postgres 1109 1030 0 17:29 ? 00:00:00 \_ postgres: archiver postgres 1110 1030 0 17:29 ? 00:00:00 \_ postgres: stats collector postgres 1111 1030 0 17:29 ? 00:00:00 \_ postgres: logical replication launcher postgres 1375 1030 0 17:30 ? 00:00:00 \_ postgres: walsender rep_user 192.168.1.197(49190) streaming 6/67241080 postgres 10636 1030 0 18:05 ? 00:00:00 \_ postgres: walsender postgres 192.168.1.198(53956) idle PostgreSQL network connection: (Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 1030/postmaster PostgreSQL status: pg_ctl: server is running (PID: 1030) /usr/pgsql-12/bin/postgres "-D" "/var/lib/pgsql/12/data/" PostgreSQL replication service (sender). Works on Master server: UID PID PPID C STIME TTY TIME CMD postgres 1375 1030 0 17:30 ? 00:00:00 postgres: walsender rep_user 192.168.1.197(49190) streaming 6/67241080 postgres 10636 1030 0 18:05 ? 00:00:00 postgres: walsender postgres 192.168.1.198(53956) idle PostgreSQL replication service (receiver). Works on Replica server: UID PID PPID C STIME TTY TIME CMD PostgreSQL logical replication service (worker). Works on Replica server: UID PID PPID C STIME TTY TIME CMD 

Reloads PostgreSQL configuration files (postgresql.conf, postgresql.auto.conf, pg_hba.conf, pg_ident.conf), displays records related to changes from the log file. If the changed parameter requires a restart, its characteristics are displayed. Operation confirmation is required.

Reload PostgreSQL configuration (Y/N)? y server signaled PostgreSQL log: /var/lib/pgsql/12/data/log/postgresql-2021-01.log 2021-01-04 13:29:00 MSK [45734]: db=,user=,app=,client= LOG: received SIGHUP, reloading configuration files 2021-01-04 13:29:00 MSK [45734]: db=,user=,app=,client= LOG: parameter "wal_level" cannot be changed without restarting the server 2021-01-04 13:29:00 MSK [45734]: db=,user=,app=,client= LOG: configuration file "/var/lib/pgsql/12/data/postgresql.conf" contains errors; unaffected changes were applied Pending restart parameters: -[ RECORD 1 ]---+------------------------------------------------- name | wal_level setting | replica unit | category | Write-Ahead Log / Settings short_desc | Set the level of information written to the WAL. extra_desc | context | postmaster vartype | enum source | configuration file min_val | max_val | enumvals | {minimal,replica,logical} boot_val | replica reset_val | replica sourcefile | /var/lib/pgsql/12/data/postgresql.conf sourceline | 194 pending_restart | t 

Shows the PosgreSQL log file with auto-update. The log file is selected automatically.

PostgreSQL log: /var/lib/pgsql/12/data/log/postgresql-2021-01.log 2021-01-04 13:31:27 MSK [47339]: db=,user=,app=,client= LOG: database system was shut down at 2021-01-04 13:31:24 MSK 2021-01-04 13:31:27 MSK [47331]: db=,user=,app=,client= LOG: database system is ready to accept connections 2021-01-04 13:31:29 MSK [47348]: db=[unknown],user=rep_user,app=walreceiver,client=192.168.1.196 LOG: received replication command: IDENTIFY_SYSTEM 2021-01-04 13:31:29 MSK [47348]: db=[unknown],user=rep_user,app=walreceiver,client=192.168.1.196 LOG: received replication command: START_REPLICATION SLOT "node_a_slot" 5/1F000000 TIMELINE 5