Re: [GENERAL] BDR replication port

[Zhu, Joshua]

Thanks for the idea, and that is it... it's indeed the other direction of 
replication was affected by blocking a port

-Original Message-
From: Alvaro Aguayo Garcia-Rada [mailto:aagu...@opensysperu.com] 
Sent: Friday, August 25, 2017 5:00 PM
To: Zhu, Joshua <j...@thalesesec.net>
Cc: PostgreSql-general <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] BDR replication port

That's weird. Another idea: Do changes on that server get replicated to the 
other servers? I'm not sure if incomming connections are used to receive WAL or 
to send it.

Regards,

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 
954183248
Website: www.ocs.pe

- Original Message -
From: "Zhu, Joshua" <j...@vormetric.com>
To: "Alvaro Aguayo Garcia-Rada" <aagu...@opensysperu.com>
Cc: "PostgreSql-general" <pgsql-general@postgresql.org>
Sent: Friday, 25 August, 2017 18:35:21
Subject: RE: [GENERAL] BDR replication port

Thought about that possibility, so postgres on the node with port blocked was 
restarted after blocking the port.

-Original Message-
From: Alvaro Aguayo Garcia-Rada [mailto:aagu...@opensysperu.com] 
Sent: Friday, August 25, 2017 3:23 PM
To: Zhu, Joshua <j...@thalesesec.net>
Cc: PostgreSql-general <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] BDR replication port

Just a guess: How did you blocked the port? Depending on that, you could be 
blocking only new connections, but connections already established would 
continue to transmit data; remember BDR only reconnects when connection is lost.

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 
954183248
Website: www.ocs.pe

- Original Message -
From: "Zhu, Joshua" <j...@vormetric.com>
To: "PostgreSql-general" <pgsql-general@postgresql.org>
Sent: Friday, 25 August, 2017 16:49:44
Subject: [GENERAL] BDR replication port

Hi, I am experimenting how network configuration impacts BDR replication, ran 
into something that I can't explain, and wonder if someone can shed light.  
Here it goes:

With a four node BDR group configured and running (all using default port 
5432), I purposely blocked port 5432 on one of the node in the group, and was 
expecting to see changes on other nodes stop being replicated to this node, but 
that's not  what happened.

Shell commands show that the port was indeed blocked  (In the following example 
session, the port 5432 is blocked on 10.3.122.31, but open on 10.3.122.21):

% nc -v --send-only 10.3.122.21 5432 http://nmap.org/ncat )
Ncat: Connected to 10.3.122.21:5432.
Ncat: 0 bytes sent, 0 bytes received in 0.00 seconds.

% nc -v --send-only 10.3.122.31 5432 http://nmap.org/ncat )
Ncat: Connection timed out.

% psql -h 10.3.122.21 mydb
psql (9.4.10)
Type "help" for help.
mydb=#

% psql -h 10.3.122.31 mydb
psql: could not connect to server: Connection timed out
Is the server running on host "10.3.122.31" and accepting
TCP/IP connections on port 5432?

At this state, I tried insertion and update on node 10.3.122.21, and all of 
which were replicated to node 10.3.122.31.  However, attempt to create a new 
table on node 10.3.122.21 was stuck (as expected) until the port 5432 on 
10.3.122.31 opened again.

So my question is, is there another port other than port 5432 that BDR uses for 
replication? If not, how could changes be replicated to 10.3.122.31 when its 
port 5432 was blocked?

Thanks,


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR replication port

[Alvaro Aguayo Garcia-Rada]

That's weird. Another idea: Do changes on that server get replicated to the 
other servers? I'm not sure if incomming connections are used to receive WAL or 
to send it.

Regards,

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 
954183248
Website: www.ocs.pe

- Original Message -
From: "Zhu, Joshua" <j...@vormetric.com>
To: "Alvaro Aguayo Garcia-Rada" <aagu...@opensysperu.com>
Cc: "PostgreSql-general" <pgsql-general@postgresql.org>
Sent: Friday, 25 August, 2017 18:35:21
Subject: RE: [GENERAL] BDR replication port

Thought about that possibility, so postgres on the node with port blocked was 
restarted after blocking the port.

-Original Message-
From: Alvaro Aguayo Garcia-Rada [mailto:aagu...@opensysperu.com] 
Sent: Friday, August 25, 2017 3:23 PM
To: Zhu, Joshua <j...@thalesesec.net>
Cc: PostgreSql-general <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] BDR replication port

Just a guess: How did you blocked the port? Depending on that, you could be 
blocking only new connections, but connections already established would 
continue to transmit data; remember BDR only reconnects when connection is lost.

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 
954183248
Website: www.ocs.pe

- Original Message -
From: "Zhu, Joshua" <j...@vormetric.com>
To: "PostgreSql-general" <pgsql-general@postgresql.org>
Sent: Friday, 25 August, 2017 16:49:44
Subject: [GENERAL] BDR replication port

Hi, I am experimenting how network configuration impacts BDR replication, ran 
into something that I can't explain, and wonder if someone can shed light.  
Here it goes:

With a four node BDR group configured and running (all using default port 
5432), I purposely blocked port 5432 on one of the node in the group, and was 
expecting to see changes on other nodes stop being replicated to this node, but 
that's not  what happened.

Shell commands show that the port was indeed blocked  (In the following example 
session, the port 5432 is blocked on 10.3.122.31, but open on 10.3.122.21):

% nc -v --send-only 10.3.122.21 5432 http://nmap.org/ncat )
Ncat: Connected to 10.3.122.21:5432.
Ncat: 0 bytes sent, 0 bytes received in 0.00 seconds.

% nc -v --send-only 10.3.122.31 5432 http://nmap.org/ncat )
Ncat: Connection timed out.

% psql -h 10.3.122.21 mydb
psql (9.4.10)
Type "help" for help.
mydb=#

% psql -h 10.3.122.31 mydb
psql: could not connect to server: Connection timed out
Is the server running on host "10.3.122.31" and accepting
TCP/IP connections on port 5432?

At this state, I tried insertion and update on node 10.3.122.21, and all of 
which were replicated to node 10.3.122.31.  However, attempt to create a new 
table on node 10.3.122.21 was stuck (as expected) until the port 5432 on 
10.3.122.31 opened again.

So my question is, is there another port other than port 5432 that BDR uses for 
replication? If not, how could changes be replicated to 10.3.122.31 when its 
port 5432 was blocked?

Thanks,


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR replication port

[Zhu, Joshua]

Thought about that possibility, so postgres on the node with port blocked was 
restarted after blocking the port.

-Original Message-
From: Alvaro Aguayo Garcia-Rada [mailto:aagu...@opensysperu.com] 
Sent: Friday, August 25, 2017 3:23 PM
To: Zhu, Joshua <j...@thalesesec.net>
Cc: PostgreSql-general <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] BDR replication port

