When connecting to a postgresdb. In most cases you want to select a schema to connect to. Otherwise you will connect to the default schema 'postgres'

psql -d some_schema

Switch to a database schema

\c someschema

show databases


show tables and sequences


show tables:


describe table

\d+ tablename

show users


check replication slots; The configured slaves

select * from pg_replication_slots \x\g\x

create replication slot

select (pg_create_physical_replication_slot('repl_acc_2_slot'));

remove replication slot

select pg_drop_replication_slot('replica_2_slot');

Check replication state

select * from pg_stat_replication\x\g\x

check for long queries

select total_time,query from pg_stat_statements

cancel a query by pid

SELECT pg_cancel_backend(PID);

kill a query by pid

SELECT pg_terminate_backend(PID);

count records of table:

SELECT COUNT(*) FROM raw_record;

Import table from sqlfile

psql dbname < infile.sql

Dropping a table in postgresql

drop table images;

dump only database grants

 pg_dumpall -s | grep 'ON DATABASE'

Dump all roles on the source database / computer with

pg_dumpall -g > roles.sql

Check for queries which are idle and have an transaction open

select *
from pg_stat_activity
where (state = 'idle in transaction')
    and xact_start is not null\x\g\x

check for long running queries

  now() - pg_stat_activity.query_start AS duration,
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';


get all data from master. Before starting slave

pg_basebackup -h -D /var/lib/postgresql/data/ -P -U replication --wal-method=fetch

clean the database from all unreferenced data



You can store json data in a postgres field. Postgres knows the datatype Json. Which means you are able to query it as well.

 select as_json->'drink' as dr from menu limit 10;
 select as_json->'menu'->0->'Url' as drinks from drinks limit 100
 select as_json->'menu'->0->'drinkName' as menu from menu limit 10
 SELECT * FROM menu o, json_array_elements(o.as_json#>'{menu}') obj WHERE obj->>'brand' = '5' LIMIT 100;
 SELECT as_json->'drink' FROM menu o, json_array_elements(o.as_json#>'{menu}') obj WHERE obj->>'drinkName' is not null LIMIT 100;
 select count(drink) from menu_v2 menu, json(menu.as_json) json where json->>'brand' = 'COLA' and  json->>'table' = '1';
select json(payload) payload from  public.drinks where payload->'brand'->>'COLA' = 'true' limit 1;

timescale extention

Check which extentions are installed/enabled

                                      List of installed extensions
    Name     | Version |   Schema   |                            Description
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
 timescaledb | 2.3.1   | public     | Enables scalable inserts and complex queries for time-series data
(2 rows)

pg_stat_statements extention

FROM pg_available_extensions
    name = 'pg_stat_statements' and
    installed_version is not null;
CREATE EXTENSION pg_stat_statements;

in config

shared_preload_libraries = 'timescaledb, pg_stat_statements'

some other unsorted nice queries:

  schemaname, relname,
  last_vacuum, last_autovacuum,
  vacuum_count, autovacuum_count  -- not available on 9.0 and earlier
FROM pg_stat_user_tables;
SELECT schemaname, relname, last_vacuum, last_autovacuum, vacuum_count, autovacuum_count FROM pg_stat_user_tables;

Find fields ending in a whitespace using regex

select DISTINCT(drink) from drink_category where drink_name ~ '\s$';