News for hackers. Stuff that matters.
Re:No More Full Table Vacuums in PostgreSQL by dvdplm on Friday March 18, 2016 @08:37AM (#11311321)
Continuously blown away with the quality and usefulness of Postgres. It is truly lightyears ahead of MySQL and I keep wondering wth took me so long to switch. Kudos.
Re:No More Full Table Vacuums in PostgreSQL by eknkc on Friday March 18, 2016 @08:47AM (#11311376)
What I don't like about postgres is that it's really hard to have a decent replication / failover setup manually.

Meanwhile, it seems like AWS RDS have more development on MySQL and their own MySQL compatible engine (aurora?). At least they have Postgres RDS though. Google Cloud does not offer any hosted Postgres solutions. Cloud SQL is all MySQL.

I love Postgres but I really don't like maintaining rdbms installations.

What is my best bet running pg on google cloud with ha and minimun hassle?

Re:No More Full Table Vacuums in PostgreSQL by BenoitP on Friday March 18, 2016 @09:31AM (#11311633)
One can hope that this will get fixed over time. 9.4 introduced some flexibility over streaming out data changes from the Write Ahead Log, from [1]:

> Add support for logical decoding of WAL data, to allow database changes to be streamed out in a customizable format.

Also 9.5 added new metadata to it [2]:

> Each WAL record now carries information about the modified relation and (s) in a standardized format. That makes it easier to write tools that need that information, like pg_rewind, prefetching the blocks to speed up recovery, etc.

Hopefully we will see the ecosystem pick up on those changes. Percona made a killing in helping replication take place in MySQL. I don't see why this would not happen with postgres.

I'm confident things will improve.

[1] http://www.postgresql.org/docs/9.4/static/release-9-4.html

[2] http://michael.otacoo.com/postgresql-2/postgres-9-5-feature-...

Re:No More Full Table Vacuums in PostgreSQL by ninkendo on Friday March 18, 2016 @10:02AM (#11311814)
> Each WAL record now carries information about the modified relation and (s) in a standardized format. That makes it easier to write tools that need that information, like pg_rewind, prefetching the blocks to speed up recovery, etc.

Maybe only tangentially related, but they also made enhancements to pg_receivexlog that make it acknowledge transactions over the wire.

It may not seem like a big deal, but with those changes I was pretty trivially able to patch a version of pg_receivexlog that replicates WALs to HDFS and flushes them (instead of the local FS), so that you can essentially treat HDFS nodes as synchronous standbys. Incredibly useful for setups where your only non-ephemeral storage is HDFS, and the 16MB chunk boundary imposed by archive_command is too course-grained.

I'd imagine similar things can be written for lots of other non-posix filesystems that support appending and flushing... Being able to replicate to something that's not just another plain old server is really useful (not to mention how much simpler it is than having to set up an entire other Postgres node just to synchronously ship logs.)

Re:No More Full Table Vacuums in PostgreSQL by daigoba66 on Friday March 18, 2016 @09:53AM (#11311760)
> What I don't like about postgres is that it's really hard to have a decent replication / failover setup manually.

This is one reason I personally have a hard time moving away from MSSQL (despite the obscene licensing). Clustering, replication, and failover of MSSQL on Windows is really quite powerful. Though not necessarily cheap or easy to setup and troubleshoot, it is wonderful when it works.

Re:No More Full Table Vacuums in PostgreSQL by eyepulp on Friday March 18, 2016 @09:53AM (#11311759)
We dislike managing PG too, so we've been migrating to https://www.compose.io/postgresql/ It's not for every situation, but they have replication and failover and downloadable daily backups with the option for SSH-only access. Been using them for about 6 months on some smaller DB projects, and haven't had any hiccups.
Re:No More Full Table Vacuums in PostgreSQL by lololomg on Friday March 18, 2016 @09:32PM (#11316729)
They say that they'll happily host 3TB databases, but the cost is too high for databases larger than a few GB.

1TB (with only 100GB of RAM) will set you back $150k a year!

For comparison we pay our generic service provider about 20% of that to manage 2 redundant dedicated servers with 24/7 monitoring and support.

Re:No More Full Table Vacuums in PostgreSQL by lobster_johnson on Friday March 18, 2016 @10:15AM (#11311896)
Compose runs on AWS, though. OP was asking about Google Cloud.
Re:No More Full Table Vacuums in PostgreSQL by brightball on Friday March 18, 2016 @09:41AM (#11311697)
AWS is investing more into MySQL because of market and the fact that MySQL is an interface to numerous database engines. It's significantly easier for them to built something like Aurora with MySQL compatibility because of that setup.

Don't get me wrong here, PostgreSQL is light years better but that's the big "why".

Re:No More Full Table Vacuums in PostgreSQL by busterarm on Friday March 18, 2016 @12:14PM (#11312729)
Write support for the SQL/MED standard was added in 9.3.

Postgres now has a variety of foreign data wrappers available and can serve that exact same function.

Re:No More Full Table Vacuums in PostgreSQL by pjlegato on Friday March 18, 2016 @10:55AM (#11312170)
My company, Databaase Labs (https://www.databaselabs.io/), runs fully managed Postgres DBaaS and now has Google Cloud as a deploy target in beta.

Mail me if you'd like to be in the beta - [email protected].

Re:No More Full Table Vacuums in PostgreSQL by mostafah on Friday March 18, 2016 @09:25AM (#11311598)
Heroku has a decent offer: https://www.heroku.com/postgres.

Another option is intoGres: https://www.intogres.com.

Re:No More Full Table Vacuums in PostgreSQL by lobster_johnson on Friday March 18, 2016 @10:14AM (#11311889)
Those aren't for Google Cloud, though. Heroku is hosted on AWS, and intoGres has their own cloud. You don't want to incur the latency of talking to a database from a different cloud.
Re:No More Full Table Vacuums in PostgreSQL by brightball on Friday March 18, 2016 @09:40AM (#11311685)
Hadn't heard of intoGres. Thanks for that.
Re:No More Full Table Vacuums in PostgreSQL by lobster_johnson on Friday March 18, 2016 @10:06AM (#11311844)
There's ElephantSQL (https://www.elephantsql.com) and Aiven (https://aiven.io/postgresql.html).

However, they are super expensive. Considerably more than running yourself, and considerably more than CloudSQL or RDS on AWS.

Their plans are so small it seems that they're not really made for running anything big. Aiven's biggest plan is 3 nodes, each with 8 cores and up to 32GB RAM.

Elephant's most expensive plan is 1 node with 4 cores and 15GB RAM. Not sure if you just buy multiple of these. They have replication support, but I don't see anything about pricing, and they don't have automatic failover.

Re:No More Full Table Vacuums in PostgreSQL by brianwawok on Friday March 18, 2016 @08:53AM (#11311418)
Usually better off just running Google cloud SQL, because for many workloads the benefits of hosted FT outweigh the gains of Postgres over MySQL.

Now if you need Postgres specific features... gets more tricky. Setting up FT is terribad.

Re:No More Full Table Vacuums in PostgreSQL by taneliv on Friday March 18, 2016 @11:04AM (#11312226)
Some ex-colleagues just started offering what you want, I think. I am sure they'd appreciate feedback even if it isn't: https://aiven.io (disclaimer: haven't tried it myself for lack of a use case)
Re:No More Full Table Vacuums in PostgreSQL by cookiecaper on Friday March 18, 2016 @02:41PM (#11313899)
I've managed some significant MySQL replication clusters for the last couple of years. They are a nightmare, frequently stopping for dumb errors and/or falling completely out of sync. It's only manageable because of Percona's tools.

While PostgreSQL replication may be a bit clunkier on the initial setup, it's a dream comparatively.

Re:No More Full Table Vacuums in PostgreSQL by jhoechtl on Friday March 18, 2016 @08:59AM (#11311457)
What about a dockerized Postgres? https://hub.docker.com/_/postgres/
Re:No More Full Table Vacuums in PostgreSQL by creshal on Friday March 18, 2016 @09:22AM (#11311578)
Throwing something into a container does not magically make it have replication nor failover.
Re:No More Full Table Vacuums in PostgreSQL by techdragon on Friday March 18, 2016 @09:26AM (#11311609)
It actually makes your life harder.
Re:No More Full Table Vacuums in PostgreSQL by dcgoss on Friday March 18, 2016 @10:46AM (#11312111)
Could you elaborate on this? I was considering using containers for a PG installation but am reevaluating.
Re:No More Full Table Vacuums in PostgreSQL by takeda on Friday March 18, 2016 @05:57PM (#11315507)
What are you trying to solve by doing this? Are you planning to run other applications on the same host. Generally it's not a good idea to share host running database with other applications due to different workloads.

If you would want to put multiple databases on one host, probably more efficient would be still to put all data in single instance. I could see this if it's a very small database, then this could work, but then wouldn't you be better off with using SQLite?

In additions when you add containers to the mix you turn a single problem into many, some examples:

- assuming you have multiple hosts, you need to figure out where you'll store persistent data (and you generally want a solution with high IOPS) - how you handle logging (where you store them?) - how the applications figure out where your database is (service discovery) - how you solve replication (and figure out which database is the master) - how you handle failover

Re:No More Full Table Vacuums in PostgreSQL by dcgoss on Friday March 18, 2016 @09:17PM (#11316685)
I was considering deploying Postgres (as a container) into a Kubernetes cluster. Kubernetes takes care of service discovery, networking, persistent disk mounting, moving the container if a server goes down, etc.

Wouldn't you have similar problems with Postgres without the container?

Re:No More Full Table Vacuums in PostgreSQL by takeda on Monday March 21, 2016 @03:51PM (#11331289)
I don't know kubernetes, but looks like it solves many of those issues already. I still believe the HA might be a challenge.

Typically to make the database highly available you might set up a second one (or perhaps more) that replicates from the master. This can become problematic if the postgres containers will be moved around.

As I said, I don't know kubernetes, if you for example can have containers that have state (e.g. you destroy and recreate it somewhere else, and they are exactly same) and also keep the same IP then this is not an issue, but if when you move it around and each instance is technically a new postgres, then such setup might become problematic.

Regarding your question, the traditional way of running it is that you set up a host and run postgres on it. It doesn't move around so you need those solutions. Granted that for example if you implement service discovery for example if something happens to a host, you can set up another and quickly point everything to it.

Re:No More Full Table Vacuums in PostgreSQL by X86BSD on Friday March 18, 2016 @12:00PM (#11312646)
Well on FreeBSD you can toss it into a jail with iocage and get snapshots, clones, easy HA with vrrp or CARP. Not replication per se. But for a lot of people that works just fine and is incredibly easy to automate and accomplish.
Re:No More Full Table Vacuums in PostgreSQL by geofft on Friday March 18, 2016 @01:08PM (#11313183)
How does this get you HA (easy or otherwise) without application-level configuration? Does this involve something like lockstep execution between the two servers?

I'd expect that if you run two Postgres servers, unaware of each other, at the same IP address you will rapidly get data corruption, but maybe I'm missing a step since you say this works fine for a lot of people.

Re:No More Full Table Vacuums in PostgreSQL by creshal on Friday March 18, 2016 @02:52PM (#11313974)
I'd guesstimate it involves shipping snapshots to a cold standby, since snapshotting/cloning is mentioned.
Re:No More Full Table Vacuums in PostgreSQL by jhoechtl on Friday March 18, 2016 @08:58AM (#11311451)
If at least temporal table support would be on the TODO list

Temporal tables: https://wiki.postgresql.org/images/6/64/Fosdem20150130Postgr...

TODO: https://wiki.postgresql.org/wiki/Todo

Re:No More Full Table Vacuums in PostgreSQL by phonon on Friday March 18, 2016 @10:26AM (#11311963)
Re:No More Full Table Vacuums in PostgreSQL by emodendroket on Friday March 18, 2016 @09:47AM (#11311721)
I don't get to use psql much anymore but the documentation is among the best I've ever seen for any project.