PostgreSQL backup from standby server

Last post 02-21-2020, 10:57 AM by Meera. 8 replies.
Sort Posts: Previous Next
  • PostgreSQL backup from standby server
    Posted: 01-03-2019, 3:03 PM

    Hello,

    I am trying to run a PostgreSQL backup using the back up from standby server (http://documentation.commvault.com/commvault/v11_sp12/article?p=93037.htm) option.  When I ran the first backup, I noticed an event in the CommCell event viewer:

    PostgreSQL Backup from Standby: Backup Failed. Reason: [~Primary and Secondary Postgres Server state are not correct for Backup from Standby.~]

    The backup continued presumably from the master node.

    Is there any documentation to troubleshoot this issue?  Replication is turned on and does replicate to the standby server.

     

    postgresql.conf

     

    wal_level = hot_standby                 # minimal, archive, hot_standby, or logical

     

    Any help on this would be greatly appriciated!

    Thanks,

    John

  • Re: PostgreSQL backup from standby server
    Posted: 01-03-2019, 3:26 PM

    Hi John,

     

    Can you please check the following documentation section and make sure steps followis as below. If still issue persists, let us know.

     

     

    http://documentation.commvault.com/commvault/v11/article?p=93038.htm

     

     

    Before You Begin

    1. Install the PostgreSQL agent, both on the master server and the standby server.

      For more information, see Deployment - PostgreSQL Agent.

    2. Create a PostgreSQL instance, both on the master server and the standby server.
    3. For streaming replication, configure the archive command. It will be used to provide a continuous backup support in case of a failover, when the standby server role changes to a master server.

    Procedure

    1. From the CommCell Browser, expand Client Computers > master client > PostgreSQL.
    2. Right-click the instance, and then click Properties.

      The Properties of PostgreSQL Instance dialog box appears.

    3. On the Standby Settingstab, select options for standby server support:
      1. Select the Enable Standby Backup check box.
      2. From the Standby Instance list, select an instance on the standby server on which you want to offload backup and restore operations.
      3. Optional: To run a backup operation from the master server if standby server is not available, select the Use master if standby is unavailable check box.
      4. Optional: To run a log backup operation from the master server, select the Always use master for log backup check box.
    4. Click OK.

    http://documentation.commvault.com/commvault/v11/article?p=21518.htm

     

    Configuring the PostgreSQL Archive Log Directory

    Archive log files are stored in the Archive Log directory. Ensure to follow the below checkpoints before running the PostgreSQL FS backup.

    Procedure

    • Specify the Archive log directory path in the postgresql.conf file prior to performing the PostgreSQL FS backup. Make sure that this path does not point to pg_log or log directories and pg_xlog or pg_waldirectories.

      archive_command = 'cp %p /opt/wal/%f' #UNIX
      archive_command = 'copy "%p" "D:\\PostgreSQL\\wal\\%f"' #Windows

    • For PostgreSQL 8.3 version and later, use the following command to turn on the archive_mode. This feature is not supported for PostgreSQL 8.2 and earlier versions.

      archive_mode = on

    • For PostgreSQL 9.x.x version, use the following configuration.

      Set wal_level = archive instead of default wal_level = minimal

    • From PostgreSQL 10.x.x version onwards, use the following configuration.

      Set wal_level = replica

    • Verify that the archive command provided in the postgresql.conf file is correct. You can test this by running the following commands and verifying that they successfully complete.

      Select pg_start_backup(‘Testing’);
      Select pg_stop_backup();

  • Re: PostgreSQL backup from standby server
    Posted: 01-03-2019, 4:35 PM

    Hello, 

     

    Yes, those settings exist. 

    For PostgreSQL 9.x.x version, use the following configuration.

    Set wal_level = archive instead of default wal_level = minimal

    This is set to
    wal_level = hot_standby

    To add onto this, I enabled 2 other postgreSQL servers and those backups run without any errors or indication something is wrong. As best as I can tell, the backups still run on the master node. Are we supposed to schedule the backups from the master node or standby?

  • Re: PostgreSQL backup from standby server
    Posted: 01-04-2019, 2:21 AM

    Hi John,

    Backups need to be scheduled from master instance only.

    What is the type of replication- streaming or log shipping based?

    What is the service pack level for your client?

    Usually we get the JPR " Primary and Secondary Postgres Server state are not ...." when standby server is down OR standby is not reachable from master. 

    Do you have commvault installed on both standby and master, instance configured for both?

    Standby settings need to be configured on master instance and backup also need to be initiated from master.

    If all this is fine, please drop a mail to Dev-PostgreSQL with client side logs from master and standby we will check logs , that will help in understanding issue. 

     

    Also please check that standby server is in recovery mode. 

    select pg_is_in_recovery():

    This query should return true in standby

     

     

    Regards,
    Meera

  • Re: PostgreSQL backup from standby server
    Posted: 01-04-2019, 9:49 AM

    We use streaming replication.    The result of the select statement is "t", so it's in recovery mode.

    SP12 on both master and standby, and PostgreSQL instance have been configured on both.  We are activley running backups from the master, but wanted to try the new backup from standby option that was introduced in SP12.

     

    I went ahead and opened support ticket 190103-543.  I was just hoping there might be additional information on troubleshooting before I did that.  

     

    Thanks for all of your help.  I will report back once we determine RC.

  • Re: PostgreSQL backup from standby server
    Posted: 02-04-2020, 3:14 AM

    Meera:

    Hi John,

    Backups need to be scheduled from master instance only.

    Hi,

    I have a case where backup need to be done from two independent datacenter, both from master and slave. Do we have that option? Other then auxilary copy at tape level. 

     

    Thanks. 

  • Re: PostgreSQL backup from standby server
    Posted: 02-17-2020, 10:54 PM

    Hi,

     

    What is the type of replication? 

    From the same instance, backup for master and standby is not supported currently. But we can try to find a workaround based on the type of replication.

     

    Regards,

    Meera

  • Re: PostgreSQL backup from standby server
    Posted: 02-18-2020, 4:50 AM

    We use logical replication. Currently, backup executed on replica ends with :

     

    13588 3514 02/03 12:47:38 1132734 AnalyzeResult() - eRRoR -- PQresultStatus indicates failure for select pg_start_backup('pgida_backup_5432_1132734_1580730458');. Error:ERROR: recovery is in progress
    HINT: WAL control functions cannot be executed during recovery.
    13588 3514 02/03 12:47:38 1132734 ExecutePostgre::postgre_Query() - eRRoR -- failed to execute query[select pg_start_backup('pgida_backup_5432_1132734_1580730458');]
    13588 3514 02/03 12:47:38 1132734 PostGresSnapBackup::QuiesceDB() - Backup Failed. Failed to execute Query [select pg_start_backup('pgida_backup_5432_1132734_1580730458');] Error [ Could not put the Cluster in backup mode.]
    13588 3514 02/03 12:47:38 1132734 DBSnapBackup::Snap() - QuiesceDB failed.

    As temporary solution we implemented replica disk library with NAS based replication to move primary replica to second location. But we want avoid cross-DC replication after each full backup. 

    Workourod i can think of is :

    • as pre backup script shutdown standby instance
    • use InteliSnap with Filesystem agent to grab filesystem image snapshot with offline database
    • as post backup script start database intance (replication will be re-estalished)
    • Lunch backup copy job taking snapshot to tape/NAS 
    Any other, more reliable solution? 
  • Re: PostgreSQL backup from standby server
    Posted: 02-21-2020, 10:57 AM

    Hi, 

    We use select pg_start_backup() query to quiesce the database during file system backup and this cannot be executed on a PostgreSQL standby server which is in recovery mode. This is the reason for the error you see while initiating a backup from the standby subclient. When we perform backup from standby we have a different logic.

    One option will be to do file system based backup from standby instance and dumpbased backup from master. But this will load the master based on how large the DBs are. I will check and come back to you on the workaround possible in this case

     

    Regards,

    Meera

The content of the forums, threads and posts reflects the thoughts and opinions of each author, and does not represent the thoughts, opinions, plans or strategies of Commvault Systems, Inc. ("Commvault") and Commvault undertakes no obligation to update, correct or modify any statements made in this forum. Any and all third party links, statements, comments, or feedback posted to, or otherwise provided by this forum, thread or post are not affiliated with, nor endorsed by, Commvault.
Commvault, Commvault and logo, the “CV” logo, Commvault Systems, Solving Forward, SIM, Singular Information Management, Simpana, Commvault Galaxy, Unified Data Management, QiNetix, Quick Recovery, QR, CommNet, GridStor, Vault Tracker, InnerVault, QuickSnap, QSnap, Recovery Director, CommServe, CommCell, SnapProtect, ROMS, and CommValue, are trademarks or registered trademarks of Commvault Systems, Inc. All other third party brands, products, service names, trademarks, or registered service marks are the property of and used to identify the products or services of their respective owners. All specifications are subject to change without notice.
Close
Copyright © 2020 Commvault | All Rights Reserved. | Legal | Privacy Policy