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.

Quick Decision Guide

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.

Architecture Overview

Installation pglogical

pglogical Setup — Step by Step

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);

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.

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.