Getting Help in psql
It doesn't matter if you do not remember a single command as long as you follow the hints given:
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
While many know their way around SQL, you might want to always use \? to find the specific psql commands.
Using Regular Expressions
You can edit column using regular expressions by running regexp_replace()
UPDATE table SET field=regexp_replace(field, 'match pattern', 'replace string', 'g');
You can get Postgres to output JSON like this:
SELECT row_to_json(<name of key column>) FROM ...
EXPLAIN ANALYZE <sql statement>;
Inspect an Installation
List Postgres Clusters
Under Debian use the pg_wrapper command
List Postgres Settings
List Databases and Sizes
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;
Show Running Queries in Postgres
SELECT * FROM pg_stat_activity;
Show Blocking Locks
SELECT bl.pid AS blocked_pid, a.usename AS blocked_user,
kl.pid AS blocking_pid, ka.usename AS blocking_user, a.current_query AS blocked_statement
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a
ON bl.pid = a.procpid
JOIN pg_catalog.pg_locks kl
JOIN pg_catalog.pg_stat_activity ka
ON kl.pid = ka.procpid
ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid
WHERE NOT bl.granted ;
Show Table Usage
If you want to know accesses or I/O per table or index you can use the pg_stat_*_tables and pg_statio_*_tables relations. For example:
SELECT * FROM pg_statio_user_tables;
to show the I/O caused by your relations. Or for the number of accesses and scan types and tuples fetched:
SELECT * FROM pg_stat_user_tables;
Kill Postgres Query
First find the query and it's PID:
SELECT procpid, current_query FROM pg_stat_activity;
And then kill the PID on the Unix shell. Or use
Kill all Connections to a DB
The following was suggested here
. Replace "TARGET_DB" with the name of the database whose connections should be killed.
SELECT pg_terminate_backend(pg_stat_activity.procpid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'TARGET_DB';
Compared to MySQL checking for replication delay is rather hard. It is usually good to script this or use ready monitoring tools (e.g. Nagios Postgres check). Still it can be done manually by running this command on the master:
and those two commands on the slave:
The first query gives you the most recent xlog position on the master, while the other two queries give you the most recently received xlog and the replay position in this xlog on the slave.
A Nagios check plugin could look like this:
# Checks master and slave xlog difference...
# Pass slave IP/host via $1
PSQL="psql -A -t "
# Get master status
master=$(echo "SELECT pg_current_xlog_location();" | $PSQL)
# Get slave receive location
slave=$(echo "select pg_last_xlog_replay_location();" | $PSQL -h$1)
master=$(echo "$master" | sed "s/\/.*//")
slave=$(echo "$slave" | sed "s/\/.*//")
master_dec=$(echo "ibase=16; $master" | bc)
slave_dec=$(echo "ibase=16; $slave" | bc)
diff=$(expr $master_dec - $slave_dec)
if [ "$diff" == "" ]; then
echo "Failed to retrieve replication info!"
# Choose some good threshold here...
if [ $diff -gt 3 ]; then
if [ $diff -gt 5 ]; then
echo "Master at $master, Slave at $slave , difference: $diff"
Postgres Backup Mode
To be able to copy Postgres files e.g. to a slave or a backup you need to put the server into backup mode.
SELECT pg_start_backup('label', true);
Read more: Postgres - Set Backup Mode
To inspect pgbouncer operation ensure to add at least one user you defined in the user credentials (e.g. /etc/pgbouncer/userlist.txt) to the "stats_users" key in pgbouncer.ini:
stats_users = myuser
Use this user to connect to pgbouncer with psql by requesting the "pgbouncer" database:
psql -p 6432 -U myuser -W pgbouncer
At psql prompt list supported commands
PgBouncer will present all statistics and configuration options:
pgbouncer=# SHOW HELP;
NOTICE: Console usage
SET key = arg
The "SHOW" commands are all self-explanatory. Very useful are the "SUSPEND" and "RESUME" commands when you use pools.
If you ever need to restart pgbouncer under traffic load use "-R" to avoid disconnecting clients. This option gets the new process to reuse the Unix sockets of the old one. A possible use case could be that you think pgbouncer has become stuck, overloaded or instable.
Aside from this in most cases SIGHUP should be fine.
Pooler Error: Auth Failed
If connections to your pgbouncer setup fail with "Pooler Error: Auth failed" check the following configuration values in your pgbouncer.ini
- auth_file = ... : Ensure to point this path to your pg_auth file in your Postgres setup.
- auth_type = ... : Ensure to set the correct authentication type. E.g. "md5" for MD5 hashed passwords.
- Check if your pg_auth file has the needed passwords entries.
The must have reading for Postgres is for sure this book: