My Maintain PostgreSQL Commands

SELECT schemaname                                    AS table_schema,
       relname                                       AS table_name,
       PG_SIZE_PRETTY(PG_TOTAL_RELATION_SIZE(relid)) AS total_size,
       PG_SIZE_PRETTY(PG_RELATION_SIZE(relid))       AS data_size,
       PG_SIZE_PRETTY(PG_TOTAL_RELATION_SIZE(relid) - PG_RELATION_SIZE(relid))
                                                     AS external_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY PG_TOTAL_RELATION_SIZE(relid) DESC,
         PG_RELATION_SIZE(relid) DESC
LIMIT 100;

List all table size. origin

for db in $(psql -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;"); do
    echo "Maintaining database: $db"
    psql -d "$db" -c "VACUUM ANALYZE;"
done

table optimization and analyzing for all databases.

Eric Guo's blog.cloud-mes.com

Hoping writing JS, Ruby & Rails and Go article, but fallback to DevOps note

Comments