Essential DBA Holiday Checklist: Keep Your Databases Safe During Chinese New Year

Background

If you work with databases, recent news about data loss incidents highlights the critical role of DBAs in safeguarding data and preventing SQL injection, accidental deletions, and information leaks.

From the Journey of a DBA

Before heading home for the holiday, a DBA should prepare thoroughly, just like checking a vehicle before a long trip.

Prepare food and water for the journey.

Bring emergency power supplies, tow cables, and anti‑slip chains.

Purchase insurance.

Perform vehicle maintenance and fix minor issues.

Check tire pressure, fuel, and windshield washer fluid.

Carry enough cash.

Pay phone bills to ensure connectivity.

Charge phone and bring a power bank.

Turn off gas, heating, and water taps.

Shut down all appliances.

Water plants.

Enable remote cameras for monitoring.

DBA Holiday Essentials

The DBA "Nine‑Yang" guide is divided into four parts: Daily, Pre‑Holiday, Holiday, and Post‑Holiday.

Daily

DBAs must continuously protect data and ensure database stability.

Establish and enforce database security policies.

Establish and enforce database management standards.

Establish and enforce database development standards.

Build automated monitoring systems.

Implement automated inspection, backup, HA, disaster‑recovery, and off‑site backup solutions.

Define network‑blocking and emergency mechanisms for holidays.

Pre‑Holiday

1. Conduct an additional routine inspection before the holiday, similar to a vehicle check.

Typical inspection items include:

<code>|+++++++++++++++++++++|
|       Operating System Info       |
|+++++++++++++++++++++|
-> Hostname: 
-> Ethernet info: 
-> IP address: 
-> Routing info: 
-> Kernel version: 
-> Memory (MB): 
-> CPU: 
-> Block devices: 
-> Topology: 
-> Process tree: 
-> OS config files: 
-> /etc/sysctl.conf 
-> /etc/security/limits.conf 
-> /etc/security/limits.d/*.conf 
-> /etc/sysconfig/iptables 
-> /etc/fstab 
-> /etc/rc.local 
-> /etc/selinux/config 
-> /boot/grub/grub.conf 
-> /var/spool/cron user cron configs 
-> chkconfig --list 
-> iptables -L -v -n -t filter 
-> iptables -L -v -n -t nat 
-> iptables -L -v -n -t mangle 
-> iptables -L -v -n -t raw 
-> sysctl -a 
-> mount info 
-> selinux info 
-> Disable Transparent Huge Pages (THP) 
-> Disk SMART info (root required) 
-> /var/log/boot.log 
-> /var/log/cron (root) 
-> /var/log/dmesg 
-> /var/log/messages (root) 
-> /var/log/secure (root) 
-> /var/log/wtmp 
|+++++++++++++++++++++|
|          Database Info          |
|+++++++++++++++++++++|
-> Database version: 
-> Installed plugin versions: 
-> Data types used: 
-> Objects created: 
-> Object size distribution: 
-> OS scheduled tasks for current user: 
-> pg_hba.conf MD5: 
-> pg_hba.conf config: 
-> postgresql.conf MD5: 
-> postgresql.conf config: 
-> Custom parameters (user/database level): 
|+++++++++++++++++++++|
|   Database Error Log Analysis   |
|+++++++++++++++++++++|
-> Retrieve error log info: 
-> Connection request stats: 
-> Authentication failure stats: 
|+++++++++++++++++++++|
|   Slow Query Log Analysis   |
|+++++++++++++++++++++|
-> Slow query statistics: 
-> Top 10 slow queries execution time (ms): 
-> Bottom 10 slow queries execution time (ms): 
-> auto_explain stats: 
|+++++++++++++++++++++|
|   Database Space Usage   |
|+++++++++++++++++++++|
-> Filesystem free space: 
-> Tablespace directories: 
-> Tablespace usage: 
-> Database usage: 
-> Top 10 size objects: 
|+++++++++++++++++++++|
|   Database Connection Analysis   |
|+++++++++++++++++++++|
-> Current activity: 
-> Total remaining connections: 
-> User connection limits: 
-> Database connection limits: 
|+++++++++++++++++++++|
|   Database Performance Analysis   |
|+++++++++++++++++++++|
-> Top 5 SQL by total_cpu_time: 
-> Tables with >4 indexes and size >10MB: 
-> Unused or rarely used indexes since last inspection: 
-> Statistics, rollback ratio, hit ratio, block read/write time, deadlocks, replication conflicts: 
-> Checkpoint and bgwriter stats: 
|+++++++++++++++++++++|
|   Database Bloat Analysis   |
|+++++++++++++++++++++|
-> Table bloat checks: 
-> Index bloat checks: 
-> Garbage data: 
-> Unreferenced large objects: 
|+++++++++++++++++++++|
|   Database Age Analysis   |
|+++++++++++++++++++++|
-> Database age: 
-> Table age: 
-> Long transactions, 2PC: 
|+++++++++++++++++++++|
|   XLOG and Streaming Replication Status   |
|+++++++++++++++++++++|
-> Archive enabled, auto‑vacuum: 
-> Archive stats: 
-> Streaming replication stats: 
-> Replication slots: 
|+++++++++++++++++++++|
|   Security and Risk Analysis   |
|+++++++++++++++++++++|
-> Password leak checks: 
-> Simple password checks: 
-> Password expiration: 
-> SQL injection risk: 
-> Rule security on user objects: 
-> Custom function security: 
-> Unlogged tables and hash indexes: 
-> Sequences with <10M remaining uses: 
-> Triggers and event triggers: 
-> Non‑alphanumeric object names: 
-> Lock wait analysis: 
-> Inheritance checks: 
|+++++++++++++++++++++|
|   Reset Statistics   |
|+++++++++++++++++++++|
-> Reset statistics: 
-> Reset pg_stat_statements: 
-> Retrieve recovery.done MD5 and config: 
-> Retrieve recovery.conf MD5 and config: 
|+++++++++++++++++++++|
|   Additional Information   |
|+++++++++++++++++++++|
-> Attachment 1: date -d '-1 day' +"%Y-%m-%d" OS sysstat stats 
-> Other suggestions: </code>

2. Scale predictable business databases and application servers to handle holiday traffic spikes.

3. Prepare standby hardware for immediate use.

4. Implement a network‑blocking period and halt changes days before the holiday.

5. Arrange 24/7 on‑call rotations so a DBA is always available.

6. Provide comprehensive training for on‑call staff to cover a broader range of services.

7. Communicate the blocking policy to business units to prevent unauthorized changes during the holiday.

During the Holiday

1. Maintain both online and offline on‑call duties, monitoring NOC metrics and logging reports.

2. Conduct thorough handovers between shifts to ensure continuity.

Post‑Holiday

After the blocking period ends, conduct a detailed post‑mortem to evaluate capacity planning, incident causes, monitoring gaps, and any unauthorized changes.

Assess whether scaling estimates were accurate and share feedback with business owners.

Identify failures, root causes, and preventive measures.

Review monitoring coverage and address gaps.

Check for policy violations and plan improvements.

Conclusion

Many organizations have similar procedures; DBAs, keep up the good work.

If you notice any inaccuracies or have suggestions, feel free to point them out.

Wishing everyone a Happy New Year!