It is done, the PostgreSQL community rocks

http://blog.pgconf.us/2017/04/its-done-postgresql-community-rocks.html

by linuxhiker • 9 years ago

167 36 9 years ago

blog.pgconf.us

Dowwie• 9 years ago

Postgresql and its community keep getting better!

digitalzombie• 9 years ago

We had a record number of attendees, a record number of sponsors and a record number of talks. The conference rocked.

Can we have data on these?

Like how much is a record? It could be 1 person more than previous convention.

Having data we can have a better understanding such as growth rate and such. It also let us appreciate the growth and momentum much better.

eo3x0• 9 years ago

What's the latest in regards to multi-master (production ready) setups in the PostgreSQL world these days? (i.e without manual or application level sharding) Last I checked, the "solutions" that were out there weren't ready for prime time.

rpedela• 9 years ago

They are working hard to get logical replication in for PG 10 (next major release), but there are still a lot of open items: https://wiki.postgresql.org/wiki/PostgreSQL_10_Open_Items

I don't know if logical replication = multi-master but I know it is a major piece of the puzzle.

redwood5• 9 years ago

I'm always curious when people ask about "multi-master" what exsctly they mean and with which tradeoffs. What use case do you envision?

user59944617• 9 years ago

It means you can write to any node.

user59944617• 9 years ago

SQL doesn't work well in a distributed setup. Give up on SQL and it gets easier.

The most popular hack for PostgreSQL is citusdb https://www.citusdata.com/ , of course it comes with many limitations and drops half of SQL

kod• 9 years ago

That's FUD, citus doesn't "drop half of sql". Anything that can be resolved to a single node works exactly like normal postgres, and the main multinode things you want (e.g. aggregations) work transparently. The actual limitations are usually easy to work around.

https://docs.citusdata.com/en/v6.1/reference/sql_workarounds...

dgregd• 9 years ago

What kind of load (number of concurrent app users) your app has so it needs multi-master db?

It is quite easy to setup powerful pg master node (32 CPU threads, fast SSDs) and many slave nodes for read-only queries. That setup can handle quite a lot of transactions.

mooneater• 9 years ago

I cant wait to see the videos.

Tostino• 9 years ago

Agreed, I can't afford the time away to attend the conference, but the videos give a ton of insight, so it's not the worst substitute.

merb2• 9 years ago

PostgreSQL is great, sadly that NOTIFY does not work with replication. Else we could've use PostgreSQL in many more ways :/

zerg2k• 9 years ago

By the way you don't need notify to hook into the Replication stream. It is possible to get access to the changes directly from the jdbc driver for example. Logical Replication is a big deal!

merb2• 9 years ago

well we are a small shop and use notify/listen as a small pubsub server.

kylequest• 9 years ago

Here's a couple of blog posts covering the conference...

Day 0 and 1: https://cloudlock.engineering/pgconf-2017-days-0-1-c897dd90c...

Day 2: https://cloudlock.engineering/pgconf-2017-day-2-8bd7e93404eb

Day 3: https://cloudlock.engineering/pgconf-2017-day-3-54e56ad8eaca

arjie3• 9 years ago

Can I have a two-node cluster of PostgreSQL machines where a failure in the first results in failing over to the second automatically? It's okay if I have to run something manual on the first just to bring that back up to speed and act as the new secondary.

Is that easy to setup (i.e. has a sequence of documented steps to follow)? https://wiki.postgresql.org/wiki/Multimaster says this HA setup is possible but does not describe it and I'm having trouble finding out if this is fully supported or is just a 'and maybe you could do it if you tried this'.

cuu508• 9 years ago

... and client libraries would need connection details for both nodes, so they can find the new master after failover.

That would be awesome, but stock Postgres only provides some of the building blocks.

For past few days I've been investigating stolon [1]. Conceptually, it makes sense to me. And so far it seems to work. But it's not simple to set up and maintain. For example, for leader election it uses etcd or consul. So initially you had one service that must not go down, --now you have two! ;-)

[1] https://github.com/sorintlab/stolon

feld• 9 years ago

No, use anycast or VRRP/CARP for Postgres servers. Use a watchdog script to monitor for master failure. When it fails, the secondary/slave IP becomes live. Watchdog script should take necessary action to make it recognize it is master/writable. Clients don't have to know what happened.

Bringing the master back live and making it primary is a more difficult task. I would not want to automate this anyway as it would be too easy to shoot your foot off.

spacemanmatt2• 9 years ago

My short answer: Yes, but...

My long answer: After years of experience with highly-available database provision with PostgreSQL, I concluded that I don't want automatic failover. It's more practical to keep a master running and constantly replicated by slaves that can be promoted to master in migration or failure situation. However, when there is a failure situation it's so exceptional that someone really needs to understand what happened before allowing the next victim (er, database) to go on stage.

arjie3• 9 years ago

Thanks for the advice. Is there a sane setup guide here with sane defaults for the master-slave replication?

I would rather not discover afterwards that I was supposed to `ulimit` the postgres process to some CPU in order to be able to successfully kill queries that are jamming the box.

snuxoll3• 9 years ago

Can I have a two-node cluster of PostgreSQL machines where a failure in the first results in failing over to the second automatically? It's okay if I have to run something manual on the first just to bring that back up to speed and act as the new secondary.

You can do this with repmgr from 2ndquadrant, see the documentation [0] for details on the configuration. Since repmgrd doesn't require anything other than postgres to be running, it doesn't add any new failure points.

One thing you need to make sure you do right in such a setup is fencing the failed node, pgBouncer or pgPool is a good way to go about this - in your failover script you can modify your configuration on the pgBouncer server to point at the new master before the failover takes place, this will prevent clients from talking to the failed master and prevent a split-brain.

Alternatively, you can use something like keepalived and STONITH, but I'm not comfortable relying on somehow shutting down or cutting off the other machine from the network as it is less reliable than modifying a configuration file if things are going really wrong.

[0] https://github.com/2ndQuadrant/repmgr

arjie3• 9 years ago

Ah ha, thank you for your advice and summary of the current state of things. It looks like I shouldn't be attempting what I said.

tmaly• 9 years ago

I love using Postgresql, one of the best pieces of software out there.

Does anyone know if there videos of the conference available and if the are all listed in a central page?

It is done, the PostgreSQL community rocks

Related Stories