Basic Postgres Administration

_etc_tuned_postgresql_tuned.conf

# Tunings

## Tuned
```
apt install tuned 
```

/etc/tuned/mypg/tuned.conf

```ini
[main]
summary=Tuned profile for PostgreSQL CPUs

[cpu]
governor=performance
energy_perf_bias=performance
min_perf_pct=100
force_latency=1
 ```

## Setra

/usr/local/bin/setra.sh – Set the block device readahead to 4k, may need adjustment.

```sh
#!/bin/bash

for disk in $(lsblk -n -a -d -e 252,7,11 -o NAME); do
	/sbin/blockdev --setra 4096 /dev/${disk}
done
```
systemctl unit file setra.service /etc/systemd/system/
```
[Unit]
Description=Set Readahead
After=local-fs.target
Requires=local-fs.target

[Service]
Type=oneshot
ExecStart=/usr/local/sbin/setra.sh
RemainAfterExit=yes

[Install]
WantedBy=multi-user.target
systemctl enable tune_interfaces.service
```

## Tune network interfaces (as needed)

```
# Tune interfaces for 10Gb throughput
# And 1GB throughput. Modify per system.

#!/bin/bash

# Settings per interface via ethtool -g <interface>
# txqueuelen 8333 for gigabit
# txqueuelen 13888 for 10 gigabit.
/sbin/ethtool -G ens192 rx 4096 tx 4096
/sbin/ip link set ens192 txqueuelen 8333
#/sbin/ip link set ens192 txqueuelen 13888
```

/etc/systemd/system/tune_interfaces.service

```
[Unit]
Description=Set buffers and queue length
After=network.target
Requires=network.target

[Service]
Type=oneshot
ExecStart=/usr/local/sbin/tune_interfaces.sh
RemainAfterExit=yes

[Install]
WantedBy=multi-user.target
```
```
systemctl enable tune_interfaces.service
```

## GRUB

/etc/default/grub

```sh
GRUB_CMDLINE_LINUX_DEFAULT="transparent_hugepage=never elevator=deadline"
update-grub
```

backup.sh

#! /bin/bash
set -E

display() {
	echo -e "\n----->" $*
}

abort() {
	display $* ; exit 77
}

# ensure root context
if [[ $EUID -ne 0 ]]; then
	echo "You must be a root user"
	exit 64 # NOTROOT
fi

# Define default values
dbname="vcenter"
pgversion="9.6"
pgport=5433
options="-v -Fc -C "
daily_backup_path="/backups/daily"
retention_path="/backups/retention"
enckey="merrymary"
backupsize=10485760 #Size is always in KB ( By defaul 10 GB)

# Print final values
echo "pgport:      ${pgport}"
echo "dbname:      ${dbname}"
echo "pgversion:   ${pgversion}"
echo "options:     ${options}"
echo "daily_backup_path:     ${daily_backup_path}"
echo "enckey:      ${enckey}"
echo "retention_path:     ${retention_path}"
echo "backup_size:     ${backupsize}"

echo "=========================================="

# Generate backupname
datestamp=$(date --iso-8601=seconds)
backupname="daily_${dbname}_${datestamp}.backup.enc"

# Set default log
log="/backups/log/backup_${datestamp}.log"
echo '' > $log

# Get space available in the retention path
space_available=$(df -k $retention_path | tail -1 | awk '{print $4}')

###### Main block #######
(
	# Move old daily backup into  retention folder
	daily=$(ls -t $daily_backup_path | tail -1)
	if [ -f "${daily_backup_path}/${daily}" ]
	then
		mv "${daily_backup_path}/${daily}" "${retention_path}/"
	fi

	# Making space for the new backup
	while [ $backupsize -gt $space_available ]
	do
		oldestenc=$(ls -t $retention_path | tail -1)
		display "Deleting old backups..${oldestenc}"
		rm "${retention_path}/${oldestenc}"
		space_available=$(df -k $retention_path | tail -1 | awk '{print $4}')
	done

	# Run the backup.
	display "Backing up the PostgreSQL database ${dbname}";
	su - postgres -c "/usr/lib/postgresql/$pgversion/bin/pg_dump -p ${pgport} -d ${dbname}  $options | openssl enc -e -aes-256-cbc -out ${daily_backup_path}/${backupname} -pass pass:${enckey}; echo \${PIPESTATUS[*]} > /tmp/dumpexit.code"
	# su - postgres -c "/usr/lib/postgresql/9.6/bin/pg_dump -p 5433 -d vcenter  -v -Fc -C  | openssl enc -e -aes-256-cbc -out /backups/daily/first.backup.enc -pass pass:merrymary; echo \${PIPESTATUS[*]} > /tmp/dumpexit.code"

	# Check return codes
	while IFS=" " read -r dumpexit encexit remainder
	do
		if [[ $dumpexit -ne 0 ]]
		then
				display "Dump failed"
				if [ -f "${daily_backup_path}/${backupname}" ]
				then
					display "Deleting failed backup ${daily_backup_path}/${backupname}"
					rm 	${daily_backup_path}/${backupname}
				fi
		else
				if [[ $encexit -ne 0 ]]
				then
						display "Enc failed"
						if [ -f "${daily_backup_path}/${backupname}" ]
						then
							display "Deleting failed backup ${daily_backup_path}/${backupname}"
							rm 	${daily_backup_path}/${backupname}
						fi
				else
					display "Backup success!!"
				fi
		fi
		echo "dumpexit: ${dumpexit}"
		echo "encexit:  ${encexit}"
	done < "/tmp/dumpexit.code"

) 2>&1 | tee -a $log

