PostgreSQL DBA

The Zero-RPO Stack

Zero-RPO PostgreSQL Configuration — Complete Guide RPO (Recovery Point Objective) = maximum acceptable data loss measured in time. Zero-RPO means zero data loss — every committed transaction must survive a primary failure.

Failover is the process of promoting a replica to become the new primary when the original primary fails. Here's a full breakdown covering manual, automated, and pglogical-aware failover.

A replication set defines which tables and sequences to replicate: -- Create a named replication set SELECT pglogical.create_replication_set( set_name := 'my_replication_set', replicate_insert := true, replicate_update := true, replicate_delete := true, replicate_truncate := true ); -- Add specific tables to the set SELECT pglogical.replication_set_add_table( set_name := 'my_replication_set', relation := 'public.orders', synchronize_data := true -- Initial data copy ); SELECT pglogical.replication_set_add_table('my_replication_set', 'public.customers', true); SELECT pglogical.replication_set_add_table('my_replication_set', 'public.products', true); -- Add ALL tables in a schema at once SELECT pglogical.replication_set_add_all_tables( set_name := 'my_replication_set', schema_names := ARRAY['public'], synchronize_data := true ); -- Add sequences SELECT pglogical.replication_set_add_sequence( set_name := 'my_replication_set', relation := 'public.orders_id_seq', synchronize_data := true ); -- Add ALL sequences SELECT pglogical.replication_set_add_all_sequences('my_replication_set', ARRAY['public'], true);

pglogical Setup — Step by Step

Installation pglogical

Architecture Overview

pglogical is a PostgreSQL extension that provides logical replication with more features than built-in logical replication — including DDL replication, conflict handling, and row/column filtering.

Quick Decision Guide

Replication in PostgreSQL ensures data is copied from one server (primary) to one or more servers (replicas/standbys). Here's a comprehensive guide covering the main approaches.