Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YXWMx-0004TU-Cx for pgsql-admin@arkaria.postgresql.org; Mon, 16 Mar 2015 14:51:51 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YXWMv-0003TM-Uh for pgsql-admin@arkaria.postgresql.org; Mon, 16 Mar 2015 14:51:50 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YXWMt-0003T9-Dl; Mon, 16 Mar 2015 14:51:47 +0000 Received: from correo.teltronic.es ([93.92.233.233] helo=teltronic.es) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YXWMj-0007Gq-VM; Mon, 16 Mar 2015 14:51:45 +0000 Received: from RMELENDOW7 (unknown [192.168.8.40]) (using TLSv1 with cipher AES256-SHA (256/256 bits)) (No client certificate requested) by correo.teltronic.es (Postfix) with ESMTP id 3844744BF72; Mon, 16 Mar 2015 15:51:35 +0100 (CET) From: "Ruth Melendo" To: , Subject: how to recovery your database when stops replicating Date: Mon, 16 Mar 2015 15:51:47 +0100 Message-ID: <003501d05ff8$ba349240$2e9db6c0$@teltronic.es> MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_NextPart_000_0036_01D06001.1BFAA7F0" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AdBf+LiLgDXE62x+SYizHpYWaPWSCQ== Content-Language: es X-Pg-Spam-Score: -1.9 (-) 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 This is a multipart message in MIME format. ------=_NextPart_000_0036_01D06001.1BFAA7F0 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0037_01D06001.1BFAA7F0" ------=_NextPart_001_0037_01D06001.1BFAA7F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi all, =20 I=B4m testing BDR and have a big deal. When it gets corrupted, after = time of working well, I cannot do a recovery and the only way to get the system working again seems to reset all again. It means: =20 - Stop all nodes. - Delete data directories in both nodes - Comment BDR entries in postgresql.conf in node A. - Start node A and restore database. - Uncomment BDR entries in node A. - Start node B with BDR entries so that executes initial copy. - It works again. =20 I think there should be a better way to do it. I have some mails from = here telling this: =20 - Execute this sql in both nodes: =20 SELECT slot_name, datoid, database, active FROM pg_replication_slots = WHERE slot_name LIKE 'bdr_'||(SELECT oid FROM pg_database WHERE datname =3D current_database())||'_%'; =20 - Execute this sql to be able to DDL again: COPY (SELECT * FROM pg_logical_slot_get_binary_changes('bdr_17911_6120567807158814813_1_16385= __' , NULL, 1, 'interactive', 'true')) TO '/dev/null'; =20 - Reconnect and do this: SET bdr.skip_ddl_replication =3D on; =20 =20 But that method don=B4t work for me. Any help? How do you recovery your database when stops replicating? =20 Thanks in advance =20 Ruth Patricia Melendo Ventura Software Engineer TELTRONIC, S.A.U. T: +34 976 465656 Ext. 179 F: +34 976 465722=20 www.teltronic.es =20 Logo40 =20 Before printing this e-mail please consider your environmental responsibility.=20 ***** DISCLAIMER ***** This message is intended exclusively for the named person. It may = contain confidential, propietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. = If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify = the sender. Your must not, directly or indirectly, use, disclose, = distribute, print, or copy any part of this message if you are not the intended recipient. Any views expressed in this message are those of the = individual sender, except where the message states otherwise and the sender is authorised to state them to be the views of TELTRONIC. If the addressee = of this message does not consent to the use of internet e-mail, please communicate it to us immediately. =20 ------=_NextPart_001_0037_01D06001.1BFAA7F0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi = all,

 

I=B4m testing BDR and have a big = deal. When it gets corrupted, after time of working well, I cannot do a = recovery and the only way to get the system working again seems to reset = all again. It means:

 

-          = Stop all = nodes.

-          = Delete data = directories in both nodes

-          = Comment BDR entries in = postgresql.conf in node A.

-          = Start node A and = restore database.

-          = Uncomment BDR entries = in node A.

-          = Start node B with BDR = entries so that executes initial copy.

-          = It works = again.

 

I think there should be a better way to do it. I have some = mails from here telling this:

 

-          = Execute this sql in = both nodes:

 

SELECT slot_name, = datoid, database, active FROM pg_replication_slots WHERE slot_name LIKE = 'bdr_'||(SELECT oid FROM pg_database WHERE datname =3D = current_database())||'_%';

 

-          = Execute this sql to be = able to DDL again:

COPY (SELECT * FROM = pg_logical_slot_get_binary_changes('bdr_17911_6120567807158814813_1_16385= __', NULL, 1, 'interactive', 'true')) TO = '/dev/null';

 

-          = Reconnect =A0and do = this:

SET = bdr.skip_ddl_replication =3D on;

 

 

But that method don=B4t work for me. Any help? How do you = recovery your database when stops replicating?

 

Thanks in = advance

 

Ruth = Patricia Melendo Ventura

Software = Engineer

TELTRONIC, = S.A.U.=

T: +34 976 465656=A0=A0 = Ext. 179

F: +34 976 = 465722

www.teltronic.es

=

 <= /p>

3DLogo40

 =

Before = printing this e-mail please consider your environmental = responsibility. = =

*****&nbs= p; DISCLAIMER  *****

This = message is intended exclusively for the named person. It may contain = confidential, propietary or legally privileged information. No = confidentiality or privilege is waived or lost by any mistransmission. = If you receive this message in error, please immediately delete it and = all copies of it from your system, destroy any hard copies of it and = notify the sender. Your must not, directly or indirectly, use, disclose, = distribute, print, or copy any part of this message if you are not the = intended recipient. Any views expressed in this message are those of the = individual sender, except where the message states otherwise and the = sender is authorised to state them to be the views of TELTRONIC. If the = addressee of this message does not consent to the use of internet = e-mail, please communicate it to us immediately.

 

