Guide DBA — Exploitation et administration des bases classiques

/srv/db-platform/
  postgres/
    data/
    wal_archive/
    backup/
    scripts/
    logs/
  oracle/
    backup/
    archivelog/
    scripts/
    logs/
  mysql/
    data/
    binlog_archive/
    backup/
    scripts/
    logs/
  runbooks/
  monitoring/
  inventory/

listen_addresses = '*'
port = 5432
max_connections = 200
shared_buffers = '4GB'
effective_cache_size = '12GB'
work_mem = '16MB'
maintenance_work_mem = '1GB'
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /srv/db-platform/postgres/wal_archive/%f && cp %p /srv/db-platform/postgres/wal_archive/%f'
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
log_min_duration_statement = 1000
log_checkpoints = on
log_connections = on
log_disconnections = on

SELECT datname, numbackends, xact_commit, xact_rollback
FROM pg_stat_database
ORDER BY numbackends DESC;

SELECT pid, usename, state, wait_event_type, wait_event, query_start, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

SELECT schemaname, relname, n_dead_tup, last_autovacuum, last_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

SELECT slot_name, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'change-me';

SELECT pg_create_physical_replication_slot('standby_01');
SELECT pg_create_physical_replication_slot('standby_02');

host replication replicator 10.10.20.11/32 scram-sha-256
host replication replicator 10.10.20.12/32 scram-sha-256