commons dba queries

# Common Admin tasks

https://pgtune.leopard.in.ua/#/
https://postgresqlco.nf/

## Multiple clusters
https://www.percona.com/blog/2019/06/24/managing-multiple-postgresql-instances-on-ubuntu-debian/

## Dump role only
pg_dumpall  -p 5433  -v --roles-only -f /tmp/roles.sql

## Get all connections
SELECT  *
FROM pg_stat_activity
WHERE datname = 'lyons_001_clone';

## Kill all the connections on one db
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname='$PG_CLON_DATABASE';

## Specific table size
select pg_size_pretty(pg_total_relation_size('public.signabledocuments'));

## Column Information
SELECT *
  FROM information_schema.columns
 WHERE table_schema = 'public'
   AND table_name   = 'addtime' and column_name ='pub_psd_id'

## Kill connection
SELECT pg_terminate_backend (24628)
FROM pg_stat_activity
WHERE datname = 'lyons_001_clone';

SELECT pg_terminate_backend(pg_stat_activity.pid) 
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'db_xxxx' AND pid <> pg_backend_pid();

## Slow Running Queries on DB from Last 5 Min
SELECT now()-query_start as Running_Since,pid, datname, usename, application_name, client_addr, left(query,60) from pg_stat_activity where state in ('active','idle in transaction') and (now() - pg_stat_activity.query_start) > interval '5 minutes';

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

## Get database size
SELECT pg_size_pretty( pg_database_size('dbname') );

## Get table size
SELECT pg_size_pretty( pg_total_relation_size('tablename') );

## Get list of table sizes
select table_schema, table_name, pg_relation_size('"'||table_schema||'"."'||table_name||'"'), pg_size_pretty(pg_relation_size('"'||table_schema||'"."'||table_name||'"'))
from information_schema.tables
order by 3 DESC

## Block all connections
update pg_database set datallowconn = false where datname = 'lyons_001_clone';

## Vacuum on tables
SELECT schemaname,
          relname,
          now() - last_autovacuum AS "noautovac",
          now() - last_vacuum AS "novac",
          n_tup_upd,
          n_tup_del,
          pg_size_pretty(pg_total_relation_size(schemaname||'.'||relname)),
          autovacuum_count,
          last_autovacuum,
          vacuum_count,
          last_vacuum
 FROM pg_stat_user_tables
 WHERE (now() - last_autovacuum > '7 days'::interval
         OR now() - last_vacuum >'7 days'::interval )
         OR (last_autovacuum IS NULL AND last_vacuum IS NULL )
 ORDER BY  novac DESC;

## Unused index
SELECT relname AS table, indexrelname AS index, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

## Duplicated index
SELECT indrelid::regclass AS table, indkey AS column_numbers, array_agg(indexrelid::regclass) AS indexes, pg_catalog.pg_get_expr(indpred, indrelid, true) AS expression
FROM pg_index
GROUP BY indrelid, indkey, pg_catalog.pg_get_expr(indpred, indrelid, true)
HAVING count(*) > 1;

## Top 10 WRITE Tables
select schemaname as "Schema Name", relname as "Table Name",
n_tup_ins+n_tup_upd+n_tup_del as "no.of writes" from
pg_stat_all_tables where schemaname not in ('snapshots','pg_catalog')
order by n_tup_ins+n_tup_upd+n_tup_del desc limit 10;

## Top 10 READ Tables
SELECT schemaname as "Schema Name", relname as "Table
Name",seq_tup_read+idx_tup_fetch as "no. of reads" FROM
pg_stat_all_tables WHERE (seq_tup_read + idx_tup_fetch) > 0 and
schemaname NOT IN ('snapshots','pg_catalog') ORDER BY
seq_tup_read+idx_tup_fetch desc limit 10;

