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.
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();