Just a guess: How did you blocked the port? Depending on that, you could be 
blocking only new connections, but connections already established would 
continue to transmit data; remember BDR only reconnects when connection is lost.

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 
954183248
Website: www.ocs.pe

- Original Message -
From: "Zhu, Joshua" <j...@vormetric.com>
To: "PostgreSql-general" <pgsql-general@postgresql.org>
Sent: Friday, 25 August, 2017 16:49:44
Subject: [GENERAL] BDR replication port

Hi, I am experimenting how network configuration impacts BDR replication, ran 
into something that I can't explain, and wonder if someone can shed light.  
Here it goes:

With a four node BDR group configured and running (all using default port 
5432), I purposely blocked port 5432 on one of the node in the group, and was 
expecting to see changes on other nodes stop being replicated to this node, but 
that's not  what happened.

Shell commands show that the port was indeed blocked  (In the following example 
session, the port 5432 is blocked on 10.3.122.31, but open on 10.3.122.21):

% nc -v --send-only 10.3.122.21 5432 http://nmap.org/ncat )
Ncat: Connected to 10.3.122.21:5432.
Ncat: 0 bytes sent, 0 bytes received in 0.00 seconds.

% nc -v --send-only 10.3.122.31 5432 http://nmap.org/ncat )
Ncat: Connection timed out.

% psql -h 10.3.122.21 mydb
psql (9.4.10)
Type "help" for help.
mydb=#

% psql -h 10.3.122.31 mydb
psql: could not connect to server: Connection timed out
Is the server running on host "10.3.122.31" and accepting
TCP/IP connections on port 5432?

At this state, I tried insertion and update on node 10.3.122.21, and all of 
which were replicated to node 10.3.122.31.  However, attempt to create a new 
table on node 10.3.122.21 was stuck (as expected) until the port 5432 on 
10.3.122.31 opened again.

So my question is, is there another port other than port 5432 that BDR uses for 
replication? If not, how could changes be replicated to 10.3.122.31 when its 
port 5432 was blocked?

Thanks,


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR replication port

[Alvaro Aguayo Garcia-Rada]

Just a guess: How did you blocked the port? Depending on that, you could be 
blocking only new connections, but connections already established would 
continue to transmit data; remember BDR only reconnects when connection is lost.

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 
954183248
Website: www.ocs.pe

- Original Message -
From: "Zhu, Joshua" 
To: "PostgreSql-general" 
Sent: Friday, 25 August, 2017 16:49:44
Subject: [GENERAL] BDR replication port

Hi, I am experimenting how network configuration impacts BDR replication, ran 
into something that I can't explain, and wonder if someone can shed light.  
Here it goes:

With a four node BDR group configured and running (all using default port 
5432), I purposely blocked port 5432 on one of the node in the group, and was 
expecting to see changes on other nodes stop being replicated to this node, but 
that's not  what happened.

Shell commands show that the port was indeed blocked  (In the following example 
session, the port 5432 is blocked on 10.3.122.31, but open on 10.3.122.21):

% nc -v --send-only 10.3.122.21 5432 http://nmap.org/ncat )
Ncat: Connected to 10.3.122.21:5432.
Ncat: 0 bytes sent, 0 bytes received in 0.00 seconds.

% nc -v --send-only 10.3.122.31 5432 http://nmap.org/ncat )
Ncat: Connection timed out.

% psql -h 10.3.122.21 mydb
psql (9.4.10)
Type "help" for help.
mydb=#

% psql -h 10.3.122.31 mydb
psql: could not connect to server: Connection timed out
Is the server running on host "10.3.122.31" and accepting
TCP/IP connections on port 5432?

At this state, I tried insertion and update on node 10.3.122.21, and all of 
which were replicated to node 10.3.122.31.  However, attempt to create a new 
table on node 10.3.122.21 was stuck (as expected) until the port 5432 on 
10.3.122.31 opened again.

So my question is, is there another port other than port 5432 that BDR uses for 
replication? If not, how could changes be replicated to 10.3.122.31 when its 
port 5432 was blocked?

Thanks,


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR replication and table triggers

[Craig Ringer]

> However if I perform any INSERT, UPDATE or DELETE operations on
> DB2 and these changes propagate over to DB1 via BDR I do not see DB1 firing
> any triggers. Is this intended behavior?

Yes.

>  My current understanding is that
> BDR is unable to invoke Postgres triggers as it operates on the rows
> directly, a layer below Postgres. Is this Correct?

Yes.

>  Is there any mechanism
> that exists that could provide notifications to a listening application when
> BDR makes changes to the underlying database?

You could listen to an underlying logical decoding stream, but it
might be a bit fiddly and complex for your needs.

Ideally we'd be able to fire triggers in BDR, but that's not
implemented or on the current roadmap and there's no funded work on it
at this point. There's some work to support it in pglogical though.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR replication and table triggers

[Sylvain Marechal]

Why not using the logical decoding feature:
https://www.postgresql.org/docs/9.4/static/logicaldecoding-example.html

On both sides, you would have a process that regularly decodes the stream
and emits notifications for event in tables you are insterested in.

Sylvain

2017-05-02 18:18 GMT+02:00 Alvaro Aguayo Garcia-Rada <
aagu...@opensysperu.com>:

