↓ Skip to main content
Vonng
Background Image
  1. PostgreSQL Posts/

PostgreSQL Routine Maintenance

·130 words·1 min· ·
PostgreSQL PG Management
Ruohang Feng
Author
Ruohang Feng
Pigsty Founder, @Vonng
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

Related

Changing Engines Mid-Flight — PostgreSQL Zero-Downtime Data Migration
·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
·301 words·2 mins
PostgreSQL PG Management Performance
Although PostgreSQL provides pgbench, sometimes you need sysbench to outperform MySQL.
Batch Configure SSH Passwordless Login
·297 words·2 mins
PostgreSQL PG Management
Quick configuration for passwordless login to all machines
Wireshark Packet Capture Protocol Analysis
·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.
Common Linux Statistics CLI Tools
·2379 words·12 mins
PostgreSQL PG Management Tools
top, free, vmstat, iostat: Quick reference for four commonly used CLI tools
PostgreSQL MongoFDW Installation and Deployment
·707 words·4 mins
PostgreSQL PG Management Extension
Recently had business requirements to access MongoDB through PostgreSQL FDW, but compiling MongoDB FDW is really a nightmare.
← → Backup and Recovery Methods Overview Implementing Mutual Exclusion Constraints with Exclude → ←