systemctl stop postgresql
rm -rf /var/lib/postgresql/18/main/*

pg_basebackup \
  --host=10.10.20.10 \
  --port=5432 \
  --username=replicator \
  --pgdata=/var/lib/postgresql/18/main \
  --format=plain \
  --write-recovery-conf \
  --slot=standby_01 \
  --checkpoint=fast \
  --progress

systemctl start postgresql

scope: pg-prod
name: pg-node-01

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.10.20.10:8008

etcd3:
  hosts: 10.10.30.10:2379,10.10.30.11:2379,10.10.30.12:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        wal_level: replica
        hot_standby: 'on'
        max_wal_senders: 10
        max_replication_slots: 10
  initdb:
    - encoding: UTF8
    - data-checksums

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.10.20.10:5432
  data_dir: /var/lib/postgresql/18/main
  bin_dir: /usr/lib/postgresql/18/bin
  authentication:
    superuser:
      username: postgres
      password: change-me
    replication:
      username: replicator
      password: change-me

listen postgres_write
  bind *:5432
  option httpchk GET /primary
  http-check expect status 200
  default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
  server pg-node-01 10.10.20.10:5432 check port 8008
  server pg-node-02 10.10.20.11:5432 check port 8008
  server pg-node-03 10.10.20.12:5432 check port 8008

listen postgres_read
  bind *:5433
  option httpchk GET /replica
  http-check expect status 200
  server pg-node-01 10.10.20.10:5432 check port 8008
  server pg-node-02 10.10.20.11:5432 check port 8008
  server pg-node-03 10.10.20.12:5432 check port 8008

[databases]
appdb = host=10.10.20.100 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 20
server_idle_timeout = 600
server_lifetime = 3600
ignore_startup_parameters = extra_float_digits
admin_users = postgres, dba_admin
stats_users = dba_readonly

SHOW POOLS;
SHOW CLIENTS;
SHOW SERVERS;
SHOW STATS;
SHOW CONFIG;
PAUSE appdb;
RESUME appdb;
RELOAD;

SELECT instance_name, status, database_status
FROM v$instance;

SELECT name, open_mode, database_role, log_mode
FROM v$database;

SELECT tablespace_name, ROUND(used_percent, 2) AS used_percent
FROM dba_tablespace_usage_metrics
ORDER BY used_percent DESC;

SELECT dest_id, status, destination, error
FROM v$archive_dest_status
ORDER BY dest_id;

SELECT username, status, lock_date, expiry_date
FROM dba_users
ORDER BY username;

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;

RUN {
  ALLOCATE CHANNEL c1 DEVICE TYPE DISK FORMAT '/backup/oracle/%d_%T_%U.bkp';
  ALLOCATE CHANNEL c2 DEVICE TYPE DISK FORMAT '/backup/oracle/%d_%T_%U.bkp';
  BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 DATABASE TAG 'DAILY_LEVEL_1';
  BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL NOT BACKED UP 2 TIMES TAG 'ARCHIVELOG_BACKUP';
  BACKUP CURRENT CONTROLFILE TAG 'CONTROLFILE_BACKUP';
  RELEASE CHANNEL c1;
  RELEASE CHANNEL c2;
}
CROSSCHECK BACKUP;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT OBSOLETE;

RESTORE DATABASE VALIDATE;
RESTORE ARCHIVELOG ALL VALIDATE;
VALIDATE CHECK LOGICAL DATABASE;

SELECT name, database_role, open_mode, protection_mode, protection_level
FROM v$database;

SELECT dest_id, status, error, recovery_mode, destination
FROM v$archive_dest_status
ORDER BY dest_id;

SELECT name, value, unit, time_computed
FROM v$dataguard_stats
ORDER BY name;

SELECT process, status, thread#, sequence#, block#, blocks
FROM v$managed_standby
ORDER BY process;

DGMGRL> CONNECT sys@primary_db
DGMGRL> SHOW CONFIGURATION;
DGMGRL> VALIDATE DATABASE primary_db;
DGMGRL> VALIDATE DATABASE standby_db;
DGMGRL> SWITCHOVER TO standby_db;
DGMGRL> SHOW CONFIGURATION;

[mysqld]
server_id = 101
bind-address = 0.0.0.0
port = 3306

innodb_buffer_pool_size = 8G
innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

log_bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
log_replica_updates = ON

slow_query_log = ON
long_query_time = 1
log_error = /var/log/mysql/error.log

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Aborted_connects';
SHOW BINARY LOGS;
SHOW PROCESSLIST;
SELECT user, host, plugin, account_locked FROM mysql.user ORDER BY user, host;

CREATE USER 'repl'@'10.10.40.%' IDENTIFIED BY 'change-me';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.10.40.%';
FLUSH PRIVILEGES;

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '10.10.40.10',
  SOURCE_PORT = 3306,
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'change-me',
  SOURCE_AUTO_POSITION = 1;

START REPLICA;
SHOW REPLICA STATUS\G

INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';

SET GLOBAL rpl_semi_sync_source_enabled = 1;
SET GLOBAL rpl_semi_sync_replica_enabled = 1;
SET GLOBAL rpl_semi_sync_source_timeout = 10000;

SHOW VARIABLES LIKE 'rpl_semi_sync%';
SHOW STATUS LIKE 'Rpl_semi_sync%';

[mysqld]
binlog_format = ROW
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2
bind-address = 0.0.0.0

wsrep_on = ON
wsrep_provider = /usr/lib/galera/libgalera_smm.so
wsrep_cluster_name = prod_galera
wsrep_cluster_address = gcomm://10.10.50.10,10.10.50.11,10.10.50.12
wsrep_node_address = 10.10.50.10
wsrep_node_name = galera-node-01
wsrep_sst_method = mariabackup
wsrep_sst_auth = sst_user:change-me

SHOW STATUS LIKE 'wsrep_cluster_status';
SHOW STATUS LIKE 'wsrep_cluster_size';
SHOW STATUS LIKE 'wsrep_ready';
SHOW STATUS LIKE 'wsrep_connected';
SHOW STATUS LIKE 'wsrep_local_state_comment';
SHOW STATUS LIKE 'wsrep_flow_control_paused';

backup_dir="/backup/postgres/$(date +%Y%m%d_%H%M%S)"
mkdir -p "$backup_dir"

pg_basebackup \
  --host=127.0.0.1 \
  --port=5432 \
  --username=backup_user \
  --pgdata="$backup_dir" \
  --format=tar \
  --gzip \
  --checkpoint=fast \
  --wal-method=stream \
  --progress

restore_command = 'cp /srv/db-platform/postgres/wal_archive/%f %p'
recovery_target_time = '2026-06-01 10:41:59+02'
recovery_target_action = 'promote'

RESTORE DATABASE VALIDATE;
RESTORE ARCHIVELOG FROM TIME "SYSDATE-1" VALIDATE;
RECOVER DATABASE VALIDATE;