Обсуждение: PostgreSQL Stand By Database Server backup (without using pg_basebackup)

Поиск
Список
Период
Сортировка

PostgreSQL Stand By Database Server backup (without using pg_basebackup)

От
girish R G peetle
Дата:
Hi, 
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) ?

Thanks
Girish

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

От
Albe Laurenz
Дата:
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

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

От
girish R G peetle
Дата:

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 /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.albe@wien.gv.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