Postgres

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

\l

show tables and sequences

\d

show tables:

\dt

describe table

\d+ tablename

show users

\du

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

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

replication

get all data from master. Before starting slave

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

clean the database from all unreferenced data

VACUUM VERBOSE ANALYZE drinks;

json

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

https://www.postgresql.org/docs/9.6/static/functions-json.html

 select as_json->'drink' as dr from menu limit 10;
 select as_json->'menu'->0->'Url' as offers from offers 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

Check which extentions are installed/enabled

\dx
                                      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)

some other unsorted nice queries:

SELECT
  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$';