🦉
Programming Notes
  • My Programming Notes
  • CKA Exam Preparation
    • Certified Kubernetes Administrator
    • Setup Minikube
    • Network Design Principles
    • Role-Based Access Control (RBAC)
    • Namespace
    • Resource Quota
    • Pod
    • Deployment
    • Deployment: Rollout
    • ConfigMap
    • Service
    • Service: kubectl expose
    • Pod: Resources Management
    • Pod & Container: Quality of Service Class
    • Pod & Container: Probes
    • Limit Range
    • Scaling: Manual
    • Scaling: Horizontal Pod Autoscaler
    • Persistent Volume & Claim
    • Secret
    • Ingress: Routing
    • Ingress: TLS
    • Ingress: Rate Limit
    • Ingress: Basic Auth
    • Ingress: CRD (Custom Resource Definition)
    • Job
    • CronJob
    • Mutli-Node Cluster
  • Golang
    • Generics
    • Context
    • Goroutines and Channels in Go
    • Goroutine: Concurrency vs Parallelism
    • Goroutine: Performance & Tradeoffs
    • JSON: omitzero
  • Rust
    • Arrays & Slices
    • Closures
    • Generics & Traits
    • Iterators
    • Run Code Simultaneously
    • String vs &str
    • Tests
    • Rustlings Exercises
      • Variables
      • Functions
      • If
      • Primitive Types
      • Vectors
      • Move Semantics
      • Structs
      • Enums and Matching Pattern
      • Strings
      • Modules
      • Hashmaps
      • Options
      • Error Handling
      • Generics
      • Traits
      • Lifetimes
      • Tests
      • Iterators
      • Smart Pointers
      • Threads
      • Macros
      • Quiz 1
      • Quiz 2
      • Quiz 3
  • Software Engineering
    • CAP Theorem
    • Circuit Breaker
    • Decoupling
    • GraphQL: Query Caching
    • HMAC Signature
    • Idempotency
    • Monolith VS Microservice
    • OWASP Top 10 2021
    • PCI DSS
    • PostgreSQL: Partitioning
    • PostgreSQL: Replication
    • Protobuf & gRPC
    • Redis: Streams
    • Resource Scaling
    • Signed URL
    • SOLID
    • Stack VS Heap
    • Stateful VS Stateless
  • Site Reliability Engineering
    • Chaos Engineering
    • Distributed Tracing
    • Kubernetes (k8s)
    • SLA, SLO, and SLI Metrics
    • Site Reliability Engineer
  • Others
    • FFMPEG Cheat sheet
Powered by GitBook
On this page
  • Streaming Replication
  • Use Case: High Availability and Disaster Recovery
  • Logical Replication
  • Use Case: Data Sharing Across Systems
  • References
  1. Software Engineering

PostgreSQL: Replication

Replication is the process of copying data from one PostgreSQL server (the primary) to one or more servers (replicas). It ensures high availability and load distribution.

Types of Replication in PostgreSQL:

  • Streaming Replication – Real-time log-based replication.

    • Synchronous Replication – Ensures data is written to both primary and replica before acknowledging.

    • Asynchronous Replication – Data is sent to the replica but does not wait for confirmation.

  • Logical Replication – Table-level replication for selective data sharing.

Streaming Replication

Streaming replication uses WAL (Write-Ahead Log) to send real-time changes from the primary database to one or more standby servers. It provides near-instantaneous replication for all changes. Streaming replication can be either synchronous or asynchronous.

Synchronous Replication: The primary waits for confirmation from the replica before committing transactions. This guarantees no data is lost if the primary fails.

synchronous_standby_names = 'standby1'
synchronous_commit = 'on'

Asynchronous Replication: The primary database does not wait for the replica to confirm. It offers faster performance but may cause data lag on replicas.

synchronous_standby_names = 'standby1'
synchronous_commit = 'off'

Use Case: High Availability and Disaster Recovery

A fintech company needs to ensure zero downtime for critical customer transactions. If the primary database fails, a standby replica can quickly take over. This enable quick recovery and minimal data loss.

Example Scenario:

  • Primary Database: Handles live customer transactions.

  • Standby Database: Fails over automatically in case of hardware failure.

Logical Replication

Logical replication is a method of replicating data objects and their changes, based upon their replication identity (usually a primary key).

Logical replication uses a publish and subscribe model with one or more subscribers subscribing to one or more publications on a publisher node. Subscribers pull data from the publications they subscribe to and may subsequently re-publish data to allow cascading replication or more complex configurations.

Use Case: Data Sharing Across Systems

A retail company wants to share specific tables (e.g., orders, products) with its BI (Business Intelligence) platform for reporting, while keeping customer data private.

Example Scenario:

  • Primary Database: Handles e-commerce transactions.

  • Replica Database: Only replicates the orders table for analytics.

  • Benefit: Selective replication for cross-system data sharing.

Example Setup:

Set up a logical publication on the primary:

CREATE PUBLICATION my_pub FOR TABLE orders;

On the replica, subscribe to the publication:

CREATE SUBSCRIPTION my_sub
CONNECTION 'host=primary_db user=replicator dbname=ecommerce'

References

PreviousPostgreSQL: PartitioningNextProtobuf & gRPC

Last updated 3 months ago

https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION
https://www.postgresql.org/docs/current/logical-replication.html