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

reload pg_hba config

SELECT pg_reload_conf();

reload postgres without closing connections. This is useful to updating run-time parameters changes in postgresql.conf

/usr/local/bin/pg_ctl reload -D /var/lib/postgresql/data/pgdata/

Check runtime parameters and see if they are updated

show enable_seqscan

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('slotname_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'

clean the database from all unreferenced data

VACUUM VERBOSE ANALYZE drinks;

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

creating a postgres role to be used with postgres (psql). With the rights of postgres

createuser -U postgres --no-superuser --no-createrole --createdb --no-password merijn

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

or

pg_basebackup -h master.lan -p 5432 -U replicationuser -X stream --verbose -C -S replication_slot_name -v -R -W -D /var/lib/postgresql/14/main/

promote a slave to master

postgres=# SELECT pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t
(1 row)

postgres=# SELECT pg_promote();
 pg_promote
------------
 t
(1 row)

postgres=# SELECT pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 f
(1 row)

or use

/usr/local/bin/pg_ctl promote -D /var/lib/postgresql/data/pgdata/

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

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