Re: [ADMIN] Steps to switch from Master to standby mode :
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 000x.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 amitlangot...@gmail.comwrote: On Mon, Jun 3, 2013 at 9:43 PM, prakhar jauhari prak...@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: [ADMIN] Steps to switch from Master to standby mode :
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 0003.history from archive cp: cannot stat `/data/pgsql/archivedir/0004.history': No such file or directory LOG: restored log file 0003.history from archive LOG: entering standby mode cp: cannot stat `/data/pgsql/archivedir/0003001E': No such file or directory cp: cannot stat `/data/pgsql/archivedir/0002001E': No such file or directory LOG: consistent recovery state reached at 0/1E80 LOG: record with zero length at 0/1E80 LOG: database system is ready to accept read only connections cp: cannot stat `/data/pgsql/archivedir/0003001E': No such file or directory cp: cannot stat `/data/pgsql/archivedir/0002001E': No such file or directory cp: cannot stat `/data/pgsql/archivedir/0004.history': No such file or directory LOG: streaming replication successfully connected to primary LOG: invalid record length at 0/1E80 FATAL: terminating walreceiver process due to administrator command cp: cannot stat `/data/pgsql/archivedir/0003001E': No such file or directory cp: cannot stat `/data/pgsql/archivedir/0003001E': No such file or directory cp: cannot stat `/data/pgsql/archivedir/0004.history': No such file or directory LOG: invalid record length at 0/1E80 cp: cannot stat `/data/pgsql/archivedir/0003001E': No such file or directory cp: cannot stat `/data/pgsql/archivedir/0003001E': No such file or directory cp: cannot stat `/data/pgsql/archivedir/0004.history': No such file or directory LOG: invalid record length at 0/1E80 cp: cannot stat `/data/pgsql/archivedir/0003001E': No such file or directory cp: cannot stat `/data/pgsql/archivedir/0003001E': No such file or directory cp: cannot stat `/data/pgsql/archivedir/0004.history': No such file or directory LOG: invalid record length at 0/1E80 cp: cannot stat `/data/pgsql/archivedir/0003001E': No such file or directory cp: cannot stat `/data/pgsql/archivedir/0003001E': No such file or directory cp: cannot stat `/data/pgsql/archivedir/0004.history': No such file or directory LOG: invalid record length at 0/1E80 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 amitlangot...@gmail.comwrote: On Wed, May 15, 2013 at 3:12 PM, prakhar jauhari prak...@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: [ADMIN] Steps to switch from Master to standby mode :
On Mon, Jun 3, 2013 at 9:43 PM, prakhar jauhari prak...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Steps to switch from Master to standby mode :
On Wed, May 15, 2013 at 3:12 PM, prakhar jauhari prak...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Steps to switch from Master to standby mode :
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: [ADMIN] Steps to switch from Master to standby mode :
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 prak...@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