> Hi.
>
> It's not like BDR is unable to replicate triggers across the cluster: BDR
> is not intended to do so.
>
> BDR replicates everything that happens inside a transaction; that includes
> both SQL run directly from the application, as well as changes made by
> triggers and extensions. As the changes are applied directly from the WAL,
> no trigger is re-run on the other nodes. If the trigger is re-run, that
> would lead to problems, such as duplicated rows.
>
> The only "problem", if it really is, is that BDR does not copy
> notifications across the databases. As this may be seen as a problem, I
> could also consider it as a chance to make the application more
> self-conscious of the distributed environment it is running in. So I would
> try one out of two alternatives:
>
> 1. Make the application listen to notifications on both databases, so it
> will get notified of changes no matter where they happen
>
> 2. Instead of using notify, create a notification table, which your app
> should scan periodically and act accordingly.
>
> Regards,
>
> Alvaro Aguayo
> Jefe de Operaciones
> Open Comb Systems E.I.R.L.
>
> Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51)
> 954183248
> Website: www.ocs.pe
>
> - Original Message -
> From: "jamesadams89" 
> To: "PostgreSql-general" 
> Sent: Wednesday, 26 April, 2017 07:48:03
> Subject: [GENERAL] BDR replication and table triggers
>
> Hi,
>
> I have some questions regarding how BDR interacts with triggers.
>
> I have two databases that are both joined to the same BDR group and
> correctly replicating between one another sharing a table created as:
>
> create table testtable(
> key varchar(16) NOT NULL PRIMARY KEY,
> data jsonb
> );
>
> With the following trigger defined:
>
> CREATE OR REPLACE FUNCTION test_table_notify()
> RETURNS TRIGGER AS
> $$
> BEGIN
> IF TG_OP='INSERT' OR TG_OP='UPDATE' THEN
> PERFORM pg_notify( 'TestTable', TG_OP || ' ' || NEW.key );
> ELSE
> PERFORM pg_notify( 'TestTable', TG_OP || ' ' || OLD.key );
> END IF;
> RETURN NULL;
> END;
> $$ LANGUAGE plpgsql;
>
>
> CREATE TRIGGER TestTableTrigger
> AFTER INSERT OR UPDATE OR DELETE
> on testtable
> FOR EACH ROW
> EXECUTE PROCEDURE test_table_notify();
>
> I then have a client application listening on the 'TestTable' Notify on one
> of the Databases:
>
> Client
>  ___
> | |
> | A  |
> |___|
>   /\
>|
>  _|_ ___
> |  | |   |
> |DB1|-|DB2|
> |_ __| ||
>
> If I perform any INSERT, UPDATE or DELETE operations directly on DB1 I see
> the trigger on the table being fired as expected and Client Application 'A'
> recieves the notify.  I also see the changes propagate to DB2 via BDR as
> expected.  However if I perform any INSERT, UPDATE or DELETE operations on
> DB2 and these changes propagate over to DB1 via BDR I do not see DB1 firing
> any triggers. Is this intended behavior?  My current understanding is that
> BDR is unable to invoke Postgres triggers as it operates on the rows
> directly, a layer below Postgres. Is this Correct? Is there any mechanism
> that exists that could provide notifications to a listening application
> when
> BDR makes changes to the underlying database?
>
> Apologies if this is all a bit elementary, this is my first foray into BDR
> and I was unable to find anything in the documentation that mentioned
> triggers.
>
> Thanks for any input
>
>
>
> --
> View this message in context: http://www.postgresql-archive.
> org/BDR-replication-and-table-triggers-tp5958463.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] BDR replication and table triggers

[Alvaro Aguayo Garcia-Rada]

Hi.

It's not like BDR is unable to replicate triggers across the cluster: BDR is 
not intended to do so.

BDR replicates everything that happens inside a transaction; that includes both 
SQL run directly from the application, as well as changes made by triggers and 
extensions. As the changes are applied directly from the WAL, no trigger is 
re-run on the other nodes. If the trigger is re-run, that would lead to 
problems, such as duplicated rows.

The only "problem", if it really is, is that BDR does not copy notifications 
across the databases. As this may be seen as a problem, I could also consider 
it as a chance to make the application more self-conscious of the distributed 
environment it is running in. So I would try one out of two alternatives:

1. Make the application listen to notifications on both databases, so it will 
get notified of changes no matter where they happen

2. Instead of using notify, create a notification table, which your app should 
scan periodically and act accordingly.

Regards,

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 
954183248
Website: www.ocs.pe

- Original Message -
From: "jamesadams89" 
To: "PostgreSql-general" 
Sent: Wednesday, 26 April, 2017 07:48:03
Subject: [GENERAL] BDR replication and table triggers

Hi,

I have some questions regarding how BDR interacts with triggers. 

I have two databases that are both joined to the same BDR group and
correctly replicating between one another sharing a table created as:

create table testtable(
key varchar(16) NOT NULL PRIMARY KEY,
data jsonb
);

With the following trigger defined:

CREATE OR REPLACE FUNCTION test_table_notify()
RETURNS TRIGGER AS
$$
BEGIN
IF TG_OP='INSERT' OR TG_OP='UPDATE' THEN
PERFORM pg_notify( 'TestTable', TG_OP || ' ' || NEW.key );
ELSE
PERFORM pg_notify( 'TestTable', TG_OP || ' ' || OLD.key );
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER TestTableTrigger
AFTER INSERT OR UPDATE OR DELETE
on testtable
FOR EACH ROW
EXECUTE PROCEDURE test_table_notify();

I then have a client application listening on the 'TestTable' Notify on one
of the Databases:

Client
 ___
| |
| A  |
|___|
  /\
   | 
 _|_ ___
|  | |   |
|DB1|-|DB2|
|_ __| ||

If I perform any INSERT, UPDATE or DELETE operations directly on DB1 I see
the trigger on the table being fired as expected and Client Application 'A'
recieves the notify.  I also see the changes propagate to DB2 via BDR as
expected.  However if I perform any INSERT, UPDATE or DELETE operations on
DB2 and these changes propagate over to DB1 via BDR I do not see DB1 firing
any triggers. Is this intended behavior?  My current understanding is that
BDR is unable to invoke Postgres triggers as it operates on the rows
directly, a layer below Postgres. Is this Correct? Is there any mechanism
that exists that could provide notifications to a listening application when
BDR makes changes to the underlying database? 

Apologies if this is all a bit elementary, this is my first foray into BDR
and I was unable to find anything in the documentation that mentioned
triggers.

Thanks for any input



--
View this message in context: 
http://www.postgresql-archive.org/BDR-replication-and-table-triggers-tp5958463.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR replication slots

[Nikhil]

On interface down:
--
<10.102.31.213(27599)postgres13082016-04-19 06:31:36
GMTprocess_journal%LOG:  terminating walsender process due to replication
timeout


Once interface is brought back

 425906 <12692016-04-19 08:32:58 GMT%LOG:  starting background worker
process "bdr (6275149074578269365,2,16386,)->bdr (6275135922714263763,1,"
 425907 <597732016-04-19 08:32:58 GMT%ERROR:  relation "mygroup" already
exists
 425908 <597732016-04-19 08:32:58 GMT%CONTEXT:  during DDL replay of ddl
statement: CREATE  TABLE  public.mygroup (id pg_catalog."varchar"(14)  NOT
NULL COLLATE pg_catalog."default", name pg_catalog."varchar"(100)   COLLATE
pg_catalog."default", device_type pg_catalog."varchar"(30)   COLLATE
pg_catalog."default", platform_type pg_catalog."varchar"(30)   COLLATE
pg_catalog."default",
CONSTRAINT mygroup_pkey PRIMARY KEY (id) )  WITH (oids=OFF)
 425909 <12692016-04-19 08:32:58 GMT%LOG:  worker process: bdr
