132 lines
7.0 KiB
Bash
Executable File
132 lines
7.0 KiB
Bash
Executable File
#!/bin/bash
|
|
#==============================================================#
|
|
# File : pg-alias.sh
|
|
# Desc : shell script to manage postgres cluster
|
|
# Path : /etc/profile.d/pg-alias.sh
|
|
# Mtime : 2025-12-26
|
|
# License : Apache-2.0 @ https://pigsty.io/docs/about/license/
|
|
# Copyright : 2018-2026 Ruohang Feng / Vonng (rh@vonng.com)
|
|
#==============================================================#
|
|
|
|
#--------------------------------------------------------------#
|
|
# patroni & postgres (pg)
|
|
#--------------------------------------------------------------#
|
|
alias pt-start='sudo systemctl start patroni'
|
|
alias pt-stop='sudo systemctl stop patroni'
|
|
alias pt-reload='sudo systemctl reload patroni'
|
|
alias pt-restart='sudo systemctl restart patroni'
|
|
alias pg-s='ps -fp $(pgrep -u postgres); systemctl status patroni; pg list'
|
|
alias pg-start='pg_ctl -D {{ pg_data|default('/pg/data') }} start'
|
|
alias pg-stop='pg_ctl -D {{ pg_data|default('/pg/data') }} stop'
|
|
alias pg-restart='pg_ctl -D {{ pg_data|default('/pg/data') }} restart'
|
|
alias pg-reload='pg_ctl -D {{ pg_data|default('/pg/data') }} reload'
|
|
alias pg-promote='pg_ctl -D {{ pg_data|default('/pg/data') }} promote'
|
|
function pg-promote(){
|
|
rm -rf {{ pg_data|default('/pg/data') }}/standby.signal;
|
|
pg_ctl -D {{ pg_data|default('/pg/data') }} promote;
|
|
}
|
|
function pg-demote(){
|
|
touch {{ pg_data|default('/pg/data') }}/standby.signal;
|
|
chown postgres:postgres {{ pg_data|default('/pg/data') }}/standby.signal;
|
|
psql postgres -c 'CHECKPOINT;';
|
|
pg_ctl -D {{ pg_data|default('/pg/data') }} restart;
|
|
}
|
|
alias pg-up='sudo systemctl start patroni'
|
|
alias pg-dw='sudo systemctl stop patroni'
|
|
alias pg-rc='sudo systemctl reload patroni'
|
|
alias pg-rb='sudo systemctl restart patroni'
|
|
alias pg-st='systemctl status patroni; systemctl status pgbouncer; systemctl status pg_exporter; '
|
|
alias pg-ps='ps -fp $(pgrep -u postgres)'
|
|
alias pg-cf="cat {{ pg_data|default('/pg/data') }}/patroni.dynamic.json | jq"
|
|
alias pg-d="cd {{ pg_data|default('/pg/data') }}"
|
|
alias pg-c="vi {{ pg_data|default('/pg/data') }}/postgresql.conf"
|
|
alias pg-h="vi {{ pg_data|default('/pg/data') }}/pg_hba.conf"
|
|
alias pg-db="oid2name | grep -v postgres | grep -v template"
|
|
alias pg-md=" sed 's/+/|/g' | sed 's/^/|/' | sed 's/$/|/' | grep -v rows | grep -v '||'"
|
|
alias pg-ts='psql -qAXtwc "SELECT CURRENT_TIMESTAMP;"'
|
|
alias pg-r="psql -qAXtwc \"SELECT CASE pg_is_in_recovery() WHEN TRUE THEN 'replica' ELSE 'primary' END\";"
|
|
alias pg-repl='psql -qXxwc "TABLE pg_stat_replication;"'
|
|
alias pg-recv='psql -qXxwc "TABLE pg_stat_wal_receiver;"'
|
|
alias pg-mt="curl -sL localhost:9630/metrics | grep -v '#' | grep pg_"
|
|
alias pg-lsn='psql -qAXtwc "SELECT CASE WHEN pg_is_in_recovery() THEN pg_last_wal_replay_lsn() ELSE pg_current_wal_lsn() END AS lsn;"'
|
|
alias pg-ll='while true; do psql -qAXtwc "SELECT NOW(), CASE WHEN pg_is_in_recovery() THEN pg_last_wal_replay_lsn() ELSE pg_current_wal_lsn() END AS lsn;"; sleep 0.2; done'
|
|
alias pg-wal='psql -qAXtwc "SELECT pg_walfile_name(pg_current_wal_lsn()) AS wal;"'
|
|
alias pg-kill="psql -wc \"SELECT pg_terminate_backend(pid) AS killed, NOW()-backend_start AS time,datname, usename, application_name AS appname, client_addr,state FROM pg_stat_activity WHERE backend_type = 'client backend' and pid <> pg_backend_pid();\""
|
|
alias pg-kk="while true; do psql -wc \"SELECT pg_terminate_backend(pid) AS killed, NOW()-backend_start AS time,datname, usename, application_name AS appname, client_addr,state FROM pg_stat_activity WHERE backend_type = 'client backend' and application_name != 'pg_exporter' and pid <> pg_backend_pid();\"; sleep 0.1; done"
|
|
alias pg-cancel="psql -wc \"SELECT pg_cancel_backend(pid) AS canceled, NOW()-state_change AS time, datname, usename, application_name AS appname, client_addr,state FROM pg_stat_activity WHERE backend_type = 'client backend' and pid <> pg_backend_pid();\""
|
|
alias pg-cc="while true; do psql -wc \"SELECT pg_cancel_backend(pid) AS canceled, NOW()-state_change AS time, datname, usename, application_name AS appname, client_addr,state FROM pg_stat_activity WHERE backend_type = 'client backend' and pid <> pg_backend_pid();\"; sleep 0.1; done"
|
|
|
|
function pg() {
|
|
local patroni_conf="/infra/conf/patronictl.yml"
|
|
if [ ! -r ${patroni_conf} ]; then
|
|
patroni_conf="/etc/patroni/patroni.yml"
|
|
if [ ! -r ${patroni_conf} ]; then
|
|
echo "error: patronictl config not found"
|
|
return 1
|
|
fi
|
|
fi
|
|
patronictl -c ${patroni_conf} "$@"
|
|
}
|
|
|
|
#--------------------------------------------------------------#
|
|
# pgbouncer (pgb)
|
|
#--------------------------------------------------------------#
|
|
alias pgb='psql -p6432 -dpgbouncer'
|
|
alias pgb-st='systemctl status pgbouncer'
|
|
alias pgb-ps='ps aux | grep pgbouncer'
|
|
alias pgb-start='sudo systemctl start pgbouncer'
|
|
alias pgb-stop='sudo systemctl stop pgbouncer'
|
|
alias pgb-restart='sudo systemctl restart pgbouncer'
|
|
alias pgb-reload='sudo systemctl reload pgbouncer'
|
|
alias pgb-new='/usr/bin/pgbouncer -d -R /etc/pgbouncer/pgbouncer.ini'
|
|
alias pgb-stat='psql -p6432 -dpgbouncer -xc "SHOW STATS;"'
|
|
alias pgb-pool='psql -p6432 -dpgbouncer -xc "SHOW POOLS;"'
|
|
alias pgb-dir="cd /etc/pgbouncer"
|
|
alias pgb-conf="cat /etc/pgbouncer/pgbouncer.ini"
|
|
alias pgb-hba="cat /etc/pgbouncer/pgb_hba.conf"
|
|
alias pgb-user="cat /etc/pgbouncer/database.txt"
|
|
alias pgb-db="cat /etc/pgbouncer/userlist.txt"
|
|
alias pgb-mt="curl -sL http://localhost:9631/metrics | grep -v '#' | grep pgbouncer_"
|
|
|
|
# route pgbouncer traffic to another cluster member
|
|
function pgb-route(){
|
|
local ip=${1-'\/var\/run\/postgresql'}
|
|
sed -ie "s/host=[^[:space:]]\+/host=${ip}/g" /etc/pgbouncer/pgbouncer.ini
|
|
cat /etc/pgbouncer/pgbouncer.ini
|
|
}
|
|
|
|
#--------------------------------------------------------------#
|
|
# pgbackrest (pb)
|
|
#--------------------------------------------------------------#
|
|
alias pb-mt="curl -sL localhost:9854/metrics | grep -v '#' | grep pgbackrest"
|
|
function pb() {
|
|
local stanza=$(grep -o '\[[^][]*]' /etc/pgbackrest/pgbackrest.conf | head -n1 | sed 's/.*\[\([^]]*\)].*/\1/')
|
|
pgbackrest --stanza=$stanza "$@"
|
|
}
|
|
function pb-init() {
|
|
local stanza=$(grep -o '\[[^][]*]' /etc/pgbackrest/pgbackrest.conf | head -n1 | sed 's/.*\[\([^]]*\)].*/\1/')
|
|
pgbackrest --stanza=${stanza} --no-online stanza-create
|
|
}
|
|
|
|
|
|
|
|
#--------------------------------------------------------------#
|
|
# log
|
|
#--------------------------------------------------------------#
|
|
function pg-log() {
|
|
local logdir=$(grep 'log_directory' {{ pg_data|default('/pg/data') }}/postgresql.conf | awk '{print $3}' | tr -d "'")
|
|
[[ -f "${logdir}/postgresql-$(date '+%Y-%m-%d').csv" ]] && tail -f "${logdir}/postgresql-$(date '+%Y-%m-%d').csv"
|
|
}
|
|
function pt-log() {
|
|
local logdir=$(grep -A2 'log:' /etc/patroni/patroni.yml | head -n3 | awk '/dir:/ {print $2}')
|
|
[[ -f "${logdir}/patroni.log" ]] && tail -f "${logdir}/patroni.log"
|
|
}
|
|
function pgb-log() {
|
|
local logfile=$(grep -A2 'logfile' /etc/pgbouncer/pgbouncer.ini | awk '/logfile/ {print $3}')
|
|
[[ -f ${logfile} ]] && tail -f "${logfile}"
|
|
}
|
|
function pbr-log() {
|
|
local logdir=$(grep 'log-path' /etc/pgbackrest/pgbackrest.conf | sed 's/log-path=//g')
|
|
[[ -d "${logdir}" ]] && tail -f ${logdir}/*.log
|
|
}
|