在Kubernetes中升级Bitnami PostgreSQL镜像：TB级大数据库能否使用pg_upgrade？

Absolutely—pg_upgrade is the perfect tool for your TB-scale PostgreSQL upgrade with Bitnami images, as it’s far more efficient than pg_dump (which requires exporting/importing your entire dataset, a process that can take hours or days for large databases). Here’s a step-by-step guide tailored to Bitnami’s containerized PostgreSQL setup:

Key Prerequisites #

• Bitnami Image Compatibility: Bitnami’s PostgreSQL images include the pg_upgrade utility by default, so you don’t need to install extra packages.
• Backup First: Take a full snapshot of your existing PostgreSQL 11 PVC before starting—this is non-negotiable for large datasets. Use Kubernetes PVC snapshots or copy the entire data directory to a safe location.
• Matching Architectures: Ensure both the old (11) and new (14) Bitnami images run on the same CPU architecture (e.g., amd64) to avoid binary compatibility issues.

Step-by-Step Upgrade Process #

1. Stop the Old PostgreSQL 11 Deployment #

Scale down your existing PostgreSQL 11 deployment to 0 replicas to ensure no writes occur during the upgrade:

kubectl scale deployment your-postgres-11-deployment --replicas=0

2. Prepare a Temporary Upgrade Container #

You’ll need a container that can access both the old PostgreSQL 11 binaries and the new PostgreSQL 14 binaries. The easiest way is:

• Start a Bitnami PostgreSQL 14 container, mounting both your old 11 PVC (let’s call it postgres-11-pvc) and a new empty PVC (postgres-14-pvc) for the upgraded data.
• Copy the PostgreSQL 11 binaries from a Bitnami PostgreSQL 11 container into the 14 container.

Example commands:

# Start a temporary 14 container to run the upgrade
kubectl run pg-upgrade-temp --image=bitnami/postgresql:14 --restart=Never \
  --volume name=old-data,claimName=postgres-11-pvc --volume name=new-data,claimName=postgres-14-pvc \
  --command -- sleep infinity

# Start a temporary 11 container to copy binaries from
kubectl run pg-11-temp --image=bitnami/postgresql:11 --restart=Never --command -- sleep infinity

# Copy 11 binaries to the upgrade temp container
kubectl cp pg-11-temp:/opt/bitnami/postgresql pg-upgrade-temp:/opt/bitnami/postgresql-old

# Clean up the 11 temp container
kubectl delete pod pg-11-temp

3. Initialize the New PostgreSQL 14 Instance (Without Starting It) #

Inside the pg-upgrade-temp container, run the Bitnami setup script to initialize the new 14 data directory but skip starting the service:

kubectl exec -it pg-upgrade-temp -- /opt/bitnami/scripts/postgresql/setup.sh --init-only

4. Run pg_upgrade #

Execute the upgrade command, pointing to the old binaries, old data directory, new binaries, and new data directory:

kubectl exec -it pg-upgrade-temp -- pg_upgrade \
  -b /opt/bitnami/postgresql-old/bin \
  -B /opt/bitnami/postgresql/bin \
  -d /bitnami/postgresql-old/data \
  -D /bitnami/postgresql/data \
  -U postgres \
  -j $(nproc)  # Use all available CPU cores to speed up the process

Parameter Breakdown:

• -b: Path to PostgreSQL 11 binaries
• -B: Path to PostgreSQL 14 binaries
• -d: Old PostgreSQL 11 data directory (mounted from postgres-11-pvc)
• -D: New PostgreSQL 14 data directory (mounted from postgres-14-pvc)
• -j: Number of parallel jobs (cuts down upgrade time for large datasets)

5. Validate the Upgrade #

After pg_upgrade completes successfully, run a vacuum to update statistics and confirm everything works:

kubectl exec -it pg-upgrade-temp -- vacuumdb --all --analyze-in-stages

You can also start the PostgreSQL 14 service temporarily to run quick sanity checks (e.g., connect and query a large table):

kubectl exec -it pg-upgrade-temp -- /opt/bitnami/scripts/postgresql/start.sh
# Run your validation queries here
kubectl exec -it pg-upgrade-temp -- /opt/bitnami/scripts/postgresql/stop.sh

6. Deploy the New PostgreSQL 14 Instance #

Delete the temporary upgrade pod, then create a new PostgreSQL 14 deployment pointing to the upgraded postgres-14-pvc. Ensure your environment variables (like POSTGRES_USER, POSTGRES_PASSWORD) match the old deployment.

Post-Upgrade Tasks #

• Recreate Extensions: If you used PostgreSQL extensions (e.g., pg_stat_statements, hstore), you’ll need to recreate them in the new 14 instance.
• Update Connection Strings: Update all your application deployments to point to the new PostgreSQL 14 service.
• Monitor Performance: Keep an eye on database performance for the first few days, as some queries may need re-planning after the upgrade.

内容的提问来源于stack exchange，提问作者krobbo