Re: Hot backup in PostgreSQL - Mailing list pgsql-general

From Mark Johnson
Subject Re: Hot backup in PostgreSQL
Date
Msg-id CADZ4tWMp-R=8k4pXcqHcfDZ2JFJjZkNyBSGZtZrvzvR3HJ6E9w@mail.gmail.com
Whole thread Raw
In response to Re: Hot backup in PostgreSQL  (Adam Brusselback <adambrusselback@gmail.com>)
Responses Re: Hot backup in PostgreSQL
List pgsql-general
User managed backups in PostgreSQL work very similar to what you know from Oracle.  You first place the cluster in backup mode, then copy the database files, and lastly take the cluster out of backup mode.  The first and last steps are done using functions pg_start_backup('label',false,false) and pg_stop_backup(false, false). [1]. 

If you use a utility supplied with PostgreSQL such as pg_basebackup, it does these steps for you.  If you are using a specific non-PostgreSQL utility (i.e., Dell Networker or IBM Tivoli) see their documentation for specifics.


On Thu, Oct 22, 2020 at 11:14 AM Adam Brusselback <adambrusselback@gmail.com> wrote:
> how to do "hot backup" (copying files) while database running?
As others have shown, there are ways to do this with PG's internal tooling (pg_basebackup).

However, I would highly recommend you use an external backup tool like pgbackrest [1] to save yourself the pain of implementing things incorrectly and ending up with non-viable backups when you need them most. I'm not affiliated with them at all, but have just used pgbackrest in production for years now with great results.  It takes care of PITR, and manages backup retention (and associated WAL retention). Those can be a bit of a pain to do manually otherwise.

Just my $0.02, hope it helps!

pgsql-general by date:

Previous
From: Ayub M
Date:
Subject: postgres materialized view refresh performance
Next
From: Lucas Possamai
Date:
Subject: PG 9.2 slave restarted - cache not impacted