Re: Steps to switch from Master to standby mode :

[prakhar jauhari <prak840@xxxxxxxxx>]

Hi all,

I tried to setup up Master to standby switch but even though i am using archiving to bump up time lines,

I noticed that Streaming replication gets setup between new standby and new master but it stops after some time and doesn't start after that.

Following are the logs found in postgresql-Sun.log when this problem was encountered.

LOG:  database system was shut down in recovery at 2013-05-31 12:13:27 UTC
LOG:  restored log file "00000003.history" from archive
cp: cannot stat `/data/pgsql/archivedir/00000004.history': No such file or directory
LOG:  restored log file "00000003.history" from archive
LOG:  entering standby mode
cp: cannot stat `/data/pgsql/archivedir/00000003000000000000001E': No such file or directory
cp: cannot stat `/data/pgsql/archivedir/00000002000000000000001E': No such file or directory
LOG:  consistent recovery state reached at 0/1E000080
LOG:  record with zero length at 0/1E000080
LOG:  database system is ready to accept read only connections
cp: cannot stat `/data/pgsql/archivedir/00000003000000000000001E': No such file or directory
cp: cannot stat `/data/pgsql/archivedir/00000002000000000000001E': No such file or directory
cp: cannot stat `/data/pgsql/archivedir/00000004.history': No such file or directory
LOG:  streaming replication successfully connected to primary
LOG:  invalid record length at 0/1E000080
FATAL:  terminating walreceiver process due to administrator command
cp: cannot stat `/data/pgsql/archivedir/00000003000000000000001E': No such file or directory
cp: cannot stat `/data/pgsql/archivedir/00000003000000000000001E': No such file or directory
cp: cannot stat `/data/pgsql/archivedir/00000004.history': No such file or directory
LOG:  invalid record length at 0/1E000080
cp: cannot stat `/data/pgsql/archivedir/00000003000000000000001E': No such file or directory
cp: cannot stat `/data/pgsql/archivedir/00000003000000000000001E': No such file or directory
cp: cannot stat `/data/pgsql/archivedir/00000004.history': No such file or directory
LOG:  invalid record length at 0/1E000080
cp: cannot stat `/data/pgsql/archivedir/00000003000000000000001E': No such file or directory
cp: cannot stat `/data/pgsql/archivedir/00000003000000000000001E': No such file or directory
cp: cannot stat `/data/pgsql/archivedir/00000004.history': No such file or directory
LOG:  invalid record length at 0/1E000080
cp: cannot stat `/data/pgsql/archivedir/00000003000000000000001E': No such file or directory
cp: cannot stat `/data/pgsql/archivedir/00000003000000000000001E': No such file or directory
cp: cannot stat `/data/pgsql/archivedir/00000004.history': No such file or directory
LOG:  invalid record length at 0/1E000080

and they just continue like this.

Please help me with the reason for this issue.

regards,

Prakhar

On Mon, May 20, 2013 at 6:06 AM, Amit Langote <amitlangote09@xxxxxxxxx> wrote:

On Wed, May 15, 2013 at 3:12 PM, prakhar jauhari <prak840@xxxxxxxxx> wrote:
> So to allow a standby to recover WAL files that are missing (using archives
> or directly copying wall from the new master to the new standby) in order to
> complete the timeline change, is a wrong approach, I mean is this not safe
> in term of data not being corrupted? Because i tried this and this seems to
> change the timeline on the new standby. For this i added following to my
> recovery file:
>
> restore_command = 'cp <pg_data_dir>/archivedir/%f %p'
> recovery_target_timeline = 'latest'
>
> regards,
> Prakhar.

Hello Prakhar,

Before PostgreSQL 9.3, to switch over from the old master to new
standby (the case in which it failed due to timeline mismatch), you
need to do what you have mentioned you did. The new standby would be
able to transition from old timeline to the new one (in fact the
newest/latest) using timeline history file that is present in the
archive which is updated by the new master to specify at what point in
WAL it branched off from the old timeline to the new timeline. The new
standby is able to follow that information to arrive at a consistent
state. Do try this and report errors if you find any.

Though, you would want to switch to 9.3 to do such things without a
WAL archive.

--
Amit Langote