Обсуждение: [HELP] Issue with standby server using WAL archive

Greetings,

* Praveen Kumar K S (praveenssit@gmail.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@template1 FATAL:  the database
> system is starting up
> 2020-07-02 13:14:02.644 UTC [7646] postgres@template1 FATAL:  the database
> system is starting up
> 2020-07-02 13:14:03.154 UTC [7649] postgres@template1 FATAL:  the database
> system is starting up
> 2020-07-02 13:14:03.664 UTC [7652] postgres@template1 FATAL:  the database
> system is starting up
> 2020-07-02 13:14:04.174 UTC [7655] postgres@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@template1 FATAL:  the database
> system is starting up
> 2020-07-02 13:14:05.194 UTC [7663] postgres@template1 FATAL:  the database
> system is starting up
> 2020-07-02 13:14:05.197 UTC [7664] [unknown]@[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

Greetings,

* Praveen Kumar K S (praveenssit@gmail.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

Greetings,

* Praveen Kumar K S (praveenssit@gmail.com) wrote:
> 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*

Yes, that's expected, the replica will constantly attempt to get the
next WAL segment to replay, unless you have streaming replication
configured.  There's nothing wrong with that.

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

The WAL file isn't going to be archived until PG is done writing into
it, which won't happen until there's been ~16MB of write activity on the
database.  If you want WAL to be archived more frequently even if the
segment isn't full then you should look at the archive_timeout option.

Thanks,

Stephen

Greetings,

* Praveen Kumar K S (praveenssit@gmail.com) wrote:
> But if you look at the output below, 00000001000000000000000D is already
> 16MB and new WAL are created. As per my understanding,
> 00000001000000000000000D should have been archived. Am I wrong here ?

Yes, you're wrong- WAL files are re-used and will always be 16MB even if
they aren't yet completed.

Thanks,

Stephen

I do not wish to get emails from these. Kindly remove me from mailing list.



-----Original Message-----
From: Stephen Frost <sfrost@snowman.net> 
Sent: 02 July 2020 20:56
To: Praveen Kumar K S <praveenssit@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: [HELP] Issue with standby server using WAL archive

Greetings,

* Praveen Kumar K S (praveenssit@gmail.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