PSQL tips and tricks

psql.md

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

HTML + PNG schemas dump

With SchemaSpy:

docker run --network host -v $PWD/output:/output \
  schemaspy/schemaspy:latest -t pgsql -host 127.0.0.1 -port 5432 -u xxx -p xxx -db xxx

With SchemaCrawler:

docker run -v $PWD/tests-examples:/tmp/share --network=host \
  schemacrawler/schemacrawler /opt/schemacrawler/bin/schemacrawler.sh \
  --info-level=standard --title "My DB Schema" --url "jdbc:postgresql://127.0.0.1:5432/xxx" \
  --user xxx --password xxx --command=schema -o /tmp/share/out.[png|html]

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, 'toto@toto.com', 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 and fill a table
CREATE TABLE t_random AS select s, md5(random()::text) FROM generate_Series(1,500) s;

-- or insert into existing
INSERT INTO User (id) VALUES (generate_series(1, 1000));

Show table structure

SELECT
   table_name,
   column_name,
   data_type
FROM
   information_schema.columns
WHERE
   table_name = 'User';

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;

• List Foreign data wrappers : \dew+
• List Foreign data server : \des+

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;