## Largest Tables in DB
SELECT QUOTE_IDENT(TABLE_SCHEMA)||'.'||QUOTE_IDENT(table_name) as
table_name,pg_relation_size(QUOTE_IDENT(TABLE_SCHEMA)||'.'||QUOTE_IDENT(table_name)) as size,
pg_total_relation_size(QUOTE_IDENT(TABLE_SCHEMA)||'.'||QUOTE_IDENT(table_name)) as total_size,
pg_size_pretty(pg_relation_size(QUOTE_IDENT(TABLE_SCHEMA)||'.'||QUOTE_IDENT(table_name))) as pretty_relation_size,pg_size_pretty(pg_total_relation_size(QUOTE_IDENT(TABLE_SCHEMA)||'.'||QUOTE_IDENT(table_name))) as pretty_total_relation_size FROM information_schema.tables WHERE QUOTE_IDENT(TABLE_SCHEMA) NOT IN ('snapshots') ORDER BY size DESC LIMIT 10;

## DB Size
SELECT datname, pg_database_size(datname),
pg_size_pretty(pg_database_size(datname))
FROM pg_database
ORDER BY 2 DESC;

## Table Size
SELECT schemaname, relname, pg_total_relation_size(schemaname
|| '.' || relname ) ,
pg_size_pretty(pg_total_relation_size(schemaname || '.' ||
relname ))
FROM pg_stat_user_tables
ORDER BY 3 DESC;

## Index Size
SELECT schemaname, relname, indexrelname,
pg_total_relation_size(schemaname || '.' || indexrelname ) ,
pg_size_pretty(pg_total_relation_size(schemaname || '.' ||
indexrelname ))
FROM pg_stat_user_indexes
ORDER BY 1,2,3,4 DESC;

## Index Utilization
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_fetch,
idx_tup_read
FROM pg_stat_user_indexes
ORDER BY 4 DESC,1,2,3;

## Tables That Are Being Updated the Most and Looking for VACUUM
select relname, /* pg_size_pretty( pg_relation_size( relid ) ) as table_size,
                 pg_size_pretty( pg_total_relation_size( relid ) ) as table_total_size, */
                 n_tup_upd, n_tup_hot_upd, n_live_tup, n_dead_tup, last_vacuum::date, last_autovacuum::date, last_analyze::date, last_autoanalyze::date
from pg_stat_all_tables
where relid in (select oid from pg_class
                       where relnamespace not in (select oid from pg_namespace
                               where nspname in ('information_schema', 'pg_catalog','pg_toast', 'edbhc' ) ) )
order by n_tup_upd desc, schemaname, relname;
SELECT schemaname,
         relname,
         now() - last_autovacuum AS "noautovac",
         now() - last_vacuum AS "novac",
         n_tup_upd,
         n_tup_del,
         autovacuum_count,
         last_autovacuum,
         vacuum_count,
         last_vacuum
FROM pg_stat_user_tables
WHERE (now() - last_autovacuum > '7 days'::interval
        AND now() - last_vacuum >'7 days'::interval)
        OR (last_autovacuum IS NULL AND last_vacuum IS NULL ) AND n_dead_tup > 0
ORDER BY  novac DESC;
SELECT relname, n_live_tup, n_dead_tup, trunc(100*n_dead_tup/(n_live_tup+1))::float "ratio%",
to_char(last_autovacuum, 'YYYY-MM-DD HH24:MI:SS') as autovacuum_date,
to_char(last_autoanalyze, 'YYYY-MM-DD HH24:MI:SS') as autoanalyze_date
FROM pg_stat_all_tables where schemaname not in ('pg_toast','pg_catalog','information_schema')
ORDER BY last_autovacuum ;

## Triggers under table
SELECT event_object_table
      ,trigger_name
      ,event_manipulation
      ,action_statement
      ,action_timing
FROM  information_schema.triggers
WHERE event_object_table = 'tableName' 
ORDER BY event_object_table
     ,event_manipulation

## Bloated Index to Run Reindexing (Locking Operation)\pgrepack (Online Rebuilding)
SELECT current_database(), nspname AS schemaname, tblname, idxname, bs*(relpages)::bigint AS real_size,
  bs*(relpages-est_pages)::bigint AS extra_size,
  100 * (relpages-est_pages)::float / relpages AS extra_ratio,
  fillfactor, bs*(relpages-est_pages_ff) AS bloat_size,
  100 * (relpages-est_pages_ff)::float / relpages AS bloat_ratio,
  is_na
  -- , 100-(sub.pst).avg_leaf_density, est_pages, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, sub.reltuples, sub.relpages -- (DEBUG INFO)
