What are the features of working with replication in databases?

• Replication methods:
  • Master-Slave: One server (master) handles writes, then asynchronously or synchronously replicates changes to one or more servers (slaves), which handle only reads.
  • Master-Master: Multiple servers can handle writes and exchange changes with each other.
• Synchronization:
  • Synchronous replication: Write is confirmed to the client only after it has been successfully replicated to all or a specified number of replicas. Ensures data integrity but may increase latency.
  • Asynchronous replication: Write is confirmed to the client immediately after writing to the master. Replication occurs later. Higher performance but potential data loss if the master fails before replication.
  • Semi-synchronous replication: A compromise where confirmation occurs after writing to the master and at least one replica.
• Conflict resolution: In Master-Master replication or during network partitioning, conflicts may arise when the same data is changed simultaneously. Resolution methods:
  • Last Write Wins (LWW): The last write wins.
  • Merge: Combining changes.
  • Conflict Resolution Logic: Application-specific logic to resolve conflicts.
• Replication lag: The time difference between writing to the master and its appearance on the replica. Can affect data consistency when reading from slaves.
• Replication topologies: Chain, star, ring, and others. Choice depends on scalability and availability requirements.
• Monitoring: Continuous monitoring of replication status, lag, errors, and server load is necessary.
• Fault tolerance: Replication increases data availability. Failover to slaves is possible in case of master failure.
• Scalability: Replication allows distributing read load across multiple servers.

Example of configuring synchronous replication in PostgreSQL (partial config):

// postgresql.conf
// Enable WAL archiving for replication
wal_level = replica
archive_mode = on
archive_command = 'cp %p /path/to/archive/%f' // Adjust command for environment
// Max number of sender processes
max_wal_senders = 10
// Replica identification
wal_sender_timeout = 60s

Example of configuring a slave in PostgreSQL for streaming replication:

// recovery.conf (for older versions), or configuration via primary_conninfo
// Setting up connection to master
primary_conninfo = 'host=master_ip port=5432 user=replication_user password=replication_password application_name=my_replica'
// Starting WAL recovery
restore_command = 'cp /path/to/archive/%f %p' // Adjust command for environment
// Master mode
standby_mode = on

Comparison table of Master-Slave and Master-Master replication types:

Feature	Master-Slave	Master-Master
Write handling	Only master	All servers
Read handling	Master and slaves	All servers
Setup complexity	Relatively simple	More complex
Conflict resolution	Not required (except master failure)	Requires logic or strategy
Scalability of writes	Limited by master capacity	Better scalability
Availability on failure	Reads available on slaves, writes unavailable	Writes can be available on other masters