Little bash script
Postgresql
-
[POSTGRES] Easy to migrate
-
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 :
Psycopg is the most popular PostgreSQL database adapter for the Python programming language.
-
[Postgres] YOUR postgresql.conf CONFIGURATIONS
-
[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';
-
[Postgres] Monitor wait event
\t
select wait_event_type, wait_event from pg_stat_activity where pid !=pg_backend_pid();
\watch 0.5 -
[Postgres] Solve PostgreSQL DataFileRead and buffer_io with Parameter Tuning
Click her to see the next.
-
[Postgres] Tuto tuning
This site helped to resolve IPC wait event : Postgres checkpoint tuning | Tembo Docs
You have to adapt the checkpoint_timeout within the max_wal_size.
If the max_wal_size is 8Go then you can increase the default value of checkpoint_timeout (which is 5 min).
For big database with big wal size, you need to increase the checkpoint_timeout to avoid IPC wait event on the Buffers.