Logical replication basics in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a publication for all tables.

PostgreSQL

CREATE PUBLICATION my_pub FOR [1];

Drag options to blanks, or click blank then click option'

Aall tables

Ball_tables

Call

Dtables

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'all' instead of 'all tables' causes syntax error.

Using 'all_tables' is not recognized.

all tables tells PostgreSQL to publish changes from all tables.

2fill in blank

medium

Complete the code to create a subscription connecting to a publisher.

PostgreSQL

CREATE SUBSCRIPTION my_sub CONNECTION '[1]' PUBLICATION my_pub;

Drag options to blanks, or click blank then click option'

Alocalhost mydb replicator secret

Bhost=localhost dbname=mydb user=replicator password=secret

Cuser=replicator password=secret dbname=mydb host=localhost

Ddbname=mydb host=localhost user=replicator

Attempts:

3 left

💡 Hint

Common Mistakes

Using space-separated values without keys causes errors.

Missing password or user key causes connection failure.

The connection string must specify host, dbname, user, and password in key=value pairs.

3fill in blank

hard

Fix the error in the publication creation by choosing the correct option.

PostgreSQL

CREATE PUBLICATION my_pub FOR TABLE [1];

Drag options to blanks, or click blank then click option'

A(users, orders)

Busers orders

Cusers

Dusers, orders

Attempts:

3 left

💡 Hint

Common Mistakes

Using spaces instead of commas between table names.

Using parentheses causes syntax error.

When specifying multiple tables, list them separated by commas without parentheses.

4fill in blank

hard

Fill both blanks to create a publication for specific tables and enable insert replication.

PostgreSQL

CREATE PUBLICATION my_pub FOR TABLE [1] WITH (publish = '[2]');

Drag options to blanks, or click blank then click option'

Ausers, orders

Bupdate

Cinsert

Ddelete

Attempts:

3 left

💡 Hint

Common Mistakes

Using spaces instead of commas between tables.

Using 'update' instead of 'insert' for insert replication.

Specify tables separated by commas and set publish to 'insert' to replicate insert operations only.

5fill in blank

hard

Fill all three blanks to create a subscription with a connection string, publication, and enable copy data.

PostgreSQL

CREATE SUBSCRIPTION my_sub CONNECTION '[1]' PUBLICATION [2] WITH (copy_data = [3]);

Drag options to blanks, or click blank then click option'

Ahost=replica dbname=shop user=replicator password=pass123

Bmy_pub

Ctrue

Dfalse

Attempts:

3 left

💡 Hint

Common Mistakes

Using incomplete connection strings.

Mismatching publication names.

Setting copy_data to false when data copy is needed.

The connection string must be complete, the publication name must match, and copy_data set to true copies existing data on subscription creation.

Practice

1. What is the main purpose of logical replication in PostgreSQL?

easy

A. To copy data changes from specific tables between databases

B. To create a full backup of the database

C. To optimize query performance

D. To encrypt data during transfer

Solution

1. Step 1: Understand logical replication concept

   Logical replication copies only data changes from selected tables, not the entire database.

2. Step 2: Compare options with concept

   Encrypting data during transfer, creating a full backup of the database, and optimizing query performance are unrelated to logical replication.

3. Final Answer:

   To copy data changes from specific tables between databases -> Option A

4. Quick Check:

   Logical replication = Copy data changes [OK]

Hint: Logical replication copies changes, not full backups [OK]

Common Mistakes:

• Confusing logical replication with physical backup
• Thinking it copies entire database
• Assuming it improves query speed

2. Which SQL command is used to create a publication for logical replication?

easy

A. CREATE REPLICATION SLOT myslot LOGICAL;

B. CREATE SUBSCRIPTION mysub CONNECTION 'conninfo' PUBLICATION mypub;

C. CREATE PUBLICATION mypub FOR ALL TABLES;

D. CREATE DATABASE mydb WITH REPLICATION;

Solution

1. Step 1: Identify command for publication creation

   The command to create a publication is CREATE PUBLICATION followed by publication name and tables.

