[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';

[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.

 

 

[Postgres] Identifier les privilèges

SELECT *   FROM information_schema.role_table_grants  WHERE grantee = '<role_name>';

[Postgres] Identifier les attentes

This query allows you to identify the wait events on which query .

How to Find and Stop Running Queries on PostgreSQL

Find a query with his pid.