[Postgres] Migrate the role to distant host

ssh <targetServer>". /usr/postgres/bin/setDB.bash <DBNAME>;export PGPASSWORD=pwd;pg_dumpall --roles-only -h <sourceServer> -p 5432 -U <username>| psql -a"

[Postgres] SET DB ENV

/tech/postgres/dba/jjy setDB.bash
#! /bin/bash
##################################################################
## Program      : setDB.bash                                 #
## Description  : Set environement variables according to        #
##                  database                                     #
##################################################################
#
#
#set -xv

# CONSTANTS
PRGNAME=$(basename $0 2> /dev/null)
if [ $? -ne 0 ]; then
  PRGNAME="Unknown"
fi
PRGPATH=$(dirname $0 2> /dev/null)
if [ "${PRGPATH}x" == "x" ] || [ ! -e "${PRGPATH}" ]; then
  PRGPATH="/tech/postgres/bin"
fi
# INCLUDE
if [ -e ${PRGPATH}/__pg.include ]; then
  . ${PRGPATH}/__pg.include
elif [ -e /tech/postgres/bin/__pg.include ]; then
  . /tech/postgres/bin/__pg.include
fi

# Check environement
if [ $# -ne 1 ]; then
  SID=""
  DBSet
  printf "nUsage: dbset SIDnn"
else
  SID=$1
  DBSet ${SID}
  echo "PGSID     : "${PGSID}" (port ${PGPORT}) STATUS : "${PGSTATUS,,}
  echo "PGVERSION : "${PGVERSION}
  echo "PGHOME    : "${PGHOME}
  echo "PGDATA    : "${PGDATA}
fi

[Postgres] AUDIT

Requêtes pour audit 

Nombre de ligne dans les tables postgres

SELECT reltuples::bigint AS estimate
FROM   pg_class
WHERE  oid = 'myschema.mytable'::regclass;

[Postgres] check_postgres

https://wiki.postgresql.org/wiki/Index_Maintenance

[Postgres] Create partitionned tables and rotate

Script to create partitionned tables and rotate it on one year

PostgreSQL performance Turing Indexes

https://www.timescale.com/learn/postgresql-performance-tuning-optimizing-database-indexes

[Postgres] Fonction pour compter les lignes

Pratique lors d'une migration pour vérifier que toutes les lignes sont bien là !

[Postgres] Mesure the bloat

intéressant mon cher watson

[Postgres] Spot a missing postgreSQL index

--tuto tuning psql
--Spot a missing PostgreSQL index

SELECT
  relname                                               AS TableName,
  to_char(seq_scan, '999,999,999,999')                  AS TotalSeqScan,
  to_char(idx_scan, '999,999,999,999')                  AS TotalIndexScan,
  to_char(n_live_tup, '999,999,999,999')                AS TableRows,
  pg_size_pretty(pg_relation_size(relname :: regclass)) AS TableSize
FROM pg_stat_all_tables
WHERE schemaname = 'public'
      AND 50 * seq_scan > idx_scan -- more than 2%
      AND n_live_tup > 10000
      AND pg_relation_size(relname :: regclass) > 5000000
ORDER BY relname ASC;

--To find unused index in a db

SELECT
  relname AS table,
  indexrelname AS index,
  pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
  idx_scan as index_scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique AND idx_scan =0 AND pg_relation_size(relid) > 5 * 8192
ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
pg_relation_size(i.indexrelid) DESC;

--returns the tables which have been hit by sequential scans the most and tells us how many rows a sequential scan has hit on average.
SELECT schemaname, 
              relname, 
              seq_scan, 
              seq_tup_read, 
              idx_scan, 
              seq_tup_read / seq_scan AS avg
 FROM         pg_stat_user_tables 
 WHERE        seq_scan > 0 
 ORDER BY     seq_tup_read DESC;

--Finding slow queries to be executed on pgadmin
SELECT query, 
              total_exec_time, 
              calls, 
              mean_exec_time 
       FROM   pg_stat_statements 
       ORDER BY total_exec_time DESC;