Обсуждение: Steps to switch from Master to standby mode :
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:
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".
"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/
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
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
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'
restore_command = 'cp <pg_data_dir>/archivedir/%f %p'
recovery_target_timeline = 'latest'
regards,
Prakhar.
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
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
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:
Hello Prakhar,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.
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
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
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_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.
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'
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'
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:Can you provide exact steps you have used "to setup up Master to
>
> and they just continue like this.
> Please help me with the reason for this issue.
>
standby switch"? That might help reproducing the problem and provide
some pointers as to what is happening.
--
Amit Langote