RE: Unable to archive logs in standby server

[Meera Nair <mnair@xxxxxxxxxxxxx>]

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@xxxxxxxxxxxx>
Sent: Thursday, July 21, 2022 6:01 PM
To: Meera Nair <mnair@xxxxxxxxxxxxx>
Cc: pgsql-general@xxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxx> 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.