Thread: Incremental Postgres database backup

On 3/6/22 23:53, Firthouse banu wrote:
> Hello Team,
>
> Needed an expert advice.
> Can you guys suggest me to a open source tool which can be used to take 
> incremental backup of individual Postgres databases…
>
> We have very large databases whose sizes are again TBs when we do pg_dump 
> or pg_dump all its taking days to complete backups ..

We use PgBackRest, and are very pleased with it.

-- 
Angular momentum makes the world go 'round.

On 3/6/22 23:53, Firthouse banu wrote:

Hello Team,

Needed an expert advice.
Can you guys suggest me to a open source tool which can be used to take incremental backup of individual Postgres databases…

We have very large databases whose sizes are again TBs when we do pg_dump or pg_dump all its taking days to complete backups ..

We use PgBackRest, and are very pleased with it.

Ditto here.  It works very well and has the advantage of being able to produce an immediately-startable copy of the master.

One caution; you have to pay attention when doing upgrades, even of minor versions.

The other obvious option is online replication.

On 3/7/22 08:30, Karl Denninger wrote:
[snip]
>
> The other obvious option is online replication.
>

Replication is not a backup solution!!!!!

-- 
Angular momentum makes the world go 'round.

On 3/7/22 08:30, Karl Denninger wrote:
[snip]

The other obvious option is online replication.

Replication is not a backup solution!!!!!

That's true, but once you have replication set up you have a local staging source against which to take backups that does not require Internet bandwidth, and thus makes solving the problem MUCH easier.

Getting the I/O from the backup process off the production machine(s) is frequently a good decision.  Online replication does this and moves the locus of your backup away from the online production environment.

If that's not material simply using existing tools (e.g. pgbackrest) does the job all by itself, but if so using replication to get the data where its convenient to use a tool such as pgbackrest absolutely works, separates the I/O load away from the production environment and opens up using block-level or filesystem-level backup tools (e.g. ZFS) as well since even if the database spans a snapshot atomic coherence boundary (which otherwise makes doing that unsafe) you can shut the replicated slave down, take said snapshots and restart it without implicating accessibility to the database.