Script pour mettre à jour les séquences. Indispensable pour la réplication logique.
Professionnel - Page 3
-
[Postgres] Mettre à jour les séquences
-
[Postgres] Compter les lignes de plusieurs tables
Petit bout de code bien pratique pour compter les lignes.
-
[PostgreSQL]Un peu plus sur les analyze
Analyser toutes les tables pour un schéma donné
-
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
-
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