Physical Replication (Streaming)

Physical replication in PostgreSQL creates an exact, byte-for-byte copy of your primary database server. Think of it as mirroring the entire data directory and all changes that happen.

This method is highly robust for disaster recovery and provides a consistent read-only copy of your data, known as a standby server. If your primary server fails, you can promote the standby to become the new primary.

Streaming replication is a form of physical replication where the primary server continuously sends its Write-Ahead Log (WAL) records to the standby server. The standby then applies these WAL records in real-time.

This ensures the standby is always up-to-date with the primary, minimizing data loss in case of a failure. It's like a constant stream of updates flowing from one database to another.

To enable streaming replication, the primary server must generate enough WAL information. The wal_level parameter controls this.

It must be set to replica (or logical for more advanced features) in your postgresql.conf. The default minimal level is not sufficient for replication.

Let's check the current wal_level:

The primary server needs processes to send WAL data to standby servers. These are called WAL sender processes.

The max_wal_senders parameter determines the maximum number of concurrent connections from standby servers. Set this to at least the number of standbys you plan to have, plus some for potential cascading standbys.

Check your current setting:

It's best practice to create a dedicated PostgreSQL user for replication. This user needs the REPLICATION privilege, which allows it to connect and stream WAL data.

This separation enhances security and makes it clear which connections are for replication purposes.

Here's how you might create one (replace with a strong password):

The primary server needs to allow connections from the standby server. This is configured in the pg_hba.conf file, which controls client authentication.

You'll need to add an entry for your replication user, specifying the IP address or range of your standby server(s). This tells PostgreSQL to trust connections from those standbys.

Before a standby can start streaming WAL, it needs an initial copy of the primary's data directory. This is called a base backup.

PostgreSQL provides the pg_basebackup utility to create this snapshot. It copies all data files from the primary to the standby, ensuring they start from a consistent point.

This command is typically run from the standby server:

Once the base backup is in place, the standby server needs to know it's a standby. For PostgreSQL 12 and later, this is signaled by the presence of an empty file named standby.signal in the data directory.

This file tells PostgreSQL to start in recovery mode and look for replication configuration.

The standby server also needs to know how to connect to the primary. This is specified using the primary_conninfo parameter in its postgresql.conf file.

It's a connection string similar to what you'd use with psql, including the primary's host, port, replication user, and password.

You can set this using ALTER SYSTEM on the standby:

You've learned about the core parameters for setting up streaming replication. Let's test your knowledge!

In this lesson, we explored physical streaming replication. You learned that it creates a byte-for-byte copy of the primary database by continuously sending WAL records.

• We covered primary server configurations like wal_level and max_wal_senders.
• You also learned about creating a dedicated replication user and configuring pg_hba.conf.
• Finally, we touched on preparing the standby with a base backup using pg_basebackup and configuring its connection using primary_conninfo.

Next, we'll put these concepts into practice by setting up a standby server!