Mirgration

pg_dumpall -h old -U postgres | psql ---single-transaction --no-psqlrc -h new -U postgres

Row Count (Approximated but Fast)

SELECT reltuples AS approximate_row_count FROM pg_class WHERE relname = 'sds011';

PsotgreSQL Settings

SELECT name, setting FROM pg_settings;

File System Location of Table

SELECT pg_relation_filepath('tablename');

Help Detecting Missing Indexes

SELECT schemaname, relname, seq_scan, seq_tup_read,    idx_scan, seq_tup_read / seq_scan FROM   pg_stat_user_tables WHERE   seq_scan > 0 ORDER BY seq_tup_read DESC;

Pretty Print Relation Size

SELECT pg_size_pretty(pg_relation_size('t_test'));

Old Snapshot Threshold

old_snapshot_threshold = -1          # 1min-60d; -1 disables; 0 is immediate 

Create Series

test=# CREATE TABLE t_test (id serial, name text); CREATE TABLE test=# INSERT INTO t_test (name) SELECT 'hans'    FROM generate_series(1, 2000000); 

Copy from Program curl

postgres=# COPY t_location FROM PROGRAM 'curl --insecure --proxy http://localhost:3128 https://www.cybertec-postgresql.com/secret/orte.txt';

Select Age

SELECT age('1986-03-17 11:00:00'::timestamptz);

Explain options

EXPLAIN (analyze true, buffers true, timing true, verbose true) SELECT * FROM test WHERE id < 10000;

Query Performance

SELECT substring(query, 1, 50) AS short_query,
round(total_time::numeric, 2) AS total_time,
calls,
round(mean_time::numeric, 2) AS mean,
round((100 * total_time / sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

PostgreSQL on ZFS

full_page_writes = off

  • postgresql/commands.txt
  • Last modified: 2019-03-11T09:10:47+0100
  • by Wolfgang