Learn SQL with PostgreSQL - Backup, Replication & PgBouncer

#Introduction

Welcome to episode 19 of the Learn SQL with PostgreSQL series! We've built a database that's fast, secure, and scalable. But there's one question that decides production's fate: what happens if the server dies, the disk fails, or someone accidentally drops a table? The answer to all of that is backup and replication — two pillars of production readiness that aren't negotiable.

Backup is the ultimate safety net: a copy of the data that can be restored whenever needed. Replication is the parallel standby system: a copy that keeps living and can take over if the primary falls. And behind both lies one silent enemy: connections exploding in number — which is where PgBouncer comes in as a connection pooler.

In this episode, we'll cover logical backup strategies with pg_dump, pg_dumpall, and pg_restore, physical backup with Write-Ahead Logging (WAL) for Point-In-Time Recovery (PITR), physical streaming replication and logical replication, then connection pooling with PgBouncer to prevent connection exhaustion.

#Database Backup & Restore Strategies

There are two complementary backup approaches: logical and physical.

#Logical Backup: pg_dump and pg_restore

pg_dump produces an SQL dump (or archive format) of a single database. It works at the logical level — structure and data are exported as SQL statements.

pg_dump -U postgres -h localhost -d shop -Fc -f shop.dump

The -Fc flag produces the custom format (compressed, selectively restorable). To back up an entire cluster (including roles and database definitions), use pg_dumpall:

pg_dumpall -U postgres -h localhost -f all.sql

Restore with pg_restore for the custom format, or pipe straight into psql for plain SQL:

pg_restore -U postgres -h localhost -d shop --clean --if-exists shop.dump

psql -U postgres -h localhost -d shop < all.sql

#Physical Backup & WAL: Point-In-Time Recovery

Physical backup copies the raw data files — far faster for large databases. The combination of pg_basebackup + WAL enables Point-In-Time Recovery (PITR): restoring the database to a specific moment, even after a deliberate DROP TABLE.

pg_basebackup -U postgres -h primary-host -D /backup/base -P

Write-Ahead Logging (WAL) is the ledger of every change — continuously shipped to the WAL archive. With a base backup + complete WAL, you can "replay" the database to a specific second:

1. Base backup lengkap di waktu T0
2. Arsip WAL menerima semua perubahan setelah T0
3. Recovery: restore base backup, lalu replay WAL sampai target waktu
4. Database pulih ke momen yang diinginkan (misal 5 menit sebelum DROP TABLE)

This is the main difference between logical and physical: logical backup = a photograph; physical + WAL = a VCR that can be rewound.

#PostgreSQL High Availability & Replication

Replication creates a database copy that stays updated automatically — the foundation of high availability.

#Physical Streaming Replication

Physical streaming replication copies the WAL in real-time from the primary (read-write) to a standby replica (read-only). The standby is byte-for-byte identical to the primary.

Primary (read-write)  ---WAL stream--->  Standby (read-only)

The key setup: the primary has wal_level = replica and max_wal_senders, then the standby is configured via primary_conninfo and bootstrapped from pg_basebackup. If the primary falls, the standby can be promoted to a new primary (failover) — downtime drops to seconds.

#Logical Replication

Logical replication works at the logical level, per table, with a publish-subscribe model: the publisher publishes specific tables, the subscriber receives them. Its advantages:

• Replication per table or per subset — not necessarily the whole database.
• The primary and subscriber can run different PostgreSQL versions.
• The subscriber can be written to (not necessarily read-only) — useful for multi-source aggregation.

CREATE PUBLICATION shop_pub FOR TABLE orders, order_items;

CREATE SUBSCRIPTION shop_sub
CONNECTION 'host=primary-host dbname=shop user=replicator'
PUBLICATION shop_pub;

Logical replication becomes the choice when you want certain data to flow to another database — e.g. operational data to a data warehouse or analytics cluster.

#Connection Pooling with PgBouncer

PostgreSQL handles connections with one process per connection. Each process consumes a few megabytes of memory. When thousands of requests arrive simultaneously from the application, you'll run out of memory and connections — a phenomenon called connection exhaustion.

PgBouncer is a connection pooler that stands between the application and PostgreSQL. Thousands of application connections are pooled into dozens of real connections to the server:

App x5000  -->  PgBouncer  -->  PostgreSQL (50 koneksi)

[databases]
shop = host=127.0.0.1 port=5432 dbname=shop
 
[pgbouncer]
listen_port = 6432
auth_type = scram-sha-256
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 50

The application now points to port 6432 (PgBouncer) instead of 5432 (PostgreSQL). pool_mode = transaction means a server connection is borrowed only for a single transaction — the best fit for web applications.

#Why Pooling Matters?

• Saves server memory: thousands of client connections become dozens of real connections.
• Prevents connection exhaustion: PostgreSQL no longer receives a flood of direct connections.
• Limits application connections: max_client_conn and default_pool_size provide measurable limits.

#Common Mistakes

#Closing

In this episode 19, we've prepared production readiness: logical backup with pg_dump and pg_restore, physical backup and WAL for Point-In-Time Recovery, physical streaming replication and logical replication, and connection pooling with PgBouncer to prevent connection exhaustion.

Key takeaways:

• pg_dump for per-database logical backups; pg_dumpall for the entire cluster.
• Physical backup + WAL enables Point-In-Time Recovery — like a VCR that can be rewound.
• A backup without restore testing isn't a backup — test periodically.
• Physical replication for identical HA; logical replication for per-table data distribution.
• PgBouncer turns thousands of client connections into dozens of server connections.

In the final episode, episode 20, we assemble all these skills into one: A Production-Grade E-Commerce Database Case Study — designing a complete schema for users with UUID and RLS, product catalog with JSONB and full-text search, inventory and order processing with locking and audit triggers, and analytics with materialized views.