Logical replication basics in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

What is the primary purpose of logical replication in PostgreSQL?

Logical replication allows data to be copied between PostgreSQL servers. What is its main use?

A To replicate data changes at the table level for selective synchronization

B To compress data for faster network transfer

C To create physical backups of the database files

D To replicate the entire database cluster including configuration files

💡 Hint

Think about what logical replication copies: is it the whole database or parts of it?

Logical replication copies data changes at the table level, allowing selective replication of specific tables. It does not replicate the entire cluster or configuration files.

❓ query_result

intermediate

What rows are replicated by default in logical replication?

Given a publication created with CREATE PUBLICATION pub1 FOR TABLE employees;, which rows will be replicated to the subscriber?

PostgreSQL

CREATE PUBLICATION pub1 FOR TABLE employees;

A All rows existing before and after the publication creation

B All rows inserted, updated, or deleted after the publication is created

C Only rows inserted after the publication is created

D Only rows updated after the publication is created

💡 Hint

Think about whether logical replication copies existing data or just changes.

Logical replication replicates data changes (inserts, updates, deletes) that happen after the publication is created. It does not automatically copy existing rows.

📝 Syntax

advanced

Which command correctly creates a subscription to replicate from a publisher?

Choose the correct SQL command to create a subscription named sub1 connecting to a publisher at host=192.168.1.10 dbname=company for publication pub1.

A CREATE SUBSCRIPTION sub1 WITH CONNECTION 'host=192.168.1.10 dbname=company' PUBLICATION pub1;

B CREATE SUBSCRIPTION sub1 CONNECTION 'host=192.168.1.10 dbname=company' FOR PUBLICATION pub1;

C CREATE SUBSCRIPTION sub1 CONNECTION 'host=192.168.1.10 dbname=company' PUBLICATION pub1;

D CREATE SUBSCRIPTION sub1 CONNECTION 'host=192.168.1.10 dbname=company' TO PUBLICATION pub1;

💡 Hint

Check the exact syntax for the CREATE SUBSCRIPTION command in PostgreSQL.

The correct syntax uses CONNECTION followed by the connection string and PUBLICATION followed by the publication name without extra keywords.

❓ optimization

advanced

How to reduce replication lag in logical replication?

You notice replication lag between publisher and subscriber. Which approach helps reduce lag?

A Reduce the <code>max_replication_slots</code> to 1

B Increase the <code>wal_sender_timeout</code> to a higher value

C Disable the replication slot on the publisher

D Use synchronous_commit = 'remote_apply' on the publisher

💡 Hint

Consider how commit settings affect replication speed and confirmation.

Setting synchronous_commit to remote_apply ensures transactions wait until changes are applied on the subscriber, reducing lag but possibly increasing transaction latency.

🔧 Debug

expert

Why does the subscription fail with error 'could not find replication origin'?

A subscription fails to start with the error: ERROR: could not find replication origin. What is the most likely cause?

A The subscriber's replication slot was dropped or corrupted

B The publication does not exist on the publisher

C The connection string has wrong credentials

D The subscriber database is missing the table being replicated

💡 Hint

Think about what replication origin means and what happens if it is missing.

The error usually means the replication slot or origin on the subscriber side is missing or corrupted, causing the subscription to fail.