Thread: Questions on Post Setup MASTER and STANDBY replication - Postgres9.1

Hi All,

After setting up two nodes with MASTER and STANDBY replication, I see “ cp: cannot stat `/opt/postgres/9.1/archive/000000010000000000000003': No such file or directory” in the log on STANDBY and the startup process recovering “000000010000000000000004” which does not exist in the archive directory. 

The replication is verified and works.  My questions are what’s the reason causing “cp: cannot stat `/opt/postgres/9.1/archive/000000010000000000000003': No such file or directory” on STANDBY and how to fix it? Also, it seems the startup process stucks on “recovering 000000010000000000000004”, how to resolve it? 

Thank you !

On STANDBY node:

LOG:  entering standby mode

cp: cannot stat `/opt/postgres/9.1/archive/000000010000000000000003': No such file or directory

LOG:  redo starts at 0/3000020

LOG:  record with zero length at 0/30000B0

cp: cannot stat `/opt/postgres/9.1/archive/000000010000000000000003': No such file or directory

LOG:  streaming replication successfully connected to primary

LOG:  consistent recovery state reached at 0/4000000

LOG:  database system is ready to accept read only connections

ls -rlt /opt/postgres/9.1/archive

-rw------- 1 postgres postgres 16777216 Oct 28 14:07 000000010000000000000001

-rw------- 1 postgres postgres 16777216 Nov  2 19:00 000000010000000000000002

ps -ef|grep startup|grep -v grep

postgres  9036  9020  0 19:00 ?        00:00:00 postgres: startup process   recovering 000000010000000000000004

ps -ef|grep receiver|grep -v grep

postgres  9040  9020  0 19:00 ?        00:00:00 postgres: wal receiver process   streaming 0/4000380

On MASTER node:

ls -rlt /opt/postgres/9.1/archive

-rw------- 1 postgres postgres 16777216 Oct 28 14:08 000000010000000000000001

-rw------- 1 postgres postgres 16777216 Nov  2 19:00 000000010000000000000002

-rw------- 1 postgres postgres 16777216 Nov  2 19:00 000000010000000000000003

-rw------- 1 postgres postgres      270 Nov  2 19:00 000000010000000000000003.00000020.backup

ps -ef|grep archiver |grep -v grep

postgres  9041  9035  0 18:57 ?        00:00:00 postgres: archiver process   last was 000000010000000000000003.00000020.backup

ps -ef|grep sender |grep -v grep

postgres  9264  9035  0 19:00 ?        00:00:00 postgres: wal sender process postgres 192.168.154.106(64182) streaming 0/4000380

Cheers,

Jim Nasby [mailto:Jim.Nasby@BlueTreble.com] wrote:

>On 11/2/16 2:49 PM, Joanna Xu wrote:

>> The replication is verified and works.  My questions are what’s the

>> reason causing “cp: cannot stat

>> `/opt/postgres/9.1/archive/000000010000000000000003': No such file or

>> directory” on STANDBY and how to fix it?

>What instructions/tools did you use to setup replication?

The following steps were involved in setting up the replication:

·         Stop the STANDBY database.

·         Start the hot backup on the MASTER database.

·         Push data to the STANDBY database.

·         Stop the hot backup on the MASTER database.

·         Reload with the new configuration on MASTER.

·         Setup new configuration on the STANDBY database.

·         Start the STANDBY database.

Also, we see the message “LOG:  terminating walsender process due to replication timeout” in the logs on MASTER. Is this expected in the condition when the system is less busy or else? 

Thanks,

Joanna

-----Original Message-----
From: Jim Nasby [mailto:Jim.Nasby@BlueTreble.com]
Sent: Wednesday, November 02, 2016 7:15 PM
To: Joanna Xu <Joanna.Xu@amdocs.com>; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Questions on Post Setup MASTER and STANDBY replication - Postgres9.1

On 11/2/16 2:49 PM, Joanna Xu wrote:

> The replication is verified and works.  My questions are what’s the

> reason causing “cp: cannot stat

> `/opt/postgres/9.1/archive/000000010000000000000003': No such file or

> directory” on STANDBY and how to fix it?

What instructions/tools did you use to setup replication?

> Also, it seems the startup

> process stucks on “recovering 000000010000000000000004”, how to

> resolve it?

As far as I know that's normal while in streaming mode.

--

Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com

855-TREBLE2 (855-873-2532)   mobile: 512-569-9461