chore: add Postgres panels in Status and Waku fleets

[Ivansete-status]

Background

This is inspired by @fryorcraken and the main purpose is to bring a better insight about how the database is performing without entering the database itself.

Details

We need to have a new Grafana dashboard, similar to what we have in nwaku-compose ( e.g. http://waku.fryorcraken.xyz:3000/d/yns_4vFVk/nwaku-monitoring?orgId=1 )

The more stats we extract, the better. For example (chatgpt:)

1. Database-Level Statistics pg_stat_database: Provides basic statistics about each database on the server. pg_stat_database_connections: Number of active connections. pg_stat_database_xact_commit: Number of committed transactions. pg_stat_database_xact_rollback: Number of rolled-back transactions. pg_stat_database_blks_read: Number of disk blocks read. pg_stat_database_blks_hit: Number of blocks found in the buffer cache (cache hit rate). pg_stat_database_tuples_returned: Number of rows returned by queries. pg_stat_database_tuples_fetched: Number of rows fetched by queries. pg_stat_database_conflicts: Number of query conflicts (e.g., in replication).
2. Table-Level Statistics pg_stat_user_tables: Tracks statistics for each table. pg_stat_user_tables_seq_scan: Number of sequential scans performed on each table. pg_stat_user_tables_idx_scan: Number of index scans on each table. pg_stat_user_tables_n_tup_ins: Number of rows inserted into each table. pg_stat_user_tables_n_tup_upd: Number of rows updated in each table. pg_stat_user_tables_n_tup_del: Number of rows deleted from each table. pg_stat_user_tables_vacuum_count: Number of vacuums performed on each table.
3. Index-Level Statistics pg_stat_user_indexes: Provides statistics related to indexes. pg_stat_user_indexes_idx_scan: Number of index scans on each index. pg_stat_user_indexes_idx_tup_read: Number of index tuples read. pg_stat_user_indexes_idx_tup_fetch: Number of index tuples fetched.
4. Connection Statistics pg_stat_activity: Shows information about active database connections. pg_stat_activity_state: Shows the state of connections (active, idle, etc.). pg_stat_activity_wait_event: Tracks wait events for each session. pg_stat_activity_query: The last query executed by each session. pg_stat_activity_backend_start: The start time of the backend process.
5. Replication Statistics pg_stat_replication: Shows replication status if PostgreSQL is configured with replication. pg_stat_replication_state: State of the replication (streaming, waiting, etc.). pg_stat_replication_sent_lsn: Location of the last log sequence number (LSN) sent to the replica. pg_stat_replication_write_lsn: Location of the last LSN written by the replica. pg_stat_replication_flush_lsn: Location of the last LSN flushed by the replica.
6. Lock Statistics pg_locks: Provides information about locks held by active processes. pg_locks_mode: The mode of the lock (AccessShareLock, RowExclusiveLock, etc.). pg_locks_granted: Whether the lock is granted or waiting. pg_locks_type: The type of lock (relation, transaction, etc.).
7. Vacuum and Autovacuum Statistics pg_stat_user_tables_autovacuum_count: Number of autovacuums performed on each table. pg_stat_user_tables_vacuum_count: Number of manual vacuums performed.
8. WAL (Write-Ahead Logging) Metrics pg_stat_wal (PostgreSQL 13 and above): Provides information about WAL (Write-Ahead Logging). pg_stat_wal_written: Number of WAL records written. pg_stat_wal_flush_time: Time spent flushing WAL data to disk. pg_stat_wal_fpi: Full page writes in WAL logs.
9. Checkpoint and Background Writer Statistics pg_stat_bgwriter: Tracks checkpoint and background writer activity. pg_stat_bgwriter_checkpoint_time: Time spent in checkpoints. pg_stat_bgwriter_checkpoint_write_time: Time spent writing during checkpoints. pg_stat_bgwriter_buffers_alloc: Number of buffers allocated.

[apentori]

For infra need, we have this issue : https://github.com/status-im/infra-role-postgres-ha/issues/12

• Postgresql metrics exporter added: https://github.com/status-im/infra-role-postgres-ha/commit/e48e129d8b92280cc171dae8959897062fb15a5f
• Dashboard experiement : https://grafana.infra.status.im/d/wGgaPlciz/postgresql?var-instance=node-db-01.gc-us-central1-a.waku.sandbox
• An exporter for query performance related metrics can be enabled if needed.