PostgreSQL Le BA BA pour bien commencer

Pour bien débuter sur postgresql 

PostgreSQL Defrag les plus gros indexes et tables

Réduire la fragmentation 

https://www.timescale.com/learn/how-to-reduce-bloat-in-large-postgresql-tables

[Postgres] Size taille table / index

SELECT
   relname  as table_name,
   pg_size_pretty(pg_total_relation_size(relid)) As "Total Size",
   pg_size_pretty(pg_indexes_size(relid)) as "Index Size",
   pg_size_pretty(pg_relation_size(relid)) as "Actual Size"
   FROM pg_catalog.pg_statio_user_tables 
ORDER BY pg_total_relation_size(relid) DESC;

[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