Re: PostgreSQL Stand By Database Server backup (without using pg_basebackup)

Hi Laurenz,
Apologize to reopen a old thread.
You had suggested to follow below steps:
- pg_start_backup - master
- File system backup of DATA directory on standby
- pg_stop_backup - master.
- Backup required archive logs - primary.

Here one issue is we won't have 'backup_label' file available on standby to
backup. We might end up restoring the database in a inconsistent state ?

I see below note for backup_label file.

pgdevel <https://bitbucket.org/adunstan/pgdevel.git>
/src/backend/access/transam/xlog.c
* read_backup_label: check to see if a backup_label file is present
*
* If we see a backup_label during recovery, we assume that we are recovering
* from a backup dump file, and we therefore roll forward from the checkpoint
* identified by the label file, NOT what pg_control says. This avoids the
* problem that pg_control might have been archived one or more checkpoints
* later than the start of the dump, and so if we rely on it as the start
* point, we will fail to restore a consistent database state.

Thanks
Girish

On Wed, Aug 26, 2015 at 3:30 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
wrote:

> girish R G peetle wrote:
> > We want to perform File System based backup of PostgreSQL Standby
> Database server.
> >
> > For Primary server we perform backup of data directory after invoking
> pg_start_backup, once this is
> > done, execute pg_stop_backup and backup archived transaction logs.
> >
> > We are not able to follow same procedure for Standby server as
> pg_start_backup and pg_stop_backup
> > cannot be executed on stand by.
> >
> >
> > I am aware that pg_basebackup utility can be used to perform backup of
> standby but we don't want to
> > use this utility. ( As backup of data directory is handled by third
> party backup vendor)
> >
> > In this case what is the recommended way to backup standby database
> server (without using
> > pg_basebackup utility) ?
>
> You can issue pg_start_backup on the primary, then take a file system
> backup
> of the standby, then run pg_stop_backup on the primary and collect the
> archived logs
> from the primary.
>
> Yours,
> Laurenz Albe
>