FROM (
  SELECT coalesce(1 +
       ceil(reltuples/floor((bs-pageopqdata-pagehdr)/(4+nulldatahdrwidth)::float)), 0 -- ItemIdData size + computed avg size of a tuple (nulldatahdrwidth)
    ) AS est_pages,
    coalesce(1 +
       ceil(reltuples/floor((bs-pageopqdata-pagehdr)*fillfactor/(100*(4+nulldatahdrwidth)::float))), 0
    ) AS est_pages_ff,
    bs, nspname, table_oid, tblname, idxname, relpages, fillfactor, is_na
    -- , stattuple.pgstatindex(quote_ident(nspname)||'.'||quote_ident(idxname)) AS pst, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples -- (DEBUG INFO)
  FROM (
    SELECT maxalign, bs, nspname, tblname, idxname, reltuples, relpages, relam, table_oid, fillfactor,
      ( index_tuple_hdr_bm +
          maxalign - CASE -- Add padding to the index tuple header to align on MAXALIGN
            WHEN index_tuple_hdr_bm%maxalign = 0 THEN maxalign
            ELSE index_tuple_hdr_bm%maxalign
          END
        + nulldatawidth + maxalign - CASE -- Add padding to the data to align on MAXALIGN
            WHEN nulldatawidth = 0 THEN 0
            WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign
            ELSE nulldatawidth::integer%maxalign
          END
      )::numeric AS nulldatahdrwidth, pagehdr, pageopqdata, is_na
      -- , index_tuple_hdr_bm, nulldatawidth -- (DEBUG INFO)
    FROM (
      SELECT
        i.nspname, i.tblname, i.idxname, i.reltuples, i.relpages, i.relam, a.attrelid AS table_oid,
        current_setting('block_size')::numeric AS bs, fillfactor,
        CASE -- MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?)
          WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8
          ELSE 4
        END AS maxalign,
        /* per page header, fixed size: 20 for 7.X, 24 for others */
        24 AS pagehdr,
        /* per page btree opaque data */
        16 AS pageopqdata,
        /* per tuple header: add IndexAttributeBitMapData if some cols are null-able */
        CASE WHEN max(coalesce(s.null_frac,0)) = 0
          THEN 2 -- IndexTupleData size
          ELSE 2 + (( 32 + 8 - 1 ) / 8) -- IndexTupleData size + IndexAttributeBitMapData size ( max num filed per index + 8 - 1 /8)
        END AS index_tuple_hdr_bm,
        /* data len: we remove null values save space using it fractionnal part from stats */
        sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) AS nulldatawidth,
        max( CASE WHEN a.atttypid = 'pg_catalog.name'::regtype THEN 1 ELSE 0 END ) > 0 AS is_na
      FROM pg_attribute AS a
        JOIN (
          SELECT nspname, tbl.relname AS tblname, idx.relname AS idxname, idx.reltuples, idx.relpages, idx.relam,
            indrelid, indexrelid, indkey::smallint[] AS attnum,
            coalesce(substring(
              array_to_string(idx.reloptions, ' ')
               from 'fillfactor=([0-9]+)')::smallint, 90) AS fillfactor
          FROM pg_index
            JOIN pg_class idx ON idx.oid=pg_index.indexrelid
            JOIN pg_class tbl ON tbl.oid=pg_index.indrelid
            JOIN pg_namespace ON pg_namespace.oid = idx.relnamespace
          WHERE pg_index.indisvalid AND tbl.relkind = 'r' AND idx.relpages > 0
        ) AS i ON a.attrelid = i.indexrelid
        JOIN pg_stats AS s ON s.schemaname = i.nspname
          AND ((s.tablename = i.tblname AND s.attname = pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE)) -- stats from tbl
          OR   (s.tablename = i.idxname AND s.attname = a.attname))-- stats from functionnal cols
        JOIN pg_type AS t ON a.atttypid = t.oid
      WHERE a.attnum > 0
      GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
    ) AS s1
  ) AS s2
    JOIN pg_am am ON s2.relam = am.oid WHERE am.amname = 'btree'
) AS sub
-- WHERE NOT is_na
ORDER BY 2,3,4;

