[PSQL] Que contient les fichiers wal

Un bon tuto à faire !

What’s Inside a WAL File

[PSQL] 5 biggest tables

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

[ISSUE]PostgreSQL: Permission denied for large object

PostgreSQL: Permission denied for large object

[POSTGRES] Easy to migrate

Little bash script

Pratique le "watch"

Je partage une commande qui peut être utilisée  pour afficher  les connections actives d'une base, qui s'actualise toutes les 2 secondes, depuis le shell : 

 

export BASE=tata

watch -n 2 "psql $BASE -c \"select * from pg_stat_activity where state='active' and datname='$BASE';\""

[Postgres] commande simple qui donne la durée depuis laquelle un instance postgres est démarrée

Connaitre la date du dernier reboot de l'instance : 

SQL :

SELECT pg_postmaster_start_time();

Savoir depuis quand l'instance tourne : 

SQL : 

select current_timestamp - pg_postmaster_start_time() as uptime;

Commande système

systemctl status postgres

postgresql - How can I get my server's uptime? - Database Administrators Stack Exchange

Client en python

client en python pour se connecter à hvault : 

 

hvac · PyPI

 

Psycopg is the most popular PostgreSQL database adapter for the Python programming language.

psycopg2-binary · PyPI

[Postgres] YOUR postgresql.conf CONFIGURATIONS

PostgresqlCO.NF: PostgreSQL configuration for humans

[PostgreSQL] Replication logique

a completer

[Postgres] Monitor autovacuum_freeze_max_age

This query allows you to monitor the : autovacuum_freeze_max_age

 

SELECT datname, round(age(datfrozenxid)/to_number(current_setting('autovacuum_freeze_max_age'),'999999999')*100,1) as Percent FROM pg_database where datname != 'template0';