(6275149074578269365,2,16386,)->bdr (6275135922714263763,1,(PID 59773)
exited with exit code 1
 425910 <10.102.31.213(13467)postgres597742016-04-19 08:32:59
GMTprocess_journal%LOG:  starting logical decoding for slot
"bdr_16386_6275135922714263763_1_16386__"
 425911 <10.102.31.213(13467)postgres597742016-04-19 08:32:59
GMTprocess_journal%DETAIL:  streaming transactions committing after
0/1014CEE8, reading WAL from 0/1014A920
 425912 <10.102.31.213(13467)postgres597742016-04-19 08:32:59
GMTprocess_journal%LOG:  logical decoding found consistent point at
0/1014A920
 425913 <10.102.31.213(13467)postgres597742016-04-19 08:32:59
GMTprocess_journal%DETAIL:  There are no running transactions.
 425914 *<10.102.31.213(13467)postgres597742016-04-19 08:32:59
GMTprocess_journal%LOG:  unexpected EOF on standby con**nection*


On Tue, Apr 19, 2016 at 10:29 AM, Alvaro Aguayo Garcia-Rada <
aagu...@opensysperu.com> wrote:

> Hello,
>
> What do you see on each node's log after enablibg interfaces?
>
> Regards,
>
> Alvaro Aguayo
> Jefe de Operaciones
> Open Comb Systems E.I.R.L.
>
> Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC:
> (+51) 954183248
> Website: www.ocs.pe
>
> Sent from my Sony Xperia™ smartphone
>
>
>  Nikhil wrote 
>
>
> Hello,
>
> I have a 2 node BDR group and replication is happening properly. if i
> bring down one of the node's interface, after sometime the replication
> slots are becoming inactive (pg_replication_slots view). Then if i bring
> back interface slots are not turning active automatically and replication
> stops. Any idea why automatically its not re-established ?
>
> Best Regards,
> Nikhil
>

Re: [GENERAL] BDR replication slots

[Sylvain Marechal]

2016-04-19 6:51 GMT+02:00 Nikhil :

> Hello,
>
> I have a 2 node BDR group and replication is happening properly. if i
> bring down one of the node's interface, after sometime the replication
> slots are becoming inactive (pg_replication_slots view). Then if i bring
> back interface slots are not turning active automatically and replication
> stops. Any idea why automatically its not re-established ?
>

May be postgres does not detect the link is down.
You could try modifying the connection strings, so that postgres detects
earlier the problem, like described in this post :
https://github.com/2ndQuadrant/bdr/issues/173 :

<<<
[...]
Example of dsn_connection field:
host=192.168.1.140 port=5432 dbname=safewalk-server connect_timeout=10
keepalives_idle=5 keepalives_interval=1
>>>


Hope this helps,

Sylvain

Re: [GENERAL] BDR replication slots

[Alvaro Aguayo Garcia-Rada]

Hello,

What do you see on each node's log after enablibg interfaces?

Regards,

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 
954183248
Website: www.ocs.pe

Sent from my Sony Xperia™ smartphone

 Nikhil wrote 

Hello,

I have a 2 node BDR group and replication is happening properly. if i bring
down one of the node's interface, after sometime the replication slots are
becoming inactive (pg_replication_slots view). Then if i bring back
interface slots are not turning active automatically and replication stops.
Any idea why automatically its not re-established ?

Best Regards,
Nikhil

Re: [GENERAL] bdr replication

[Alvaro Aguayo Garcia-Rada]

I'd rather use something like this:

bdr_dump -N bdr -h localhost -U postgres -W mydb > /backup.sql

That's for each database. You would restore it, you must first create the empty 
database:

CREATE DATABASE mydb WITH ENCODING 'UTF-8' TEMPLATE template0;

(change encoding according to your needs. Run "psql --list" on your current 
system to see current encoding for each db)
Then, restore its contents:

cat /backup.sql | psql mydb

During restore, you may see some errors due to the backup containing the bdr 
truncate trigger on each table; it's normal at this point, so just ignore it.

You may need to backup your postgres users:

pg_dumpall -g > /globals.sql

And restore it BEFORE any database with

cat /globals.sql | psql

Backing up and restoring globals may be important when rewtoring databases, 
mostly to preserve permissions on tables/sequences during each database restore.

Regards,

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 
954183248
Website: www.ocs.pe

Sent from my Sony Xperia™ smartphone

 Slava Bendersky wrote 

Hello Alvaro, 
That sound like a good plan. I will trying tomorrow, because need maintenance 
window. 

To do back up I can use something like 

bdr_dump -Fp -h localhost -U postgres mydb -f /tmp/mydb.data > --data-only 
--exclude-table='bdr_* 

volga629 


From: "Alvaro Aguayo Garcia-Rada" <aagu...@opensysperu.com> 
To: "volga629" <volga...@skillsearch.ca> 
Cc: "pgsql-general" <pgsql-general@postgresql.org>, "John R Pierce" 
<pie...@hogranch.com> 
Sent: Thursday, 31 March, 2016 02:41:17 
Subject: Re: [GENERAL] bdr replication 

We are overlaping mails :P 

What I don't understand is the need of a shared storage in this case. It would 
be a lot better to have the data folder inside each server virtual disk to 
avoid troubles with the shared storage; I really see no reason for such 
configuration here. 

Now, getting into the solution rather than in the problem. I suggest you to do 
the following: 

1. First of all, backup your data folder for both nodes. Just in case. Make 
backup with postgres stopped to avoid problems. 
2. Choose one node which will be considered up-to-date(Let's say "Node A") 
3. Dump your database(s) on that node, excluding the bdr schema on each db. 
Dump also your globals 
4. Wipe or rename your data folder on each node, and then initialize each node. 
Do not configure BDR yet. 
5. Restore your data(backed up at step 3) on Node A 
6. Configure BDR on Node A 
7. Add Node B to the replication group, using "bdr_init_copy" to make it 
replicate from Node A. 

That should do the trick. There is another possibility: Drop the replication 
configuration no Node A, and then start from scratch(Steps 1, 6 & 7). However, 
this can be troublesome, as it involves editing bdr & postgres schemas, and 
that can lead you to problems on the future, so I'd recommend you the "long" 
way. 

Feel free to ask any question regarding this issue. Looks serious 

Alvaro Aguayo 
Jefe de Operaciones 
Open Comb Systems E.I.R.L. 

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 
954183248 
Website: www.ocs.pe 

- Original Message - 
From: "Slava Bendersky" <volga...@skillsearch.ca> 
To: "Alvaro Aguayo Garcia-Rada" <aagu...@opensysperu.com> 
Cc: "pgsql-general" <pgsql-general@postgresql.org>, "John R Pierce" 
<pie...@hogranch.com> 
Sent: Thursday, 31 March, 2016 12:28:09 AM 
Subject: Re: [GENERAL] bdr replication 



Hello Alvaro, 
We running BDR where each PostgreSQL vm is a master and shared storage only on 
hypervisor level. All vm leave with own virtual disk. Right now we have 2 
server for redundancy which have shared network between them. Issue that BDR is 
working right now see log below. And my question how to restore BDR replication 
correctly. 


volga629 

- Original Message - 

From: "Alvaro Aguayo Garcia-Rada" <aagu...@opensysperu.com> 
To: "volga629" <volga...@skillsearch.ca> 
Cc: "pgsql-general" <pgsql-general@postgresql.org>, "John R Pierce" 
<pie...@hogranch.com> 
Sent: Thursday, 31 March, 2016 02:19:42 
Subject: Re: [GENERAL] bdr replication 


What's the purpose of such configuration? Doesn't makes sense for me. The only 
reasonable case where you would want to put the data folder on a shared storage 
is for usage with warm standby, where you can have a secondary server which 
serves as a read-only replica, and can be rpomoted to master on master failure. 

If you intend high availability, you'd rather try it at VM level, like vmware 
HA or Proxmox HA. That will make your VM run on any hypervisor in the group 
disregarding the failure of some node. 

Regards, 

Alvaro Aguayo 
Jefe de Ope

Re: [GENERAL] bdr replication

[John R Pierce]

On 3/30/2016 10:41 PM, Alvaro Aguayo Garcia-Rada wrote:

What I don't understand is the need of a shared storage in this case. It would 
be a lot better to have the data folder inside each server virtual disk to 
avoid troubles with the shared storage; I really see no reason for such 
configuration here.


I believe what the original poster is trying to say, his physical 
servers have a shared storage pool, but each VM has its own private 
virtual disks allocated out of this shared pool, and thats what his BDR 
database servers are using.


this should be fine, so its a BDR problem. how to diagnose and 
repair BDR problems.   I have no experience running BDR, so will step 
out of this thread (please delete me from CC's).




--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] bdr replication

[Alvaro Aguayo Garcia-Rada]

We are overlaping mails :P

What I don't understand is the need of a shared storage in this case. It would 
be a lot better to have the data folder inside each server virtual disk to 
avoid troubles with the shared storage; I really see no reason for such 
configuration here.

Now, getting into the solution rather than in the problem. I suggest you to do 
the following:

1. First of all, backup your data folder for both nodes. Just in case. Make 
backup with postgres stopped to avoid problems.
2. Choose one node which will be considered up-to-date(Let's say "Node A")
3. Dump your database(s) on that node, excluding the bdr schema on each db. 
Dump also your globals
4. Wipe or rename your data folder on each node, and then initialize each node. 
Do not configure BDR yet.
5. Restore your data(backed up at step 3) on Node A
6. Configure BDR on Node A
7. Add Node B to the replication group, using "bdr_init_copy" to make it 
replicate from Node A.

That should do the trick. There is another possibility: Drop the replication 
configuration no Node A, and then start from scratch(Steps 1, 6 & 7). However, 
this can be troublesome, as it involves editing bdr & postgres schemas, and 
that can lead you to problems on the future, so I'd recommend you the "long" 
way.

Feel free to ask any question regarding this issue. Looks serious

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 
954183248
Website: www.ocs.pe

- Original Message -
From: "Slava Bendersky" <volga...@skillsearch.ca>
To: "Alvaro Aguayo Garcia-Rada" <aagu...@opensysperu.com>
Cc: "pgsql-general" <pgsql-general@postgresql.org>, "John R Pierce" 
<pie...@hogranch.com>
Sent: Thursday, 31 March, 2016 12:28:09 AM
Subject: Re: [GENERAL] bdr replication



Hello Alvaro, 
We running BDR where each PostgreSQL vm is a master and shared storage only on 
hypervisor level. All vm leave with own virtual disk. Right now we have 2 
server for redundancy which have shared network between them. Issue that BDR is 
working right now see log below. And my question how to restore BDR replication 
correctly. 


volga629 

- Original Message -

From: "Alvaro Aguayo Garcia-Rada" <aagu...@opensysperu.com> 
To: "volga629" <volga...@skillsearch.ca> 
Cc: "pgsql-general" <pgsql-general@postgresql.org>, "John R Pierce" 
<pie...@hogranch.com> 
Sent: Thursday, 31 March, 2016 02:19:42 
Subject: Re: [GENERAL] bdr replication 


What's the purpose of such configuration? Doesn't makes sense for me. The only 
reasonable case where you would want to put the data folder on a shared storage 
is for usage with warm standby, where you can have a secondary server which 
serves as a read-only replica, and can be rpomoted to master on master failure. 

If you intend high availability, you'd rather try it at VM level, like vmware 
HA or Proxmox HA. That will make your VM run on any hypervisor in the group 
disregarding the failure of some node. 

Regards, 

Alvaro Aguayo 
Jefe de Operaciones 
Open Comb Systems E.I.R.L. 

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 
954183248 
Website: www.ocs.pe 

- Original Message - 
From: "Slava Bendersky" <volga...@skillsearch.ca> 
To: "John R Pierce" <pie...@hogranch.com> 
Cc: "pgsql-general" <pgsql-general@postgresql.org> 
Sent: Wednesday, 30 March, 2016 10:57:13 PM 
Subject: Re: [GENERAL] bdr replication 



In my case only virtual hosts are use share storage (feed from glusterfs), but 
actual virtual machines have own separate disks and all PostgreSQL run on 
separate data directories. 


volga629 

- Original Message - 

From: "John R Pierce" <pie...@hogranch.com> 
To: "pgsql-general" <pgsql-general@postgresql.org> 
Sent: Thursday, 31 March, 2016 00:34:55 
Subject: Re: [GENERAL] bdr replication 


On 3/30/2016 8:09 PM, Slava Bendersky wrote: 
> Is any share storage technology recommended for PostgreSQL in virtual 
> environment ? 
> Ok what I will do is going take backups, shutdown both virtual servers 
> and place all vm use local disk on server only. 


'share storage technology'... um. thats such a vague term, it can 
mean lots of things. 

each postgres instance needs its own data store, two instances can NOT 
share the same files under any condition. these data stores can be 
on SAN or NAS, as long the storage is reliable about committed random 
writes, and as long as two different servers aren't using the SAME 
directory for their data stores. 

-- 
john r pierce, recycling bits in santa cruz 



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) 
To make changes to your subscription: 
http://www.postgresql.org/mailpref/pgsql-general 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] bdr replication

[Slava Bendersky]

Hello Alvaro, 
We running BDR where each PostgreSQL vm is a master and shared storage only on 
hypervisor level. All vm leave with own virtual disk. Right now we have 2 
server for redundancy which have shared network between them. Issue that BDR is 
working right now see log below. And my question how to restore BDR replication 
correctly. 

volga629 


From: "Alvaro Aguayo Garcia-Rada" <aagu...@opensysperu.com> 
To: "volga629" <volga...@skillsearch.ca> 
Cc: "pgsql-general" <pgsql-general@postgresql.org>, "John R Pierce" 
<pie...@hogranch.com> 
Sent: Thursday, 31 March, 2016 02:19:42 
Subject: Re: [GENERAL] bdr replication 

What's the purpose of such configuration? Doesn't makes sense for me. The only 
reasonable case where you would want to put the data folder on a shared storage 
is for usage with warm standby, where you can have a secondary server which 
serves as a read-only replica, and can be rpomoted to master on master failure. 

If you intend high availability, you'd rather try it at VM level, like vmware 
HA or Proxmox HA. That will make your VM run on any hypervisor in the group 
disregarding the failure of some node. 

Regards, 

Alvaro Aguayo 
Jefe de Operaciones 
Open Comb Systems E.I.R.L. 

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 
954183248 
Website: www.ocs.pe 

- Original Message - 
From: "Slava Bendersky" <volga...@skillsearch.ca> 
To: "John R Pierce" <pie...@hogranch.com> 
Cc: "pgsql-general" <pgsql-general@postgresql.org> 
Sent: Wednesday, 30 March, 2016 10:57:13 PM 
Subject: Re: [GENERAL] bdr replication 



In my case only virtual hosts are use share storage (feed from glusterfs), but 
actual virtual machines have own separate disks and all PostgreSQL run on 
separate data directories. 


volga629 

- Original Message - 

From: "John R Pierce" <pie...@hogranch.com> 
To: "pgsql-general" <pgsql-general@postgresql.org> 
Sent: Thursday, 31 March, 2016 00:34:55 
Subject: Re: [GENERAL] bdr replication 


On 3/30/2016 8:09 PM, Slava Bendersky wrote: 
> Is any share storage technology recommended for PostgreSQL in virtual 
> environment ? 
> Ok what I will do is going take backups, shutdown both virtual servers 
> and place all vm use local disk on server only. 


'share storage technology'... um. thats such a vague term, it can 
mean lots of things. 

each postgres instance needs its own data store, two instances can NOT 
share the same files under any condition. these data stores can be 
on SAN or NAS, as long the storage is reliable about committed random 
writes, and as long as two different servers aren't using the SAME 
directory for their data stores. 

-- 
john r pierce, recycling bits in santa cruz 



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) 
To make changes to your subscription: 
http://www.postgresql.org/mailpref/pgsql-general 

Re: [GENERAL] bdr replication

[Alvaro Aguayo Garcia-Rada]

What's the purpose of such configuration? Doesn't makes sense for me. The only 
reasonable case where you would want to put the data folder on a shared storage 
is for usage with warm standby, where you can have a secondary server which 
serves as a read-only replica, and can be rpomoted to master on master failure.

If you intend high availability, you'd rather try it at VM level, like vmware 
HA or Proxmox HA. That will make your VM run on any hypervisor in the group 
disregarding the failure of some node.

Regards,

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 
954183248
Website: www.ocs.pe

- Original Message -
From: "Slava Bendersky" <volga...@skillsearch.ca>
To: "John R Pierce" <pie...@hogranch.com>
Cc: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Wednesday, 30 March, 2016 10:57:13 PM
Subject: Re: [GENERAL] bdr replication



In my case only virtual hosts are use share storage (feed from glusterfs), but 
actual virtual machines have own separate disks and all PostgreSQL run on 
separate data directories. 


volga629 

- Original Message -

From: "John R Pierce" <pie...@hogranch.com> 
To: "pgsql-general" <pgsql-general@postgresql.org> 
Sent: Thursday, 31 March, 2016 00:34:55 
Subject: Re: [GENERAL] bdr replication 


On 3/30/2016 8:09 PM, Slava Bendersky wrote: 
> Is any share storage technology recommended for PostgreSQL in virtual 
> environment ? 
> Ok what I will do is going take backups, shutdown both virtual servers 
> and place all vm use local disk on server only. 


'share storage technology'... um. thats such a vague term, it can 
mean lots of things. 

each postgres instance needs its own data store, two instances can NOT 
share the same files under any condition. these data stores can be 
on SAN or NAS, as long the storage is reliable about committed random 
writes, and as long as two different servers aren't using the SAME 
directory for their data stores. 

-- 
john r pierce, recycling bits in santa cruz 



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) 
To make changes to your subscription: 
http://www.postgresql.org/mailpref/pgsql-general 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] bdr replication