## Bloated Tables to Do Vacuumfull (Locking Operation)\pgrepack (Online Rebuilding)
SELECT current_database(), schemaname, tblname, bs*tblpages AS real_size,
  (tblpages-est_tblpages)*bs AS extra_size,
  CASE WHEN tblpages - est_tblpages > 0
    THEN 100 * (tblpages - est_tblpages)/tblpages::float
    ELSE 0
  END AS extra_ratio, fillfactor, (tblpages-est_tblpages_ff)*bs AS bloat_size,
  CASE WHEN tblpages - est_tblpages_ff > 0
    THEN 100 * (tblpages - est_tblpages_ff)/tblpages::float
    ELSE 0
  END AS bloat_ratio, is_na
  -- , (pst).free_percent + (pst).dead_tuple_percent AS real_frag
FROM (
  SELECT ceil( reltuples / ( (bs-page_hdr)/tpl_size ) ) + ceil( toasttuples / 4 ) AS est_tblpages,
    ceil( reltuples / ( (bs-page_hdr)*fillfactor/(tpl_size*100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff,
    tblpages, fillfactor, bs, tblid, schemaname, tblname, heappages, toastpages, is_na
    -- , stattuple.pgstattuple(tblid) AS pst
  FROM (
    SELECT
      ( 4 + tpl_hdr_size + tpl_data_size + (2*ma)
        - CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END
        - CASE WHEN ceil(tpl_data_size)::int%ma = 0 THEN ma ELSE ceil(tpl_data_size)::int%ma END
      ) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages,
      toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, fillfactor, is_na
    FROM (
      SELECT
        tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples,
        tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages,
        coalesce(toast.reltuples, 0) AS toasttuples,
        coalesce(substring(
          array_to_string(tbl.reloptions, ' ')
          FROM '%fillfactor=#"__#"%' FOR '#')::smallint, 100) AS fillfactor,
        current_setting('block_size')::numeric AS bs,
        CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma,
        24 AS page_hdr,
        23 + CASE WHEN MAX(coalesce(null_frac,0)) > 0 THEN ( 7 + count(*) ) / 8 ELSE 0::int END
          + CASE WHEN tbl.relhasoids THEN 4 ELSE 0 END AS tpl_hdr_size,
        sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024) ) AS tpl_data_size,
        bool_or(att.atttypid = 'pg_catalog.name'::regtype) AS is_na
      FROM pg_attribute AS att
        JOIN pg_class AS tbl ON att.attrelid = tbl.oid
        JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
        JOIN pg_stats AS s ON s.schemaname=ns.nspname
          AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname
        LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
      WHERE att.attnum > 0 AND NOT att.attisdropped
        AND tbl.relkind = 'r'
      GROUP BY 1,2,3,4,5,6,7,8,9,10, tbl.relhasoids
      ORDER BY 2,3
    ) AS s
  ) AS s2
) AS s3;

## Never-Used Indexes
WITH table_scans as (
    SELECT relid,
        tables.idx_scan + tables.seq_scan as all_scans,
        ( tables.n_tup_ins + tables.n_tup_upd + tables.n_tup_del ) as writes,
                pg_relation_size(relid) as table_size
        FROM pg_stat_user_tables as tables
),
all_writes as (
    SELECT sum(writes) as total_writes
    FROM table_scans
),
indexes as (
    SELECT idx_stat.relid, idx_stat.indexrelid,
        idx_stat.schemaname, idx_stat.relname as tablename,
        idx_stat.indexrelname as indexname,
        idx_stat.idx_scan,
        pg_relation_size(idx_stat.indexrelid) as index_bytes,
        indexdef ~* 'USING btree' AS idx_is_btree
    FROM pg_stat_user_indexes as idx_stat
        JOIN pg_index
            USING (indexrelid)
        JOIN pg_indexes as indexes
            ON idx_stat.schemaname = indexes.schemaname
                AND idx_stat.relname = indexes.tablename
                AND idx_stat.indexrelname = indexes.indexname
    WHERE pg_index.indisunique = FALSE
),
index_ratios AS (
SELECT schemaname, tablename, indexname,
    idx_scan, all_scans,
    round(( CASE WHEN all_scans = 0 THEN 0.0::NUMERIC
        ELSE idx_scan::NUMERIC/all_scans * 100 END),2) as index_scan_pct,
    writes,
    round((CASE WHEN writes = 0 THEN idx_scan::NUMERIC ELSE idx_scan::NUMERIC/writes END),2)
        as scans_per_write,
    pg_size_pretty(index_bytes) as index_size,
    pg_size_pretty(table_size) as table_size,
    idx_is_btree, index_bytes
    FROM indexes
    JOIN table_scans
    USING (relid)
),
index_groups AS (
SELECT 'Never Used Indexes' as reason, *, 1 as grp
FROM index_ratios
WHERE
    idx_scan = 0
    and idx_is_btree
UNION ALL
SELECT 'Low Scans, High Writes' as reason, *, 2 as grp
FROM index_ratios
WHERE
    scans_per_write <= 1
    and index_scan_pct < 10
    and idx_scan > 0
    and writes > 100
    and idx_is_btree
UNION ALL
SELECT 'Seldom Used Large Indexes' as reason, *, 3 as grp
FROM index_ratios
WHERE
    index_scan_pct < 5
    and scans_per_write > 1
    and idx_scan > 0
    and idx_is_btree
    and index_bytes > 100000000
UNION ALL
SELECT 'High-Write Large Non-Btree' as reason, index_ratios.*, 4 as grp
FROM index_ratios, all_writes
WHERE
    ( writes::NUMERIC / ( total_writes + 1 ) ) > 0.02
    AND NOT idx_is_btree
    AND index_bytes > 100000000
ORDER BY grp, index_bytes DESC )
SELECT reason, schemaname, tablename, indexname,
    index_scan_pct, scans_per_write, index_size, table_size
FROM index_groups;

## Duplicated Indexes
SELECT
    indrelid::regclass AS TableName
    ,array_agg(indexrelid::regclass) AS Indexes
FROM pg_index
GROUP BY
    indrelid
    ,indkey
HAVING COUNT(*) > 1;

## Blocked Queries
SELECT blocked_locks.pid     AS blocked_pid,
         blocked_activity.usename  AS blocked_user,
         blocking_locks.pid     AS blocking_pid,
         blocking_activity.usename AS blocking_user,
         blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.GRANTED;
   
## Locking session :
SELECT    bl.pid AS blocked_pid,
        a.query AS blocking_statement,
        now ( ) - ka.query_start AS blocking_duration,
        kl.pid AS blocking_pid,
        a.query AS blocked_statement,
        now ( ) - a.query_start AS blocked_duration
   FROM pg_catalog.pg_locks bl
   JOIN pg_catalog.pg_stat_activity a ON bl.pid = a.pid
   JOIN pg_catalog.pg_locks kl
   JOIN pg_catalog.pg_stat_activity ka
        ON kl.pid = ka.pid
        ON bl.transactionid = kl.transactionid
    AND bl.pid != kl.pid
  WHERE NOT bl.granted;

## Blocking query 
SELECT blocked_locks.pid     AS blocked_pid,
         blocked_activity.usename  AS blocked_user,
         blocking_locks.pid     AS blocking_pid,
         blocking_activity.usename AS blocking_user,
         blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid    WHERE NOT blocked_locks.GRANTED;

## table size corrected
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 5;

etc_sysctl.d_postgresql_sysctl.conf

# Wakeups to write back in hundredths of a second
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 250
# Byte percentages to sync without causing I/O stalls.
# Generally about the size of a RAID cache
vm.dirty_background_ratio=0
vm.dirty_ratio=0
vm.dirty_bytes=67108864
vm.dirty_background_bytes=268435456
# Don't overcommit memory, allocate normally to 100%
# available memory, keep the OOMKiller from randomly
# firing.
vm.overcommit_memory=2
vm.overcommit_ratio=100
# Keep filesystem stuff cached unless the memory is
# under pressure
vm.swappiness=10 # could also be 3
# NUMA memory reclamation from zones, can cause 
# performance issues
# This is really only useful to set if you know your
# workload fits into NUMA partitions.
# Most of the time, caching is more important than data locality
vm.zone_reclaim_mode=0

# No NUMA
kernel.numa_balancing = 0

# Keeps threads from bouncing across cores willy nilly.
# 
kernel.sched_migration_cost_ns=5000000
kernel.sched_autogroup_enabled=0
# 10ms and 15ms
kernel.sched_wakeup_granularity_ns=15000000
kernel.sched_min_granularity_ns=10000000

# Core dumps with pids
kernel.core_uses_pid=1

kernel.msgmax=65536
kernel.msgmnb=65536
kernel.shmall=4194304
kernel.shmmax=4294967296

# Reboot after 10 seconds on panic
kernel.panic=10
kernel.printk=4 4 1 7

# HTCP and fair queueing
net.ipv4.tcp_congestion_control=htcp
net.core.default_qdisc=fq

# expand the local port range as far as possible
net.ipv4.ip_local_port_range=1024 65535

# Expand the memory available to various kernel
# structures for the network stack
net.core.netdev_max_backlog=262144
net.ipv4.tcp_max_orphans=1048576
net.ipv4.tcp_max_syn_backlog=16384
net.ipv4.route.max_size=8048576
net.ipv4.tcp_max_tw_buckets=1440000
net.core.optmem_max=25165824

# 256 MB
net.core.rmem_default=268435456
net.core.rmem_max=268435456
net.core.wmem_default=268435456
net.core.wmem_max=268435456

# 128 MB
net.ipv4.tcp_rmem=4096 87380 134217728
net.ipv4.tcp_wmem=4096 65536 134217728

net.ipv4.tcp_mem=65536 131072 262144
net.ipv4.udp_mem=65536 131072 262144

net.ipv4.udp_rmem_min=16384
net.ipv4.udp_wmem_min=16384

net.core.somaxconn=65535

net.core.netdev_budget=600

# Disallow forwarding and other
# router functionality.
net.ipv4.conf.all.accept_redirects=0
net.ipv4.conf.all.accept_source_route=0
net.ipv4.conf.all.rp_filter=1
net.ipv4.conf.all.send_redirects=0
net.ipv4.icmp_echo_ignore_broadcasts=1
net.ipv4.icmp_ignore_bogus_error_responses=1
net.ipv4.ip_forward=0
net.ipv4.ip_no_pmtu_disc=1

# Adjust TCP stack
net.ipv4.tcp_syncookies=1
net.ipv4.tcp_timestamps=1
net.ipv4.tcp_window_scaling=1
net.ipv4.tcp_mtu_probing=1
net.ipv4.tcp_no_metrics_save=1
net.ipv4.tcp_rfc1337=1

# This is only necessary for very high
# latency and low throughput networks
net.ipv4.tcp_slow_start_after_idle=0

# Fail quickly on lingering connections
# And recycle resources quickly as well
net.ipv4.tcp_syn_retries=2
net.ipv4.tcp_synack_retries=2
net.ipv4.tcp_sack=1
net.ipv4.tcp_dsack=1
net.ipv4.tcp_ecn=2
net.ipv4.tcp_fack=1
net.ipv4.tcp_fastopen=3
net.ipv4.tcp_fin_timeout=5
net.ipv4.tcp_keepalive_intvl=15
net.ipv4.tcp_keepalive_intvl=60
net.ipv4.tcp_keepalive_probes=10
net.ipv4.tcp_keepalive_probes=5
net.ipv4.tcp_keepalive_time=60
net.ipv4.neigh.default.gc_interval=5
net.ipv4.neigh.default.gc_stale_time=120
net.ipv4.neigh.default.gc_thresh1=4096
net.ipv4.neigh.default.gc_thresh2=8192
net.ipv4.neigh.default.gc_thresh3=16384

# Reuse sockets in TIME_WAIT
net.ipv4.tcp_tw_reuse=1

handy SQL function

# Run after temp tables creation
CREATE OR REPLACE FUNCTION test (name text) RETURNS void AS $$
    BEGIN
        EXECUTE 'ANALYZE VERBOSE ' || name;
    END;
$$ LANGUAGE plpgsql;

# Expand work mem and temp buffers for heavy functions
CREATE OR REPLACE FUNCTION test(i integer) RETURNS integer AS $$
        DECLARE
            guc_value text;
        BEGIN
                SET work_mem='1024MB';
                SET temp_buffers='512MB';
                SELECT setting||unit INTO guc_value FROM pg_settings WHERE name = 'work_mem';
                RAISE NOTICE 'Current setting is: %',guc_value;
                SELECT setting||unit INTO guc_value FROM pg_settings WHERE name = 'temp_buffers';
                RAISE NOTICE 'Current setting is: %',guc_value;
                RESET work_mem;
                RESET temp_buffers;
                SELECT setting||unit INTO guc_value FROM pg_settings WHERE name = 'work_mem';
                RAISE NOTICE 'Default setting is: %',guc_value;
                SELECT setting||unit INTO guc_value FROM pg_settings WHERE name = 'temp_buffers';
                RAISE NOTICE 'Default setting is: %',guc_value;
                RETURN i + 1;
        END;
$$ LANGUAGE plpgsql;

pgbadger usage

# PGBadger Usage

## Concatane logs
```sh
cd /var/log/postgresql/11/main/
ls -lthr
cat *.log >aggregated.log
```

## Generate report
```sh
pgbadger --prefix '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ' /var/log/postgresql/11/main/aggregated.log -o $(hostname)_$(date +%F).html
```

Postgres Better Logging

shared_preload_libraries = 'pg_stat_statements,auto_explain'

# May need to increase kernel.shmmax or kernel.shmall
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.save = true

# Set this to -1 when it's not needed to effectively disable it.
auto_explain.log_min_duration = '5s'
auto_explain.log_analyze = true
auto_explain.log_nested_statements = true

log_destination = 'stderr'
log_directory = '/var/log/postgresql/11/main'    # It may need to have a mkdir and chown postgres:postgres done if this directory does not exist.
logging_collector = on
log_filename = 'postgresql-%a.log'         # postgresql-Thu.log
log_rotation_age = 1d                            # Rotate every day
log_rotation_size = 0                             # Don't rotate based on size
log_truncate_on_rotation = on              # Truncate last week's log before starting.
log_checkpoints = on                            # How often are checkpoints happening and statistics about them
log_connections = on                           # When does something connect
log_disconnections = off                      # When does something disconnect
log_lock_waits = on                              # Logs backends and the duration of lock waits
log_temp_files = 0                                # If there are tmp files that spill to disk, of any size, let us know.
log_autovacuum_min_duration = 0      # Gives a lot of statistics for what autovacuum is doing in the logs.
log_min_duration_statement = '3s'      # If there's a statement taking 2 seconds or longer, log it.
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ' # Recommended log line prefix for pgbadger and other tools. Very informative.

PostgreSQL server image filesystem layout

/ root 50GB RAID 1 – Needs enough to have home directories and system logs not continually run out of space.

/data/postgresql/main RAID 10 – Sized according to the disk space 

/data/postgresql/pg_wal RAID 1 – Sized according to bursts of WAL. 

/data/postgresql/pg_tmp Raid not needed – Size according to the largest temp files generated

/data/postgresql/pg_logs RAID 1 – Sized to keep an appropriate amount of logging for postgresql for debugging.

/data/postgresql/pg_tablespaces RAID 10 – Sized per table that needs to be moved to separate storage

/data/postgresql/pgarchive RAID 1  – For the amount of database WAL files you’ll anticipate keeping

/data/postgresql/pgbackup RAID 1 – For the size and number of backups you recommend keeping locally.

All filesystems should be mounted with 'noatime' or 'noatime, nodiratime'

Swap 8GB

RAM: 25% of the size of the db on disk

Disk Space (main) : Multiply the current db size on disk by 8.

Disk Space (logs) : Multiply the current db size on disk by 8.

Disk Space(WAL): Multiply the size of pg_wal/pg_xlog on disk by 4.

Disk Space(tablespaces): Multiply according to the size of the tables that need to be moved.

Disk Space(pgbackup): Multiply according to the retaining requirements for database backups available locally.

Disk Space(pgarchive): Multiply according to the retaining requirements for WAL in cases where you need to do Point in Time Recovery, e.g. someone runs DELETE FROM <critical_table>;

Disk Space(pg_tmp): Multiply according to the size of pg_temp or the maximum number of temp files by 4.

CPUs: Number of connected sessions / 50 or minimum of 8.

Recommended filesystems for the database are xfs or zfs.

For security best practices the following partitions should be separated and used with the following mount options:

/var/log        nodev,noexec,nosuid,relatime
/var/log/audit  nodev,noexec,nosuid,relatime
/var
/usr
/tmp            nodev,noexec,nosuid,relatime
/var/tmp        nodev,noexec,nosuid,relatime
/home           nodev

reads.md

Reads

SQL

• Fix sequences
• Lateral Joins

Tuning

• https://www.enterprisedb.com/postgres-tutorials/introduction-postgresql-performance-tuning-and-optimization
• https://www.percona.com/blog/2018/08/29/tune-linux-kernel-parameters-for-postgresql-optimization/
• https://www.percona.com/blog/2020/09/11/how-much-memory-does-the-process-really-take-on-linux/?utm_campaign=pzcampaign&utm_source=twitter&utm_medium=paidsocial&utm_content=blog

pg_restore

• Restore certain tables

psql

• gexec
• psqlrc

autovacuum

• debugging autovacuum problems

tools

• partitioning
• pgreplay - replay a real-world database workload as exactly as possible.
• Running postgres on Docker

sar

# sysstat/sar commands

## All stats
```sh
sar -A 
```

## devices
```sh
sar -d 1
sar -d -f /var/log/sysstat/sa<day_of_month> 
```

## network interfaces
```sh
sar -n DEV 
```
## network errors
```sh
sar -n EDEV
```

## memory used
```sh
sar -r 
```
## pages swapping in and out from disk
```sh
sar -W 
```
## tasks created and context switches
```sh
sar -w
```

## CPU utilization
```
sar -u
```
## queue length and load average
```sh
sar -q 
```

## higher level swapspace utilization.
```sh
sar -S 
```