[HELP] Issue with standby server using WAL archive

From: Praveen Kumar K S <praveenssit(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: [HELP] Issue with standby server using WAL archive
Date: 2020-07-02 13:27:13
Message-ID: CANSiNp6iAACN_4HjHUqFEmTOd_0QvdBiE5W1XXjwFniNwUWAYQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello,

I'm facing this issue with a standby server which is setup to use WAL
archive.

Config on Master:
archive_mode = on
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p
/mnt/server/archivedir/%f'

Config on standby:
standby_mode = 'on'
restore_command = 'cp /mnt/server/archivedir/%f %p'

Before starting standby, I took base backup

pg_basebackup -v -D main -R -P -h pg1 -p 5432 -U replication
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
43518/43518 kB (100%), 1/1 tablespace

NOTICE: pg_stop_backup complete, all required WAL segments have been
archived
pg_basebackup: base backup completed

Now, copied recovery.conf to data directory

Started postgres server on standby and seeing below error in log.

2020-07-02 13:14:02.134 UTC [7643] postgres(at)template1 FATAL: the database
system is starting up
2020-07-02 13:14:02.644 UTC [7646] postgres(at)template1 FATAL: the database
system is starting up
2020-07-02 13:14:03.154 UTC [7649] postgres(at)template1 FATAL: the database
system is starting up
2020-07-02 13:14:03.664 UTC [7652] postgres(at)template1 FATAL: the database
system is starting up
2020-07-02 13:14:04.174 UTC [7655] postgres(at)template1 FATAL: the database
system is starting up
cp: cannot stat '/mnt/server/archivedir/00000001000000000000000D': No such
file or directory
2020-07-02 13:14:04.684 UTC [7660] postgres(at)template1 FATAL: the database
system is starting up
2020-07-02 13:14:05.194 UTC [7663] postgres(at)template1 FATAL: the database
system is starting up
2020-07-02 13:14:05.197 UTC [7664] [unknown](at)[unknown] LOG: incomplete
startup packet
cp: cannot stat '/mnt/server/archivedir/00000001000000000000000D': No such
file or directory
cp: cannot stat '/mnt/server/archivedir/00000001000000000000000D': No such
file or directory

On Master:
postgres(at)pg1:~$ ls -lrt /var/lib/postgresql/9.6/main/pg_xlog/
total 65544
-rw------- 1 postgres postgres 16777216 Jul 2 13:09
00000001000000000000000E
-rw------- 1 postgres postgres 16777216 Jul 2 13:13
00000001000000000000000F
-rw------- 1 postgres postgres 16777216 Jul 2 13:13
00000001000000000000000C
-rw------- 1 postgres postgres 302 Jul 2 13:13
00000001000000000000000C.00000028.backup
drwx------ 2 postgres postgres 4096 Jul 2 13:18 archive_status
-rw------- 1 postgres postgres 16777216 Jul 2 13:18
00000001000000000000000D

postgres(at)pg1:~$ ls -lrt /var/lib/postgresql/9.6/main/pg_xlog/archive_status/
total 0
-rw------- 1 postgres postgres 0 Jul 2 13:13 00000001000000000000000C.done
-rw------- 1 postgres postgres 0 Jul 2 13:13
00000001000000000000000C.00000028.backup.done

On standby:
postgres(at)pg3:~$ ls -lrt /var/lib/postgresql/9.6/main/pg_xlog/
total 16388
-rw------- 1 postgres postgres 16777216 Jul 2 13:13
00000001000000000000000C
drwx------ 2 postgres postgres 4096 Jul 2 13:13 archive_status
postgres(at)pg3:~$ ls -lrt /var/lib/postgresql/9.6/main/pg_xlog/archive_status/
total 0
-rw------- 1 postgres postgres 0 Jul 2 13:13 00000001000000000000000C.done

/mnt/server/archivedir/ is a NAS mount and is accessible from master and
standby.

postgres(at)pg3:~$ ls -lrt /mnt/server/archivedir/
total 32788
drwx------ 2 postgres postgres 16384 Jul 1 14:44 lost+found
-rw------- 1 postgres postgres 16777216 Jul 2 13:13
00000001000000000000000B
-rw------- 1 postgres postgres 16777216 Jul 2 13:13
00000001000000000000000C
-rw------- 1 postgres postgres 302 Jul 2 13:13
00000001000000000000000C.00000028.backup

Am I missing anything here ? Please suggest. TIA.

PS: I have another slave streaming using synchronous replication and not
WAL archive and is working fine.

--

*Regards,*

*K S Praveen Kumar*

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message M Jane 2020-07-02 14:04:28 Re: pg_dump backup issue
Previous Message srini ram 2020-07-02 13:12:15 Re: pg_dump backup issue