Read Replicas for Render Postgres

Read replicas are separate, managed instances of your Render Postgres database that only allow read operations. As you write data to your primary instance, Render asynchronously replicates those changes to your read replicas:

Use read replicas to reduce load on your primary database and make one-off queries safer. They're great for analysis tools that don't need to write data, or for running computationally expensive queries without affecting the performance of your primary database.

Want to enable logical replication with CREATE PUBLICATION?

Logical replication requires reaching out to our support team. See below.

Requirements

Read replicas always have the same instance type and storage as their primary database and are billed accordingly.

For your Render Postgres database to support read replicas, it must:

• Have at least 10 GB of storage
• Use the Basic-1gb instance type or higher
  • If your database uses a legacy instance type, it must use the Standard instance type or higher.

Any database that meets these requirements can have up to five read replicas.

Setup

Go to your database's Info page in the Render Dashboard and click Add Read Replica:

A confirmation dialog appears. If you confirm, Render spins up the replica instance and starts copying over data from the primary instance. That's it! Your read replica should become available within a few minutes. If it takes longer, please reach out to our support team in the Render Dashboard.

After a read replica becomes available, you can connect to it just like you do your primary instance, using its internal or external connection URL.

Performance

Changes to your primary database are synced to its read replicas after a short delay. This means replicas are best suited to use cases that don't require instant access to the most recent data possible.

The length of this delay depends on your primary instance's load. You can monitor this from the primary instance's Metrics page, under Replication Lag.

Read replicas vs. high availability

Read replica instances are different from a standby instance that's used for high availability. Read replicas help decrease load on your primary instance, and they're safer for one-off and expensive queries. In contrast, a standby instance helps reduce downtime in the event of instance failure.

Logical replication

PostgreSQL logical replication enables "subscriber" databases to continually pull data changes from a "publisher" database. Unlike with read replicas, these subscriber databases do not need to be Render-managed.

Render Postgres databases do not enable logical replication by default. To enable:

• Reach out to our support team in the Render Dashboard.
• In your message, provide the service ID for each Render Postgres database you want to enable logical replication for (both publishers and subscribers).
  • This value is available from your database's Info page. It starts with dpg-.
  • You can enable logical replication on a read replica. However, a read replica can act only as a publisher, not as a subscriber.
• Also mention if you specifically want to publish all tables for a particular database.
  • The command CREATE PUBLICATION [name] FOR ALL TABLES requires superuser privileges and must be executed by our support team.
  • Commands of the form CREATE PUBLICATION [name] FOR TABLE… can be executed by you.

After logical replication is enabled, you can do the following:

1. On your "publisher" database, create a publication for the tables you want to replicate.

   Common examples include:

2. On each "subscriber" database, create a subscription that points to the publication on the publisher:

Learn more in the PostgreSQL documentation for CREATE PUBLICATION and CREATE SUBSCRIPTION.