Export operation efficiency in read replica

On Thu, 2025-03-20 at 17:22 +0530, Siraj G wrote:

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?

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

Hello Laurenz

As per my understanding coming to a proper conclusion wrt RPO with export
operation is challenging. Eg., the export started at x and ended at z, the
time stamp here for many data sets is different. Moreover, I do not think
there is an incremental way available for export, correct?

Please correct me if my understanding is wrong.

Although I do agree the primary choice of backup to be storage based, we
wanted to have export backups as well as a secondary.

Concerning the impact taking export on read only.. Would you think we may
run into recovery issues on the replica side or anything that would prevent
the operation from being successful?

Regards
Siraj

On Thu, Mar 20, 2025 at 5:29 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

On 3/20/25 05:58, Siraj G wrote:

Hello Laurenz

As per my understanding coming to a proper conclusion wrt RPO

You still have not defined what RPO is.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, Mar 20, 2025 at 7:52 AM Siraj G <tosiraj.g@gmail.com> wrote:

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

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
operation?

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!

On Thu, 2025-03-20 at 18:28 +0530, Siraj G wrote:

As per my understanding coming to a proper conclusion wrt RPO with
export operation is challenging. Eg., the export started at x and
ended at z, the time stamp here for many data sets is different.
Moreover, I do not think there is an incremental way available for export, correct?

You are talking about "pg_dump", right?
A dump is always consistent, no matter how long it takes.

But if you want to take a daily dump of a 1TB database, you
are doing something wrong. You should change the requirements.

Yours,
Laurenz Albe

On 20/03/2025 15:04, Adrian Klaver wrote:

On 3/20/25 05:58, Siraj G wrote:

Hello Laurenz

As per my understanding coming to a proper conclusion wrt RPO

You still have not defined what RPO is.

I guess the OP is talking about Recovery Point Objective, which is one
of two important parameters WRT to disaster recovery. It's the maximum
data loss you agree on with your backup solution. That mostly depends on
the database context, something we can't tell. And according to how much
you agree to lose (one minute of activity? one hour?), you then can
choose between pg_dump or PITR backups.

But with a 1TB-database, I wouldn't dare using pg_dump. PITR backup is
the only option.

--
Guillaume Lelarge
Consultant
https://dalibo.com

Thank you everyone!

On Fri, Mar 21, 2025 at 7:23 PM Guillaume Lelarge <
guillaume.lelarge@dalibo.com> wrote: