Postgrsql

optimize-db.md

-- CREATE EXTENSION IF NOT EXISTS pgstattuple;

WITH all_tables AS (
   SELECT 
        n.nspname AS schemaname,
        c.relname AS tablename
    FROM pg_class c
    JOIN pg_namespace n ON c.relnamespace = n.oid
    WHERE c.relkind = 'r'  -- обычные таблицы
    AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
    AND n.nspname !~ '^pg_temp_'  -- исключаем временные таблицы
    AND n.nspname !~ '^pg_toast_temp_'  -- исключаем временные TOAST-таблицы
)
SELECT 
    t.schemaname,
    t.tablename,
    pg_size_pretty(p.table_len) AS "Размер таблицы",
    p.tuple_count AS "Количество кортежей",
    pg_size_pretty(p.tuple_len) AS "Размер кортежей",
    ROUND(p.tuple_len::numeric * 100 / NULLIF(p.table_len, 0), 2) AS "Кортежи (%)",
    pg_size_pretty(p.dead_tuple_len) AS "Мертвые кортежи",
    ROUND(p.dead_tuple_len::numeric * 100 / NULLIF(p.table_len, 0), 2) AS "Мертвые (%)",
    pg_size_pretty(p.free_space) AS "Свободное пространство",
    ROUND(p.free_space::numeric * 100 / NULLIF(p.table_len, 0), 2) AS "Свободное (%)",
		p.*
FROM all_tables t
CROSS JOIN LATERAL (
    SELECT * FROM pgstattuple(concat('"',t.schemaname,'"','.','"',t.tablename,'"'))
) p
ORDER BY p.table_len DESC;

SELECT
  schemaname, relname,
  n_tup_upd, n_tup_hot_upd,
  n_live_tup, n_dead_tup
FROM pg_stat_all_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC
LIMIT 10;

postgresql.md

Copy database

-- SELECT pid, usename, client_addr 
-- FROM pg_stat_activity 
-- WHERE datname in ('sourcedb','targetdb');

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname in ('sourcedb','targetdb') AND pid <> pg_backend_pid();

DROP DATABASE IF EXISTS targetdb;

CREATE DATABASE targetdb 
WITH TEMPLATE sourcedb;

Forward port for connection throught ssh

ssh -N -L 54320:localhost:5432 root@host