PostgreSQL Tools

Postgres has few tools that help to query or do maintenance tasks:

1. psql  

2. pg_ctl

3. pg_lsclusters

4. pg_ctlcluster

Psql is an terminal based front-end interface for PostgreSQL for querying anything about Postgres server and its databases. Few examples are here.

psql commands

• \?: show all psql commands.
• \h sql-command: show syntax on SQL command.
• \c dbname [username]: Connect to database, with an optional username (or \connect).
• Display Commands: You can append + to show more details.
  • \l: List all database (or \list).
  • \d: Display all tables, indexes, views, and sequences.
  • \dt: Display all tables.
  • \di: Display all indexes.
  • \dv: Display all views.
  • \ds: Display all sequences.
  • \dT: Display all types.
  • \dS: Display all system tables.
  • \du: Display all users.
• \x auto|on|off: Toggle|On|Off expanded output mode.

For automations this below may be helpful as this is executed from Linux command line and the output can be redirected to a file.

psql <<EOF
\x
SELECT * FROM <schema>.<table>;
EOF

Other simple way to execute a statement through psql from command prompt. 

psql -c '\x' -c 'SELECT * FROM <schema>.<table>;'

or

echo '\x \\ SELECT * FROM <schema>.<table>;' | psql

For more details on psql options, please refer to :

https://www.postgresql.org/docs/current/app-psql.html

pg_ctl commands:

How to restart using pg_ctl, assuming you have $PGDATA set :

pg_ctl restart -D $PGDATA

Hint: $PGDATA points to the directory where your database files are location including postmaster.pid 

pg_ctl status -D $PGDATA

pg_lsclusters commands:

This command is used to find the name of the cluster and other details about the postgres server

$  pg_lsclusters

Ver Cluster Port Status Owner    Data directory Log file

13  main    5432 online postgres /postgres      /var/log/postgresql/postgresql-13-main.log

$pg_lsclusters -j    << gives json output with all details of this server>>

The following is a sample program written in python for getting the extensive details in readable format to automate some of your stuff in managing the postgres monitoring.

$ pg_lsclusters -j > postgres_details.json

$ vi mypostgres_details.py

#/usr/bin/python
import json

pgres_json = open("postgres_details.json","r+")

data = json.load(pgres_json)

mykeys = data[0]

for x in mykeys:
    if not isinstance(mykeys[x], dict):
         print(x, "=",  mykeys[x])
    else:
        print ("Nested dictionary object => ")
        for y in mykeys[x]:
            print ("   ", y, "=", mykeys[x][y])

pgres_json.close()

pg_ctlcluster commands:

This command used for executing some of the maintenance actions on the server. You can use pg_ctl options here as well.

pg_ctlcluster <version> <name> <action>

$ pg_ctlcluster 13 main status

pg_ctl: server is running (PID: 93268)

/usr/lib/postgresql/13/bin/postgres "-D" "/postgres" "-c" "config_file=/etc/postgresql/13/main/postgresql.conf"