PSQL tips and tricks

psql.md

Cheat sheet

• better display of results:

\pset format wrapped
\pset columns 100
\pset linestyle unicode

• list all databases: \l
• List all schemas: \dn
• list all stored procedures and functions: \df
• list all views: \dv
• lists all tables in a current database: \dt
• change current db: \c new_current_db
• add schema to path: SET SEARCH_PATH TO my_other_schema,public;

Active queries

• Long queries

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

• Blocked queries

select pid,
       usename,
       pg_blocking_pids(pid) as blocked_by,
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

• Cancel all long running queries

SELECT pg_cancel_backend(pid)
FROM   pg_stat_activity
WHERE
       now() - pg_stat_activity.query_start >= interval '5 minutes' AND
       state = 'active';

or SELECT pg_terminate_backend(pid);

Backups/Restore

dump

pg_dump --dbname "postgres://user:pass@host:port/db" \
  --clean --if-exists \
  --quote-all-identifiers \
  --format=custom \
  -f /backup.dump;

restore

pg_restore --dbname "postgres://user:pass@host:port/db" \
  --clean --if-exists --role owner \
  /backup.dump;

JSON dump some table

docker run--rm -e PGPASSWORD=xxx postgres:11 \
  psql -A -h 127.0.0.1 -p 5435 -U postgres postgres \
  -tc "SELECT json_agg(tests) FROM tests;" > out.json

CSV

Client-side dump

\COPY (select * from audit.logs where time > now()-interval '24 hour') to '/path/to/audit-logs.csv' CSV HEADER;

Load into table

\COPY matomo FROM '/path/to/some.csv' CSV HEADER;

Security

Create bcrypt password

INSERT INTO USERS (active, email, password) 
   VALUES (true, '[email protected]', crypt('toto', gen_salt('bf',8)));

Create read-only user

CREATE ROLE readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
CREATE USER read WITH PASSWORD 'someCr@zyP4ssw0rd';
GRANT readonly TO read;

Create new user and BD as admin :

CREATE USER "some-db" WITH PASSWORD '19865K69IeT0cm98ajQ=';
CREATE DATABASE "some-db";
GRANT "some-db" to "admin"; -- grant new role to ADMIN USER
ALTER DATABASE "some-db" OWNER TO "some-db";
\c some-db;
ALTER SCHEMA public OWNER TO "some-db";
GRANT ALL PRIVILEGES ON DATABASE "some-db" TO "some-db";

Various

Generate random data

create table t_random as select s, md5(random()::text) from generate_Series(1,500) s;

List rows per table

WITH tbl AS
  (SELECT table_schema,
          TABLE_NAME
   FROM information_schema.tables
   WHERE TABLE_NAME not like 'pg_%'
     AND table_schema in ('public'))
SELECT table_schema,
       TABLE_NAME,
       (xpath('/row/c/text()', query_to_xml(format('select count(*) as c from %I.%I', table_schema, TABLE_NAME), FALSE, TRUE, '')))[1]::text::int AS rows_n
FROM tbl
ORDER BY rows_n DESC;

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

To kill some query : SELECT pg_terminate_backend(__pid__);

JSON

Functions : https://www.postgresql.org/docs/current/functions-json.html

Replace value in JSONb arrays

update my_table set json_col=((json_col - 'TO_REPLACE')||'["REPLACE_WITH"]') where json_col  ?| array['TO_REPLACE']

example : https://dbfiddle.uk/?rdbms=postgres_11&fiddle=e0e3a235b47f833af37049243b3b97dc

postgres FDW foreign data wrapper + CNPG

    BEGIN;
      CREATE EXTENSION IF NOT EXISTS postgres_fdw;
      GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO "pastek-mirror";
      CREATE SCHEMA pastek_remote_schema;
      ALTER SCHEMA pastek_remote_schema OWNER TO "pastek-mirror";
      CREATE SERVER pastek_remote FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'host.docker.internal', port '5455', dbname 'kysely2', sslmode 'disable');
      GRANT USAGE ON FOREIGN SERVER pastek_remote TO "pastek-mirror";
      CREATE USER MAPPING FOR PUBLIC SERVER pastek_remote OPTIONS (user 'postgres', password 'postgres');
      IMPORT FOREIGN SCHEMA public FROM SERVER pastek_remote INTO pastek_remote_schema;
      ALTER FOREIGN TABLE pastek_remote_schema.* OWNER TO "pastek-mirror";
    COMMIT;

Indexes

List all indexes

SELECT tablename, indexname, indexdef FROM pg_indexes WHERE schemaname = 'public'
ORDER BY tablename, indexname;

Monitoring index construction status

SELECT
  now()::TIME(0),
  a.query,
  p.phase,
  round(p.blocks_done / p.blocks_total::numeric * 100, 2) AS "% done",
  p.blocks_total,
  p.blocks_done,
  p.tuples_total,
  p.tuples_done,
  ai.schemaname,
  ai.relname,
  ai.indexrelname
FROM pg_stat_progress_create_index p
JOIN pg_stat_activity a ON p.pid = a.pid
LEFT JOIN pg_stat_all_indexes ai on ai.relid = p.relid AND ai.indexrelid = p.index_relid;

List indexes stats

SELECT
    pt.tablename AS TableName
    ,t.indexname AS IndexName
    ,to_char(pc.reltuples, '999,999,999,999') AS TotalRows
    ,pg_size_pretty(pg_relation_size(quote_ident(pt.tablename)::text)) AS TableSize
    ,pg_size_pretty(pg_relation_size(quote_ident(t.indexrelname)::text)) AS IndexSize
    ,to_char(t.idx_scan, '999,999,999,999') AS TotalNumberOfScan
    ,to_char(t.idx_tup_read, '999,999,999,999') AS TotalTupleRead
    ,to_char(t.idx_tup_fetch, '999,999,999,999') AS TotalTupleFetched
FROM pg_tables AS pt
LEFT OUTER JOIN pg_class AS pc 
    ON pt.tablename=pc.relname
LEFT OUTER JOIN
( 
    SELECT 
        pc.relname AS TableName
        ,pc2.relname AS IndexName
        ,psai.idx_scan
        ,psai.idx_tup_read
        ,psai.idx_tup_fetch
        ,psai.indexrelname 
    FROM pg_index AS pi
    JOIN pg_class AS pc 
        ON pc.oid = pi.indrelid
    JOIN pg_class AS pc2 
        ON pc2.oid = pi.indexrelid
    JOIN pg_stat_all_indexes AS psai 
        ON pi.indexrelid = psai.indexrelid 
)AS T
    ON pt.tablename = T.TableName
WHERE pt.schemaname='public'
ORDER BY 1;