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

Hello,

Thanks for your response.

I'm using 9.6

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.

pg1 and pg2 are in the same network and are in sync.

I followed official documentation and used the cp command. Are there any
other best approaches ?

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

> Greetings,
>
> * Praveen Kumar K S (praveenssit(at)gmail(dot)com) wrote:
> > Config on Master:
> > archive_mode = on
> > archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p
> > /mnt/server/archivedir/%f'
>
> You really shouldn't use 'cp' or such a simple archive command, you'll
> very likely lose WAL on a system crash with that setup.
>
> > 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
>
> > Am I missing anything here ? Please suggest. TIA.
>
> Do you have hot_standby enabled in postgresql.conf? What version of PG?
>
> Thanks,
>
> Stephen
>

--

*Regards,*

*K S Praveen Kumar*