Re: [HELP] Issue with standby server using WAL archive

Hello,

Thanks for your response. I agree with all your suggestions.

At this moment, I'm concerned about the WAL archiving.

I'm seeing below error on pg3 (DR server) in log. Is this expected ?
*cp: cannot stat '/mnt/server/archivedir/00000001000000000000000D': No such
file or directory*

On master, I see below info. I would like to know why the WALs are not
being archived.

*postgres(at)pg1:~/9.6/main/pg_xlog$ ls -lrthtotal 65M-rw------- 1 postgres
postgres 16M Jul 2 13:09 00000001000000000000000E-rw------- 1 postgres
postgres 16M Jul 2 13:13 00000001000000000000000F-rw------- 1 postgres
postgres 16M Jul 2 13:13 000000010000000000000010-rw------- 1 postgres
postgres 302 Jul 2 13:13
00000001000000000000000C.00000028.backupdrwx------ 2 postgres postgres 4.0K
Jul 2 13:23 archive_status-rw------- 1 postgres postgres 16M Jul 2 15:38
00000001000000000000000D*

*postgres(at)pg1:~/9.6/main/pg_xlog$ ls -lrt /mnt/server/archivedir/total
32788drwx------ 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*

On Thu, Jul 2, 2020 at 8:56 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Greetings,
>
> * Praveen Kumar K S (praveenssit(at)gmail(dot)com) wrote:
> > I'm using 9.6
>
> That's quite old- I'd strongly recommend that you look at using a newer
> version.
>
> > I have 3 servers. Let us call it pg1,pg2,pg3
> >
> > pg1 is master
> > pg2 is hot stand by
> > pg3 is a DR server which doesn't serve any requests. hot_standby is not
> > enabled in postgresql.conf on DR server.
>
> If hot_standby isn't enable then you won't be able to connect- which is
> entirely fine, but if that's what you want then I'm not sure what the
> error is that you're concerned about..?
>
> > pg1 and pg2 are in the same network and are in sync.
>
> Ok. Note that having only one replica when you're using syncronous
> replication will lead to a situation where, if either node fails, the
> database system won't accept writes.
>
> > I followed official documentation and used the cp command. Are there any
> > other best approaches ?
>
> The documentation provides an example of how to use the command, using
> 'cp' isn't actually recommended. I'd strongly suggest you look at
> proper backup/archiving solutions for PostgreSQL like pgbackrest to use
> instead.
>
> Thanks,
>
> Stephen
>

--

*Regards,*

*K S Praveen KumarM: +91-9986855625 *