RE: Unable to archive logs in standby server

Hi,

I am still facing issue with executing pg_stop_backup in standby manually.
Copying from previous email -
Archival hangs. Is this expected?
postgres=# select pg_start_backup('test', true, false);
pg_start_backup
-----------------
1/F9000060
(1 row)

postgres=# select pg_stop_backup('f');
NOTICE: base backup done, waiting for required WAL segments to be archived
WARNING: still waiting for all required WAL segments to be archived (60 seconds elapsed)
HINT: Check that your archive_command is executing properly. You can safely cancel this backup, but the database backup will not be usable without all the WAL segments.
WARNING: still waiting for all required WAL segments to be archived (120 seconds elapsed)
HINT: Check that your archive_command is executing properly. You can safely cancel this backup, but the database backup will not be usable without all the WAL segments.
……..
…………

Regards,
Meera

From: subin <subtitle(dot)indo(at)gmail(dot)com>
Sent: Friday, September 2, 2022 12:36 AM
To: Meera Nair <mnair(at)commvault(dot)com>
Subject: Re: Unable to archive logs in standby server

External email. Inspect before opening.

Hope you had a good time.

On Tue, Aug 30, 2022 at 6:00 PM Meera Nair <mnair(at)commvault(dot)com<mailto:mnair(at)commvault(dot)com>> wrote:
Hi Guillaume/team,

I set archive_mode = always in master and standby.
Archival to standby WAL directory completed when

· standby server was restarted

· pg_stop_backup was executed in master

But archival hangs when pg_stop_backup is executed in standby.
Could someone help to get this working?

postgres=# select pg_start_backup('test', true, false);
pg_start_backup
-----------------
1/F9000060
(1 row)

postgres=# select pg_stop_backup('f');
NOTICE: base backup done, waiting for required WAL segments to be archived
WARNING: still waiting for all required WAL segments to be archived (60 seconds elapsed)
HINT: Check that your archive_command is executing properly. You can safely cancel this backup, but the database backup will not be usable without all the WAL segments.
WARNING: still waiting for all required WAL segments to be archived (120 seconds elapsed)
HINT: Check that your archive_command is executing properly. You can safely cancel this backup, but the database backup will not be usable without all the WAL segments.
……..
…………

postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)

postgres=# show wal_level;
wal_level
-----------
replica
(1 row)

postgres=# show archive_mode;
archive_mode
--------------
always
(1 row)

postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)

Regards,
Meera

From: Guillaume Lelarge <guillaume(at)lelarge(dot)info<mailto:guillaume(at)lelarge(dot)info>>
Sent: Thursday, July 21, 2022 6:01 PM
To: Meera Nair <mnair(at)commvault(dot)com<mailto:mnair(at)commvault(dot)com>>
Cc: pgsql-general(at)lists(dot)postgresql(dot)org<mailto:pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: Unable to archive logs in standby server

External email. Inspect before opening.

Hi,

Le jeu. 21 juil. 2022 à 13:58, Meera Nair <mnair(at)commvault(dot)com<mailto:mnair(at)commvault(dot)com>> a écrit :
Hi team,

With non-exclusive backup method, trying backup from standby node.
But pg_stop_backup function returns “WAL archiving is not enabled…” and the logs are not archived to WAL directory configured.

Please check if I am missing anything in configuring this properly,
Server was restarted after setting the archiving params in postgresql.conf

Below is from version 14:

postgres=# select pg_start_backup('label', false, false);
pg_start_backup
-----------------
0/60000D8
(1 row)

postgres=# select pg_stop_backup('false');
NOTICE: WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup
pg_stop_backup
---------------------------------------------------------------------------
(0/60001C0,"START WAL LOCATION: 0/60000D8 (file 000000010000000000000006)+
CHECKPOINT LOCATION: 0/6000110 +
BACKUP METHOD: streamed +
BACKUP FROM: standby +
START TIME: 2022-07-21 12:42:11 IST +
LABEL: label +
START TIMELINE: 1 +
","")
(1 row)

postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)

postgres=# show wal_level;
wal_level
-----------
replica
(1 row)

postgres=# show archive_mode;
archive_mode
--------------
on
(1 row)

You're doing backups from the standby, and to allow archiving on the backups, archive_mode should be set to always.

--
Guillaume.