How to faster PITR of PostgreSQL using BARMAN if we have database size more than 15 TB?

[Md. Monirul Islam Khan/TECHNOLOGY/BRAC]

How to faster PITR of PostgreSQL using BARMAN if we have database size more than 15 TB?

[Luca Ferrari]

On Tue, Oct 17, 2023 at 9:31 PM 'Md. Monirul Islam
Khan/TECHNOLOGY/BRAC' via Barman, Backup and Recovery Manager for

PostgreSQL <pgba...@googlegroups.com> wrote:
>
> How to faster PITR of PostgreSQL using BARMAN if we have database size more than 15 TB?

Is a little vague, don't you think? Faster than what?
Despite hardware, network and other latency things, the PITR will be
as much as faster as less you require to replay wals, in other words,
the most you are near your backup, the less it will require to reach
the point in time.

Luca

[mohammad sherafat]

Hi,

1. Create backups on regular times.

2. Create a ready replica (reduce base backup copy time)

3. Think about archiving non OLTP data in OLAP Database

4. Summering data 

--
--
You received this message because you are subscribed to the "Barman for PostgreSQL" group.
To post to this group, send email to pgba...@googlegroups.com
To unsubscribe from this group, send email to
pgbarman+u...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/pgbarman?hl=en?hl=en-GB

---
You received this message because you are subscribed to the Google Groups "Barman, Backup and Recovery Manager for PostgreSQL" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pgbarman+u...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/pgbarman/CAKoxK%2B50OJgjp_7aperyvkSroZbsBb39u3men3UG_tMpmHf4rw%40mail.gmail.com.

[Luca Ferrari]

On Wed, Oct 18, 2023 at 11:39 AM mohammad sherafat
<mohammad.sher...@gmail.com> wrote:
>
> 2. Create a ready replica (reduce base backup copy time)

Why this should reduce the base backup time?

> 3. Think about archiving non OLTP data in OLAP Database

Can you elaborate more on this? It is not clear.

> 4. Summering data

What does it mean to "summering" data?

Luca

[mohammad sherafat]

2. If copying base backup is part of PITR, you should do it before the need to PITR

3. OLTP/OLAP transactions should be handled on different servers to reduce workload. It depends on your system that whether is it possible or not?

OLTP data is not required for subsequent OLTP transactions, so transferring them to OLAP database in a maintenance routine should be scheduled.

4. Summering data:

What do you need from a bunch of data, record details or total metrics such as count, sum, ... Any already computed analysis.

In many cases the previous data are required for it's summary.

--
--
You received this message because you are subscribed to the "Barman for PostgreSQL" group.
To post to this group, send email to pgba...@googlegroups.com
To unsubscribe from this group, send email to
pgbarman+u...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/pgbarman?hl=en?hl=en-GB

---
You received this message because you are subscribed to the Google Groups "Barman, Backup and Recovery Manager for PostgreSQL" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pgbarman+u...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/pgbarman/CAKoxK%2B6yS3AfEXzKymc-QNOVb%2Bzj9DQgh5R79iSpLoWw_FezXw%40mail.gmail.com.

[Luca Ferrari]

On Wed, Oct 18, 2023 at 1:20 PM mohammad sherafat
<mohammad.sher...@gmail.com> wrote:
>
> 2. If copying base backup is part of PITR, you should do it before the need to PITR
>

Obvious, but having a replica (as you originally mentioned) is not
going to speed up things with regard to the effort needed to do the
replica.

> 3. OLTP/OLAP transactions should be handled on different servers to reduce workload. It depends on your system that whether is it possible or not?
> OLTP data is not required for subsequent OLTP transactions, so transferring them to OLAP database in a maintenance routine should be scheduled.
>

So what? This has nothing, _nothing_ to do with PITR. Physical backup
in general does not care how much connections nor transactions you are
running, nor the type of things you are doing. I suspect you are
messing up logical and physical backup here.

> 4. Summering data:
> What do you need from a bunch of data, record details or total metrics such as count, sum, ... Any already computed analysis.

The word you are looking for is "materializng".
And again, this has nothing to do with the speed of PITR, rather with
the speed of your own queries.

Luca