Postgres Pro
Downloads
Home   >   mailing lists

Thread: Steps to switch from Master to standby mode :

Steps to switch from Master to standby mode :

From
prakhar jauhari
Date:
Hi all,

I have been trying to setup Postgres 9.2 in HA using streaming replication and base backup.
There is no problem in switching from:
Standby -> Master using the trigger file mechanism provided by postgres.

The problem comes when switching from:
Master -> Stanbdy : I try to set up Streaming replication from the new standby to the new Master,
but replication doesn't start, rather i find the following error in postgres logs

"FATAL:  timeline 2 of the primary does not match recovery target timeline 1".

Is there any way the timeline can be bumped up to the correct number on the new standby, without taking
base backup. And is it safe to use the method described in
http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-timeline-switch-of-slave-node-without-archives/
to bump up the timeline.

regards,
Prakhar

Re: Steps to switch from Master to standby mode :

From
Strahinja Kustudić
Date:
I think that you need to create a new base backup from the new master to make the old master a standby server. I usually do this using rsync, so that it takes as fast as possible, but you could also use a tool like http://www.repmgr.org/

Regards,
Strahinja


On Mon, May 13, 2013 at 9:23 AM, prakhar jauhari <prak840@gmail.com> wrote:
Hi all,

I have been trying to setup Postgres 9.2 in HA using streaming replication and base backup.
There is no problem in switching from:
Standby -> Master using the trigger file mechanism provided by postgres.

The problem comes when switching from:
Master -> Stanbdy : I try to set up Streaming replication from the new standby to the new Master,
but replication doesn't start, rather i find the following error in postgres logs

"FATAL:  timeline 2 of the primary does not match recovery target timeline 1".

Is there any way the timeline can be bumped up to the correct number on the new standby, without taking
base backup. And is it safe to use the method described in
http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-timeline-switch-of-slave-node-without-archives/
to bump up the timeline.

regards,
Prakhar

Re: Steps to switch from Master to standby mode :

From
prakhar jauhari
Date:
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.

Re: Steps to switch from Master to standby mode :

From
Amit Langote
Date:
On Wed, May 15, 2013 at 3:12 PM, prakhar jauhari <prak840@gmail.com> 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

Re: Steps to switch from Master to standby mode :

From
prakhar jauhari
Date:
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@gmail.com> wrote:
On Wed, May 15, 2013 at 3:12 PM, prakhar jauhari <prak840@gmail.com> 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

Re: Steps to switch from Master to standby mode :

From
Amit Langote
Date:
On Mon, Jun 3, 2013 at 9:43 PM, prakhar jauhari <prak840@gmail.com> wrote:

>
> and they just continue like this.
> Please help me with the reason for this issue.
>

Can you provide exact steps you have used "to setup up Master to
standby switch"? That might help reproducing the problem and provide
some pointers as to what is happening.


--
Amit Langote

Re: Steps to switch from Master to standby mode :

From
prakhar jauhari
Date:
Hey,

Using these steps to for archiving and HA:

Node1 : current master ......         Node2 : current standby


On master  in (postgresql.conf):

archive_mode = on
archive_command = 'test ! -f /data/pgsql/archivedir/%f && cp %p /data/pgsql/archivedir/%f'
archive_timeout = 3600

Note : I sync /data/pgsql/archivedir/ between master and standby using rsync.

On standby in (recovery.conf):

standby_mode = 'on'
primary_conninfo = 'host=<MASTER IP> port=5432 user=replicationuser'
trigger_file = '/tmp/pg_failover_trigger'
restore_command = 'cp /data/pgsql/archivedir/%f %p'
recovery_target_timeline = 'latest'

So when a switchover happens,

Node1 = new standby .......         Node2 = new master.

On Node1 :
1. I stop postgres.
2. Update postgresql.conf to remove archiving settings.
3. Place recovery.conf in data cluster.
5. Forcefully sync /data/pgsql/archivedir/ from Node2. (To get 0000000x.history file in Node1 archives).
4. And restart postgres.

So Node1 comes up in Standby mode now.
It connects Streaming Replication with Node2.
But then breaks the SR connection giving above errors.

One doubt that i have is, that when Node1 comes up it has all the archives (including that it generated when Node1 was master) when it comes up in standby mode.
Is that a problem?

Please let me know the problem with this approach.


regards,
Prakhar.



On Mon, Jun 3, 2013 at 7:46 PM, Amit Langote <amitlangote09@gmail.com> wrote:
On Mon, Jun 3, 2013 at 9:43 PM, prakhar jauhari <prak840@gmail.com> wrote:

>
> and they just continue like this.
> Please help me with the reason for this issue.
>

Can you provide exact steps you have used "to setup up Master to
standby switch"? That might help reproducing the problem and provide
some pointers as to what is happening.


--
Amit Langote