Subject: Export operation efficiency in read replica

From: Siraj G <[email-masked]>
Date: Thu, 20 Mar 2025 17:22:21 +0530

Hello Experts!

I have a DB with 1TB in size serving needs of one of our critical
applications. I have a requirement to take export of the DB on a daily
basis, but want to carry out this operation in read replica. The postgresql
version is: 16.6

What would be the RPO of such backup?
What would be the impact on the READ REPLICA with a long running export
operation?

Thank you!
Siraj

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 20 Mar 2025 12:59:44 +0100

On Thu, 2025-03-20 at 17:22 +0530, Siraj G wrote:
> applications. I have a requirement to take export of the DB on a
> daily basis, but want to carry out this operation in read replica.
> The postgresql version is: 16.6
> 

Depends on the speed of disk and network and on what an RPO is.

> What would be the impact on the READ REPLICA with a long running
> export operation?

Potentially severe.

You could look into storage technologies that allow you to take
a snapshot and clone it.

Yours,
Laurenz Albe

---

From: Ron Johnson <[email-masked]>
Date: Thu, 20 Mar 2025 10:17:46 -0400

> basis, but want to carry out this operation in read replica. The postgresql

Use PgBackRest from the streaming replica.

> What would be the RPO of such backup?

Are you asking how long it takes to restore the backup back to the primary?

> What would be the impact on the READ REPLICA with a long running export

Why not use the standby server (aka "read replica") as the hot standby in
case the Primary crashes hard?

Then pg_basebackup (or PgBackRest) to get the database back to the primary
once it's back up.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!