Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aI1A2-0007wT-F1 for pgsql-admin@arkaria.postgresql.org; Sat, 09 Jan 2016 21:34:58 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aI1A2-000370-1G for pgsql-admin@arkaria.postgresql.org; Sat, 09 Jan 2016 21:34:58 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aI18L-0000fD-2N for pgsql-admin@postgresql.org; Sat, 09 Jan 2016 21:33:13 +0000 Received: from mail-oi0-x22b.google.com ([2607:f8b0:4003:c06::22b]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1aI18G-0004Hy-TA for pgsql-admin@postgresql.org; Sat, 09 Jan 2016 21:33:11 +0000 Received: by mail-oi0-x22b.google.com with SMTP id o124so22586557oia.3 for ; Sat, 09 Jan 2016 13:33:08 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=content-type:mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=4ef9SbEMAMvOxi0Uae5+Rq0/Dcma3hiSbsdInAOVKX4=; b=rV4bCoru5ATxDZq3xnI8imslkLZQeexmN5vVevkFVVNOsQ4Nul3dt29aPxtlbk314K eTMl6k6j6Gn8bRuHw3z0siqqzB3ojkCuqpDIBOGVsINS600fYaeUvx4HCYBc/vyHxfjm H1SRLkGNlgkyfc5Bh3UAqXlWg1r4Ubxxk+sqRKl9bvNnzJ8I9AzrmGYB5o1EVu1iAPmY 3gB5E83gWjpbM6HxTF7vTu9diKgfHsdmI1J9Ja0D6BNUrgE3jqNopF4n6wmH3O8GC6XJ XxhoHZn1RtX9txHF0VzBvxwxk+SbKxz+nK2gi3+JLiH8gT+mL6TsEXTKgsEX8Qya2i/P 9nmw== X-Received: by 10.202.48.6 with SMTP id w6mr80318810oiw.97.1452375187474; Sat, 09 Jan 2016 13:33:07 -0800 (PST) Received: from [192.168.0.2] (cpe-72-182-26-34.austin.res.rr.com. [72.182.26.34]) by smtp.gmail.com with ESMTPSA id mm4sm46928344obb.1.2016.01.09.13.33.06 (version=TLSv1/SSLv3 cipher=OTHER); Sat, 09 Jan 2016 13:33:06 -0800 (PST) Content-Type: multipart/alternative; boundary=Apple-Mail-08B2F1E3-E125-4897-BE9B-4700EF093D5E Mime-Version: 1.0 (1.0) Subject: Re: Recovery - New Slave PostgreSQL 9.2 From: John Scalia X-Mailer: iPad Mail (13C75) In-Reply-To: Date: Sat, 9 Jan 2016 15:33:05 -0600 Cc: Shreeyansh Dba , Ian Barwick , "pgsql-admin@postgresql.org" Content-Transfer-Encoding: 7bit Message-Id: <0650DA9A-49A7-451B-9FAB-39B576BCB5BF@gmail.com> References: <56906451.4010107@2ndquadrant.com> To: "drum.lucas@gmail.com" X-Pg-Spam-Score: -1.7 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --Apple-Mail-08B2F1E3-E125-4897-BE9B-4700EF093D5E Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable Hi, I'm a little late to this thread, but in looking at the errors you originall= y posted, two things come to mind: First, when you built the slave server, I'm assuming you used pg_basebackup a= nd if you did, did you specify -X s in your command? Second, the missing history file isn't an issue, in case you're unfamiliar w= ith this. However, yeah, the missing WAL segment is, as well as the bad time= line error. Is that missing segment still on your primary? You know you co= uld just copy it manually to your standby and start from that. As far as the= timeline error, that's disturbing to me as it's claiming the primary is act= ually a failed over standby. AFAIK, that's the main if not only way transact= ion timelines increment. I would suggest, in spite of of the 2TB size, rebuilding the standby servers= with a proper pg_basebackup. -- Jay Sent from my iPad > On Jan 9, 2016, at 2:19 PM, "drum.lucas@gmail.com" w= rote: >=20 > Hi, thanks for your reply... I've been working on this problem for 20h =3D= ( >=20 > # cat postgresql.conf | grep synchronous_standby_names > #synchronous_standby_names =3D '' - It's commented=20 >=20 > # cat postgresql.conf | grep application_name > log_line_prefix =3D '%m|%p|%q[%c]@%r|%u|%a|%d ' > ( %a =3D application name ) >=20 > I can't resyc all the DB again, because it has 2TB of data :( >=20 > Is there anything else I can do? > Thank you >=20 >=20 >=20 > Lucas Possamai >=20 > kinghost.co.nz >=20 >> On 10 January 2016 at 04:22, Shreeyansh Dba wr= ote: >>=20 >>=20 >>> On Sat, Jan 9, 2016 at 3:28 PM, drum.lucas@gmail.com wrote: >>> My recovery was like that! >>> I was already using that way.. I still have the problem =3D\ >>>=20 >>> Is there anything I can do? >>>=20 >>>=20 >>>=20 >>> Lucas Possamai >>>=20 >>> kinghost.co.nz >>>=20 >>>> On 9 January 2016 at 22:53, Shreeyansh Dba w= rote: >>>>=20 >>>> Hi Lucas, >>>>=20 >>>> Yes , now recovery.conf looks good. >>>> Hope this solve you problem. >>>>=20 >>>>=20 >>>> Thanks and regards, >>>> ShreeyanshDBA Team >>>> Shreeyansh Technologies >>>> www.shreeyansh.com >>>>=20 >>>>=20 >>>>=20 >>>>> On Sat, Jan 9, 2016 at 3:07 PM, drum.lucas@gmail.com wrote: >>>>> Hi there! >>>>>=20 >>>>> Yep, it's correct:=20 >>>>> It looks like You have a set up A (Master) ---> B (Replica) ---> C Rep= lica (Base backup from Replica B) >>>>>=20 >>>>> Master (A): 192.168.100.1 >>>>> Slave1 (B): 192.168.100.2 >>>>> Slave2 (C): 192.168.100.3 >>>>>=20 >>>>> My recovery.conf in slave2(C) is: >>>>> restore_command =3D 'exec nice -n 19 ionice -c 2 -n 7 ../../bin/restor= e_wal_segment.bash "../wal_archive/%f" "%p"' >>>>> archive_cleanup_command =3D 'exec nice -n 19 ionice -c 2 -n 7 ../../bi= n/pg_archivecleaup_mv.bash -d "../wal_archive" "%r"' >>>>> recovery_target_timeline =3D 'latest' >>>>> standby_mode =3D on >>>>> primary_conninfo =3D 'host=3D192.168.100.2 port=3D5432 user=3Dreplicat= or application_name=3Dreplication_slave02' >>>>> So, seems to be right to me... Is that u mean? >>>>>=20 >>>>> Thanks >>>>>=20 >>>>>=20 >>>>> Lucas Possamai >>>>>=20 >>>>> kinghost.co.nz >>>>>=20 >>>>>> On 9 January 2016 at 22:25, Shreeyansh Dba = wrote: >>>>>>> On Sat, Jan 9, 2016 at 8:29 AM, drum.lucas@gmail.com wrote: >>>>>>> * NOTE: I ran the pg_basebackup from another STANDBY SERVER. Not fro= m the MASTER >>>>>>>=20 >>>>>>>=20 >>>>>>>=20 >>>>>>> Lucas Possamai >>>>>>>=20 >>>>>>> kinghost.co.nz >>>>>>>=20 >>>>>>>> On 9 January 2016 at 15:28, drum.lucas@gmail.com wrote: >>>>>>>> Still trying to solve the problem... >>>>>>>> Anyone can help please? >>>>>>>>=20 >>>>>>>> Lucas >>>>>>>>=20 >>>>>>>>=20 >>>>>>>> Lucas Possamai >>>>>>>>=20 >>>>>>>> kinghost.co.nz >>>>>>>>=20 >>>>>>>>> On 9 January 2016 at 14:45, drum.lucas@gmail.com wrote: >>>>>>>>> Sure... Here's the total information: >>>>>>>>> http://superuser.com/questions/1023770/new-postgresql-slave-server= -error-timeline >>>>>>>>>=20 >>>>>>>>> recovery.conf: >>>>>>>>> restore_command =3D 'exec nice -n 19 ionice -c 2 -n 7 ../../bin/re= store_wal_segment.bash "../wal_archive/%f" "%p"' >>>>>>>>> archive_cleanup_command =3D 'exec nice -n 19 ionice -c 2 -n 7 ../.= ./bin/pg_archivecleaup_mv.bash -d "../wal_archive" "%r"' >>>>>>>>> recovery_target_timeline =3D 'latest' >>>>>>>>> standby_mode =3D on >>>>>>>>> primary_conninfo =3D 'host=3D192.168.100.XX port=3D5432 user=3Drep= licator application_name=3Dreplication_new_slave' >>>>>>>>>=20 >>>>>>>>>=20 >>>>>>>>>=20 >>>>>>>>> Lucas Possamai >>>>>>>>>=20 >>>>>>>>> kinghost.co.nz >>>>>>>>>=20 >>>>>>>>>> On 9 January 2016 at 14:37, Ian Barwick wro= te: >>>>>>>>>> On 16/01/09 9:23, drum.lucas@gmail.com wrote: >>>>>>>>>> > Hi all! >>>>>>>>>> > >>>>>>>>>> > I've done the pg_basebackup from the live to a new slave server= ... >>>>>>>>>> > >>>>>>>>>> > I've recovery the wal files, but now that I configured to repli= cate from the master (recovery.conf) I got this error: >>>>>>>>>> > >>>>>>>>>> > ../wal_archive/0000000400000C68000000C8` not found >>>>>>>>>> > ../wal_archive/00000005.history` not found >>>>>>>>>> > >>>>>>>>>> > FATAL: timeline 2 of the primary does not match recovery targe= t timeline 1 >>>>>>>>>>=20 >>>>>>>>>> Can you post the contents of your recovery.conf file, suitably >>>>>>>>>> anonymised if necessary? >>>>>>>>>>=20 >>>>>>>>>> Regards >>>>>>>>>>=20 >>>>>>>>>> Ian Barwick >>>>>>=20 >>>>>>=20 >>>>>> Hi Lucas, >>>>>>=20 >>>>>> I followed your question I generated the same error: >>>>>>=20 >>>>>> cp: cannot stat `/pgdata/arch/00000003.history': No such file or dire= ctory >>>>>> 2016-01-09 14:11:42 IST FATAL: timeline 1 of the primary does not >>>>>> match recovery target timeline 2 >>>>>>=20 >>>>>> It looks like You have a set up A (Master) ---> B (Replica) ---> C Re= plica (Base backup from Replica B) >>>>>>=20 >>>>>> It seems you have used recovery.conf (to replicate from master to sla= ve) to new replica setup C and there is high probability not changing the pr= imary connection info >>>>>> in C's recovery.conf (Replica B's Connection info) >>>>>>=20 >>>>>> During testing providing B's connection info in C's recovery.conf res= olved the issue. >>>>>>=20 >>>>>> Please verify the Primary connection info parameter in recovery.conf (= C replica) might resolve your problem. >>>>>>=20 >>>>>>=20 >>>>>>=20 >>>>>> Thanks and regards, >>>>>> ShreeyanshDBA Team >>>>>> Shreeyansh Technologies >>>>>> www.shreeyansh.com=20 >>=20 >> Hi Lucas, >>=20 >> It looks like application_name parameter that set in recovery.conf may mi= smatch.=20 >> Please verify the value to synchronous_standby_names value set in the po= stgresql.conf of Replica - C and the value that using as application_name in= recovery.conf >>=20 >> Also, check whether the Async replication works with out using applicatio= n_name in recovery.conf of replica -C and check the status in pg_stat_replic= ation catalog table. >>=20 >>=20 >> Thanks and regards >> ShreeyanshDBA Team >> Shreeyansh Technologies >> www.shreeyansh.com >=20 --Apple-Mail-08B2F1E3-E125-4897-BE9B-4700EF093D5E Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Hi,

I'm a little late to this thread,= but in looking at the errors you originally posted, two things come to mind= :

First, when you built the slave server, I'm assuming you used pg_baseback= up and if you did, did you specify -X s in your command?

Second, the missin= g history file isn't an issue, in case you're unfamiliar with this. However,= yeah, the missing WAL segment is, as well as the bad timeline error.  = Is that missing segment still on your primary?  You know you could just= copy it manually to your standby and start from that. As far as the timelin= e error, that's disturbing to me as it's claiming the primary is actually a f= ailed over standby. AFAIK, that's the main if not only way transaction timel= ines increment.

I would suggest, in spite of of the 2TB size, rebuilding th= e standby servers with a proper pg_basebackup.
--
Jay

Sent from my iPad

On Jan 9, 2016, at 2:19 PM, "drum.lucas@gmail.com" <= drum.lucas@gmail.com> wrote:

<= div>
Hi, thanks for your reply... I've been working on this p= roblem for 20h =3D(

# cat postgresql.conf | grep = synchronous_standby_names
<= div>#synchronous_standby_names =3D '' - It'= s commented 

=
# cat postgresql.conf |=  grep application_name
log_line_prefix =3D '%m|%p|%q[%c]@%r|%u|%a|%d '
= ( %a =3D application name )

I can't resyc all the DB again, because it has 2TB of dat= a :(

Is there anything else I can do?
Thank you



Lucas Possamai

king= host.co.nz

On 10 January 2016 at 04:22, Shreeyansh Dba <= span dir=3D"ltr"><shreeyansh2014@gmail.com> wrote:


On Sat, Jan 9, 2016 at 3:28 PM, drum.lucas@gmail.com <drum.lucas@gmail.com> wrote:
My recovery was like that!
I was already using that way.. I still hav= e the problem =3D\

Is there anything I can do?



Lucas Possamai


On 9 January 2016 at 22:53, Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:

Hi Lucas,=

= Yes , now recovery.conf looks good.
Hope this solve you problem= .


Thanks and regards,
ShreeyanshDBA Team
Shreeyansh Technologies



On Sat, Jan 9, 2= 016 at 3:07 PM, dr= um.lucas@gmail.com <drum.lucas@gmail.com> wrote:
Hi there!

Yep, it's correct:=  
It looks like You have a set up A (Master) ---> B (Replica) -= --> C Replica (Base backup from Replica B)

Master (A): 192.168.100.1
Slave1 (B): 192.168.100.2
Slave2 (C): 192.168.100.3

My recovery.conf= in slave2(C) is:
restore_comman=
d =
=3D 'exec nice -n 19 ionice -c 2 -n 7 ../../bin/restore_wal_segment.bash "../w=
al_archive/%f" "%p"'
archive_cleanup_command =3D 'exec nice -n 19 ionice -c 2 -n 7 ../../bin/pg_archi=
vecleaup_mv.bash -d "../wal_archive" "%r"'
recovery_target_timeline =3D 'latest'
standby_mode =3D on
primary_conninfo =3D 'host=3D192.168.100.2 port=3D5432 user=3Dreplicator applica=
tion_name=3Dreplication_slave02'
So, seems to b= e right to me... Is that u mean?

Thanks
<= br>

Lucas Possamai

<= div>

On 9 January 2016 at 22:25, Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:
O= n Sat, Jan 9, 2016 at 8:29 AM, drum.lucas@gmail.com <drum.lucas@gmail.com> wrote:
* NOTE: I ran the pg_b= asebackup from another STANDBY SERVER. Not from the MASTER



Lucas Possamai


On 9 January 2016 at 15:28, drum.lucas@gmail.com <drum.luc= as@gmail.com> wrote:
Still t= rying to solve the problem...
Anyone can help please?

Lucas


Lucas Possamai


On 9 January 2016 at 14:45, drum.lucas@gmail.com <drum.luc= as@gmail.com> wrote:
Sure..= . Here's the total information:

recovery.conf:
restore_command =3D 'exec nice -n 19 ionice -c 2 -n 7 .=
./../bin/restore_wal_segment.bash "../wal_archive/%f" "%p"'
archive_cleanup_command =3D 'exec nice -n 19 ionice -c 2 -n 7 ../../bin/pg_a=
rchivecleaup_mv.bash -d "../wal_archive" "%r"'
recovery_target_timeline =3D 'latest'
standby_mode =3D on
primary_conninfo =3D 'host=3D192.168.100.XX port=3D5432 user=3Dreplicator ap=
plication_name=3Dreplication_new_slave'



Lucas Possamai


On 9 January 2016 at 14:37, Ian Barwick <ia= n@2ndquadrant.com> wrote:
On 16/01/09 9= :23, drum.lucas@gm= ail.com wrote:
> Hi all!
>
> I've done the pg_basebackup from the live to a new slave server...
>
> I've recovery the wal files, but now that I configured to replicate fro= m the master (recovery.conf) I got this error:
>
> ../wal_archive/0000000400000C68000000C8` not found
> ../wal_archive/00000005.history` not found
>
> FATAL:  timeline 2 of the primary does not match recovery target t= imeline 1

Can you post the contents of your recovery.conf file, suitably
anonymised if necessary?

Regards

Ian Barwick


Hi Lucas,

<= /font>
I followed your questio= n I generated the same error:

cp: cannot s= tat `/pgdata/arch/00000003.history': No such file or directory
<= div>2016-01-09 14:11:42 IST FATAL:  = ;timeline 1 of the primary does not
match recovery target timeline 2

It looks like You have a set up A (Master) ---> B (Replica) ---> C= Replica (Base backup from Replica B)

It s= eems you have used recovery.conf (to replicate from master to slave) to new r= eplica setup C and there is high probability not changing the primary connec= tion info
in C's recove= ry.conf (Replica B's Connection info)

Dur= ing testing providing B's connection info in C's recovery.conf resolved the i= ssue.

=
Please verify the Primary connectio= n info parameter in recovery.conf (C replica) might resolve your problem.



Tha= nks and regards,
Shreey= anshDBA Team
Shreeyansh= Technologies





Hi Lucas= ,

It looks like applicat= ion_name parameter that set in recovery.conf may mismatch. 
Please verify the= value to synchronous_standby_names  value set in the postgresql.conf o= f Replica - C and the value that using as application_name in recovery.conf<= /span>

Also, check whether the Async replication works wit= h out using application_name in recovery.conf of replica -C and check the st= atus in pg_stat_replication catalog table.

Thanks and regards
ShreeyanshDBA= Team
Shreeyansh Technolog= ies
=

= --Apple-Mail-08B2F1E3-E125-4897-BE9B-4700EF093D5E--