Physical replication - AWS Prescriptive Guidance
ArchitectureLimitations

Physical replication

Physical replication is block-level replication where a WAL file is shipped from a primary database to a secondary database. Physical replication is also called streaming replication because it allows a standby server to stay more up-to-date than is possible with file-based log shipping. The standby server connects to the primary database. Then, the primary database streams WAL records to the standby database without waiting for the WAL file to be filled. Physical replication is an option worth considering if you have a small or medium-sized database and you're planning to use the same database version. Also, you can use physical replication for larger databases, but the sync can take a considerable amount of time. You can use either of the following two methods with physical replication:

  1. Asynchronous – The asynchronous method is the default option. If the primary server crashes, then some transactions that were committed to the database could fail to be replicated on the standby server and cause data loss.

  2. Synchronous – The synchronous method offers the ability to confirm that all changes made by a transaction are transferred to one or more synchronous standby servers.

Architecture

The following diagram shows the architecture for setting up HADR for your on-premises PostgreSQL database on Amazon EC2 by using physical replication.

Physical replication architecture

The diagram shows the following workflow:

  1. Replicate the database on an EC2 instance and copy over the archive files.

  2. Promote the new replica as the database writer endpoint.

  3. Point the application to the new target database.

Limitations

We recommend that you consider the following limitations of using physical replication before starting your migration:

  • A significant amount of diskspace is required on the server to take backups and then copy the backups on Amazon EC2.

  • A significant amount of bandwidth is required to synchronize the source and target databases and achieve faster copying for the archive log.

  • Source and target databases must have the same version of PostgreSQL.