0Pricing
PostgreSQL Performance & Query Optimization · 课时

复制策略（流式、逻辑）

了解并配置流式复制、逻辑复制等多种复制方法，以实现高可用和读扩展

复制策略（流式、逻辑） 是 CoddyKit 上的免费 PostgreSQL Performance & Query Optimization 课时。 这是第 2 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 PostgreSQL Performance & Query Optimization 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

本课时的部分内容尚未翻译，以英文显示。

Why Data Replication?

Imagine your database goes down! Replication is key to preventing data loss and ensuring your application stays online. It creates copies of your database.

It's vital for:

  • High Availability (HA): If the main server fails, a copy can take over.
  • Read Scaling: Distribute read queries across multiple servers, reducing load on the primary.

The Primary-Replica Setup

The most common replication setup is a Primary-Replica (or Master-Slave) model. One database server acts as the primary, handling all writes.

  • Primary: Processes all write operations (INSERT, UPDATE, DELETE).
  • Replica(s): Receive copies of data changes from the primary and can serve read-only queries.

This ensures data consistency and offloads read traffic.

Streaming Replication: The Basics

Streaming Replication is PostgreSQL's built-in, physical replication method. It works by continuously shipping the Write-Ahead Log (WAL) records from the primary to one or more replicas.

WAL records are low-level descriptions of every change made to the database. Replicas apply these changes to stay in sync.

WAL Shipping in Action

Here's how streaming replication works:

  • The primary server writes all database changes to its WAL files.
  • A dedicated process (WAL sender) on the primary sends these WAL records to replica servers.
  • A process (WAL receiver) on each replica receives and applies these records.

This keeps the replica's data identical to the primary's, block by block.

Setting Up Streaming Replication (Primary)

To enable streaming replication on the primary, you need to configure postgresql.conf:

  • wal_level = replica (or higher)
  • max_wal_senders = 10 (number of replicas/connections)
  • listen_addresses = '*' (allow connections from replicas)

You'll also need to set up pg_hba.conf to allow replica connections.

ALTER SYSTEM SET wal_level = 'replica';
ALTER SYSTEM SET max_wal_senders = 10;
ALTER SYSTEM SET listen_addresses = '*';

-- After these, restart PostgreSQL

Initializing a Replica with pg_basebackup

Before a replica can stream WAL, it needs a full copy of the primary's data. pg_basebackup is the standard tool for this.

It creates a consistent snapshot of the primary's data directory, which then becomes the replica's initial data.

pg_basebackup -h primary_host -p 5432 -U replication_user -D /var/lib/postgresql/data_replica -F p -Xs stream -P

Configuring the Replica

After pg_basebackup, the replica needs a few files:

  • A standby.signal file in its data directory (for PostgreSQL 12+).
  • A postgresql.conf with hot_standby = on (if you want to run queries).
  • A primary_conninfo setting in postgresql.conf to tell it where to connect to the primary.

This allows it to start up and connect as a replica.

ALTER SYSTEM SET hot_standby = 'on';
ALTER SYSTEM SET primary_conninfo = 'host=primary_host port=5432 user=replication_user password=your_password';

-- Create an empty standby.signal file:
touch /var/lib/postgresql/data_replica/standby.signal

-- Then start the replica PostgreSQL instance

Logical Replication: A Different Approach

Logical Replication is a newer, more flexible replication method introduced in PostgreSQL 10. Unlike streaming replication, it replicates data based on its logical representation (row changes, not physical blocks).

This allows for selective replication of tables or even databases, and cross-version/cross-platform replication.

Publications and Subscriptions

Logical replication uses a publish-subscribe model:

  • A Publication is created on the primary, defining which tables or changes to publish.
  • A Subscription is created on the replica, defining which publications to subscribe to.

The primary then sends logical decoding output (changes) to the subscribers, which apply them.

Setting Up Logical Replication (Example)

Here's a simplified example of setting up logical replication:

On Primary:

CREATE PUBLICATION my_pub FOR TABLE my_table;

On Replica:

CREATE SUBSCRIPTION my_sub CONNECTION 'host=primary_host dbname=mydb user=repl_user password=xyz' PUBLICATION my_pub;

Remember to configure wal_level = logical and max_replication_slots on the primary.

ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_replication_slots = 5;

-- On Primary:
CREATE PUBLICATION my_pub FOR TABLE my_table;

-- On Replica:
CREATE SUBSCRIPTION my_sub CONNECTION 'host=primary_host dbname=mydb user=repl_user password=xyz' PUBLICATION my_pub;

Replication Strategy Check

You need to set up replication for a PostgreSQL database. You want an exact, block-for-block copy of your entire database on a standby server for high availability. Which replication method is best suited for this goal?

Recap: Streaming vs. Logical

We've explored two key PostgreSQL replication strategies:

  • Streaming Replication: A physical, continuous copy of the entire database using WAL files. Great for high availability and read scaling with identical replicas.
  • Logical Replication: A flexible, publish-subscribe model replicating logical changes (rows). Ideal for selective replication, cross-version upgrades, or integrating with other systems.

Choosing the right strategy depends on your specific needs for consistency, flexibility, and performance.

常见问题解答

「复制策略（流式、逻辑）」课时是免费的吗？

是的 — 「复制策略（流式、逻辑）」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 PostgreSQL Performance & Query Optimization 课程的其余内容，请升级到 CoddyKit PRO。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

「复制策略（流式、逻辑）」这节课中我会学到什么？

了解并配置流式复制、逻辑复制等多种复制方法，以实现高可用和读扩展 你通过在浏览器中直接运行的动手代码来练习 PostgreSQL Performance & Query Optimization，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 PostgreSQL Performance & Query Optimization 需要有经验吗？

无需任何先前经验。CoddyKit 上的 PostgreSQL Performance & Query Optimization 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 2 节课，共 4 节。

「复制策略（流式、逻辑）」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 PostgreSQL Performance & Query Optimization 课中编写并运行代码吗？

能。每节 PostgreSQL Performance & Query Optimization 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

  1. 使用 PgBouncer 实现连接池
  2. 复制策略（流式、逻辑）
  3. 分片与分布式 PostgreSQL
  4. 使用热备与负载均衡扩展读取能力
← 返回 PostgreSQL Performance & Query Optimization