[Slava Bendersky]

Hello Everyone, 
Is possible recovery from my situation at all ? I was looking on tool which 
might will help and only bdr_init_copy. If possible initialize second node 
again ? Also is it good idea enable wal archiving with bdr ? 

volga629 


From: "volga629" <volga...@skillsearch.ca> 
To: "John R Pierce" <pie...@hogranch.com> 
Cc: "pgsql-general" <pgsql-general@postgresql.org> 
Sent: Thursday, 31 March, 2016 00:57:13 
Subject: Re: [GENERAL] bdr replication 

In my case only virtual hosts are use share storage (feed from glusterfs), but 
actual virtual machines have own separate disks and all PostgreSQL run on 
separate data directories. 

volga629 


From: "John R Pierce" <pie...@hogranch.com> 
To: "pgsql-general" <pgsql-general@postgresql.org> 
Sent: Thursday, 31 March, 2016 00:34:55 
Subject: Re: [GENERAL] bdr replication 

On 3/30/2016 8:09 PM, Slava Bendersky wrote: 
> Is any share storage technology recommended for PostgreSQL in virtual 
> environment ? 
> Ok what I will do is going take backups, shutdown both virtual servers 
> and place all vm use local disk on server only. 


'share storage technology'... um. thats such a vague term, it can 
mean lots of things. 

