[GENERAL] hstore/jsonb support in hibernate/JPA
Hi all! I saw that Rails 4 comes with hstore support out of the box. Does anyone know if there’s any integrated support like that in hibernate or any other JPA implementation? I know your able to write your own custom datatypes, but I’m looking for integrated standardized support in the base library. Kind regards, Martin -- 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] Petition: Treat #!... shebangs as comments
+1 Skickat från min iPhone 18 jul 2014 kl. 17:58 skrev Adrian Klaver adrian.kla...@aklaver.com: On 07/18/2014 08:52 AM, Karsten Hilbert wrote: On Fri, Jul 18, 2014 at 08:32:53AM -0700, Adrian Klaver wrote: I think the OP is talking about executable scripts so both of $ psql -f the-file.sql and $ ./the-file.sql (where the-file.sql starts with #!/usr/bin/env psql) would work given that the-file.sql has got execute permission. Yea, it finally dawned on me what was being asked, so ignore my previous post. Karsten -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Petition: Treat #!... shebangs as comments
18 jul 2014 kl. 17:31 skrev Dennis Jenkins dennis.jenkins...@gmail.com: On Fri, Jul 18, 2014 at 10:16 AM, Andrew Pennebaker andrew.penneba...@gmail.com wrote: Could we please have the PostgreSQL lexer treat #!... on the first line of a file as a comment? This would enable .psql scripts to be run with dot-slash notation preferred by many unix users: ./script.psql While still allowing the traditional (and Windows compatible) style: psql -f script.psql +1 +1, Sounds great! Even though you can accomplish most things in other ways, this seems like the easiest in many scenarios.
Re: [GENERAL] Bi-Directional replication client awareness
I think we (as in postgres) will probably get the ability to run individual transactions in such a mode, but you surely wouldn't want to run every transaction in it. So, would then these transactions be ”2-phase”? I mean either all nodes commit the transaction or none of them do? Or only to those in the synchronous_standby_names? Perhaps to early to say? Been playing around with BDR all weekend, really interesting. Greetings, Andres Freund -- Andres Freundhttp://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] Bi-Directional replication client awareness
13 jul 2014 kl. 21:54 skrev Martin Gudmundsson martingudmunds...@gmail.com: I think we (as in postgres) will probably get the ability to run individual transactions in such a mode, but you surely wouldn't want to run every transaction in it. So, would then these transactions be ”2-phase”? Sorry, should be 2-safe, not 2-phase I mean either all nodes commit the transaction or none of them do? Or only to those in the synchronous_standby_names? Perhaps to early to say? Been playing around with BDR all weekend, really interesting. Greetings, Andres Freund -- Andres Freund 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
[GENERAL] BDR: Strange values in pg_stat_replication
Hi! I wanted to test synchronous bi-dircetional replication using synchronous_standby_names with bdr. So I set this up as follows: Node alpha has the following settings in postgresql.conf port=5432 wal_level = 'logical' max_replication_slots = 3 max_wal_senders = 4 synchronous_standby_names=’beta' shared_preload_libraries = 'bdr' bdr.connections='beta' bdr.beta_dsn = ’dbname=bdrdemo username=postgres port=5433' track_commit_timestamp = on Node beta has the following settings in postgresql.conf port=5433 wal_level = 'logical' max_replication_slots = 3 max_wal_senders = 4 synchronous_standby_names=’alpha' shared_preload_libraries = 'bdr' bdr.connections=’alpha' bdr.alpha_dsn = ’dbname=bdrdemo username=postgres port=5432' track_commit_timestamp = on§1 bdr.alpha_init_replica = on bdr.alpha_replica_local_dsn = 'dbname=bdrdemo user=postgres port=5432’ It seems to work fine. Bringing down one node stalls the other one, just like synch rep should. But the view pg_stat_replication view shows async in sync_state column. Shouldn’t this really be sync? Kind regards, Martin -- 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: Strange values in pg_stat_replication
Hi! Never mind this one. Wrong config of synchronous replication by me. It looks good once I got the replication setup correctly. Kind regards, Martin 13 jul 2014 kl. 23:10 skrev Martin Gudmundsson martingudmunds...@gmail.com: Hi! I wanted to test synchronous bi-dircetional replication using synchronous_standby_names with bdr. So I set this up as follows: Node alpha has the following settings in postgresql.conf port=5432 wal_level = 'logical' max_replication_slots = 3 max_wal_senders = 4 synchronous_standby_names=’beta' shared_preload_libraries = 'bdr' bdr.connections='beta' bdr.beta_dsn = ’dbname=bdrdemo username=postgres port=5433' track_commit_timestamp = on Node beta has the following settings in postgresql.conf port=5433 wal_level = 'logical' max_replication_slots = 3 max_wal_senders = 4 synchronous_standby_names=’alpha' shared_preload_libraries = 'bdr' bdr.connections=’alpha' bdr.alpha_dsn = ’dbname=bdrdemo username=postgres port=5432' track_commit_timestamp = on§ 1 bdr.alpha_init_replica = on bdr.alpha_replica_local_dsn = 'dbname=bdrdemo user=postgres port=5432’ It seems to work fine. Bringing down one node stalls the other one, just like synch rep should. But the view pg_stat_replication view shows async in sync_state column. Shouldn’t this really be sync? Kind regards, Martin -- 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] Bi-Directional replication client awareness
12 jul 2014 kl. 04:58 skrev Craig Ringer cr...@2ndquadrant.com: On 07/12/2014 02:42 AM, Martin Gudmundsson wrote: Hi all! I was wondering if there are any specific load balancing/failover functionality planned for client drivers connection to a BDR group. In my case the jdbc driver, but could be relevant for other drivers as well. PgJDBC actually already supports rudimentary client-based failover. I’ve seen some mailing list threads regarding this but was that ever implemented? Do you know where it’s documented? It's not very smart - it doesn't maintain any persistent state, so if a host just vanishes it'll take a long time to connect to the next one as it has to wait for a TCP connection timeout. Making it stateful would be interesting, and may be more useful now that there's more reason to bother with client-side failover. Making it reliable in the face of classloader isolation, load/unload, etc will be interesting, but it's a problem we eventually need to solve anyway if we're to support asynchronous notification callbacks. AFAIK libpq doesn't have any kind of failover - but if we added something similar, it'd be transparent to drivers like psycopg2, the Pg gem, etc that use libpq. I'm not sure how we'd go about making it stateful though - API changes would likely be needed for that bit. psqlODBC would also need significant changes. Or is the long term plan that we need we need to rely on middleware like pgpool or other third party frameworks for this? At this point PgBouncer will likely be the way to go if you want to try to make it client transparent, but I don't think that's a good idea. Because BDR is asynchronous multi-master _replication_ though, clients are expected to be aware of some of the anomalies that can occur. A naïve client that just picked a random BDR server and did the next transaction on it would be very likely to cause unwanted replication anomalies, apply conflicts, etc. For example, if the client inserted a row on one server then tried to immediately update it on another, the update would likely fail because the row probably hasn't replicated yet. Any ideas giving BDR an option to be synchronous. I mean in ”close quarters” with low latency it should work ok. It is generally a good idea to make clients sticky to a given server, but clients also need to be aware of replication anomalies unless each server's data is a self-contained shard that doesn't interact with the others. In which case you probably wouldn't be using BDR Great explanation. Making them sticky i most likely the most reasonable approach in our case. But it would be good to have a place to list of servers to try on startup and then stick to one. Perhaps the rudimentary support you mention above would do it? Having client awareness of the nodes ip, port numbers, status and current load, could probably bring powerful features to this as a whole. That is some how other database vendors take care of client failover and load balancing and has in my experience proved to work very well. Yes, that'd certainly be interesting. And not only for BDR, but also streaming replication setups, could use this to do automatic client failovers, in the case there is a server side failover. Anyone who knows if there is anything in progress regarding this? Other than the limited failover already in place for PgJDBC I'm not aware of anything. Work on its design, implementation and testing would be greatly appreciated, so polish up your C skills. Phew, C oh C, that was a while a go :-) Thanks for your detailed answer. Kind regards, Martin -- 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] Bi-Directional replication client awareness
Because BDR is asynchronous multi-master _replication_ though, clients are expected to be aware of some of the anomalies that can occur. A naïve client that just picked a random BDR server and did the next transaction on it would be very likely to cause unwanted replication anomalies, apply conflicts, etc. For example, if the client inserted a row on one server then tried to immediately update it on another, the update would likely fail because the row probably hasn't replicated yet. Any ideas giving BDR an option to be synchronous. I mean in ”close quarters” with low latency it should work ok. Just to clarify, I don't mean a 48 node synchronous group, but perhaps allow one synchronous ”neighbor” node. That way we could get two synch writeable nodes in one datacenter(or 2 datacenter really close to each other. common in Europe), and can be combined with asynch nodes in more distant datacenters. That would enable load balancing between 2 nodes for all type of queries. Synchrounous Streaming Replication is supported with BDR, but that only gives support for a read-only replica. It does not give a fully writeable node. Figuring out what queries are readonly to direct to a readonly replica, is not the easiest thing, if I’ve understood documentation for pgpool etc correct. It is generally a good idea to make clients sticky to a given server, but clients also need to be aware of replication anomalies unless each server's data is a self-contained shard that doesn't interact with the others. In which case you probably wouldn't be using BDR
Re: [GENERAL] Bi-Directional replication client awareness
12 jul 2014 kl. 13:45 skrev Andres Freund and...@2ndquadrant.com: On 2014-07-12 13:23:08 +0200, Martin Gudmundsson wrote: 12 jul 2014 kl. 12:33 skrev Andres Freund and...@2ndquadrant.com: On 2014-07-12 18:22:30 +0800, Craig Ringer wrote: On 07/12/2014 02:24 PM, Martin Gudmundsson wrote: Any ideas giving BDR an option to be synchronous. I mean in ”close quarters” with low latency it should work ok. BDR doesn't do synchronous replication _yet_, but it's on the roadmap. Just to clarify: There's two things that sometimes are referred to as 'synchronous' in the context of multimaster replication. Just like with HS' builtin synchronous replication it can mean that the client doesn't get a reply until the COMMIT has safely been replicated to other systems. That's supported by BDR today. Would that be to a streaming replication synchronous standby? I.e replicated to a read only node? It's possible to do it to a streaming replication sync standby, but also to another BDR node. The logical decoding facility added in 9.4 allows logical replication solutions to use the same mechanism as streaming rep does. Sounds interesting, but it does not sound like it’s being bi-directional in that case? Is it just a standard streaming synchronous replication you setup for that? Ideally I’m looking for a solution where I can run an application and it does not really matter on what node a transaction executes, or if it changes data or not. With BDR in it’s current state, I need to look out for replication lag. That is what I was looking for a solution to when I mentioned synchronous bdr in the beginning. Don’t get me wrong, BDR can solve a lot of problems for us in it’s current state. You’ve done a tremendous job on this. But some scenarios are tricky. Greetings, Andres Freund -- Andres Freundhttp://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] Bi-Directional replication client awareness
I’ve seen some mailing list threads regarding this but was that ever implemented? Do you know where it’s documented? I don't think the patch ever got accompanying documentation. It's not mentioned here, anyway: http://jdbc.postgresql.org/documentation/93/connect.html Failover URLs are of the form: jdbc:postgresql://host1:port1,host2:port2/dbnjame with the port being optional. I'll document that now, before I get caught up in working on something else. In case you're looking, the code was added by git bddc05f939ac9227b682e85d1ba0a9b902da814c . Great! Will look have a look at the code. Any ideas giving BDR an option to be synchronous. I mean in ”close quarters” with low latency it should work ok. BDR doesn't do synchronous replication _yet_, but it's on the roadmap. Right now BDR as a whole is in its early days; the feature is being shaped by feedback and the requirements of sponsors and contributors. It's changing quite quickly, which is one good reason most of it is implemented as a plugin to PostgreSQL. Synchronous replication is one of a number of things that the team has looked into but hasn't prioritized implementing yet. Out of interest, what sorts of problems are you interested in solving using BDR? What needs are you trying to meet? Are you looking to use it for scaling? For HA? For data protection/redundancy? For transparent distributed databases (client/node locality)? I'm curious about what part you see synchronous replication support for BDR playing in any deployment you're contemplating. One thing I’m thinking about a possible future replacement for other database vendors solutions. Solutions like DB2 DataSharing on z/OS and DB2 PureScale on Linux. They would be somewhat comparable to a synch multimaster setup, but of course only works in low latency environments. But they provide online upgrades. Perfromed one node at a time(only z/OS), and every node is equal. But they have central locking facilities, which creates other problems of course. But the applications would not need to care about if the transactions have been applied or not. The nodes are always in synch. Scaling is also something that has been on my mind for some apps that has intense write activity at times. BDR gives us that but currently we would need to watch out for potential situations where the data might not have been replicated to all nodes. Online scaling up and down a write intense environment without needing to worry if transactions have been applied or not, would be neat. But I know it would put constraints on the infrastructure, and possibly also not give us complete HA protection in case of a site failure, since the latency needs to be kept low. But if there could be a mix of synch and async nodes in a BDR group, it could cover more HA. Hope that explains a bit more what I’m thinking about. Currently I’m just looking at what BDR could give us here, so it’s nice to understand your plans. No real projects ongoing currently. Thanks for taking your time answering my questions. Best regards, Martin -- 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] Bi-Directional replication client awareness
12 jul 2014 kl. 14:48 skrev Andres Freund and...@2ndquadrant.com: On 2014-07-12 14:37:02 +0200, Martin Gudmundsson wrote: It's possible to do it to a streaming replication sync standby, but also to another BDR node. The logical decoding facility added in 9.4 allows logical replication solutions to use the same mechanism as streaming rep does. Sounds interesting, but it does not sound like it’s being bi-directional in that case? It is bidirectional if you configure a bdr node in synchronous_standby_names. OK, I will play around with this a bit. Thanks for the hint. Note that streaming replication's synchronous mode probably doesn't give the guarantees you're thinking it does: a) A sent commit might succeed locally on the primary, but the system might fail to send it to the synchronous standby. I.e. postgres' synchronous replication of normal commits is *not* 2-safe. b) Even if a transaction is replicated to the standby it's *not* guaranteed to be applied. All that's guaranteed is that it has been shipped to the synchronous standby Very good info! I’m not sure that is so clear in the documentation but I have to revisit it. Ideally I’m looking for a solution where I can run an application and it does not really matter on what node a transaction executes, or if it changes data or not. I'd like that too, but I don't think that's going to happen. There's just too many caveats (CAP theorem et al) to make that generally useful/applicable. We are currently using solutions that works like that, DB2 Datasharing on z/OS is one example. But it’s not built around replication, but global filesystems and central locking facilities. That approach also have it’s issues, with performance bottlenecks etc. I think we (as in postgres) will probably get the ability to run individual transactions in such a mode, but you surely wouldn't want to run every transaction in it. That sounds like o good trade off. Looking forward to it. Greetings, Andres Freund -- Andres Freundhttp://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] Bi-Directional replication client awareness
12 jul 2014 kl. 12:33 skrev Andres Freund and...@2ndquadrant.com: On 2014-07-12 18:22:30 +0800, Craig Ringer wrote: On 07/12/2014 02:24 PM, Martin Gudmundsson wrote: Any ideas giving BDR an option to be synchronous. I mean in ”close quarters” with low latency it should work ok. BDR doesn't do synchronous replication _yet_, but it's on the roadmap. Just to clarify: There's two things that sometimes are referred to as 'synchronous' in the context of multimaster replication. Just like with HS' builtin synchronous replication it can mean that the client doesn't get a reply until the COMMIT has safely been replicated to other systems. That's supported by BDR today. Would that be to a streaming replication synchronous standby? I.e replicated to a read only node? The more complex thing is support for performing transactions that, when the COMMIT returns successfully, can't have conflicts. That's *not* yet supported, but we're thinking of implementing it for individual transactions. Greetings, Andres Freund -- Andres Freundhttp://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
[GENERAL] Bi-Directional replication client awareness
Hi all! I was wondering if there are any specific load balancing/failover functionality planned for client drivers connection to a BDR group. In my case the jdbc driver, but could be relevant for other drivers as well. Or is the long term plan that we need we need to rely on middleware like pgpool or other third party frameworks for this? The reason I’m asking is because I was reading up on BDR and started thinking about who important the client interfaces also are to be able to take full advantage of these features. Having client awareness of the nodes ip, port numbers, status and current load, could probably bring powerful features to this as a whole. That is some how other database vendors take care of client failover and load balancing and has in my experience proved to work very well. And not only for BDR, but also streaming replication setups, could use this to do automatic client failovers, in the case there is a server side failover. Anyone who knows if there is anything in progress regarding this? Kind regards, Martin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