------=_NextPart_001_0037_01D06001.1BFAA7F0-- ------=_NextPart_000_0036_01D06001.1BFAA7F0 Content-Type: image/jpeg; name="image002.jpg" Content-Transfer-Encoding: base64 Content-ID: /9j/4AAQSkZJRgABAQEAYABgAAD/2wBDAAoHBwkHBgoJCAkLCwoMDxkQDw4ODx4WFxIZJCAmJSMg IyIoLTkwKCo2KyIjMkQyNjs9QEBAJjBGS0U+Sjk/QD3/2wBDAQsLCw8NDx0QEB09KSMpPT09PT09 PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT3/wAARCAA0AD8DASIA AhEBAxEB/8QAHwAAAQUBAQEBAQEAAAAAAAAAAAECAwQFBgcICQoL/8QAtRAAAgEDAwIEAwUFBAQA AAF9AQIDAAQRBRIhMUEGE1FhByJxFDKBkaEII0KxwRVS0fAkM2JyggkKFhcYGRolJicoKSo0NTY3 ODk6Q0RFRkdISUpTVFVWV1hZWmNkZWZnaGlqc3R1dnd4eXqDhIWGh4iJipKTlJWWl5iZmqKjpKWm p6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uHi4+Tl5ufo6erx8vP09fb3+Pn6/8QAHwEA AwEBAQEBAQEBAQAAAAAAAAECAwQFBgcICQoL/8QAtREAAgECBAQDBAcFBAQAAQJ3AAECAxEEBSEx BhJBUQdhcRMiMoEIFEKRobHBCSMzUvAVYnLRChYkNOEl8RcYGRomJygpKjU2Nzg5OkNERUZHSElK U1RVVldYWVpjZGVmZ2hpanN0dXZ3eHl6goOEhYaHiImKkpOUlZaXmJmaoqOkpaanqKmqsrO0tba3 uLm6wsPExcbHyMnK0tPU1dbX2Nna4uPk5ebn6Onq8vP09fb3+Pn6/9oADAMBAAIRAxEAPwD2as3W NdstEg8y6k+cj5I15Zvw/rVXxJ4hXRoVjgCy3knCR+g9T/h3qhonhYmb+0tcYzXbHcEc5VPr7+3Q VvCklHnqbfmc06zc/Z0tX1fRFC5vta121kvJLn+x9IUZ3Z/eOPboTnt0/GtTwHMW8LhpCwjWWTY8 jcsuc5Pp3/KsTV7mbxl4gTSLNtunwNukde+OC39B+dXfF+pCzt4tFsAFUoA4Uchf4VH1rqlBzSpJ Wb19F5+ZyKoqblWbulp6vy8iDxD41mkla00clcNtMwGS/so/rWSvh3xIxfUt7pNbjzA0kx3nvgf1 H4V1vhXwzHpUC3Vyub2ReQekY9B7+tXvE+oDTPD15OSA3llE92PA/nS9tGElTor59yvYSqRdWu/O 3Yd4c1Vta0K1vXXbJIuHA6bgcHH5VqVyXhWefR/DtjFc27eS671cdRuOf611MM8dwgeJwyn0rzZV qUqsqcXqm9D0acZ+yjKXVI8+tNVtIvEdzqGrJK0iufLRVDbSDjnnsBiruu+OYLnTZbbT45lklXaZ HAG0d+/pWxe+DdOvruS4Zp42kO5ljYAZ79q4zXdKtbDxFFp8Bk8rMYdpG5O488/Q168HRrST1ul8 tDxpRxGHi1pZvfq7kGg+IrnRQ1vp9nDLPcMBufJY+gABrd8MWj6z4hl1C6wfKbzG9N56Y+n+Fdja 6TYWQ/0W0hiONu5EAbH161yGl6onhK7vrS+gmO5wyFAOQM4644qfaqqp+zj7xU6Loyp+1l7qb9F2 O6JCqSxwBySa871S9PjXxNa6fa7jYQtlmxjcP4m/LgVHquv6l4omFjYQOkJP+rQ5LD1Y+ldh4b8O xaBaMu4SXEpBkkxj8B7CsVBYZc0vi6LsdLqfWpcsPhW77+RqG3iNuINi+UFChewHasCCZtOvZFU5 VSVI9fStW/1SO1BSMiSY8BR2+tVtM01y/wBouRyckKe+e5r5vG3r14Ro/Et329f8j2aPuQbnszYr Km8P6fPrH9ozx+ZcALgM3yjHQ4rVrHEM7XbRkhnJLueoP93P9BXsxk47M45QjL4lc0HuJFkZVh8w jsrjOPXBrNv4rfVlxc6Z54jO3cZUBU+mQamitbhrdd8eJCcPl+XH1HbPPqaFsZXtJEeJNwclFzgH t+HHSs7STvGTX3f5FNRkrSVyK2hk06FY7DT7a1Rmx88nLH8M5/OrBsr24yLm8Cof4YVxn8TQbOZp kRo4xDGVZCG+7jtitGs5Ued3nJv5/wCVioy5VaKSKtrpttaHMUfzf3m5NWqKKuEIwXLFWQnJyd2F NCKpYgAFjkn1ooqxDqKKKACiiigAooooA//Z ------=_NextPart_000_0036_01D06001.1BFAA7F0--