CodeBlanks Logo Icon CodeBlanks
Login Register

What is replication?

Copy data to another server.

What is replication?

Replication copies data to multiple nodes. It provides read scaling and high availability. If one node fails, another takes over.

CREATE PUBLICATION pub_orders
FOR TABLE orders;

Replication copies data.

The publication pub_orders is created.

What is replication? — details

The publisher publishes the table, the subscriber pulls copies. This is logical replication; in physical replication the whole database is copied block by block.

Practice What is replication?

Create the pub_orders publication for the orders table: CREATE PUBLICATION pub_orders FOR TABLE orders.

Starter code

query.sql
-- replication
CREATE PUBLICATION pub_orders

Sample solution

query.sql
CREATE PUBLICATION pub_orders FOR TABLE orders;

Expected output

CREATE PUBLICATION

Questions and answers

Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.

1 What does replication provide?

  • Less disk space
  • High availability Correct answer
  • Faster writes
  • Column compression

Explanation: Replication provides high availability.

2 Create a publication

Question 
CREATE PUBLICATION pub_orders FOR TABLE ____
Answer 
CREATE PUBLICATION pub_orders FOR TABLE orders

Explanation: CREATE PUBLICATION … FOR TABLE orders publishes changes of the orders table to subscribers.

3 What is replication: match the terms

Term Meaning
replication replication
publisher publisher
subscriber subscriber
node node

All lessons in Expert

1 Database design

  1. ER model
  2. Cardinality
  3. Surrogate keys
  4. Natural keys
  5. Denormalization
  6. Summary - Design

2 Index tuning

  1. B-tree structure
  2. Covering index
  3. Partial index
  4. Index selectivity
  5. Index maintenance
  6. Summary - Index tuning

3 Query optimizer

  1. What is an optimizer?
  2. Statistics
  3. Cost model
  4. Join strategies
  5. Plan cache
  6. Summary - Optimizer

4 Project: Performance tuning

  1. Scenario design
  2. Reading plans
  3. Adding indexes
  4. Rewriting queries
  5. Comparing results

5 Replication and sharding

  1. What is replication?
  2. Master-slave
  3. Sync vs async
  4. What is sharding?
  5. Shard key
  6. Summary - Replication and sharding

6 Distributed and big data

  1. Distributed queries
  2. MPP
  3. Columnar storage
  4. SQL on Hadoop
  5. SQL vs NoSQL
  6. Summary - Distributed and big data

7 Security and backup

  1. SQL injection
  2. Parameterised queries
  3. Authorisation
  4. Backup
  5. Recovery
  6. Summary - Security and backup

8 Expert final

  1. Design challenge
  2. Optimization challenge
  3. Security challenge
  4. Backup challenge