Read more

Useful PostgreSQL commands

Claus-Theodor Riegg
March 16, 2017Software engineer at makandra GmbH

on the bash (issued as postgres user)

Start/Stop/Restart PostgreSQL

pg_ctl -D $configdir start|stop|restart

Start/Stop/Restart the corresponding PostgreSQL using the given configuration directory. The configuration directory should contain the postgresql.conf file.

Illustration online protection

Rails Long Term Support

Rails LTS provides security patches for old versions of Ruby on Rails (2.3, 3.2, 4.2 and 5.2)

  • Prevents you from data breaches and liability risks
  • Upgrade at your own pace
  • Works with modern Rubies
Read more Show archive.org snapshot

The following example would start the PostgreSQL of our governor instances: pg_ctl -D /var/lib/postgresql/config start

PostgreSQL fast shutdown

pg_ctl -D $configdir stop -m fast

Normal shutdown can take forever because postgres will wait until all clients close the connections. -m fast closes all connections and then does a clean shutdown.

Reload PostgreSQL config

pg_ctl -D $configdir reload

Reloads all configuration files

Create Checkpoint

psql -c "CHECKPOINT"

Immediately creates a checkpoint. This is useful if you want to stop postgres. (Postgres creates a checkpoint on shutdown. The more data there is for the shutdown checkpoint the longer it needs to shutdown. Creating a checkpoint before the shutdown makes the shutdown faster.)

On the psql commandline (as psql superuser)

Enable alternate print mode

Enabling this shows each column in a own line. It's similar to mysql \G

\x on

Prevent users FROM connecting to a database

ALTER DATABASE foo_db CONNECTION LIMIT 0;

No normal users can connect to this database. But superusers are still allowed. Use this in case of emergency maintenance. Don't forget to reset it (value -1)! Remember that already established sessions will continue to run.

terminate existing connections

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename NOT IN ('root', 'replication', 'postgres', '');

Show PostgreSQL config settings

SELECT * FROM pg_file_settings;

Shows which settings FROM which configfile has been applied for the current running PostgreSQL

Processlist

SELECT * FROM pg_stat_activity;

Show process activity within PostgreSQL. It's a bit too much information to fit on your screen. The following query shows the essential informations:

SELECT pid, usename, application_name, state, query_start, query FROM pg_stat_activity;

Or even less information with the connection summary:

SELECT COUNT(*) AS cnt, usename FROM pg_stat_activity GROUP BY usename ORDER BY cnt DESC;

Open transactions with their minimum needed transaction ID

SELECT pid, datname, usename, state, backend_xmin FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY age(backend_xmin) DESC;

Transactions

To get the current transaction ID:

SELECT * FROM txid_current();

PostgreSQL stats

SELECT * FROM pg_stat_database;

Shows number of connected clients to a database, how many commits and rollbacks there are. You can see the how many reads hit the cache (blks_hit) and how many had to be served FROM disk (blks_read)

SELECT schemaname,relname,last_autovacuum,last_autoanalyze,autovacuum_count,autoanalyze_count FROM pg_stat_all_tables;

Shows the number and last point in time when autovacuum and autoanalyze happened for tables belonging to the currently connected database.

SELECT relation::regclass, * FROM pg_locks WHERE not granted;

Show what locks are granted and what processes are waiting for locks to be acquired.

Further reading debugging PostgreSQL locks.

sudo -i -u postgres
psql -t -c "SELECT datname FROM pg_database WHERE datistemplate = false ORDER BY datname;" |
  xargs -I% psql -t -c "SELECT table_catalog,table_schema,table_name FROM information_schema.tables \
  WHERE table_schema = 'public' ORDER BY table_name;" %

List all tables (schema public) from every database.

Replication

For updates method names in postgres 10 look in the wiki Show archive.org snapshot .

Current xlog position on the master:

SELECT pg_current_xlog_location();
-- postgres 10
SELECT pg_current_wal_lsn();
-- repeat command every second on psql
\watch 1

Received xlog position on the slave:

SELECT pg_last_xlog_receive_location();
-- postgres 10
SELECT pg_last_wal_receive_lsn();

Replayed xlog position on the slave (these are the log entries really applied on the slave):

SELECT pg_last_xlog_replay_location();
-- postgres 10
SELECT pg_last_wal_replay_lsn();

Show the replication slots to get information about the connected slaves:

SELECT * FROM pg_replication_slots;

Show current timeline:

SELECT timeline_id FROM pg_control_checkpoint();
Claus-Theodor Riegg
March 16, 2017Software engineer at makandra GmbH
Posted by Claus-Theodor Riegg to makandra Operations (2017-03-16 16:43)