Exploring the Key Differences Between pglogical and Native Logical Replication in PostgreSQL

Hey, PostgreSQL enthusiasts! 🌟 Ready to dive into the fascinating world of logical replication?

Whether you’re a database guru or just dipping your toes into the PostgreSQL pool, this quick guide will clarify the differences between Native and pglogical logical replication. Let's make it as fun as it is informative!

1. Physical vs. Logical: The Slot Scoop

Physical Replication: When you’re using good ol’ streaming WAL replication, the pg_replication_slots.slot_type will be ‘physical’.

Logical Replication: For logical replication, it’s ‘logical’. Easy enough, right? But here’s the twist: the pg_replication_slots.plugin changes based on the logical replication type. 🌀

2. Plugin Playtime

Native Logical Replication: If you see pgoutput, you’re in the native Pub/Sub model (available from v10+).

pglogical Extension: If it says pglogical_output, you’re rocking the pglogical extension.

3. Migration Magic

Database Migration Services (DMS): Various Cloud Service Providers (CSPs) use logical replication to whisk your data away to new homes. 🏡✨

4. CDC Tools Galore

Change Data Capture (CDC): Different tools come with their own logical replication plugins. They’re like different flavors of your favorite ice cream. 🍦

5. DML No-Go on Physical Standbys

Physical Standbys: Trying to perform DMLs? You’ll hit a brick wall with this error: "cannot execute INSERT in a read-only transaction". Ouch!

6. DML Freedom on Logical Standbys

Logical Standbys: Perform DMLs to your heart’s content! 📝✔️

7. Logical Standbys as Migration Sources

AWS/GCP Migration: Logical standbys can be the source for DMS in both AWS and GCP. 🚀

8. Physical Standbys and DMS Jobs

CSP Limitation: Physical standbys in AWS/GCP/Azure? Sorry, they can't be DMS sources. 😞

9. Recovery Status Check

Logical Replicas: pg_is_in_recovery() returns ‘f’, and pg_is_wal_replay_paused() will throw an error. Drama!

Physical Standbys: pg_is_in_recovery() returns ‘t’, and pg_is_wal_replay_paused() returns ‘f’ if all’s smooth sailing. Plus, pg_stat_wal_receiver gives you the deets.

10. Sequences: To Replicate or Not?

pglogical: It replicates sequences! Use pglogical.replication_set_add_sequence and pglogical.replication_set_add_all_sequences to get those sequences synced.

Native Replication: Not so much. Sorry!

11. Selective Row Replication

pglogical: Offers row filtering at both publisher and subscriber sides.

Native Replication: Supports row filter at the publisher side from Postgres v15 onwards.

12. Column Replication

pglogical: Allows selective replication of table columns at the publisher side.

13. Data Gathering & Conflict Resolution

pglogical: Gather data from multiple upstream servers and resolve conflicts with pglogical.conflict_resolution. It’s like being a mediator for your data!

14. Primary Key Predicament

pglogical: No primary keys? Only INSERTs get replicated. No UPDATEs or DELETEs.

Native Logical Replication: Allows tables without primary keys if they have REPLICA IDENTITY FULL set.

15. Schema Sync

pglogical: Automatically copies schema at the subscriber node using synchronize_structure of pglogical.create_subscription.

Native Replication: No automatic schema or DDL replication. Time to roll up your sleeves!

16. Table Resync

pglogical: Resync a single table that’s fallen behind with pglogical.alter_subscription_resynchronize_table.

Native Replication: Requires a full subscription refresh. Yikes!

And there you have it! A whirlwind tour of Native vs. pglogical Logical Replication in PostgreSQL. Keep these quick facts handy, and you’ll be navigating your replication needs like a pro in no time. 🚀

Stay tuned for more PostgreSQL adventures, and don’t forget to share your thoughts and questions in the comments below!