2. Step 2: Analyze options

   Only CREATE PUBLICATION mypub FOR ALL TABLES; creates a publication. CREATE SUBSCRIPTION mysub CONNECTION 'conninfo' PUBLICATION mypub; creates a subscription, CREATE REPLICATION SLOT myslot LOGICAL; creates a replication slot, and CREATE DATABASE mydb WITH REPLICATION; creates a database.

3. Final Answer:

   CREATE PUBLICATION mypub FOR ALL TABLES; -> Option C

4. Quick Check:

   Publication creation = CREATE PUBLICATION [OK]

Hint: Publication uses CREATE PUBLICATION, subscription uses CREATE SUBSCRIPTION [OK]

Common Mistakes:

• Mixing publication and subscription commands
• Using replication slot command for publication
• Confusing database creation with replication setup

3. Given the following commands on the publisher:

CREATE PUBLICATION mypub FOR TABLE customers;

And on the subscriber:

CREATE SUBSCRIPTION mysub CONNECTION 'host=source dbname=mydb user=replicator password=secret' PUBLICATION mypub;

What will happen when a new row is inserted into the customers table on the publisher?

medium

A. The new row will be replicated to the subscriber's customers table

B. The new row will not be replicated because subscription is missing

C. The entire customers table will be copied again

D. An error will occur because publications cannot replicate inserts

Solution

1. Step 1: Understand publication and subscription setup

   The publication includes the customers table, and the subscription connects to it, enabling replication of changes.

2. Step 2: Analyze effect of insert on replication

   Inserts on the published table are sent to the subscriber, so the new row will appear there.

3. Final Answer:

   The new row will be replicated to the subscriber's customers table -> Option A

4. Quick Check:

   Insert on published table = replicated row [OK]

Hint: Inserts on published tables replicate if subscription exists [OK]

Common Mistakes:

• Thinking subscription is missing when it is created
• Assuming full table copy on each insert
• Believing inserts are not replicated

4. You created a subscription but notice no data is replicating. Which of the following is a likely cause?

medium

A. Logical replication does not support inserts

B. The subscriber database is offline

C. You forgot to create a replication slot on the subscriber

D. The publication does not include the tables you want to replicate

Solution

1. Step 1: Check publication includes tables

   If the publication does not include the desired tables, no changes will be sent to the subscriber.

2. Step 2: Evaluate other options

   The subscriber database is offline is unlikely because subscription requires the subscriber to be online. Logical replication does not support inserts is false; inserts are supported. You forgot to create a replication slot on the subscriber is incorrect because replication slots are created on the publisher, not subscriber.

3. Final Answer:

   The publication does not include the tables you want to replicate -> Option D

4. Quick Check:

   Missing tables in publication = no replication [OK]

Hint: Ensure publication includes tables to replicate [OK]

Common Mistakes:

• Confusing replication slot location
• Assuming inserts are unsupported
• Ignoring publication table list

5. You want to replicate only changes from the orders table but exclude the order_logs table, which is large and not needed on the subscriber. How should you set up the publication?

hard

A. CREATE PUBLICATION mypub FOR TABLE orders, order_logs;

B. CREATE PUBLICATION mypub FOR TABLE orders;

C. CREATE PUBLICATION mypub FOR ALL TABLES;

D. CREATE PUBLICATION mypub FOR TABLE order_logs;

Solution

1. Step 1: Understand selective table replication

   To replicate only the orders table, the publication must include only that table.

2. Step 2: Analyze options for correct table inclusion

   CREATE PUBLICATION mypub FOR ALL TABLES; replicates all tables, including order_logs which is unwanted. CREATE PUBLICATION mypub FOR TABLE orders, order_logs; includes both tables. CREATE PUBLICATION mypub FOR TABLE order_logs; includes only order_logs, which is unwanted.

3. Final Answer:

   CREATE PUBLICATION mypub FOR TABLE orders; -> Option B

4. Quick Check:

   Include only needed tables in publication [OK]

Hint: List only needed tables in publication to exclude others [OK]

Common Mistakes:

• Using FOR ALL TABLES when exclusion is needed
• Including unwanted tables in publication
• Confusing publication and subscription roles