Oracle生产集群迁移PostgreSQL：高负载下复制策略及流复制疑问

Hey there! Let's tackle your two key questions around ensuring PostgreSQL can handle your 2000 TPS workload during the migration from Oracle:

1. Best Replication Strategy: Streaming Replication vs DRBD-Based Replication #

Let's break down the pros and cons of each for your use case:

Streaming Replication (PostgreSQL Native) #

• Primary Advantages:
  • It's built directly into PostgreSQL, so no extra third-party software or complex storage layer setup is needed. This keeps maintenance and troubleshooting straightforward.
  • Minimal performance impact on the primary node: Replication works by shipping Write-Ahead Log (WAL) records to replicas after they're written to disk. For a 2000 TPS OLTP workload, this overhead is negligible.
  • Replicas can handle read-only queries (like reports or analytics), which helps offload traffic from the primary and keep your TPS targets on track.
  • Supports both asynchronous and synchronous modes: Asynchronous is the default (maximizes primary performance), while synchronous replication ensures zero data loss at the cost of slightly higher latency (you can tune this based on your consistency needs).
  • Mature tooling for failover: Tools like patroni or repmgr automate failover and replica management, reducing downtime risks.

DRBD-Based Replication #

• Primary Notes:
  • DRBD is a block-level replication tool (operates at the storage layer, not the PostgreSQL level). It mirrors entire disk partitions between nodes.
  • While it offers strong data consistency, it adds significant overhead to the primary node: Every write operation has to be synchronized to the replica's disk before completing, which can bottleneck your 2000 TPS workload.
  • Replicas can't be used for read traffic directly (since you can't mount the replicated disk on both nodes without risking corruption), so you lose the ability to offload the primary.
  • Requires additional cluster management tools (like Pacemaker) for failover, increasing overall complexity and maintenance burden.

Recommendation: For your 2000 TPS OLTP scenario, Streaming Replication is the clear best choice. It's lightweight, performance-friendly, leverages PostgreSQL's native capabilities, and gives you the flexibility to scale read traffic with replicas.

2. Streaming Replication Specific Questions #

Can the primary node handle traffic when there are no replicas? #

Absolutely! The primary node operates completely independently of replicas. Replicas are just passive consumers of WAL data for redundancy and read scaling—their absence doesn't impact the primary's ability to process all write and read requests at full capacity. You can add/remove replicas at any time without disrupting the primary's service.

Can a replica sync data lost during its downtime once it's restored? #

Yes, but it depends on how you've configured WAL retention/archiving:

• If the replica was shut down gracefully, it will remember the last WAL position it successfully synced. When it comes back online, it will automatically connect to the primary and start replicating from that point, catching up on all changes made during downtime.
• If the replica crashed unexpectedly, you'll need to ensure the primary still has the WAL records the replica needs. If you've enabled archive_mode and archive_command to store WALs in a secure location (like a network storage or object store), the replica can restore from these archived WALs to get back to the latest state before resuming streaming.
• The only caveat: If the primary has already rotated and deleted the WAL records the replica needs (based on your wal_keep_size or retention policy), you'll have to take a new base backup of the primary and restore it to the replica before it can start syncing again. To avoid this, configure wal_keep_size to a value that covers your typical replica downtime window, or use WAL archiving.

内容的提问来源于stack exchange，提问作者Nishantha Pradeep