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.
scripts/pg_database_activity.sh. 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.
- 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.
scripts/pg_database_information.sh. PostgreSQL information script, single tape with the current status of a group of PostgreSQL servers.
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