each postgres instance needs its own data store, two instances can NOT 
share the same files under any condition. these data stores can be 
on SAN or NAS, as long the storage is reliable about committed random 
writes, and as long as two different servers aren't using the SAME 
directory for their data stores. 

-- 
john r pierce, recycling bits in santa cruz 



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) 
To make changes to your subscription: 
http://www.postgresql.org/mailpref/pgsql-general 

Re: [GENERAL] bdr replication

[Slava Bendersky]

In my case only virtual hosts are use share storage (feed from glusterfs), but 
actual virtual machines have own separate disks and all PostgreSQL run on 
separate data directories. 

volga629 


From: "John R Pierce" <pie...@hogranch.com> 
To: "pgsql-general" <pgsql-general@postgresql.org> 
Sent: Thursday, 31 March, 2016 00:34:55 
Subject: Re: [GENERAL] bdr replication 

On 3/30/2016 8:09 PM, Slava Bendersky wrote: 
> Is any share storage technology recommended for PostgreSQL in virtual 
> environment ? 
> Ok what I will do is going take backups, shutdown both virtual servers 
> and place all vm use local disk on server only. 


'share storage technology'... um. thats such a vague term, it can 
mean lots of things. 

each postgres instance needs its own data store, two instances can NOT 
share the same files under any condition. these data stores can be 
on SAN or NAS, as long the storage is reliable about committed random 
writes, and as long as two different servers aren't using the SAME 
directory for their data stores. 

