Re: [GENERAL] BDR replication port
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 Cc: PostgreSql-general 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" To: "Alvaro Aguayo Garcia-Rada" Cc: "PostgreSql-general" 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 Cc: PostgreSql-general 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" 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 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" To: "Alvaro Aguayo Garcia-Rada" Cc: "PostgreSql-general" 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 Cc: PostgreSql-general 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" 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 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 Cc: PostgreSql-general 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" 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 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" 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
> 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
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
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
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
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
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
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" To: "volga629" Cc: "pgsql-general" , "John R Pierce" 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" To: "Alvaro Aguayo Garcia-Rada" Cc: "pgsql-general" , "John R Pierce" 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" To: "volga629" Cc: "pgsql-general" , "John R Pierce" 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" To: "John R Pierce" Cc: "pgsql-general" Sent: Wednesday, 30 March, 2016 10:57:1
Re: [GENERAL] bdr replication
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" To: "volga629" Cc: "pgsql-general" , "John R Pierce" 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" To: "Alvaro Aguayo Garcia-Rada" Cc: "pgsql-general" , "John R Pierce" 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" To: "volga629" Cc: "pgsql-general" , "John R Pierce" 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" To: "John R Pierce" Cc: "pgsql-general" 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" To: "pgsql-general" 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
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
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" To: "Alvaro Aguayo Garcia-Rada" Cc: "pgsql-general" , "John R Pierce" 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" To: "volga629" Cc: "pgsql-general" , "John R Pierce" 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" To: "John R Pierce" Cc: "pgsql-general" 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" To: "pgsql-general" 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
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" To: "volga629" Cc: "pgsql-general" , "John R Pierce" 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" To: "John R Pierce" Cc: "pgsql-general" 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" To: "pgsql-general" 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
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" To: "John R Pierce" Cc: "pgsql-general" 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" To: "pgsql-general" 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
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" To: "John R Pierce" Cc: "pgsql-general" 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" To: "pgsql-general" 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
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" To: "pgsql-general" 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
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
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" To: "volga629" Cc: "pgsql-general" 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
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
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" To: "volga629" Cc: "pgsql-general" 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
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
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
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
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
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
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
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 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
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 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