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 

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 

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

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->'competitorOfferUrl' 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;

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