-- 
john r pierce, recycling bits in santa cruz 



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) 
To make changes to your subscription: 
http://www.postgresql.org/mailpref/pgsql-general 

Re: [GENERAL] bdr replication

[Slava Bendersky]

Hello Craig, 
Is any share storage technology recommended for PostgreSQL in virtual 
environment ? 
Ok what I will do is going take backups, shutdown both virtual servers and 
place all vm use local disk on server only. 
volga629 


From: "Craig Ringer" <cr...@2ndquadrant.com> 
To: "volga629" <volga...@skillsearch.ca> 
Cc: "pgsql-general" <pgsql-general@postgresql.org> 
Sent: Wednesday, 30 March, 2016 23:57:28 
Subject: Re: [GENERAL] bdr replication 

On 31 March 2016 at 10:43, Slava Bendersky < volga...@skillsearch.ca > wrote: 



Hello Craig, 
The current setup is two server which run libvirt and for storage which run 
glusterfs (storage server feed two virtual servers). Right now is no fencing in 
place. Each of the nodes have one PostgreSQL vm with bdr. 



That's a disaster waiting to happen. You can't just share storage like that in 
PostgreSQL, BDR or otherwise. I'm amazed it didn't fail earlier. 

Try to dump whatever data you can recover, initdb a new normal PostgreSQL 
instance, restore, and set up normal replication. Look into repmgr, pgbarman, 
etc. Read the manual on replication, backup and failover. Do _not_ use shared 
storage. 

-- 
Craig Ringer http://www.2ndQuadrant.com/ 
PostgreSQL Development, 24x7 Support, Training & Services 

Re: [GENERAL] bdr replication

[Craig Ringer]

On 31 March 2016 at 10:43, Slava Bendersky  wrote:

> Hello Craig,
> The current setup is two server which run libvirt and for storage which
> run glusterfs (storage server feed two virtual servers).  Right now is no
> fencing in place. Each of the nodes have one  PostgreSQL vm with bdr.
>

That's a disaster waiting to happen. You can't just share storage like that
in PostgreSQL, BDR or otherwise. I'm amazed it didn't fail earlier.

Try to dump whatever data you can recover, initdb a new normal PostgreSQL
instance, restore, and set up normal replication. Look into repmgr,
pgbarman, etc. Read the manual on replication, backup and failover. Do
_not_ use shared storage.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] bdr replication

[Slava Bendersky]

Hello Craig, 
The current setup is two server which run libvirt and for storage which run 
glusterfs (storage server feed two virtual servers). Right now is no fencing in 
place. Each of the nodes have one PostgreSQL vm with bdr. 

volga629. 

From: "Craig Ringer" <cr...@2ndquadrant.com> 
To: "volga629" <volga...@skillsearch.ca> 
Cc: "pgsql-general" <pgsql-general@postgresql.org> 
Sent: Wednesday, 30 March, 2016 23:20:49 
Subject: Re: [GENERAL] bdr replication 

On 31 March 2016 at 09:38, Slava Bendersky < volga...@skillsearch.ca > wrote: 



Hello Everyone, 
I am looking for suggestion how to recover bdr replication. 
The short story we have 2 virtual nodes with share storage. 



Can you describe the "shared storage" setup in more detail? 

In general, with PostgreSQL "shared storage" is a shortcut to "massive database 
corruption" unless you have extremely careful fencing and STONITH. 


-- 
Craig Ringer http://www.2ndQuadrant.com/ 
PostgreSQL Development, 24x7 Support, Training & Services 

Re: [GENERAL] bdr replication

[Craig Ringer]

On 31 March 2016 at 09:38, Slava Bendersky  wrote:

> Hello Everyone,
> I am looking for suggestion how to recover bdr replication.
> The short story we have 2 virtual nodes with share storage.
>

Can you describe the "shared storage" setup in more detail?

In general, with PostgreSQL "shared storage" is a shortcut to "massive
database corruption" unless you have extremely careful fencing and STONITH.



-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] BDR replication on Postgresql 9.5.0

[Kaushal Shriyan]

On Mon, Feb 1, 2016 at 12:24 AM, John R Pierce  wrote:

> On 1/29/2016 3:00 AM, Kaushal Shriyan wrote:
>
>>
>> Do i need to install any BDR specific package to enable it in postgresql
>> 9.5 version. While reading
>> http://bdr-project.org/docs/0.9.0/install-requirements.html i assumed
>> that it is available in 9.5 version by default without using any patches.
>> Please comment.
>>
>
> "As of the time of writing, the upcoming PostgreSQL 9.5 release is not yet
> supported. Neither is Microsoft Windows. Support for both will be added in
> later releases; please check the BDR website for the latest information."
>
>
>
Thanks Pierce for the explanation.

Re: [GENERAL] BDR replication on Postgresql 9.5.0

[John R Pierce]

On 1/29/2016 3:00 AM, Kaushal Shriyan wrote:


Do i need to install any BDR specific package to enable it in 
postgresql 9.5 version. While reading 
http://bdr-project.org/docs/0.9.0/install-requirements.html i assumed 
that it is available in 9.5 version by default without using any 
patches. Please comment.


"As of the time of writing, the upcoming PostgreSQL 9.5 release is not 
yet supported. Neither is Microsoft Windows. Support for both will be 
added in later releases; please check the BDR website for the latest 
information."




--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR replication on Postgresql 9.5.0

[Sachin Jain]

BDR extension is not available in 9.5. You need to install it separately.

- Sachin
On 29 Jan 2016 4:31 p.m., "Kaushal Shriyan" 
wrote:

