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