Re: [GENERAL] [HACKERS] SSL and Encryption
On 3 November 2017 at 11:16, chiru r <chir...@gmail.com> wrote: > Hi , > > Please suggest the best chiper suite to configure openSSL for PostgreSQL > Server and client?. > > How to use other than md5 encryption algorithm to encrypt the passwords in > PostgreSQL? This is probably off topic for pgsql-hackers. For password crypto please go read the SCRAM thread and the PostgreSQL 10 release notes. -- 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 question on dboid conflicts
On 27 October 2017 at 01:15, Zhu, Joshua <j...@vormetric.com> wrote: > Database oid is used in both bdr.bdr_nodes, as node_dboid, and > bdr.bdr_connections, as conn_dboid, also used in construction of replication > slot names. Correct. However, it's used in conjunction with the sysid and node timeline ID. > I noticed that when trying to join a bdr group, if the database oid on the > new node happens to be the same as that of an node already in the bdr group, > the join would fail, and the only way to resolve the conflict that I was > able to come up with has been to retry with dropping/recreating the database > until the dboid does not conflict with any node already in the group. That is extremely surprising. In our regression tests the database oids should be the same quite often, as we do various tests where we create multiple instances. More importantly, every time you bdr_init_copy, you get a clone with the same database oid, and that works fine. There's no detail here to work from, so I cannot guess what's actually happening, but I can confidently say it's not a database oid conflict. Nowhere in BDR should the database oid be considered without the rest of the (sysid,timeline,dboid) tuple. -- 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, wal sender, high system cpu, mutex_lock_common
On 12 October 2017 at 11:03, Andres Freund <and...@anarazel.de> wrote: > On 2017-10-12 10:25:43 +0800, Craig Ringer wrote: >> On 4 October 2017 at 00:21, milist ujang <ujang.mil...@gmail.com> wrote: >> > On Tue, Oct 3, 2017 at 8:49 PM, Craig Ringer <cr...@2ndquadrant.com> wrote: >> >> >> >> >> >> Can you get stacks please? >> >> >> >> Use -g >> > >> > >> > # Events: 2K cpu-clock >> > # >> > # Overhead Command Shared ObjectSymbol >> > # . >> > # >> > 86.96% postgres [kernel.kallsyms] [k] __mutex_lock_common.isra.5 >> > | >> > --- __mutex_lock_common.isra.5 >> > read >> >> >> Unfortunately it looks like you're using a postgres built with >> -fomit-frame-pointers (the default) on x64, with an older perf not >> built with libunwind. This produces useless stacks. > > Just read this mail, but for libunwind to work you'd have to specify > "--call-graph dwarf", no? I think you're right. But only on a version of perf where it's available and used. I haven't recently checked if perf has finally grown the ability to load external debug symbols either. It never used to. -- 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, wal sender, high system cpu, mutex_lock_common
On 4 October 2017 at 00:21, milist ujang <ujang.mil...@gmail.com> wrote: > On Tue, Oct 3, 2017 at 8:49 PM, Craig Ringer <cr...@2ndquadrant.com> wrote: >> >> >> Can you get stacks please? >> >> Use -g > > > # Events: 2K cpu-clock > # > # Overhead Command Shared ObjectSymbol > # . > # > 86.96% postgres [kernel.kallsyms] [k] __mutex_lock_common.isra.5 > | > --- __mutex_lock_common.isra.5 > read Unfortunately it looks like you're using a postgres built with -fomit-frame-pointers (the default) on x64, with an older perf not built with libunwind. This produces useless stacks. You may need to recompile with -fno-omit-frame-pointer -- 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, wal sender, high system cpu, mutex_lock_common
On 3 October 2017 at 19:45, milist ujang <ujang.mil...@gmail.com> wrote: > Hi all, > > I've an environment 9.4 + bdr: > PostgreSQL 9.4.4 > You're on a pretty old postgres-bdr. Update. You're missing a lot of fixes from mainline. > This is consolidation databases, in this machine there are around 250+ wal > sender processes. > Not a great use case for BDR. Consider pglogical. > > finally get which processes (wal senders) that are using mutexes: > > perf top -e task-clock -p 55382 > > Can you get stacks please? Use -g -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] Logical Replication - test_decoding - unchanged-toast-datum
On 26 September 2017 at 05:01, Abhinav Singh <abhinav.theg...@gmail.com>
wrote:
> Hello,
>
> I am currently using PostgreSQL Community version 9.4.9 and then using
> this instance, I am doing logical replication(using replication slots). I
> have created the replication slots using the following query:
>
> SELECT xlog_position FROM pg_create_logical_replication_
> slot('cjkimqvfuvixqyjd_00016389_e6f7c975_a311_4067_bcf1_a6accb57ab37',
> 'test_decoding')
>
> ...
> 3. So now I started my replication.
>
>
This is one of the MANY reasons test_decoding isn't suitable as the base
for a replication solution. It has "test" in its name for a reason.
Your replication model, whatever it is, is broken, since it's not handling
special cases like unchanged TOASTed values in UPDATEs. This is a bug in
your replication tool.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR, near xid wraparound, a lot of files in pg_subtrans directory
On 15 September 2017 at 11:46, milist ujang <ujang.mil...@gmail.com> wrote: > Hi Craig, > > Thanks again for pointing to inactive replication slot. > After inactive replication slot been dropped, the relfrozenxid now moving. > > I wonder if replication identifier will have some issue if left > un-chained? since at other side there are inactive replication identifier. > No, that's harmless. However, if there's still an "other side" at all, you presumably have broken replication. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR, near xid wraparound, a lot of files in pg_subtrans directory
On 14 September 2017 at 13:35, milist ujang <ujang.mil...@gmail.com> wrote: > HI list, > > I have a database with bdr environment which keep alerting these messages > in log file: > > HINT: Close open transactions soon to avoid wraparound problems. > WARNING: oldest xmin is far in the past > Do you have any idle/old replication slots, perhaps from failed node joins or abandoned nodes not properly parted? SELECT * FROM pg_replication_slots; Also check SELECT oid,* FROM pg_database; -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR, limit number of groups in a cluster
On 11 September 2017 at 09:32, milist ujang <ujang.mil...@gmail.com> wrote: > Hi all, > > Based on the docs and look at the processes, it seems 1 wal sender on each > node per group. > > If there is scenario of consolidating many databases (say hundreds) into 1 > database (in this central cluster there are hundreds wal sender), what is > the limit number of groups? > It's not a use case I've paid much attention to. I expect it'll be limited by performance and memory, rather than have any firm limit. Maybe you should look into pglogical. This seems like a weird use for BDR. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR, wal segment has been removed, is it possible move forward?
On 7 September 2017 at 21:16, milist ujang <ujang.mil...@gmail.com> wrote: > Hi Craig, > > On Wed, Sep 6, 2017 at 4:07 PM, Craig Ringer <cr...@2ndquadrant.com> > wrote: > >> >> You could drop and re-create the replication slot, I guess. But your >> nodes would be hopelessly out of sync and need manual resync (with data >> replication disabled) of one node vs another. >> > > Thanks for pointing to replication slot. > I Simulate the similar situation in dev env by remove the wal segment on > node1, when node2 keep inserting into a table, now it perfectly can move > forward to latest wal segment, but the difference situation is at > node_status. > > In production node_status is i in node1 > there's a known bug in bdr1 where sometimes the node status doesn't update from 'i' after joining. > can I safely update the node_status directy on bdr.bdr_nodes? > Usually not. In this one specific case where a node is known to be fully joined and online, but its status is stuck at 'i', yes. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR, wal segment has been removed, is it possible move forward?
On 6 September 2017 at 08:47, milist ujang <ujang.mil...@gmail.com> wrote: > Hi Craig > > On Wed, Sep 6, 2017 at 7:21 AM, Craig Ringer <cr...@2ndquadrant.com> > wrote: >> >> >> BDR can, see bdr.skip_changes_upto . >> >> Unluckily my bdr is 0.9.3 > > >> But PostgreSQL's logical decoding requires a contiguous WAL stream to >> maintain a valid catalog_xmin and restart_lsn, so it'll still fail to >> advance. So your replication from that node is broken, and you have to part >> the node then rejoin. You'll need to manually recover any diverged data. >> >> Yup, I willing to reconcile data manualy via dblink or else, > is it still possible to move wal segment in 0.9.3? > Well, you can skip changes on the logical decoding stream manually in 0.9.3 using the underlying postgres functions. But it won't help you because what you broke when you deleted the WAL segments wasn't BDR, it was postgres logical decoding. It *requires* a contiguous stream of WAL. It cannot recover if you do not have that. And you cannot really reset it. You could drop and re-create the replication slot, I guess. But your nodes would be hopelessly out of sync and need manual resync (with data replication disabled) of one node vs another. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR, wal segment has been removed, is it possible move forward?
On 6 September 2017 at 01:52, milist ujang <ujang.mil...@gmail.com> wrote: > Hi all, > > due to space issue and high volume transaction, some wal segments removed > from pg_xlog on bdr environment. > What, you deleted them? > I had played streams and goldengate (oracle product) , that at capture > side we can move forward to certain sequence (archivedlog/redolog - wal > segment in postgres). > > So, is it possible to move forward to read recent wal segment in bdr > environment? (assume data reconciliation will be done manually). > BDR can, see bdr.skip_changes_upto . But PostgreSQL's logical decoding requires a contiguous WAL stream to maintain a valid catalog_xmin and restart_lsn, so it'll still fail to advance. So your replication from that node is broken, and you have to part the node then rejoin. You'll need to manually recover any diverged data. Don't go in and randomly delete things in the postgres data directory, or things will break. The BDR manual warns of the importance of disk space monitoring... -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR node removal and rejoin
On 14 July 2017 at 00:09, Zhu, Joshua <j...@vormetric.com> wrote:
>
>
> Found these log entries from one of the other node:
>
>
>
> t=2017-07-13 08:35:34 PDT p=27292 a=DEBUG: 0: found valid replication
> identifier 15
>
> t=2017-07-13 08:35:34 PDT p=27292 a=LOCATION:
> bdr_establish_connection_and_slot, bdr.c:604
>
> t=2017-07-13 08:35:34 PDT p=27292 a=ERROR: 53400: no free replication
> state could be found for 15, increase max_replication_slots
>
>
>
> Increased max_replication_slots, things are looking good now, thanks.
>
>
>
> This does bring up a couple of questions:
>
>
>
>1. Given the fact there is no real increase in the number of nodes in
>this repeated removal/rejoining exercise, yet it caused replication slots
>being used up, wouldn’t removal of a node also automatically free up the
>replication slot allocated for the node?
>
>
Yes, it should. Open issue. A patch would be welcomed.
>
>1. Or is there a way to manually free up no longer needed slots? (the
>don’t seem to show up in pg_replication_slots view, I made sure to use
>pg_drop_replication_slot when they do show up there)
>
>
It'll be complaining about replication identifiers ("origins" in 9.6); see
pg_replication_identifier
>1. If there is such a thing, what is the rule of thumb for best value
>of max_replication_slots (are they somehow related to the value
>max_wal_senders as well), with respect to, say, the max number of nodes
> intended to support?
>
>
I think that's covered in the docs, but it's safe to err fairly high. The
cost of extra slots is minimal.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR node removal and rejoin
On 13 July 2017 at 01:56, Zhu, Joshua <j...@vormetric.com> wrote: > Thanks for the clarification. > > > > Looks like I am running into a different issue: while trying to pin down > precisely the steps (and the order in which to perform them) needed to > remove/rejoin a node, the removal/rejoining exercise was repeated a number > of times, and stuck again: > > > >1. The status of the re-joining node (node4) on other nodes is “I” >2. The status of the re-joining node on the node4 itself started at >“I”, changed to “o”, then stuck there >3. From the log file for node4, the following entries are constantly >being generated: > > > > 2017-07-12 10:37:46 PDT [24943:bdr > (6334686800251932108,1,43865,):receive:::1(33883)]DEBUG: > 0: received replication command: IDENTIFY_SYSTEM > > 2017-07-12 10:37:46 PDT [24943:bdr > (6334686800251932108,1,43865,):receive:::1(33883)]LOCATION: > exec_replication_command, walsender.c:1309 > > 2017-07-12 10:37:46 PDT [24943:bdr > (6334686800251932108,1,43865,):receive:::1(33883)]DEBUG: > 08003: unexpected EOF on client connection > > 2017-07-12 10:37:46 PDT [24943:bdr > (6334686800251932108,1,43865,):receive:::1(33883)]LOCATION: > SocketBackend, postgres.c:355 > > 2017-07-12 10:37:46 PDT [24944:bdr > (6408408103171110238,1,24713,):receive:::1(33884)]DEBUG: > 0: received replication command: IDENTIFY_SYSTEM > > 2017-07-12 10:37:46 PDT [24944:bdr > (6408408103171110238,1,24713,):receive:::1(33884)]LOCATION: > exec_replication_command, walsender.c:1309 > > 2017-07-12 10:37:46 PDT [24944:bdr > (6408408103171110238,1,24713,):receive:::1(33884)]DEBUG: > 08003: unexpected EOF on client connection > Check the logs on the other end. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR node removal and rejoin
On 11 July 2017 at 05:49, Zhu, Joshua <j...@vormetric.com> wrote: > An update… after manually removing the record for ‘node4’ from > bdr.bdr_nodes, corresponding record in bdr.bdr_connections, and associated > replication slot (with pg_drop_replication_slot), rejoining was successful. > > > > I was under the impression that there is no need to perform manual cleanup > before a removed node (with database dropped and recreated) rejoining a BDR > group. > BDR1 requires that you manually remove the bdr.bdr_nodes entry if you intend to re-use the same node name. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] How does BDR replicate changes among nodes in a BDR group
On 9 June 2017 at 02:47, Zhu, Joshua <j...@vormetric.com> wrote: > Thanks for the clarification. > > A follow up question, then, given *once joined all nodes are equal*, is that: > > should the node A dies or taken out of the group, the remaining three node > group (with B, C and D) would continue to function properly, correct? Yes. Though you'll need to tell them node A isn't coming back by parting it from one of the other nodes at some point, otherwise they'll accumulate WAL in pg_xlog and run out of disk space eventually as they hold onto the resources A would need to sync back up. Monitoring is important. -- 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] How does BDR replicate changes among nodes in a BDR group
On 8 June 2017 at 04:50, Zhu, Joshua <j...@vormetric.com> wrote: > How does BDR replicate a change delta on A to B, C, and D? It's a mesh. Once joined, it doesn't matter what the join node was, all nodes are equal. > e.g., A > replicates delta to B and D, and B to C, or some other way, or not > statically determined? Each node replicates to all other nodes in an undefined order determined by network timing etc. -- 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
> 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] Replication (BDR) problem: won't catch up after connection timeout
See also https://github.com/2ndQuadrant/bdr/issues/233 -- 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] Replication (BDR) problem: won't catch up after connection timeout
Increase wal_sender_timeout to resolve the issue. I've been investigating just this issue recently. See https://www.postgresql.org/message-id/camsr+ye2dsfhvr7iev1gspzihitwx-pmkd9qalegctya+sd...@mail.gmail.com . It would be very useful to me to know more about the transaction that caused this problem. -- 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: changing dsn on a running node
On 12 October 2016 at 00:55, Sylvain MARECHAL <marechal.sylva...@gmail.com> wrote: > Le 07/10/2016 à 23:54, Natan Abolafya a écrit : > > Is it possible to change the dsn connection string of a node without leaving > the group? I couldn’t find the related documentation unfortunately. > > We’re using BDR in a dynamic environment where the hostname/ip of a node may > be changed any time. Leaving and rejoining the BDR group seems to be a > solution but is a bit tedious and a slow process. You can update the bdr.bdr_connections entry and then kill the apply workers. Right now bdr.bdr_connections_changed() doesn't know to check for a changed DSN. I'd welcome a patch to address that, since I probably won't have time to get to it soon. We should have a bdr.bdr_connection_set_dsn(...) function, really. Again, a patch would be welcomed. -- 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: Transactions with global lock
On 31 August 2016 at 22:38, Salvatore Tomaselli <salvatore.tomase...@cryptzone.com> wrote: > Hello, > > I have been looking around in the documentation and I didn't find anything, > so I wonder if there is support in bdr for having transactions that happen > while the global lock is acquired and get replicated everywhere before the > transaction ends. > > Is there a way to achieve this? The purpose of the global DDL lock to to prevent that. If you ALTER a table on one node, say by ALTERing the type of a column, while you continue to do write transactions to that table on other nodes, when the other nodes' data replicates to the node that does the DROP it won't make sense anymore. What value should be in the new column? BDR 1.0 adds a weaker DDL lock mode that is used for types of DDL where that is not a concern. It only blocks other concurrent DDL. But for DDL that alters tables it's necessary to block concurrent DML. BTW, now that it's clear in-core logical replication is going in another direction there's now a bdr-l...@2ndquadrant.com mailing list; see https://groups.google.com/a/2ndquadrant.com/forum/#!forum/bdr-list . -- 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] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1
On 12 August 2016 at 18:43, Alexander Farber <alexander.far...@gmail.com> wrote: > Thank you, I have rewritten it into: > > BEGIN > PERFORM check_positions(in_uid, in_gid, in_tiles); > > CREATE TEMP TABLE _words(word varchar, score integer) ON COMMIT > DROP; > > INSERT INTO _words > SELECT > out_word AS word, > max(out_score) AS score > FROM check_words(in_uid, in_gid, in_tiles) > GROUP BY word, gid; > > Or use CREATE TABLE ... AS SELECT ... That's the SQL-standard spelling anyway. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR Cluster vs DB Config
On 20 July 2016 at 13:22, Jonathan Eastgate <jonathan.eastg...@simpro.co> wrote: > Hi everyone. > > We've been testing BDR on and off for the last 2 years and are keen to > start looking at implementing it in production as it seems 0.93 has > resolved most of the issues we faced with it in the early days. > > However there is still one item that makes it a difficult proposition... > > DSN config per database. > > Is there any way to configure BDR on a cluster wide basis so that all DB's > on a cluster are replicated via BDR instead of having to configure a > connection for each DB we want to replicate? > No. Not only that, but if you're replicating lots of databases between PostgreSQL instances you're likely to start facing some performance problems around the sheer number of background workers required, the way WAL needs to be processed multiple times, etc. If you're using this for multi-tenancy or similar, see if you can isolate by schema not by database. > The problem we have is over 20 clusters with about 200 DB's per cluster > and growing constantly so this would make deploying BDR a painful process - > if we had to add a connection for each existing DB and then every new DB. > Yeah. That's going to cause you pain even aside from the management of it. > Is there a way around this or are there plans to make this type of config > available? > There are no plans to automate this configuration. BDR works at a database level, with the exception of bdr_init_copy bringing up all BDR-enabled databases on the join target node as a one-time operation at setup. Maybe once we eventually have some kind of answer for how to replicate instance-global DDL that affects shared catalogs, like database creation/drop, user creation/drop, etc, then it might make sense to extend BDR or its successor to do this. But not at the moment. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR
On 11 June 2016 at 02:12, Rakesh Kumar <rakeshkumar46...@gmail.com> wrote: > Sorry if this question was asked before. As I understand currently > BDR does not support the replicating nodes to run different major > versions, like > 9.4 <-> 9.5. > > Is this in the works? > Not with BDR between 9.4 and 9.5, no, as there will not be a 9.5 version of BDR. It'll be skipping straight to 9.6. pglogical, a simplified and streamlined version of the logical replication facilities used in BDR, can replicate from 9.4 to 9.5 (or to/from any other combo of verisons 9.4+). It doesn't support multimaster or DDL replication like BDR does, though. You can also look into Londiste and Slony-I. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR
On 11 June 2016 at 02:26, David G. Johnston <david.g.johns...@gmail.com> wrote: > On Fri, Jun 10, 2016 at 2:12 PM, Rakesh Kumar <rakeshkumar46...@gmail.com> > wrote: > >> Sorry if this question was asked before. As I understand currently >> BDR does not support the replicating nodes to run different major >> versions, like >> 9.4 <-> 9.5. >> >> Is this in the works? >> > > This seems relevant... > > http://bdr-project.org/docs/stable/logical-vs-physical.html > > > > But you question seems vague since BDR is a concept for which many > implementations exist. > I think they're specifically referring to 2ndQ's BDR project here, rather than bi-directional logical replication general. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] [HACKERS] Online DW
On 11 June 2016 at 12:29, Sridhar N Bamandlapally <sridhar@gmail.com> wrote: > Ok, let me put this way, > > I need every transaction coming from application sync with both production > and archive db, > but the transactions I do to clean old data(before 7 days) on production > db in daily maintenance window should not sync with archive db, > Would you please remove pgsql-hackers from the Cc list for future replies? This isn't specific discussion of a concrete feature patch or detailed design or bug, so it's more appropriate for pgsql-general. I think everyone's understood what you want, but you can't just throw random syntax at the wall and expect someone to magically make it work. What you propose will not work with PostgreSQL's block based replication model. It just can't. You can't do this with built-in streaming replication. You can, however, do roughly what you want with Londiste by (ab)using session_replication_role so that only some transactions get replicated. It is something that would be reasonably practical to add to pglogical too; in fact, you can already set pglogical up so it only replicates INSERTs and UPDATEs but not DELETEs, there's just no per-session/per-transaction/per-statement control over that yet. You could probably achieve it fairly easily with replication origin filtering and a custom replication origin for things you want to skip replication for though. > I say, every online archive db is use case for this. > > Yeah, I don't think anyone's saying it wouldn't be useful. The point is that your proposal is extremely vague and lacks any useful detail, let alone a proposed implementation or a patch. I wish PostgreSQL had autonomous transactions, a shared-across-sessions JVM or Mono runtime with first-class Java or C# support, built-in commands to dump table definitions over SQL, and lots more, but it does no good to talk about them unless I'm willing to implement them or find someone else who will. You are being pointed to alternatives that would meet your needs, but seem to be ignoring that because it's not the solution you have already decided you need for your problem. I doubt anybody will implement this for you, especially since I don't think it's really possible in PostgreSQL's block-based physical replication architecture. So saying what you want repeatedly probably won't achieve anything. What are you going to do about it? -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] [HACKERS] Online DW
On 10 June 2016 at 18:56, John R Pierce <pie...@hogranch.com> wrote: > On 6/10/2016 2:18 AM, Sridhar N Bamandlapally wrote: > >> This/These will be performed in Production to clean-up archive which will >> not be sync with Archive/DW DB only >> >> one heads-up is Archive/DW DB may need to build WITHOUT CONSTRAINTS >> >> May need to introduce ARCHIVE system/tag in pg_hba.conf >> > > there's a whole lot of implied magic here unless you want to get way more > specific what these features do, exactly, under all possible conditions. I'd go further than that and say I can't see how something like this could possibly work with physical (block based) replication. It's total hand-waving. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] [HACKERS] Online DW
On 10 June 2016 at 16:11, Sridhar N Bamandlapally <sridhar@gmail.com> wrote: > Hi > > Is there any feature in PostgreSQL where online DW (Dataware housing) is > possible ? > > am looking for scenario like > > 1. Production DB will have CURRENT + LAST 7 DAYS data only > > 2. Archive/DW DB will have CURRENT + COMPLETE HISTORY > > expecting something like streaming, but not ETL > There's nothing built-in, but that's exactly the sort of thing pglogical is intended for. You can also build something along those lines with Londiste fairly easily. Hopefully this is the sort of thing we can move toward with built-in logical replication in coming releases. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR Alter table failing
On 28 April 2016 at 02:47, Will McCormick <wmccorm...@gmail.com> wrote: > So if I wanted to extend a column from 100 characters to 255 characters is > this permitted? The fact that I'm not making a change and the BDR kicked me > out makes me skeptical. > Off the top of my head I'm not sure and would need to test. There's no specific logic in there for detecting such changes and permitting them, so I suspect not. If you're changing types in BDR you're expected to do it the long way. Add a new col, update to copy the data, drop the old col and rename the new col. Yes, that's ugly. We'd like to change it at some point. If you find this particular problem annoying enough it'd be helpful if you could send a patch for bdr_commandfilter.c to detect and permit changes that only affect a column's typmod. In the specific case of varchar columns, personally I recommend just using 'text' and adding a CHECK constraint on length. That's what I do most places, not just BDR. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR Alter table failing
On 27 April 2016 at 23:43, Alvaro Aguayo Garcia-Rada < aagu...@opensysperu.com> wrote: > Based on my experience, I can say BDR does not performs pre-DDL checks. > For example, if you try to CREATE TABLE with the name of an existing table, > BDR will acquire lock anyway, and then will fail when executing the DDL > statement on the first node, because the table already exists. > Correct, and it has to because otherwise it'd face a race condition where the table might be created between when it checked and when it tries to create it. > In your case, it's the same: BDR does not checks(nor needs to) if the DDL > statement is or not required, as that's a dba dutty. Then, BDR executes the > statement(ane acquires locks), and fails because it would require a full > table rewrite, which, at the time, is not supported by BDR. > Yeah. This is more of a "we never thought anyone would want to do that and didn't much care" problem. In this case we could lock the table and then inspect it. In fact we really should be locking it to prevent races, but we rely on the global DDL lock mechanism for that right now. (That's not what it's for, just a side-effect that's kind of handy). Applications *must* be adapted to run on BDR. You can't just point it at a BDR-enabled DB and go "we'll be right". DDL is one thing, but multimaster async replication conflicts are rather more significant concerns. Also handling of the currently somewhat quirky global sequence support's habit of ERRORing if you go too fast, trying to keep your transaction sizes down, and not trusting row locking for mutual exclusion between nodes. You can't use LISTEN/NOTIFY between nodes either, or advisory locking, or pg_largeobject ... yeah. Apps require audit and usually require changes. Changing an expected error code will be the least of your worries. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] Multimaster
On 14 April 2016 at 17:11, Moreno Andreo <moreno.and...@evolu-s.it> wrote: > All databases that have to be replicated have a flag in the app > configuration, and the replication config is set up, for each database and > each site, when the first synchronization is made, so we could spawn a > process per database and replicate. > Not to mention we should be able, if necessary, to exclude one or more > tables from the replication. > That should all be pretty simple with what's already there and supported in BDR using replication sets. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] Multimaster
On 14 April 2016 at 17:14, konstantin knizhnik <k.knizh...@postgrespro.ru> wrote: > > On Apr 14, 2016, at 8:41 AM, Craig Ringer wrote: > > On 1 April 2016 at 19:50, Konstantin Knizhnik <k.knizh...@postgrespro.ru> > wrote: > > Right now the main problem is parallel apply: we need to apply changes >> concurrently to avoid unintended dependencies causing deadlocks and provide >> reasonable performance. >> > > How do you intend to approach that? > > > Actually we already have working implementation of multimaster... > There is a pool of pglogical executors. pglogical_receiver just reads > transaction body from connection and append it to ready-for-execution queue. > I intend to make the same split in pglogical its self - a receiver and apply worker split. Though my intent is to have them communicate via a shared memory segment until/unless the apply worker gets too far behind and spills to disk. Any vacant worker form this pool can dequeue this work and proceed it. > How do you handle correctness of ordering though? A naïve approach will suffer from a variety of anomalies when subject to insert/delete/insert write patterns, among other things. You can also get lost updates, rows deleted upstream that don't get deleted downstream and various other exciting ordering issues. At absolute minimum you'd have to commit on the downstream in the same commit order as the upstream.. This can deadlock. So when you get a deadlock you'd abort the xacts of the deadlocked worker and all xacts with later commit timestamps, then retry the lot. BDR has enough trouble with this when applying transactions from multiple peer nodes. To a degree it just throws its hands up and gives up - in particular, it can't tell the difference between an insert/update conflict and an update/delete conflict. But that's between loosely coupled nodes where we explicitly document that some kinds of anomalies are permitted. I can't imagine it being OK to have an even more complex set of possible anomalies occur when simply replaying transactions from a single peer... It is certainly possible with this approach that order of applying > transactions can be not the same at different nodes. > Well, it can produce downright wrong results, and the results even in a single-master case will be all over the place. But it is not a problem if we have DTM. > How does that follow? > The only exception is recovery of multimaster node. In this case we have > to apply transaction exactly in the same order as them were applied at the > original node performing recovery. It is done by applying changes in > recovery mode by pglogical_receiver itself. > I'm not sure I understand what you area saying here. > We also need 2PC support but this code was sent to you by Stas, so I hope >> that sometime it will be included in PostgreSQL core and pglogical plugin. >> > > I never got a response to my suggestion that testing of upstream DDL is > needed for that. I want to see more on how you plan to handle DDL on the > upstream side that changes the table structure and acquires strong locks. > Especially when it's combined with row changes in the same prepared xacts. > > > We are now replicating DDL in the way similar with one used in BDR: DDL > statements are inserted in special table and are replayed at destination > node as part of transaction. > We have also alternative implementation done by Artur Zakirov < > a.zaki...@postgrespro.ru> > which is using custom WAL records: > https://gitlab.postgrespro.ru/pgpro-dev/postgrespro/tree/logical_deparse > Patch for custom WAL records was committed in 9.6, so we are going to > switch to this approach. > How does that really improve anything over using a table? This doesn't address what I asked above though, which is whether you have tried doing ALTER TABLE in a 2PC xact with your 2PC replication patch, especially one that also makes row changes. > Well, recently I have made attempt to merge our code with the latest > version of pglogical plugin (because our original implementation of > multimaster was based on the code partly taken fro BDR) but finally have to > postpone most of changes. My primary intention was to support metadata > caching. But presence of multiple apply workers make it not possible to > implement it in the same way as it is done node in pglogical plugin. > Not with a simplistic implementation of multiple workers that just round-robin process transactions, no. Your receiver will have to be smart enough to read the protocol stream and write the metadata changes to a separate stream all the workers read. Which is awkward. I think you'll probably need your receiver to act as a metadata broker for the apply workers in the end. Also now pglogical plugin contains a lot of code which performs mapping > betwe
Re: [GENERAL] Multimaster
On 2 April 2016 at 02:15, Moreno Andreo <moreno.and...@evolu-s.it> wrote: > Actually we have to improve what our replicator is doing: it's only > replicating the single user's database. The improvement should that we can > put it on the "server" (in some cases there are groups of users sharing a > dedicated server) and, given a configuration of what and how to replicate, > it should replicate more than one DB a time. > That's a layer on top as far as pglogical is concerned. It's only interested in a single database at a time. The same is true of BDR. A tool that automatically configures newly found databases to be replicated should be pretty trivial to write, though. > We were beginning to "translate" (and then improve) this program in c#, > when I bumped into articles pointing to BDR, and I started taking a look. > But it seems that is good to replicahe whole servers, and still hasn't the > granularity we need. Huh? BDR is configured database-by-database. The only exception is with bdr_init_copy, for initial setup using binary base backups; in that case all databases are copied. It sounds like you actually *want* to replicate all databases at once. Presumably the reason you're not just using physical streaming replication for that is that different hosts have a different set of writeable databases? E.g. [Node A] [Node B] [DB-1] -> [DB-1] [DB-2] -> [DB-2] [DB-3] <- [DB-3] [DB-4] <- [DB-4] so each DB is written from only one node at a time, but both nodes have writeable DBs. Right? -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] Multimaster
On 1 April 2016 at 19:50, Konstantin Knizhnik <k.knizh...@postgrespro.ru> wrote: Right now the main problem is parallel apply: we need to apply changes > concurrently to avoid unintended dependencies causing deadlocks and provide > reasonable performance. > How do you intend to approach that? You should be able to do parallel apply between nodes trivially, i.e. node A applies changes in parallel from nodes B C and D. Doing parallel apply of multiple changes from node A to node B is much harder. I wrote about parallel logical apply separately so I won't repeat it here; search the archives for the notes if interested. > We also need 2PC support but this code was sent to you by Stas, so I hope > that sometime it will be included in PostgreSQL core and pglogical plugin. > I never got a response to my suggestion that testing of upstream DDL is needed for that. I want to see more on how you plan to handle DDL on the upstream side that changes the table structure and acquires strong locks. Especially when it's combined with row changes in the same prepared xacts. I'd really like separate patches for the core changes and the pglogical support for them, too. > There are also some minor technical issues which lead us to making few > changes in pglogical code but we tried to do our best to keep original > versions unchanged, so we can switch to public version in future. > Details? Now is exactly the time to address those points. -- 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 <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 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
On 15 March 2016 at 05:17, Dustin Kempter <dust...@consistentstate.com> wrote: > However my instances are not on the same server and I attempted to simply > add a host=(the ip) but that failed. Please help > Review the logs on both hosts to see any errors during setup. Note that you will need to drop and re-create the database if you need to attempt setup again. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] Slave-Master replication on top of BDR
On 4 March 2016 at 23:02, Alvaro Aguayo Garcia-Rada <aagu...@opensysperu.com > wrote: > Hi. I currently have two servers in different geographical locations; both > of them are replicating with Postgres-BDR, that's OK. However, I need two > more servers to get a read only replication of only some tables from the > master ones. > > At first I tried with Slony, but it just didn't work(don't know why). I've > also checked docs for UDR and pglogical, but don't keep it clear if they > can coexist with BDR. > > pglogical should theoretically work as both provider and subscriber on BDR, but we haven't put the test infrastructure together to validate that and make it an officially supported configuration yet. It's certainly desired and on the roadmap. Using UDR with BDR doesn't work well; the issues we found there are part of why pglogical was created and why it works the way it does. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR concern/issue
On 3 March 2016 at 07:54, cchee-ob <carter.c...@objectbrains.com> wrote: > I queried pg_replication_slots after I removed an BDR node and I noticed a > slot_name that isn't in bdr.bdr_node_slots. And active is 'f' and it has > been retaining bytes. Should I be concerned and is there a way to remove > it. I do still have one UDR node which is running > (bdr_16385_6228994276814368133_1_16384). Any suggestions? > > svp2=# SELECT > slot_name, database, active, > pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn) > AS retained_bytes > FROM pg_replication_slots > WHERE plugin = 'bdr'; > slot_name| database | active | > retained_bytes > > -+--++ > bdr_16385_6206441431541275808_1_16385__ | svp2 | f | > 410036551440 > bdr_16385_6228994276814368133_1_16384__ | svp2 | t | > 285760 > (2 rows) > > svp2=# SELECT * FROM pg_stat_replication; > -[ RECORD 1 ]+--- > pid | 1122 > usesysid | 10 > usename | postgres > application_name | bdr (6228994276814368133,1,16384,):receive > client_addr | 10.253.0.8 > client_hostname | > client_port | 43724 > backend_start| 2016-02-25 17:53:21.10519-08 > backend_xmin | > state| streaming > sent_location| 184/AE210BC8 > write_location | 184/AE210BC8 > flush_location | 184/AE20E748 > replay_location | 184/AE210BC8 > sync_priority| 0 > sync_state | async > > svp2=# select * from bdr.bdr_node_slots; > node_name | slot_name > ---+--- > (0 rows) > > svp2=# SELECT * FROM bdr.bdr_nodes; > -[ RECORD 1 ]--+-- > node_sysid | 6206439726032130602 > node_timeline | 1 > node_dboid | 16385 > node_status| r > node_name | BDR1 > node_local_dsn | host=10.253.228.105 port=5432 dbname=svp2 > node_init_from_dsn | > -[ RECORD 2 ]--+-- > node_sysid | 6206440469625465777 > node_timeline | 1 > node_dboid | 16385 > node_status| k > node_name | BDR2 > node_local_dsn | host=10.253.16.25 port=5432 dbname=svp2 > node_init_from_dsn | host=10.253.228.105 port=5432 dbname=svp2 > > If you have a slot you know is unused, drop it. You can check it's the slot for the parted node by comparing the slot name against the bdr local node identity for the parted node (see the bdr docs for relevant functions to get node identity). BDR makes a best-effort attempt at dropping slots when parting a node but there are known race conditions. We really need a two-phase part, where we first agree to part and *then* actually remove the node, but that's not yet implemented. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] [JDBC] JDBC behaviour
On 18 February 2016 at 16:13, Vladimir Sitnikov <sitnikov.vladi...@gmail.com > wrote: If you want to shoot yourself in a foot for fun and profit, you can > try https://github.com/pgjdbc/pgjdbc/pull/477. > I think this should be incorporated, once it's ready, as a non-default connection option. It's handy for porting applications. I think PostgreSQL's behaviour is the most correct, but if people are porting apps and want to wear the performance impact of all those savepoints and have written their code to handle partially-failed xacts, then sure, they can have that. I'm constantly astounded by how many people are willing to simply ignore errors and carry on with the transaction without even properly verifying that the error was the exact one they expected though. Seriously bad application development and it *will* bite them. The best, most correct thing to do remains to retry the whole transaction. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] database corruption
On 12 February 2016 at 17:56, Oliver Stöneberg <olive...@online.de> wrote: > A few weeks ago we already had a data corruption when the disk was > full. There are other services running on the same machine that could > cause the disk to fill up (e.g. local chaching when the network is > acting up). It happened a few times so far but the database was never > compromised. In that case thought it was but fortunately we only lost > a huge table/toast (300+ GB) that has very verbose data stored which > is not essential. That happened with an earlier 9.4 version. > What remedial action was taken to restore the database to normal functionality at this time? Is the current database a direct descendant of the one that was corrupted here? i.e. has it had a complete dump, initdb and restore since then, or not? Unfortunately we don't have a recent backup of the database (a tool > to back up all the relevant data was just finished recently and was > not set up for this system yet). > Read and act on https://wiki.postgresql.org/wiki/Corruption immediately. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] Unrecognized configuration parameter in bdr 0.9.3
On 11 February 2016 at 20:16, Kaushal Shriyan <kaushalshri...@gmail.com>
wrote:
> Hi,
>
> I am following http://bdr-project.org/docs/stable/index.html for setting
> up multimaster replication.
>
Are you sure that's the documentation you were using?
You seem to have settings that only applied to the older 0.7.x versions:
> # (BEGIN) BDR connection settings for node 1, port 5600
> bdr.connections = 'secondarymasterport5601'
> bdr.secondarymasterport5601_dsn = 'dbname=bdrdemo user=postgres port=5601'
> # (END) BDR connection settings for node 1, port 5600
>
> # (BEGIN) BDR connection settings for node 2, port 5601
> #bdr.connections = '{DevEnvName}node01port5600'
> #bdr.{DevEnvName}node01port5600_dsn = 'dbname={DevDBName} user=postgres
> port=5600'
> #bdr.{DevEnvName}node01port5600_init_replica = on
> #bdr.{DevEnvName}node01port5600_replica_local_dsn = 'dbname={DevDBName}
> user=postgres port=5601'
> # (END) BDR connection settings for node 2, port 5601
>
The above is not used in BDR 0.9.x. Configuration is done at the SQL level.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR error trying to replay a invalid statement
On 2 February 2016 at 05:07, cchee-ob <carter.c...@objectbrains.com> wrote:
> I noticed that the BDR replication continually trying to replay a ddl
> statement that has a syntax error. Is there anything that can be done to
> skip this statement or do I need to rebuild the replicated node?
That's a DDL deparse bug. Ouch. Noted.
Honestly, rebuilding the node is the easiest option, but you can otherwise
skip the transaction. It's a bit tricky and I haven't had to do it in a
while or tested this procedure recently, please be cautious.
Stop the node(s) that are stuck. On the upstream node they're receiving the
statement from, check pg_replication_slots to get the slot name(s).
For each slot use pg_logical_slot_peek_binary_changes('slot_name', NULL,
NULL, 'interactive', 't') to find the problem xact. This is made more
exciting by the fact that the protocol is binary, but you'll be able to
find the SQL text in question in the stream. Determine the LSN associated
with the statement. Look further down the stream until you find the commit
record for it (command type 'c' in the binary protocol) and note the LSN
there.
Now call pg_logical_slot_get_binary_changes('slot_name', '123/456', NULL,
'interactive', 't') to skip up to the end of the problem transaction.
When you start the BDR nodes back up they'll ask for the old replay
position but it won't be available and they'll resume replay from the
restart position on the slot.
This process probably seems a bit baroque. It is. One of the things we're
working on right now with pglogical is a better way to skip over part of
the change stream, for whatever reason desired. Starting with a decoder
that lets you see the human readable protocol stream and reports the commit
boundaries.
> t=2016-02-01 13:02:27 PST d= h= p=21795 a=ERROR: 42601: syntax error at or
> near "ON" at character 8
> t=2016-02-01 13:02:27 PST d= h= p=21795 a=CONTEXT: during DDL replay of
> ddl
> statement: GRANT ON TABLE table1 TO user2 WITH GRANT OPTION
> <http://www.postgresql.org/mailpref/pgsql-general>
Yup. Deparse bug.
Do you know what the original statement was?
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR replication
On 29 January 2016 at 18:27, Nikhil <nikhilsme...@gmail.com> 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] Difference between UDR and BDR replication
On 28 January 2016 at 21:16, Kaushal Shriyan <kaushalshri...@gmail.com> wrote: > > > On Thu, Jan 28, 2016 at 6:32 PM, Craig Ringer <cr...@2ndquadrant.com> > wrote: > >> On 28 January 2016 at 19:16, Kaushal Shriyan <kaushalshri...@gmail.com> >> wrote: >> >>> Hi, >>> >>> Can somebody please help me understand the difference between UDR and >>> BDR with examples? >>> >> >> >> BDR is for multiple masters that all replicate to each other. >> >> UDR takes data from one server and copies it to another. One way. (By the >> way, I strongly advise you to now use pglogical instead of UDR). >> >> BDR: >> >> A <==> B >> >> UDR/pglogical: >> >> A ==> B >> > > Hi Craig, > > Thanks for the explanation. Does it mean UDR is Master to Slave > replication? > > Correct. Please use either PostgreSQL's built-in streaming replication features or pglogical instead, though. If you're not sure which, use the built-in replication features. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] Difference between UDR and BDR replication
On 28 January 2016 at 19:16, Kaushal Shriyan <kaushalshri...@gmail.com> wrote: > Hi, > > Can somebody please help me understand the difference between UDR and BDR > with examples? > BDR is for multiple masters that all replicate to each other. UDR takes data from one server and copies it to another. One way. (By the way, I strongly advise you to now use pglogical instead of UDR). BDR: A <==> B UDR/pglogical: A ==> B -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR with postgres 9.5
On 22 January 2016 at 04:24, Merlin Moncure <mmonc...@gmail.com> wrote: > On Wed, Jan 20, 2016 at 12:52 PM, Vik Fearing <v...@2ndquadrant.fr> wrote: > > On 01/20/2016 11:41 AM, Nikhil wrote: > >> Hello All, > >> > >> > >> What is the timeline for BDR with postgres 9.5 released version. > > > > Currently there are no plans for BDR with 9.5. > > https://github.com/2ndQuadrant/bdr/issues/157#issuecomment-172402366 > > 9.6 looks like a possibility though. I have big plans for BDR > personally, but for various reasons need to lay it on top of a stock > postgres. Yeah. 9.6 is the goal right now, with it built around pglogical and (hopefully) stock 9.6 to make it more maintainable and modular. If you want BDR on 9.6, help with getting sequence access methods committed, the logical decoding for sequences patch, and/or failover slots would be valuable since they are all going to be important for BDR on 9.6. If you want to use it please help make it happen. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] adding a bdr node using bcv backup
On 21 January 2016 at 20:46, (Daniel Stolf) <dst...@gmail.com> wrote: > So here's what I don't get: > > 1) if I have to create a new replication slots on node1 and 2 beforehand > using "pg_create_physical_replication_slot" , don't they need the if of > node3 on their name? > You need to create a logical replication slot with the 'bdr' plugin, since that's what BDR uses. > 2) If node3 has the same name and if as node1, won't that introduce a > conflic? Don't I need to clean that up before node3 can join the > replication group? > It will not have the same sysid. bdr_init_copy resets it normally. If you're doing it manually you'd have to run pg_resetxlog with the option to reset the sysid, create the new slots with the new sysid, then make sure bdr_init_copy doesn't reset the sysid again it afterwards when it brings the new node up. Honestly I don't remember the exact steps that had to be performed before bdr_init_copy got support for automating the pg_basebackup step. That's the supported way to do it. I'm trying to prepare some conference presentations and a new pglogical release so I can't presently dig into it further for you; you may need to take a look at the bdr_init_copy sources and/or study how the node bringup works in more detail. I can see it being useful to add a new mode to bdr_init_copy where you tell it to generate a sysid and make new slots for that sysid; *then* you make a snapshot and restore it, then you run bdr_init_copy again to finish bringup, resetting the sysid to the new value and finishing setup. There's nothing like that now though. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] adding a bdr node using bcv backup
On 21 January 2016 at 08:29, (Daniel Stolf) <dst...@gmail.com> wrote: > Hello there... > > I'm new to postgres and I'm trying out BDR replication... > > I know that when I issue the bdr.bdr_group_join command, it will copy the > entire database from the host I specify on parameter 'join_using_dsn' and > this may take a while depending on the network and the size of the > database... > > What I wanted to know is if I can leverage a bcv backup... Is it possible? > BCV seems to be an EMC backup system. It looks like a snapshot. If the snapshot taken is consistent and atomic, and if it includes both pg_xlog and the rest of the datadir and all tablespaces in the SAME snapshot taken at the SAME instant, then you can treat it much like a pg_basebackup. In that case you can use bdr_init_copy to bring it up as a new BDR node. You must either stop all writes to all other nodes or pre-create the replication slots *before* taking the snapshot though, otherwise the new node won't be able to catch up to writes done after the snapshot and before it was started. If this sounds too complex then stick to the documented methods that work. Working from separately taken snapshots is hard to get right and could lead to subtle data problems if you get it wrong. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] Postgres BDR bdr_init_copy fails
On 15 January 2016 at 03:41, Nikhil <nikhilsme...@gmail.com> wrote: > > pg_ctl: another server might be running; trying to start server anyway > > It looks like you may have run bdr_init_copy on a non-empty data directory containing an existing server. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] [BDR] Best practice to automatically abort a DDL operation when one node is down
On 13 January 2016 at 21:45, Sylvain MARECHAL <marechal.sylva...@gmail.com> wrote: > The problem is that the (1) DDL request will wait indefinitely, meaning > all transactions will continue to fail until the DDL operation is manually > aborted (for example, doing CTRL C in psql to abort the "CREATE TABLE"). > Correct, and by design. I'd like to do a pre-check where we sync up with the peer nodes and see if they're all alive before we take the DDL lock. This would reduce the impact a bit and allow an early ERROR like "ERROR: cannot perform DDL when one or more nodes is unreachable". However... we have something pretty close already. You can just set a statement_timeout in the session doing the DDL. It'll cancel the operation if it takes too long. Note that a lock_timeout will NOT work because the BDR global DDL lock is not recognised as a true lock by PostgreSQL. > What is the best practice to make sure the DDL operation will fail, > possibly after a timeout, if one of the node is down? statement_timeout > I could check the state of the node before issuing the DDL operation, but > this solution is far from being perfect as the node may fail right after > this. > Correct, but it's still useful to do. I'd check to see all nodes are connected in pg_stat_replication then I'd issue the DDL with a statement_timeout set. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] Adding node to bdr group
> Can I just do a bdr.bdr_group_join <http://bdr-project.org/docs/stable/functions-node-mgmt.html#FUNCTION-BDR-GROUP-JOIN>? Just want to confirm what the best practice is as I haven't seen anything in the documentation about this. Yes, you can just do a bdr.bdr_group_join ( http://bdr-project.org/docs/stable/functions-node-mgmt.html) on the new node, specifying the dsn of either existing node, just like you did when creating the first node. Alternately, you can use bdr_init_copy ( http://bdr-project.org/docs/stable/command-bdr-init-copy.html) to do a clone of the whole database installation and bring it up as a new peer. Note that this will copy other non-BDR databases too, since it does a pg_basebackup and pg_basebackup doesn't have any facility for including/excluding individual databases. This can be a better option for a big database. In either case if the new node join fails for any reason you must make sure to manually remove the replication slots created on either/both existing nodes to prevent WAL from accumulating until they run out of disk space. On 16 January 2016 at 02:45, Cj B <blackc2...@gmail.com> wrote: > Hi there, I want to add another node to my BDR group and was wondering > what the best practice is. My database is about 4gb. Current servers on > west coast and new server is on east coast. > > Can I just do a bdr.bdr_group_join > <http://bdr-project.org/docs/stable/functions-node-mgmt.html#FUNCTION-BDR-GROUP-JOIN>? > Just want to confirm what the best practice is as I haven't seen anything > in the documentation about this. > -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR: cascading setup
On 11 January 2016 at 18:55, Oleksii Kliukin <al...@hintbits.com> wrote: > We are evaluating BDR for a multi-master cross-datacenter replication, > with 2 masters actually communicating across datacenter, supplemented by a > local in-datacenter replicas to provide HA. > This is strongly desirable ... but not currently supported. > As far as I see, I cannot make a promoted physical replica a member of the > multi-master group without re-joining the group after promotion (which > leads to re-transfering the whole database over the network from the > surviving master), see https://github.com/2ndQuadrant/bdr/issues/98 > Correct. The fundamental issue is that logical replication slots are not copied by pg_basebackup or replicated via physical WAL-based replication. Without this a promoted replica has no knowledge of the replay position of its peers, nor does it know how much extra WAL to retain to allow them to catch up on replication. This will hopefully be fixed in 9.6 as there are patches in the queue to address these issues. There are > - BDR multi-master being part of more than one replication group (I could > create one group for cross-DS multi-masters, and another for DS-local > communications)? > It won't help. BDR always replicates in a mesh and doesn't do cascading. Replication sets won't change that. > - BDR multi-master being also master with several UDR replicas attached > (so that DS-local nodes will be running as UDR replicas of a master, that > at the same time communicates via BDR to another master in another DS), and > allowing the UDR replica to join the BDR group if the master dies. > Again this won't work. You can't promote a UDR replica to a full BDR peer. For now you're stuck with the extra replication traffic of the two local nodes speaking directly to their remote peers. What you need is non-mesh topologies and support for selective changeset forwarding, and/or support for promotion of physical replicas to replace failed nodes. Both of those are in the pipeline. pglogical has a more flexible model of replication topology and forwarding, and we plan to rewrap BDR around pglogical for 9.6. This should (time permitting) allow for non-mesh topologies and cascading. To make it work well we'll need logical decoding support for logical slots, though, and that may not make it into 9.6. There's no practical way to add this to 9.4bdr since it relies heavily on (sysid,timeline,dboid) tuples to identify nodes, so it'll be 9.6-only. If the pg_basebackup and replication patches to replicate slots are accepted into 9.6 then we'll be able to have physical standbys of pglogical/bdr nodes. It may be possible to backport this to 9.4bdr but I'm not aware of any plans to do so and available time/resources are mainly focused on driving 9.6/pglogical forward. Get in touch if you think this is something you could use more urgently. I realise this isn't quite the answer you hoped for, but at least there's improvement on the horizon. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR and TX obeyance
On 5 January 2016 at 04:09, Riley Berton <rber...@appnexus.com> wrote: > > The conflict on the "thingy" table has resulted in node2 winning based > on last_update wins default resolution. However, both inserts have > applied. My expectation is that the entire TX applies or does not > apply. This expectation is clearly wrong. > Correct. Conflicts are resolved row-by-row. Their outcomes are determined (by default) by transaction commit timestamps, but the conflicts themselves are row-by-row. Because BDR: * applies changes to other nodes only AFTER commit on the origin node; and * does not take row and table locks across nodes it has no way to sensibly apply all or none of a transaction on downstream peers because the client has already committed and moved on to other things. If the xact doesn't apply, what do we do? Log output on the failing node(s) and throw it away? It's probably practical to have xacts abort on the first conflict, though some thought would be needed about making sure that doesn't break consistency requirements across nodes. It's not clear if doing so is useful though. For that you IMO want synchronous replication where the client doesn't get a local COMMIT until all nodes have confirmed they can commit the xact. That's something that could be added to BDR in future, but doing it well it requires support for logical decoding of prepared transactions which is currently missing from PostgreSQL's logical decoding support. If it's something you think is important/useful you might want to explore what's involved in implementing that. Question is: is there a way (via a custom conflict handler) to have the > TX obeyed? No. Even if you ERROR in your handler, BDR will just retry the xact. It has no concept of "throw this transaction away forever". > I can't see a way to even implement a simple bank account > database that changes multiple tables in a single transaction without > having the data end up in an inconsistent state. Am I missing something > obvious here? > You're trying to use asynchronous multimaster replication as if it was an application-transparent synchronous cluster with a global transaction manager and global lock manager. BDR is not application-transparent. You need to understand replication conflicts and think about them. It does not preserve full READ COMMITTED semantics across nodes. This comes with big benefits in partition tolerance, performance and latency tolerance, but it means you can't point an existing app at more than one node and expect it to work properly. The documentation tries over and over to emphasise this. Can you suggest where it can be made clearer or more prominent? -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR and synchronous replication
On 26 December 2015 at 19:29, Nikhil <nikhilsme...@gmail.com> wrote: > Hello, > > i am experimenting BDR project. As BDR does asynchronous replication, i > have a query regarding bdr.synchronous_commit=on option. > > Will aforementioned configuration in postgresql.conf makes the replication > synchronous? > No. It does not make replication synchronous. It makes the BDR downstream apply process commit and flush to disk each transaction it replays as soon as it has replayed it, instead of waiting until it's time to send replay feedback to the upstream. This means there's a pause for fsync with each applied xact, which performs terribly for high transaction rates. By default with synchronous_commit off BDR instead sends confirmation lazily as the transactions actually flush to disk. See bdr_send_feedback in the source code for details. If you want synchronous replication you can use synchronous_standby_names to make replication to exactly one peer server synchronous (ish). You have to set an application_name in the connection dsn on the peer server so you can identify it in synchronous_standby_names. It is not necessary to set bdr.synchronous_commit = on. Doing so may result in quicker confirmation of commit on the upstream at the cost of lower overall performance. There is no way to get n-safe synchronous replication because PostgreSQL doesn't support that, and BDR uses PostgreSQL's synchronous replication support. You can only have one synchronous replica active at a time and PostgreSQL will automatically choose the first reachable one in the replica list. > Does this require any other setting? any side effect for using this setup? > The usual downsides of synchronous replication. A potentially significant performance hit, the upstream stopping when the downstream isn't reachable, etc. Personally I don't think it's a good idea to try to combine BDR and synchronous replication. There are too many pitfalls, especially around the 1-synchronous-replica limitation. It'll be better if/when core gets support for n-safe synchronous replication. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR error while adding 3rd node to cluster
On 22 December 2015 at 17:00, Amit Bondwal <bondwal.a...@gmail.com> wrote: > I remove all the bdr packages and reinstall it and setup again the BDR > cluster, still facing the same issue on 3rd node. > At this point I'd really need to see the steps taken, in detail, to get to that point from a clean initial state. If I had to guess right now I'd say that the host pg3 isn't actually the node node3 that you are connected to when you're joining the node, i.e. the error message is correctly telling you that you've given the wrong external DSN. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR error while adding 3rd node to cluster
On 21 December 2015 at 22:57, Amit Bondwal <bondwal.a...@gmail.com> wrote: > Hi Everyone, > > I am trying to setup three node bdr cluster, I am following the quick > start guide, > It is working well between first and 2nd node, but When I try to add 3rd > node, it give the below error. > > hakuna=# SELECT bdr.bdr_group_join( > local_node_name := 'node3', > node_external_dsn := 'host=pg3 port=5432 dbname=hakuna', > join_using_dsn := 'host=pg1 port=5432 dbname=hakuna' > ); > ERROR: node identity for node_external_dsn does not match current node > when connecting back via remote > DETAIL: The dsn '' connects to a node with identity > (6229651217067355961,1,17161) but the local node is > (6229649404569370556,1,19247) > Huh. That's interesting. The dsn ''. How'd we get there? Can you show the output of select * from bdr.bdr_nodes; select * from bdr.bdr_connections; on the new node you're trying to join? -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR
On 15 December 2015 at 16:49, Andreas Kretschmer <akretsch...@spamfence.net> wrote: > BDR is currently an addon for 9.4, I don't believe its available for 9.5 > > yet. > > apparently, thx for the answer. Correct, there's no BDR for 9.5. There's a pretty good chance we'll skip 9.5 entirely and deliver an improved BDR on top of 9.6 down the track, but that's not something that'll be happening until 9.6 is much closer to ready. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] bdr manual cleanup required
I really couldn't say with the available information. Can you set provide a step-by-step process by which you set up these nodes?
Re: [GENERAL] bdr manual cleanup required
Are you adding more than one node at once? BDR isn't currently smart enough to handle that. Make sure to wait until one node is fully synced up before adding another.
Re: [GENERAL] BDR: ALTER statement hanging
If you're not sure what's going on on a node, look at its logs. The background worker API and PostgreSQL's lack of autonomous transactions makes it quite challenging for BDR workers to capture logs and expose them to users at the SQL level. So always, if in doubt, examine the log files.
Re: [GENERAL] BDR and Backup and Recovery
On 19 November 2015 at 00:54, Will McCormick <wmccorm...@gmail.com> wrote: > The below is from the 0.9.3 BDR documentation: > > "Because logical replication is only supported in streaming mode (rather > than WAL archiving) it isn't suitable for point-in-time recovery. Logical > replication may be used in conjunction with streaming physical replication > and/or PITR, though; it is not necessary to choose one or the other." > > Am I misinterpreting that BDR uses Logical Decoding and as such I cannot > perform PITR? > The point is that you cannot use the logical decoding data stream for point-in-time recovery. Nothing stops you archiving WAL like normal from a node that's participating in logical replication as an upstream and/or downstream. You just can't use the logical replication data stream its self for that purpose. Sounds like I need to clarify that part of the docs. Note the caveats in my prior mail re PITR and BDR, though; you can't just PITR-restore a replacement for a failed node and have it catch up and rejoin replication. Regarding logical PITR: Theoretically we could actually save a base pg_dump and a change stream as logical changes from pg_recvlogical, then use that for transaction-level logical PITR. It's not impossible, but it'd require new tools and require changes to BDR/UDR to allow the stream to be applied. Nobody's written them yet. I don't have any plans to do this in the near to mid term. It'd be an interesting project to build with pglogical. Its protocol is better suited to this than BDR's. You could do selective PITR of just a subset of tables you were interested in. If anyone's keen to tackle that, get in touch and I'll see if I can offer any help. >> I don't know why PITR wouldn't work with BDR, other than you can't use >> binary backups across incompatible versions and BDR might be considered >> incompatible with community Postgres. I would think it should still work >> fine if you try to restore to a BDR server. >> > It does, with the caveat that it can't be a drop-in replacement for a failed node due to the timeline increment. The data is there, but it won't participate in replication. See the steps outlined in my prior mail for details. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] Postgres BDR Conflict Issue
On 20 November 2015 at 14:18, vikas452 <vikas...@gmail.com> wrote: > Hi > > We are setting 4 node BDR replication setup at our end. > BDR Version is 0.8.0-2014-12-28-15f97b3. > > I am continuously getting following logs in the log file. > > CONFLICT: remote UPDATE on relation public.agents originating at node > 6208111525856296403:1:16384 at ts 2015-11-19 00:02:01.861176+05:30; row > was previously updated at node 0:0. Resolution: > last_update_wins_keep_local; > Can somebody help me with this?Any solution to avoid such conflicts. > Avoid issuing updates to tuples on multiple nodes, at least before the prior update has replicated. BDR is asynchronous. If you have asynchronous multi-master and you perform simultaneous actions on two nodes, when the changes replicate there can be conflicts. Some form of resolution is required. Async multi-master is hard. The docs try to address these topics. I would value your comments on them in terms of which parts are clear, which are hard to follow, etc, after a detailed reading. The docs have been significantly updated for the 0.10.0 development version, so I suggest going to those for the conflict resolution coverage. See: http://bdr-project.org/docs/next/conflicts.html Your comments would be appreciated. > Also is there any way to set the value of bdr.bdr_conflict_resolution. > I want to test last_update_wins_keep_remote as the conflict resolution > type. The conflict resolution strategy used here is "last update wins", i.e. the most recent change is the one that is kept. In this case the most recent change was the one made locally, so the remote change is discarded. You can't force last_update_wins_keep_remote, that doesn't make sense. If you force keeping the remote tuple it isn't a last-update-wins resolution anymore. What you can do is define a custom conflict handler that always keeps the remote tuple on one node and the local tuple on the other, based on inspecting the local node id. You *must* make sure the resolution is consistent on all nodes or data will diverge. I do not recommend doing this. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR and Backup and Recovery
other nodes you have to restore them temporarily, dump the tables that aren't in the first node's replication sets, and restore them. I'd really like to bring together a more complete picture here, but the development time currently available has to focus on robustness work and on progress toward 9.6. As always, contribution would be greatly valued, whether in terms of docs or code. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] bdr appears to be trying to replicate to itself
On 17 November 2015 at 00:33, Cj B <blackc2...@gmail.com> wrote: > select pg_drop_replication_slot(‘bdr_16385_6188730679935789649_1_16385__’) > Correct. > What impact will this have? If the slot is unused, it'll allow the WAL that's being held by the slot to be removed. It'll also unpin the catalog xmin to allow autovacuum to clean up dead tuples in the catalogs. This doesn't explain how the system got into this state. For that it'd really be necessary to see the steps taken during setup. BDR tries to protect against attempts to replicate-from-self. Presumably there's an oversight in those checks. If you're able to reproduce this state I'd like to hear details on how. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] BDR - Remove & Join
On 10 November 2015 at 05:10, Will McCormick <wmccorm...@gmail.com> wrote: > I then run a script which I used to setup replication before removal. The > problem I encounter is node B after join in bdr.bdr_nodes is stuck in status > 'c'. [snip] > We are using the following version of bdr: 0.9.2.0 Please update to 0.9.3, which fixes this issue, per https://github.com/2ndQuadrant/bdr/issues/126 -- 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: name conflict when joining a rebuilt node
On 30 October 2015 at 08:24, Florin Andrei <flo...@andrei.myip.org> wrote: > The problem is, bdr_node_join_wait_for_ready() never returns, it just waits > forever. If I go on pg11 and run SELECT * FROM bdr.bdr_nodes, I see pg12 > listed twice, with node_status k and i, respectively. On pg11 I see this in > the logs: > > "System identification mismatch between connection and slot","Connection for > bdr (6211167104388615363,1,16387,) resulted in slot on node bdr > (6211167104388615363,1,17163,) instead of expected node""bdr > (6211167104388615363,1,17163,): perdb" This is a bug fixed in 0.9.3. https://github.com/2ndQuadrant/bdr/issues/126 -- 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 - DDL Locking
Will, I saw after replying that there's more detail I missed in your mail, so please see the more detailed reply inline below. On 20 October 2015 at 23:31, Will McCormick <wmccorm...@gmail.com> wrote: > First time user here and new to PostgreSQL and BDR so I hope I have the > right place. You do. > I attempted to issues a TRUNCATE TABLE without the cascade option on a > Parent table that had a child FK constraint. I've looked at your logs, and it looks like the TRUNCATE suceeded on the node that was doing the DDL and it was queued for replication. Then, when applying to another node, it failed because there was a foreign key relationship referencing the target table. This is odd, because the way BDR captures TRUNCATEs should prevent that from happening. It uses triggers to capture TRUNCATES and enqueues them for execution. However, I can see upon inspection that the approach used just isn't sufficient to handle FK relationships, and that the current test suite doesn't cover this. I'm going to write a test to confirm what I think is going on, then follow up. -- 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 - DDL Locking
What's the *exact* BDR version? When you say you "attempted to" - what was the outcome? Presumably an ERROR from the TRUNCATE, right? That would roll back the transaction, and in the process abort the DDL lock acquisition attempt. Are you sure replication was working normally prior to this point, with no issues? The global DDL lock isn't a true lock in the sense that it appears in pg_locks, etc. If you roll back the transaction trying to acquire it, or terminate the PostgreSQL backend attempting to acquire it - such as your TRUNCATE - using pg_terminate_backend(...) then it will be removed automatically. If for any reason that is not the case (which it shouldn't be) then restarting the nodes will clear it. -- 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: no free replication state could be found
On 10 October 2015 at 02:53, Selim Tuvi <st...@ilm.com> wrote: > node: deliver_sing (the problem node): > > postgres=# SELECT * FROM pg_catalog.pg_replication_identifier; > riident | riname > -+ >1 | bdr_6197393155020108291_1_47458_16385_ >2 | bdr_6199712740068695651_1_16385_16385_ >3 | bdr_6197393155020108291_1_47458_17167_ >4 | bdr_6199712740068695651_1_16385_17167_ >5 | bdr_6199712740068695651_1_18817_17951_ >6 | bdr_6197393155020108291_1_48609_17951_ >7 | bdr_6197393155020108291_1_48609_19685_ >8 | bdr_6199712740068695651_1_18817_19685_ > (8 rows) > On 9 October 2015 at 06:54, Selim Tuvi <st...@ilm.com> wrote: >> "recovered replication state of node 6 to 0/59F35A8","" >> "no free replication state could be found, increase >> max_replication_slots","" The number of supported replication identifiers (in bdr 9.4) is controlled by max_replication_slots, hence the error message. This should be documented; I'll amend the docs appropriately. https://github.com/2ndQuadrant/bdr/issues/133 The identifiers aren't currently dropped during node part, which should be changed. It hasn't come up to date because frequent node addition and removal is something to be avoided, and because most deployments configure room for more slots than needed to avoid future restarts. https://github.com/2ndQuadrant/bdr/issues/134 -- 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 workers exiting?
BDR is currently memory-limited for extremely large transactions. At a guess, I'd say one of your big tables is large enough that the logical decoding facility BDR uses can't keep track of the transaction properly. There's no hard limit, it depends on details of the transaction and a number of other variables, but "many tens or hundreds of GB" is generally too much. If I was to load such a big DB, I'd probably do it with ETL tools that could split up the load and do it progressively. -- 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: no free replication state could be found
On 9 October 2015 at 06:54, Selim Tuvi <st...@ilm.com> wrote: > Hi I am testing BDR functionality with Postgres 9.4. I had went through the > bdrdemo example with a 3 node cluster and then tried to set up my own db. > > My "max_replication_slots" is set to 6. After getting removing the bdrdemo > db I am having trouble starting up the postgres instance unless I increase > the value of "max_replication_slots". I get the following error in the log: > > "starting up replication identifier with ckpt at 0/28E8250","" > "recovered replication state of node 1 to 0/54DDCD0","" > "recovered replication state of node 2 to 0/1ECBEA0","" > "recovered replication state of node 3 to 0/59FB1C0","" > "recovered replication state of node 4 to 0/2AA5320","" > "recovered replication state of node 5 to 0/27F2F98","" > "recovered replication state of node 6 to 0/59F35A8","" > "no free replication state could be found, increase > max_replication_slots","" > > pg_replication_slots is only reporting two slots: > > postgres=# SELECT * FROM pg_catalog.pg_replication_slots; > slot_name| plugin | slot_type | datoid | > database | active | xmin | catalog_xmin | restart_lsn > -++---++--++--+--+- > bdr_19685_6199712740068695651_1_18817__ | bdr| logical | 19685 | > deliver | t | | 2280 | 0/28EA5E0 > bdr_19685_6197393155020108291_1_48609__ | bdr| logical | 19685 | > deliver | t | | 2280 | 0/28EA5E0 > > How can I get rid of the stale node recovery on startup? Can you show the output of select * from pg_replication_identifiers; please? On all nodes. Also pg_catalog.pg_replication_slots on the other nodes. -- 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 Rejoin of failed node, hangs.
On 5 October 2015 at 20:58, Steve Pribyl <spri...@akunacapital.com> wrote:
> Clean up node 1.
>select bdr.bdr_part_by_node_names('{node2}');
>delete from bdr.bdr_nodes where node_status='k';
You need to delete the bdr.bdr_connections entry too.
0.9.3 will fix that, so orphan connections entries and those
associated with terminated nodes are ignored.
--
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: cannot drop database even after parting the node
On 17 September 2015 at 06:15, Florin Andrei <flo...@andrei.myip.org> wrote:
> Then, from node1, I've parted node2 like this:
>
> SELECT bdr.bdr_part_by_node_names('{node2}');
>
> And then also on node1 I've parted node1 like this:
>
> SELECT bdr.bdr_part_by_node_names('{node1}');
The second step is not necessary. In fact we should detect that case
and ERROR, since it doesn't make sense to issue bdr_part_by_node_names
from a node that's already left, it can't have any effect.
> Now I want to start over with a clean slate, so I want to drop the bdrdemo
> database on node1.
That's because it's still a live BDR instance, just with one node.
> But I can't:
>
> postgres=# DROP DATABASE bdrdemo;
> ERROR: database "bdrdemo" is being accessed by other users
> DETAIL: There is 1 other session using the database.
> postgres=# SELECT pid FROM pg_stat_activity where pid <> pg_backend_pid();
> pid
> ---
> 10259
> 10260
> (2 rows)
>
> # ps ax | grep -e 10259 -e 10260 | grep -v grep
> 10259 ?Ss 0:00 postgres: bgworker: bdr supervisor
> 10260 ?Ss 0:00 postgres: bgworker: bdr db: bdrdemo
>
> If I kill those workers and then drop the database, the workers get
> respawned, and then the logs fill up with complaints from the workers that
> they can't find the bdrdemo database.
>
> Is there a way to stop BDR completely, so that those workers are laid to
> rest and never respawn?
You've made a good point. We address shutdown and removal on some
nodes, but not the case where you want to shut down and remove BDR on
a single remaining node.
I've been meaning to write a helper function for this for some time,
but other priorities keep intervening.
Here's the manual process:
BEGIN;
SET LOCAL bdr.skip_ddl_locking = on;
SET LOCAL bdr.permit_unsafe_ddl_commands = on;
SET LOCAL bdr.skip_ddl_replication = on;
SECURITY LABEL FOR bdr ON DATABASE mydb IS NULL;
DELETE FROM bdr.bdr_connections;
DELETE FROM bdr.bdr_nodes;
SELECT bdr.bdr_connections_changed();
COMMIT;
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = current_database() AND application_name LIKE '%): perdb';
DROP EXTENSION bdr;
... then remove 'bdr' from shared_preload_libraries .
> Basically, how do I reset BDR completely? It seems to retain the memory of
> the bdrdemo database somewhere.
Sort-of. What happens in your example is that when you part the nodes,
they're separated and stop communicating. So your second part command
never reaches the remaining node. That's expected and normal, but we
should issue an error when it's attempted to make it clearer to the
user what's going on.
--
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 truncate and replication sets
On 17 September 2015 at 15:17, Sylvain MARECHAL <marechal.sylva...@gmail.com> wrote: > Le 15/09/2015 18:56, Alvaro Herrera a écrit : >> >> Sylvain MARECHAL wrote: >>> >>> [...] The exception is with TRUNCATE: In case it is called, data is >>> removed on >>> both nodes. >>> >>> Is it a feature or a bug? >> >> I think it's an oversight. Replication sets were added later than the >> TRUNCATE trigger, so the design for the latter does not consider the >> former as far as I know. > > Ok. May I fill a bug report? Please. It's clearly a bug and will need to be addressed in the next point release. -- 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 problem
On 12 September 2015 at 05:21, Charles Lynch <charleslynchpostgre...@gmail.com> wrote: > We have, just recently, ran into a problem. I created a test cluster only > within NV and after about a week of working without any problems, we got an > error: Unexpected EOF on SSL connection. I had seen something like this > before but on initial cluster join and chalked it up to me doing something > wrong. That's generally network level, though it could also occur if a worker exits unexpectedly. > This was after a week of working without issue. I wasn't sure what to > do next. restarting the database started producing errors like this: > > LOG: starting background worker process "bdr > (6188205071755053119,1,16385,)->bdr (6188203625564571611,1," > FATAL: mismatch in worker state, got 3, expected 1 That's ... very odd. It's violating a sanity check that shouldn't really ever be triggered. How exactly did you restart the database? Can you send more info on your configuration via direct mail to me? > This would repeat. So I removed this node from the cluster using the proper > bdr commands and tried re-joining You can't just re-join a removed node. Once it's removed it's removed for ever. You have to drop the database (or re-initdb), create a new blank database, and join it as a new node. The reason for this is that when you remove the node the replication slots on other nodes get dropped, so there's no record of what catchup work needs to be done. It's not really possible to resync the node with the rest after that. That's the point of node removal, to free the resources from those slots when a node is retired, otherwise you'd just switch it off. > My problem is I don't know what caused this and, more importantly, I'm not > sure how to fix it / prevent it and I can't launch this into production > without figuring this out. The "mismatch in worker state" is strongly likely to be a bug. The trick will be figuring out how you triggered it. Did you retain the malfunctioning cluster, or have you deleted it? > One other thing: I've seen a lot of conflicting information on how to setup > BDR on ubuntu (using ppas, what pkg to install, and where to get source) I'm > curious now if I don't have a younger version and that this issue is all but > fixed now. Here are my build steps if anyone has any comments on how to > setup bdr better, please let me know. You should use the apt respository referenced by http://bdr-project.org/docs/stable/installation-packages.html#INSTALLATION-PACKAGES-DEBIAN . Support is focused mainly on RHEL/CentOS/Fedora, but Debian/Ubuntu packages are also produced. We're a little behind at the moment and haven't got 0.9.2 packages out. I'll be pushing 0.9.3 soon and will produce 0.9.3 packages for Debian/Ubuntu as well as for Fedora/RHEL/CentOS. -- 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 global sequences in two machine failover
On 7 September 2015 at 20:56, Giovanni Maruzzelli <gmar...@gmail.com> wrote: > If I do not use global sequences, and I use uuid as primary keys, would BDR > be a correct choice? For something like a VoIP service where eventual consistency is usually OK and geographic redundancy with latency tolerance and partition tolerance is needed, yes, it could make a lot of sense. You could use UUID keys or use normal sequences with different offsets on the nodes. UUID will probably be easier to manage. -- 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 admin role
On 7 September 2015 at 20:34, Ray Stell <ste...@vt.edu> wrote: > > > On 9/6/15 10:55 PM, Craig Ringer wrote: >> >> On 4 September 2015 at 21:46, Ray Stell <ste...@vt.edu> wrote: >>>>>> >>>>>> FATAL: role "postgresql" does not exist >>>>> >>>>> It works if I init with "-U postgresql" >>> >>> The demo works fine if >>> I use the string "postgresql" as the admin role. It looks like you might >>> want to build -U support into bdr at some point. >> >> It's not at all clear to me how this is happening. The string >> "postgresql" isn't hardcoded by BDR anywhere that it could conceivably >> be passed as a username for a connection string, so I'm not sure >> what's going on. >> >> I've never used "postgresql" as the default superuser name. Typically >> I use "postgres". >> >> I don't see anything particularly informative in the main PostgreSQL >> sources that could explain it either. We pass "postgresql" as the >> PGSQL_PAM_SERVICE and use it as the default RADIUS identifier, but I >> fail to see how those could get passed as the login role identifier. >> >> Are you running it under a unix user named "postgresql", by any chance? > > Yes. Hm, ok. So somewhere a connection string lacks a 'user=' entry, and libpq is defaulting to the operating system user name, which doesn't exist as database user. I don't see this in my tests. Can you confirm the exact BDR version you're testing/using? "git rev-parse --short HEAD" and "git branch" please. -- 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 admin role
> $ git rev-parse --short HEAD > 6a60690 > > $ git branch > * bdr-pg/REL9_4_STABLE OK, that's PostgreSQL. What about the BDR extension its self? SELECT bdr.bdr_version() will show you if you're starting up OK, otherwise again the git rev please. -- 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 admin role
On 4 September 2015 at 21:46, Ray Stell <ste...@vt.edu> wrote: >>>> FATAL: role "postgresql" does not exist >>> >>> It works if I init with "-U postgresql" > > The demo works fine if > I use the string "postgresql" as the admin role. It looks like you might > want to build -U support into bdr at some point. It's not at all clear to me how this is happening. The string "postgresql" isn't hardcoded by BDR anywhere that it could conceivably be passed as a username for a connection string, so I'm not sure what's going on. I've never used "postgresql" as the default superuser name. Typically I use "postgres". I don't see anything particularly informative in the main PostgreSQL sources that could explain it either. We pass "postgresql" as the PGSQL_PAM_SERVICE and use it as the default RADIUS identifier, but I fail to see how those could get passed as the login role identifier. Are you running it under a unix user named "postgresql", by any chance? -- 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 global sequences in two machine failover
On 7 September 2015 at 00:18, Giovanni Maruzzelli <gmar...@gmail.com> wrote: > Hello, > > Typical HA situation. > > I have master-master, two only machines, one active and one passive > (standby) with floating IP. > I write to only one machine at time, the one with the floating IP. This is a deployment that is better suited to the typical approach with an active node, a standby streaming replica, and failover. Tools like repmgr help with this. > When one machine is down I can no more refill sequence allocated chunk (eg: > next pool of values)... Global sequence allocation requires a quorum of half the nodes plus one. So in a 2-node system that means both nodes. > How do you deal with this? Don't use a 2-node multi-master asynchronous replication system as an active/standby failover system. (BTW, newer BDR versions allow you to increase the preallocated chunk size, but that's just kicking the ball down the road a bit). > Seems that BDR global sequences will not be good for master-master failover. It's fine with more nodes. You have bigger worries, though, due to the *asynchronous* nature of the replication. You don't know if the peer node(s) have received all the changes from the master that failed. Not only that, but if it comes back online later, it'll replay those changes, and they might get discarded if more recent updates have since been applied to those rows, resulting in lost updates. See the documentation on multi-master conflicts and last-update-wins. This is very good behaviour for append-mostly applications, apps that are designed to work well with last-update-wins resolution, etc. It's really not what you want for some apps, though, and is extremely bad for a few workloads like apps that try to generate gapless sequences using counter tables. You *must* review the application if you're going to deploy it against a BDR system ... or any other asynchronous replication based solution. You can't just deploy a multi-master system like this and treat it as a single node. The very design choices that make it tolerant of latency and network partitions also means you have to think much more about how the application interacts with the system. With normal streaming replication you can make it synchronous, so there's no such concern. Or you can use it asynchronously, and accept that you'll lose some transactions, but you'll at least know (if you monitor replica lag) how big a time window you lose, and on failover you'll be making the decision to discard those transactions. There are no multi-master conflicts to be concerned with, and failover becomes a simple (albeit painful) known quantity. > So, when you consumed the preallocated chunk (default to 15000 values), your > surviving machine will no more be able to insert into a table with a serial > column with underlying BDR global sequence. Correct. If you don't mind being tied to a fixed limit on the number of nodes you can instead use step/offset local sequences. > We're back to changing the start and increment of each sequence that underly > the "serial" field in each table. > And must do so differently for each node (only two in a master-master > failover). Correct. > Is there any workaround? Keep it simple. Use streaming replication and a hot standby. > For "traditional" (non BDR) serial, there is a way to set into configuration > what will be START and INCREMENT of all sequences? No. > Or each serial sequence must be individually ALTERed for each serial column > in each table? Yes. -- 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 admin role
Please show your connection strings, and the contents of bdr.bdr_nodes and bdr.bdr_connections. On 3 September 2015 at 05:00, Ray Stell <ste...@vt.edu> wrote: > This doc specifies to initdb with the admin user "postgres," > http://bdr-project.org/docs/stable/quickstart-instances.html > but if I do that the supervisor falls over with: > $ cat bdr5598.log > LOG: registering background worker "bdr supervisor" > LOG: database system was shut down at 2015-09-02 16:04:45 EDT > LOG: starting up replication identifier with ckpt at 0/171EBF8 > LOG: MultiXact member wraparound protections are now enabled > LOG: starting background worker process "bdr supervisor" > LOG: autovacuum launcher started > LOG: database system is ready to accept connections > LOG: Created database bdr_supervisordb (oid=16384) during BDR startup > LOG: worker process: bdr supervisor (PID 21666) exited with exit code 1 > FATAL: role "postgresql" does not exist > LOG: starting background worker process "bdr supervisor" > > It works if I init with "-U postgresql" > > > -- > 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 -- 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] Installation of BDR from source
On 3 September 2015 at 21:18, Ray Stell <ste...@vt.edu> wrote: > This doc: http://bdr-project.org/docs/stable/installation-source.html > > at section: 3.3.3. Installation of BDR from source > > needs to have a "make install" added here: > > cd /path/to/bdr-plugin-source/ > PATH=/path/to/install:"$PATH" ./configure > make -j4 -s all Thanks, now fixed in the devel tree. -- 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] FDW and BDR
On 2 September 2015 at 20:40, Andres Freund <and...@anarazel.de> wrote: > On 2015-09-02 20:27:40 +0800, Craig Ringer wrote: >> The reason for this is that BDR replicates at a database level, but >> CREATE SERVER and CREATE USER MAPPING are global, affecting all >> databases on a PostgreSQL install. BDR can't therefore guarantee to >> replicate CREATE SERVER to other nodes, since it might get run on a >> non-BDR-enabled database. > > As Tom explained thats not the case for either of those two. To my > knowledge the only reason those two commands aren't implemented is that > either nobody implemented the required ddl deparsing or, actually > somewhat likely, nobody removed the error check. Either way it should be > simple to implement. Well, that's embarrassing. Whoops. I could've sworn CREATE SERVER was global. Thanks Tom and Andres for clearing up the misinformation. -- 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 setup - bdr_node_join_wait_for_ready()
On 2 September 2015 at 00:12, cchee-ob <carter.c...@objectbrains.com> wrote: > Craig, > > By an existing database, I mean I have a database that has our data on it. > I have created the btree_gist and bdr extensions and ran the > bdr.bdr_group_create () function and the bdr.bdr_node_join_wait_for_ready(), > all run successfully. After setting up the next BDR node, and after running > bdr.bdr_group_join() function I run the bdr.bdr_node_join_wait_for_ready() > function it just sits there and nothing returns. It doesn't look like it's > replicating data either. As I said above, please check the PostgreSQL logs on both nodes. That should generally be more informative. Also please show the output of select * from bdr.bdr_nodes from both nodes. The database for the 2nd node you were joining should've been completely blank before you tried to join it. Was it? Try CREATE DATABASE ... TEMPLATE template0; to make sure. > What is the best practice for creating a BDR environment with 2 BDR nodes > and 1 UDR node? At this time UDR and BDR don't mix especially well. It's something we're working to address, but is lower priority than getting more of the underlying functionality into PostgreSQL 9.6 and streamlining the user experience. -- 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] FDW and BDR
On 2 September 2015 at 18:46, Willy-Bas Loos <willy...@gmail.com> wrote: > Sorry, forgot [GENERAL] in the subject at first. You don't need to add it. The list manager software does that. > I've read that CREATE FOREIGN DATA WRAPPER currently is prohibited on BDR > enabled databases. And other FDW and FTS related commands too. Correct. The reason for this is that BDR replicates at a database level, but CREATE SERVER and CREATE USER MAPPING are global, affecting all databases on a PostgreSQL install. BDR can't therefore guarantee to replicate CREATE SERVER to other nodes, since it might get run on a non-BDR-enabled database. If you CREATE FOREIGN TABLE it'll succeed (since the foreign server exists locally) but might then fail on remote nodes. We've already committed it locally, though. This will cause a stop-the-world replication halt until the administrator intervenes by creating the foreign server on the other nodes. It's made even worse by the impact of the global DDL lock that's held until the CREATE FOREIGN TABLE commits on the other nodes. To prevent that, we reject CREATE FOREIGN TABLE. Supporting this requires one of: - Global DDL replication support in BDR. This is quite hard to do and is not currently supported. - 2-phase transaction DDL replication in BDR. Logical decoding would have to support decoding prepared transactions for this, and it doesn't currently. So it's 9.6 at the very soonest, and we have other higher priorities for this. - Detecting global dependencies in a statement and checking that all peer nodes have those dependencies, then locking them before allowing the DDL to commit locally. This requires nontrivial statement-specific support and a bunch of extensions to how BDR nodes communicate. We have higher priorities, like getting more of the BDR foundations into 9.6 and meeting the needs of customers actively using BDR. So at this point there's no set time frame on support for FDWs. Basically, PostgreSQL having global objects when we replicate on a per-database basis is messy. It creates issues with users/roles, too. > This seems obvious, but i want to make sure: > Does that mean that FDW's are not supported at all in databases that use > Bi-Directional Replication? Correct at this time. You can have FDWs in a different database on the same Pg install, but not use FDWs in a BDR-enabled database. > (maybe one could create the FDW before configuring replication) No. It's possible to override the filter using documented settings, but I don't advise doing so unless you're extremely sure you need this, and understand exactly what you're getting into. If you break it, you get to keep the pieces. -- 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 setup - bdr_node_join_wait_for_ready()
On 28 August 2015 at 00:35, cchee-ob <carter.c...@objectbrains.com> wrote: > What exactly does bdr_node_wait_for_ready() do? I've been trying to setup > BDR on an existing database and once I run the select > bdr.bdr_node_wait_for_ready(); command on the 2nd node after running the > bdr.bdr_group_join() command it just hangs . I've done the same setup > procedure on brand new nodes and have no issue with this command and BDR > works fine. Is there anything that can be done to insure this command > executes properly on an existing database? Check the PostgreSQL logs on the node(s). The wait for ready function does just that - it waits. If the node never joins properly and never reaches the ready state, it'll wait indefinitely. There is presumably an issue with the node joining. To learn more you'll need to take a look at the log files. We cannot, unfortunately, have bdr_node_join wait until the join completes, because it's necessary to co-ordinate with background workers, commit multiple transactions, etc. It's not clear from your description what you mean by "on an existing database". Details? -- 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: cannot remove node from group
On 26 August 2015 at 07:19, Florin Andrei flo...@andrei.myip.org wrote: What do I need to do to start over? I want to delete all traces of the BDR configuration I've done so far. you need to DROP the database you removed, then re-create it as a new empty database. You cannot re-join a node that has been removed. -- 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] vs pgpool-II v3
On 13 August 2015 at 23:52, Wayne E. Seguin wayneeseg...@gmail.com wrote: The context of this is using BDR to implement a HA solution where we have one node getting all connections at a time, if the node fails we move all connections to another node. (eg. only one node gets all connections at any given time). This sounds like a job better suited to a normal active/standby configuration with regular built-in streaming replication. Use a tool like repmgr to manage failover and a proxy like pgbouncer to redirect traffic. There's no reason to use async multi-master replication when simple single-master replication will do just as well. -- 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] Is BDR support distributed table on slave nodes with ACID and join support.
On 17 July 2015 at 19:37, Amit Bondwal bondwal.a...@gmail.com wrote: Thank you very much all of you for quick response and clearing my mind. Please reply to the mailing list, not just to me directly. One more question, can I run a databse of postgres 9.4 on postgres-XL cluster? postgres-XL is based on PostgreSQL 9.2, so no. Postgres-XL doesn't run on PostgreSQL. It's a modified (forked) version of PostgreSQL. Or you can advise me any better solution. Not without knowing a lot more about what you're trying to do, what your requirements are, etc. As per my knowledge partitioning of a table have some limits in nos. and I can't extend more storage in a server upto a limit. Yes, that's true, but those limits are quite large, and you may be trying to solve a problem you don't and won't have. This may well be premature optimisation. I can't know without a lot more info. Rather than starting with the solution (horizontal partitioning, sharding) try starting with the problem and requirements, then looking for solutions from there. -- 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] Is BDR support distributed table on slave nodes with ACID and join support.
On 17 July 2015 at 15:02, Amit Bondwal bondwal.a...@gmail.com wrote: I looked at pg_shard, as per my understanding it is for nosql data, in our case we are going to use postgresql RDBMS features without nosql, pg_shard have lot on limitation as per our case. You'll find that _most_ products have a lot of limitations when doing things like horizontal scaling. BDR is no exception either. 1. If start with postgresql 9.4 database, later can I upgrade it to BDR with these already large table, can I distribute these tables or shard them. You need a patched PostgreSQL 9.4 if you're going to use BDR because it relies on a bunch of functionality that didn't make it into the 9.4 release. (Some didn't make it into 9.5 either). That patched PostgreSQL can be used like normal PostgreSQL 9.4, but it's not 100% on-disk compatible. You have to do a dump and reload to convert to or from stock community PostgreSQL, you can't pg_upgrade or otherwise convert in-place. You can run normal PostgreSQL applications on a standalone database on the BDR-patched PostgreSQL 9.4 by just not installing the BDR extension in the database. You can then activate BDR later. Be aware, though, that BDR imposes quite a few limitations on what the app can do when it comes to DDL. If you activate BDR later, you might find that things you were expecting to work and that worked fine before stop working. I'd strongly suggest planning to deploy with BDR from the start if you're going to use it at all. 2. how can I distribute some of tables on my slave nodes, if it have any limitations please let me know. BDR doesn't support sharding. A table can be on all nodes or - using replication sets - some subset of nodes, but it's the whole table or none of it. You can partition the table and shard the partitions using replication sets, but then each node will only see a subset of the data. There's no transparent cross-node querying. You could try to combine foreign data wrappers and inheritance with partitioning, but by then you're playing database jenga and things will come toppling down very, very hard. 3. if I started with 4 or 5 node cluster with one master and rest of slave nodes, how can I take backup of this distributed database to one of my other server or backup location. pg_dump Please suggest me what would be the best approch for this. From your description it sounds like BDR is not particularly suitable for your use case at this time. We're looking at adding sharding down the track, but it's quite a way down the track because there's a fair bit of work on making sure the core functionality is rock solid and easy to manage. -- 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] Is BDR support distributed table on slave nodes with ACID and join support.
On 17 July 2015 at 17:33, Dorian Hoxha dorian.ho...@gmail.com wrote: 1,2,3: You can't shard with BDR. It's only for multimaster (at least for now). Please read the docs. You can kind-of shard using table partitioning, but the application has to deal with shard integration because there's no cross-node querying. So in practice it's no benefit over a bunch of standalone databases. -- 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] Removing and readding bdr nodes
On 17 May 2015 at 04:14, Mathew Moon mathew.m...@vipaar.com wrote: Hi, Wanted to bump this (Is that ok in this list?). Like I mentioned, I see now that the error I am seeing in the log possibly indicates that the node I am reinitializing is creating a slot that points to its own old sysid. Checking manually I see that this slot does in fact exist. I still have no idea why it is creating this incorrect slot and trying to connect to it. If anyone has any insight into this it would be greatly appreciated. I fixed a bug just after the removal of 0.9.2 that is the most likely explanation for this. BDR wasn't correctly ignoring bdr.bdr_connections entries for 'k'illed nodes when joining a new node. Also, does anyone have experience using repmgr for streaming replication (Log shipping standby)? If so what do you think about it? 2ndQuadrant maintains repmgr. I think it's a decent tool for the job. Is it a good tool or is there something better for the same use case that repmgr fulfills. It's moderately easy to just do it by hand if you don't want to use repmgr. I haven't looked for alternative tools for this use case. -- 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: Can a node live alone after being detached
On 26 June 2015 at 04:59, Sylvain MARECHAL marechal.sylva...@gmail.com wrote:
You shouldn't part a node from its self. The next revision will
prevent this with an error.
Ok, this was not clear for me.
Or anyone else, hence the coming docs and code changes.
Sorry to bother again about that, but what about the detached node cleanup
best practice?
Suppose I have 2 nodes, 'node1' and 'node2'. So I am OK to call
bdr_part_by_node_names('node2') on node1 to detach node2. But then, is it
safe to call bdr_part_by_node_names('node1') to do the cleanup on node2, or
should I better call pg_drop_replication_slot('...') instead?
BDR 0.9.1 drops the slot on remaining nodes correctly on node part, so
no further action is required there. Just part_by_node_names the node
you are removing, running the query from one of the nodes you wish to
retain. (0.9.1 enforces this).
If you want to retain the node you're removing as a standalone, it
should be sufficient to drop any slots on that node, truncate
bdr.bdr_nodes and bdr.bdr_connections, and remove the bdr security
label with:
SECURITY LABEL FOR bdr ON DATABASE thedb IS NULL;
then restart the DB. You may also wish to remove 'bdr' from
'shared_preload_libraries'.
--
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] Issues setting up BDR with multiple databases
On 19 June 2015 at 08:09, Jorge Torralba jorge.torra...@gmail.com wrote: Could not add local node to cluster, status PGRES_FATAL_ERROR: ERROR: node identity for local dsn does not match current node DETAIL: The dsn 'dbname=jorge host=10.5.101.179' connects to a node with identity (6161869759719318325,2,16389) but the local node is (6161869759719318325,2,16385) Interesting - it looks like it's connecting to the wrong DB at this step. You've also posted this as a github issue, and I'm following up there rather than on the mailing list. Lets keep the discussion in one place. https://github.com/2ndQuadrant/bdr/issues/88 -- 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] Issues trying to run bdr_init_copy with new setup
On 17 June 2015 at 07:49, Jorge Torralba jorge.torra...@gmail.com wrote: Just started playing with BDR. Originally setup the environment on two separate servers as per the quick start guid and used the sql commands to add nodes. Moving on to command line, I am running into some issues. This has also been opened by Jorge as a github issue; see https://github.com/2ndQuadrant/bdr/issues/88 https://github.com/2ndQuadrant/bdr/issues/89 so I'm following up there to avoid splitting the discussion. Jorge, if you post the same thing multiple places, please link between them! It saves considerable time and hassle. -- 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: Can a node live alone after being detached
On 16 June 2015 at 18:40, Sylvain MARECHAL marechal.sylva...@gmail.com wrote:
Nothing special in the logs here. What I would like to emphasize is that a
single node can not live alone.
Yes, that's right.
Single node BDR, i.e. running with BDR enabled but no peers, would
be nice to support for testing and robustness. It's just not a key
priority at this point.
-- Now detach the group, in the hope to create some table
-- But this does not work. DDL are still forbidden
test=# select bdr.bdr_part_by_node_names('{node1}');
You shouldn't part a node from its self. The next revision will
prevent this with an error.
Ok, this was not clear for me.
Or anyone else, hence the coming docs and code changes.
Ok, I will do this as a workaround.
But having a function doing the detach() properly would be really nice.
We have a very long list of would be nices, but have to focus on
things that are core requirements for functionality or for specific
customer needs. At this point this comes under neither category, so I
don't anticipate working on it soon. If you're interested in getting
into the codebase it would be an interesting and manageable project,
though...
Of course, in a real production scenario, the user won't probably change
often its configuration, but I think this is really useful to have this kind
of flexibility when taking control of the application.
Absolutely. The trouble is that all such things have trade-offs.
For example, with the ability to re-attach a node that you asked
about, doing so can't be done without accumulating lots of upstream
WAL. It'd be effectively identical to just shutting down the node then
starting it up again - with all the same costs and downsides.
--
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: Can a node live alone after being detached
On 15 June 2015 at 17:19, Sylvain MARECHAL marechal.sylva...@gmail.com wrote:
Is it possible to completely detach a node so that it can live alone
Yes. On a different node to the one you want to remove,
bdr.bdr_part_by_node_names(ARRAY['the_node_to_remove']) to safely
remove it without disrupting the other nodes.
in particular that DDL are again possible on that node?
DDL is possible on a BDR node anyway, just with some limitations.
I tried with a simple node without success:
postgres=# create database test template template0;
CREATE DATABASE
postgres=# \c test
You are now connected to database test as user dbadmin.
test=# CREATE EXTENSION btree_gist;
CREATE EXTENSION
test=# CREATE EXTENSION bdr;
CREATE EXTENSION
-- DDL are still possible
test=# create table before_node_creation (i int primary key not null);
CREATE TABLE
-- Now create the group. After that, DDL are now forbidden
test=# SELECT bdr.bdr_group_create(local_node_name :=
'node1',node_external_dsn := 'host=172.27.118.64 port=5432 dbname=test');
test=# create table after_node_creation (i int primary key not null);
ERROR: No peer nodes or peer node count unknown, cannot acquire DDL lock
HINT: BDR is probably still starting up, wait a while
Well, DDL is only forbidden because it hasn't successfully joined yet.
Check the PostgreSQL logs to see what it's doing.
-- Now detach the group, in the hope to create some table
-- But this does not work. DDL are still forbidden
test=# select bdr.bdr_part_by_node_names('{node1}');
You shouldn't part a node from its self. The next revision will
prevent this with an error.
test=# create table after_node_creation (i int primary key not null);
ERROR: No peer nodes or peer node count unknown, cannot acquire DDL lock
HINT: BDR is probably still starting up, wait a while
Currently, once detached, the BDR extension isn't disabled on the node.
Support for that is possible, but not yet implemented. We need to add
a two-phase part protocol where the node confirms it has left the
system and disables BDR. At present removed nodes aren't really a
focus; it's expected that you're removing the node because you're
going to retire it and will be deleting the cluster or dropping the
database.
In the mean time you can remove the security label on the database to
disable BDR once the node has been parted, so that BDR no longer
activates on that node. The command filter prevents this so you'll
have to do this with the command filter off.
BEGIN;
SET LOCAL bdr.permit_unsafe_ddl_commands = true;
SET LOCAL bdr.skip_ddl_locking = true;
security label for 'bdr' on database bdr_testdb is '{bdr: false}';
COMMIT;
Out of interest, why do you want to detach a node and keep using it as
a standalone DB?
--
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: DDL lock problem with function inside a schema
Questions: --- -Is it a known bug? If yes should I report it (and where)? Please open a report on github for this - https://github.com/2ndQuadrant/bdr/issues -How to recover such DDL lock problems operation without recreating the database? In other words, what is the proper way to cleanly restart both nodes? Restarting both nodes should be sufficient, and I am surprised to see that is not the case here. This needs more investigation. -Is it a well known method to properly detach and attach nodes? I'm not sure I understand. If you mean join and remove nodes, join with bdr.bdr_group_join, remove with bdr.bdr_part_by_node_names . -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
