PostgreSQL Routine Maintenance

Table of Contents

Cars need oil changes, databases need maintenance.

Maintenance Tasks in PG
#

For PG, there are three important maintenance tasks: backup, repack, vacuum

Backup: The most important routine work, a lifeline.
- Create base backups
- Archive incremental WAL
Repack
- Repacking tables and indexes eliminates bloat, saves space, and ensures query performance doesn’t degrade.
Vacuum
- Maintains table and database age, prevents transaction ID wraparound failures.
- Updates statistics, generates better execution plans.
- Reclaims dead tuples, saves space, improves performance.

Backup
#

Backup can use pg_backrest as an all-in-one solution, but here we consider using scripts for backup.

Reference: pg-backup

Repack
#

Repack uses pg_repack. PostgreSQL’s official repository includes pg_repack.

Reference: pg-repack

Vacuum
#

Although AutoVacuum exists, manual vacuum execution is still helpful. Check database age and report promptly when aging occurs.

Reference: pg-vacuum

Changing Engines Mid-Flight — PostgreSQL Zero-Downtime Data Migration

2018-02-06·681 words·4 mins

PostgreSQL PG Management Migration

Data migration typically involves stopping services for updates. Zero-downtime data migration is a relatively advanced operation.

Using sysbench to Test PostgreSQL Performance

2018-02-06·301 words·2 mins

PostgreSQL PG Management Performance

Although PostgreSQL provides pgbench, sometimes you need sysbench to outperform MySQL.

Batch Configure SSH Passwordless Login

2018-01-07·297 words·2 mins

PostgreSQL PG Management

Quick configuration for passwordless login to all machines

Wireshark Packet Capture Protocol Analysis

2018-01-05·982 words·5 mins

PostgreSQL PG Management Tools

Wireshark is a very useful tool, especially suitable for analyzing network protocols. Here’s a simple introduction to using Wireshark for packet capture and PostgreSQL protocol analysis.