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 : status-im/infra-role-postgres-ha#12

• Postgresql metrics exporter added: status-im/infra-role-postgres-ha@e48e129
• 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.