Зручні запити для PostgreSQL

commands.md

Створити дамп:

pg_dump -Fc --no-acl --no-owner -U myuser mydb > mydb.dump

Створити зашифрований дамп (FF000000 приклад ідентифікатора публічного ключа):

pg_dump -Fc --no-acl --no-owner -U myuser mydb | gpg --encrypt --recipient FF000000 --cipher-algo AES256 --output mydb.dump.gpz

Basebackup (починаючи з версії 9.4 можна вказати опцію --rate-limit):

pg_basebackup -U postgres -D /pg-backup/backup/2015-09-07 -Ft -Z9 --progress --xlog-method=fetch --write-recovery-conf --rate-limit=64m

Коректне відновлення дампу:

pg_restore --verbose --clean --no-acl --no-owner --jobs 4 -U myuser -d mydb mydb.dump

Відновлення зашифрованого дампу (параметр --jobs не підтримується при вході з STDIN):

gpg --decrypt mydb.dump.gpz | pg_restore --verbose --clean --no-acl --no-owner -U myuser -d mydb

Створення БД (initdb може бути відсутня в $PATH, тому потрібен повний шлях, наприклад, /usr/lib/postgresql/9.4/bin/initdb):

initdb --pgdata=/pgdata/9.4/main -E 'UTF-8' --lc-collate='uk_UA.UTF-8' --lc-ctype='uk_UA.UTF-8'

Підключитися до віддаленої БД через ssh тунель:

ssh -L 54321:localhost:5432 <host or host alias>

Відформатувати диск в XFS:

sudo -s
mkdir /pgdata
mkfs.xfs -f /dev/xvdf
mount -t xfs -o inode64,nobarrier,noatime,nosuid,nodev,noexec /dev/xvdf /pgdata
cat /proc/mounts | grep /dev/xvdf >> /etc/fstab

Апгрейд (з версії 9.4 до версії 9.5, наприклад):

sudo -s
apt-get install postgresql-9.5
service postgresql stop

/usr/lib/postgresql/9.5/bin/initdb --pgdata=/pgdata/9.5/main -E 'UTF-8' --lc-collate='uk_UA.UTF-8' --lc-ctype='uk_UA.UTF-8'

pg_upgrade \
  --old-datadir /pgdata/9.4/main \
  --new-datadir /pgdata/9.5/main \
  --old-bindir /usr/lib/postgresql/9.4/bin/ \
  --new-bindir /usr/lib/postgresql/9.5/bin/ \
  --jobs 4 \
  --verbose

gistfile1.sql

-- Згенерувати часові (денні) інтервали
SELECT d::date FROM generate_series(
    date_trunc('month', now()),
    date_trunc('month', now()) + '1 month' - '1 day'::interval,
    '1 day'
) AS series(d);


-- Основні параметри СУБД
SELECT name, setting, unit FROM pg_settings;


-- Розмір БД:
SELECT pg_size_pretty(pg_database_size('database_name')) AS "db_size";


-- Розмір найбільших 20 таблиць БД:
SELECT nspname || '.' || relname AS "relation",
  pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  AND C.relkind <> 'i'
  AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 20;


-- Розмір конкретної таблиці БД (разом з індексами):
SELECT pg_size_pretty(pg_total_relation_size('db_table')) AS "table_with_indexes_size";


-- Розмір конкретної таблиці БД (без індексів):
SELECT pg_size_pretty(pg_relation_size('db_table')) AS "table_size";


-- Розмір індексів конкретної таблиці БД:
SELECT pg_size_pretty(pg_indexes_size('db_table')) AS "table_indexes_size";


-- Згрупувати та порахувати події за тижнями (прикл. формату: 2013-W14):
SELECT
  to_char(started_on - 7/24, 'IYYY-"W"IW') AS "week",
  count(*)
FROM
  events
GROUP BY to_char(started_on - 7/24, 'IYYY-"W"IW')
ORDER BY "week";


-- Фактор ефективності кешу:
SELECT
  sum(heap_blks_read) AS heap_read,
  sum(heap_blks_hit)  AS heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio
FROM
  pg_statio_user_tables;


-- Фактор ефективності кешу індексів:
SELECT
  sum(idx_blks_read) as idx_read,
  sum(idx_blks_hit)  as idx_hit,
  (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
FROM
  pg_statio_user_indexes;


-- Відсоток разів, коли був використаний індекс:
SELECT
  relname,
  100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used,
  n_live_tup rows_in_table
FROM
  pg_stat_user_tables
WHERE
  seq_scan + idx_scan > 0
ORDER BY
  n_live_tup DESC;


-- Загальна статистика по індексам:
SELECT
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN x.is_unique = 1  THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
       (SELECT indrelid,
           max(CAST(indisunique AS integer)) AS is_unique
       FROM pg_index
       GROUP BY indrelid) x
       ON c.oid = x.indrelid
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch,indexrelname FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;


-- ТОП 10 запитів до БД.
SELECT
  query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM
  pg_stat_statements
ORDER BY
  total_time
DESC LIMIT 10;