> Hi,
>
> I am following http://bdr-project.org/docs/stable/index.html to setup BDR
> for postgresql version 9.5 as per
>
> [root@ip-172-31-1-17 9.5]# cat /etc/redhat-release
> *Red Hat Enterprise Linux Server release 6.6 (Santiago)*
> [root@ip-172-31-1-17 9.5]# rpm -qa | grep postgre
> postgresql95-libs-9.5.0-2PGDG.rhel6.x86_64
> postgresql95-server-9.5.0-2PGDG.rhel6.x86_64
> postgresql95-9.5.0-2PGDG.rhel6.x86_64
> [root@ip-172-31-1-17 9.5]#
>
> cat pgstartup.log
> < 2016-01-29 05:42:01.716 EST >FATAL:  could not access file "bdr": No
> such file or directory
>
> Do i need to install any BDR specific package to enable it in postgresql
> 9.5 version. While reading
> http://bdr-project.org/docs/0.9.0/install-requirements.html i assumed
> that it is available in 9.5 version by default without using any patches.
> Please comment.
>
> Any help will be highly appreciable.
>
> Regards,
>
> Kaushal
>
>
>

Re: [GENERAL] BDR replication

[Craig Ringer]

On 29 January 2016 at 18:27, Nikhil  wrote:


> Is there any way to specify priority for replication. or any parameter
> which guarantees something about replication (speed at which it replicates,
> number of minimum replicas to write).
>

Not yet. Not in core PostgreSQL streaming replication, nor in BDR or
pglogical etc.

Right now you have "synchronous" or "not synchronous" and at most one
synchronous node.

Does BDR has a configuration for differentiated services in replication.
>

No. It's mesh multimaster and all replicated data is treated equally.
There's no concept of replication priority, nor am I sure how we could
implement such a thing. Data is either replicated or not replicated.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] BDR replication

[John R Pierce]

On 1/29/2016 2:27 AM, Nikhil wrote:


Is there any way to specify priority for replication. or any parameter 
which guarantees something about replication (speed at which it 
replicates, number of minimum replicas to write).


Does BDR has a configuration for differentiated services in 
replication. I want to categorize my data that i replicate. I am ok if 
some logs are lost in replication. journal entries must not be lost in 
replication.


its all or nothing, postgres doesn't do 'data maybe'.



--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] bdr replication latency monitoring

[Steve Boyle]

Craig,

Your response was very helpful, thank you.

I was looking at some of the standard bits in Postgres like 
txid_current_snapshot() and txid_snapshot_xmin().  Can the results from 
txid_snapshot_xmin be used with pg_get_transaction_committime() to get the 
latency?

Thanks again,
Steve Boyle



Steve,

The relevant change was made during the commit of logical decoding to
PostgreSQL 9.4, where the field of interest was renamed from 'xmin' to
'catalog_xmin'. It's around then that pg_stat_logical_decoding was renamed
to pg_replication_slots too.

To get lag in bytes, use:

SELECT slot_name, database, active,
pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn)
FROM pg_replication_slots
WHERE plugin = 'bdr';

The catalog_xmin doesn't really reflect lag at all. Replay may have
continued past that xid and fully caught up. Additionally, the commit
timestamp records for the catalog xmin may be truncated away, rendering its
commit time unknown and causing pg_get_transaction_committime(...) to
report the epoch 2000-01-01 as the commit time. So using
pg_get_transaction_committime on the catalog xmin isn't as useful as it was
in earlier versions of BDR. I don't currently have a good way to get you a
sense of replay lag in wall-clock time and will need to get back to you on
that one.


Note that we're in the process of updating all that documentation, moving
it into the same SGML format used for PostgreSQL's official documentation
and putting it in the BDR source tree. Some of the documentation on the
wiki has become outdated since 0.7.x as a result. The coming 0.9.x release
will bundle the documentation in the source tree and make the wiki docs
obsolete.

Thanks for your patience in the mean time. Please bring up any other issues
you encounter, as it'll help make sure I and the rest of the team don't
miss anything.





On 14 March 2015 at 03:06, Steve Boyle sboyle(at)connexity(dot)com wrote:

 I'm trying to follow the BDR monitoring docs:
 https://wiki.postgresql.org/wiki/BDR_Monitoring

 My postgres version string is (its from the 2nd Quadrant repo):
 PostgreSQL 9.4.0 on x86_64-unknown-linux-gnu, compiled by gcc
 (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

 My BDR plugin is version 0.8.0beta1

 From the docs, I've come up with this query:
 select slot_name, plugin, database, active, xmin,
 pg_get_transaction_committime(xmin)
 FROM pg_replication_slots ;

 BDR is working.  When I run that query, the 'xmin' value is always null,
 even though there is activity on the database.  I do/can get a catalog_xmin
 value.  Should I expect the 'xmin' value to be null?  Is there another way
 to monitor the replication latency when using BDR?

 Thanks,
 Steve Boyle





 --
 Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] bdr replication latency monitoring

[Craig Ringer]

Steve,

The relevant change was made during the commit of logical decoding to
PostgreSQL 9.4, where the field of interest was renamed from 'xmin' to
'catalog_xmin'. It's around then that pg_stat_logical_decoding was renamed
to pg_replication_slots too.

To get lag in bytes, use:

SELECT slot_name, database, active,
pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn)
FROM pg_replication_slots
WHERE plugin = 'bdr';

The catalog_xmin doesn't really reflect lag at all. Replay may have
continued past that xid and fully caught up. Additionally, the commit
timestamp records for the catalog xmin may be truncated away, rendering its
commit time unknown and causing pg_get_transaction_committime(...) to
report the epoch 2000-01-01 as the commit time. So using
pg_get_transaction_committime on the catalog xmin isn't as useful as it was
in earlier versions of BDR. I don't currently have a good way to get you a
sense of replay lag in wall-clock time and will need to get back to you on
that one.


Note that we're in the process of updating all that documentation, moving
it into the same SGML format used for PostgreSQL's official documentation
and putting it in the BDR source tree. Some of the documentation on the
wiki has become outdated since 0.7.x as a result. The coming 0.9.x release
will bundle the documentation in the source tree and make the wiki docs
obsolete.

Thanks for your patience in the mean time. Please bring up any other issues
you encounter, as it'll help make sure I and the rest of the team don't
miss anything.





On 14 March 2015 at 03:06, Steve Boyle sbo...@connexity.com wrote:

 I'm trying to follow the BDR monitoring docs:
 https://wiki.postgresql.org/wiki/BDR_Monitoring

 My postgres version string is (its from the 2nd Quadrant repo):
 PostgreSQL 9.4.0 on x86_64-unknown-linux-gnu, compiled by gcc
 (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

 My BDR plugin is version 0.8.0beta1

 From the docs, I've come up with this query:
 select slot_name, plugin, database, active, xmin,
 pg_get_transaction_committime(xmin)
 FROM pg_replication_slots ;

 BDR is working.  When I run that query, the 'xmin' value is always null,
 even though there is activity on the database.  I do/can get a catalog_xmin
 value.  Should I expect the 'xmin' value to be null?  Is there another way
 to monitor the replication latency when using BDR?

 Thanks,
 Steve Boyle





 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general




-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services