Skip to content

EMS DB Operation Runbook

1. How to Do DB Schema Changes

When adding/changing columns, tables, or indexes in a replicated database environment, follow these rules:

General Principles

  • Never/Avoid modify or delete existing columns or tables. Always use addition instead of modification/deletion.

Steps for Schema Changes (with Replication)

Since EMS uses pglogical replication, schema changes must follow a specific order to avoid replication breakage:

  1. Add the column/table/index on the target (inactive) side first.
  2. Then add the column/table/index on the source (active) side.

Some extreame cases may require additional steps, such as switching replication modes from table-level to column-level. Configure table replication to column-level in pglogical.psql - line 258

2. How to Fix Replication Mismatch

When replication gets out of sync between publisher and subscriber:

pglogical Auto Fix

pglogical has a built-in mechanism to detect and auto-fix replication mismatches. Check pglogical status to see if it has self-recovered.

Replication is based on the WAL (Write-Ahead Log) files located at: /pgwal

DB log files are also used during replication. The replication process reads from these WAL logs to replay changes on the subscriber side. Check the WAL log directory to ensure logs are not corrupted or missing.

3. How to Start/Stop Replication

TOBE ADDED

4. Full Disk - How to Clear

Expand Storage with a New Volume

When the disk is full, create a new larger volume and migrate data to it:

  1. Create a new larger volume, then attach it to the DB instance in OCP Dashboard.
  2. Migrate the data using the procedure documented in move_big_table.sql.
  3. Verify data integrity after migration.
  4. Remove the old volume.

Safe Directories for Cleanup

  • /pgarchive — This is the only directory where files can be safely deleted to reclaim space.
  • Do NOT delete files from /pgdata or /pgwal as this will cause data loss or replication failure.

5. DB Log Configuration

The PostgreSQL configuration file is located at:

/pgdata/default/postgresql.conf

Slow Query Logging

The following setting logs all queries that take longer than 100ms:

log_min_duration_statement = 100ms

This is useful for identifying slow queries that may cause performance degradation. Check the DB logs for entries exceeding this threshold when troubleshooting performance issues.