6 ms·

It is done, the PostgreSQL community rocks

167 points by linuxhiker 10y ago

Dowwie 10y ago
Postgresql and its community keep getting better!
digitalzombie 10y 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 10y 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 10y 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 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.
- redwood 10y 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?
  - user5994461 10y ago
    It means you can write to any node.
    - spacemanmatt 10y ago
      That is what people think they want when they do not understand the tradeoffs involved, typically.
      - bsg75 10y ago
        Multi-master works wonderfully until it does not. Then you have a terrible mess of inconsistent data.
      - user5994461 10y ago
        There can't be high availability if there is only ever one node that accepts writes. There can't be sharding if there is only ever one node that performs writes.
        redwood 10y ago
        I'm genuinely curious what you mean here when you say high availability? Do you mean that during a distributed consensus failover timeframe, it is unacceptable for writes to be queued at the client?
        user5994461 10y ago
        It means that the system is still available when one node is dead.
        redwood 10y ago
        When you say that there can't be sharding without more than one one node taking writes, I totally see where you're coming from. And I agree with you. Typically that means that there is a logical isolation between which data goes to which shard so this avoids conflicts too. Usually when people talk about "multimaster" I do not believe they're thinking of this particular scenario but I may be wrong. I'm not familiar but does postgres not shard?
- user5994461 10y 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/ https://www.citusdata.com/ , of course it comes with many limitations and drops half of SQL
  - kod 10y 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.html https://docs.citusdata.com/en/v6.1/reference/sql_workarounds...
    - user5994461 10y ago
      I guess they improved significantly since my last work around it.
- dgregd 10y 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 10y ago
I cant wait to see the videos.
- Tostino 10y 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.
merb 10y ago
PostgreSQL is great, sadly that NOTIFY does not work with replication. Else we could've use PostgreSQL in many more ways :/
- zerg2k 10y 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!
  - merb 10y ago
    well we are a small shop and use notify/listen as a small pubsub server.
kylequest 10y ago
Here's a couple of blog posts covering the conference... Day 0 and 1: https://cloudlock.engineering/pgconf-2017-days-0-1-c897dd90c970 https://cloudlock.engineering/pgconf-2017-days-0-1-c897dd90c... Day 2: https://cloudlock.engineering/pgconf-2017-day-2-8bd7e93404eb https://cloudlock.engineering/pgconf-2017-day-2-8bd7e93404eb Day 3: https://cloudlock.engineering/pgconf-2017-day-3-54e56ad8eaca https://cloudlock.engineering/pgconf-2017-day-3-54e56ad8eaca
arjie 10y 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 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 10y 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 https://github.com/sorintlab/stolon
  - feld 10y 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.
    - snuxoll 10y ago
      Personally I think pgBouncer is a better solution for handling the switch to the correct master during a failure. I use keepalived (VRRP) for managing my HAProxy machines and while it's certainly reliable there's more issues with it that you have to be aware of than simply flipping the connection string in pgBouncer.
tmaly 10y 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?

167 points by linuxhiker 10y ago

Postgresql and its community keep getting better!

> 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 10y 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 10y 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 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.
redwood 10y 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?
- user5994461 10y ago
  It means you can write to any node.
  - spacemanmatt 10y ago
    That is what people think they want when they do not understand the tradeoffs involved, typically.
    - bsg75 10y ago
      Multi-master works wonderfully until it does not. Then you have a terrible mess of inconsistent data.
    - user5994461 10y ago
      There can't be high availability if there is only ever one node that accepts writes. There can't be sharding if there is only ever one node that performs writes.
      - redwood 10y ago
        I'm genuinely curious what you mean here when you say high availability? Do you mean that during a distributed consensus failover timeframe, it is unacceptable for writes to be queued at the client?
        user5994461 10y ago
        It means that the system is still available when one node is dead.
      - redwood 10y ago
        When you say that there can't be sharding without more than one one node taking writes, I totally see where you're coming from. And I agree with you. Typically that means that there is a logical isolation between which data goes to which shard so this avoids conflicts too. Usually when people talk about "multimaster" I do not believe they're thinking of this particular scenario but I may be wrong. I'm not familiar but does postgres not shard?
user5994461 10y 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/ https://www.citusdata.com/ , of course it comes with many limitations and drops half of SQL
- kod 10y 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.html https://docs.citusdata.com/en/v6.1/reference/sql_workarounds...
  - user5994461 10y ago
    I guess they improved significantly since my last work around it.
dgregd 10y 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 10y ago

I cant wait to see the videos.

Tostino 10y 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.

merb 10y ago

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

zerg2k 10y 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!
- merb 10y ago
  well we are a small shop and use notify/listen as a small pubsub server.

kylequest 10y ago

Here's a couple of blog posts covering the conference... Day 0 and 1: https://cloudlock.engineering/pgconf-2017-days-0-1-c897dd90c970 https://cloudlock.engineering/pgconf-2017-days-0-1-c897dd90c... Day 2: https://cloudlock.engineering/pgconf-2017-day-2-8bd7e93404eb https://cloudlock.engineering/pgconf-2017-day-2-8bd7e93404eb Day 3: https://cloudlock.engineering/pgconf-2017-day-3-54e56ad8eaca https://cloudlock.engineering/pgconf-2017-day-3-54e56ad8eaca

arjie 10y 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 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 10y 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 https://github.com/sorintlab/stolon
- feld 10y 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.
  - snuxoll 10y ago
    Personally I think pgBouncer is a better solution for handling the switch to the correct master during a failure. I use keepalived (VRRP) for managing my HAProxy machines and while it's certainly reliable there's more issues with it that you have to be aware of than simply flipping the connection string in pgBouncer.

tmaly 10y 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?