Logical replication basics in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is logical replication in PostgreSQL?

Logical replication is a method to copy data changes from one database to another in real-time, using a publish-subscribe model. It replicates data at the level of individual changes (like insert, update, delete) rather than copying entire files.

beginner

What are the main components involved in logical replication?

The main components are: <br>1. Publisher: The source database that sends data changes.<br>2. Subscriber: The target database that receives and applies changes.<br>3. Publication: Defines which tables and changes to send.<br>4. Subscription: Defines which publications to receive.

beginner

How do you create a publication for logical replication?

Use the SQL command: <br>CREATE PUBLICATION my_pub FOR TABLE table_name;<br>This tells PostgreSQL to publish changes for the specified table.

beginner

How do you create a subscription to receive changes?

Use the SQL command: <br>CREATE SUBSCRIPTION my_sub CONNECTION 'conninfo' PUBLICATION my_pub;<br>This connects to the publisher and starts receiving changes.

beginner

Can logical replication replicate schema changes automatically?

No, logical replication only replicates data changes (inserts, updates, deletes). Schema changes like adding or dropping columns must be done manually on both publisher and subscriber.

Which component in logical replication sends data changes?

A Publisher

B Subscriber

C Subscription

D Publication

The publisher is the source database that sends data changes to subscribers.

What SQL command creates a publication for a table named 'orders'?

A CREATE SUBSCRIPTION orders_sub FOR TABLE orders;

B CREATE SUBSCRIPTION orders_pub FOR TABLE orders;

C CREATE PUBLICATION orders_sub FOR TABLE orders;

D CREATE PUBLICATION orders_pub FOR TABLE orders;

CREATE PUBLICATION orders_pub FOR TABLE orders; creates a publication named 'orders_pub' for the 'orders' table.

Does logical replication copy entire database files?

A Yes, it copies all files.

B No, it copies only data changes.

C Yes, but only schema files.

D No, it copies only schema changes.

Logical replication copies only data changes like inserts, updates, and deletes, not entire files.

Which command connects a subscriber to a publication?

A CREATE PUBLICATION

B ALTER SUBSCRIPTION

C CREATE SUBSCRIPTION

D DROP SUBSCRIPTION

CREATE SUBSCRIPTION connects the subscriber to the publisher's publication.

Are schema changes replicated automatically in logical replication?

A No, never.

B Yes, always.

C Only if enabled explicitly.

D Only for certain data types.

Schema changes must be applied manually; logical replication does not replicate them automatically.

Explain the roles of publisher and subscriber in logical replication.

Think about who sends and who receives data.

Describe how to set up logical replication between two PostgreSQL databases.

Focus on the SQL commands and their purpose.