Backup and Recovery

Back Up All PostgreSQL Databases with pg_dumpall Before a major-version upgrade or a disaster-recovery rehearsal, a DBA needs a single artifact that captures the whole cluster: every database, plus the global objects that live outside any one database. pg_dumpall produces exactly that — one plain-SQL script that …

Monitor PostgreSQL WAL Archiver Status with pg_stat_archiver pg_stat_archiver is the canonical place to check whether PostgreSQL's continuous archiving is keeping up — a single-row view that exposes last_archived_wal, last_failed_wal, and the rolling failure count without parsing log files. On a healthy cluster the row …

PostgreSQL Physical Backups with pg_basebackup Seeding a new streaming standby and starting a point-in-time recovery chain are the same job at the first step: copy the whole cluster, consistently, while it keeps serving traffic. pg_basebackup does that over a normal replication connection, with no downtime and no …

Importing Data into an Existing PostgreSQL Database Managing your PostgreSQL database often involves importing data from backups or external sources. This post explores a simple yet effective command-line approach to achieve this: psql -d anexistingdb -f dump.sql. We'll break down its components, explain its …

Backing Up a Remote PostgreSQL Database with pg_dump The database lives on a managed host three networks away, the only backup is whatever the provider snapshots, and nobody has ever tried to restore from it. A logical dump taken from a laptop or a jump box fixes that in one command, because pg_dump is an ordinary …