

Heroku releases Followers into General Availability - jorde
https://postgres.heroku.com/blog/past/2012/10/25/announcing_follow/

======
jhuckestein
After heroku's most recent outage, I've been trying to find a way to replicate
my heroku database to a non-heroku server and so far haven't found an easy way
to do this. Has anybody gotten the log streaming to work with a postgres box
that is hosted somewhere else (in my case on Rackspace)?

~~~
fdr
This doesn't work via the hot standby/follower mechanism because one needs
access to a user with the replication role, which Heroku does not provide. The
snag for that is that that stream is architecture, operating system, and
postgres-minor-version specific, and that is why we generally do not feel
inclined to support that as an interface.

Postgres is learning logical replication that doesn't have these restrictions.
I sure hope it can make 9.3 -- there's still a lot of ground and risk to cover
-- and maybe that will deliver hope of what you seek.

In addition, the archives (base backups and archived logs) would also have to
be exposed to deal with clients that may have been disconnected for a
reasonable period of time, and using a direct interface to that at the storage
level is not something I think is very pleasant or maintainable, because I
want the freedom to change our archiving layout as we see fit to enable
disaster recovery/followers as-is. I think converting those archives to
streaming protocol traffic is the way to go, but this doesn't handle the issue
of base-backup propagation, which is a serious blocker.

So, there's a lot of work to do all around...if anyone thinks this general
kind of work is really interesting and perhaps would like to work on it on
Heroku's behalf, he/she might want to consider emailing me at
daniel@heroku.com.

------
garindra
A question : is there any way for my application code to transparently
"recognize" that (at runtime) there is a new follower PG database that is
assigned to the application and that it should route some of the reads to the
new follower DB? Or do I have to manually change my app's db configuration and
include the new follower's address?

Does Postgres actually natively support this and is there any client library
that makes use of that? I know that Zookeeper serves this sort of purpose
(automatic service discovery etc.), but I have no idea whether/how it works
with Postgres.

------
adrianpike
Can someone more versed in the current state of Postgres chime in on something
for me?

> "One use case that has historically been challenging in database management
> is setting up a read replica, often referred to as a read slave."

Having only inherited MySQL stuff in production, this is pretty trivial in
MySQL land - I was under the impression that Postgres also had a solution for
this, with Slony and recently as a part of Postgres core. Is my info wrong on
this?

I thought master/slave replication was a pretty solved problem at this point,
am I wrong?

~~~
hgimenez
Postgres, as of 9.0, has binary log streaming replication built in, truth.
Slony ad other logical replication solutions out there are quite hard to set
up and come with their gotchas.

A rock solid one command (cli)/click (web) is a whole different story though,
making it super easy to form more complex DB topologies and adapting them as
your needs change.

------
aneth4
Can anyone comment on how this affects consistency from a writing client?

For instance, let's say I update a user record with a new email address, then
display then redirect to an action to display the user record. The write may
not have reached the replica, so I may get the old email address, correct? It
seems this might require quite a bit more thought than simply enabling a
replica.

~~~
hgimenez
A little-known feature of Postgres' streaming replication is Synchronous
Replication or 2-safe replication, available from version 9.1 onward. It
allows you to specify that a given commit must be durable on a standby server
prior to the server returning to the client.

You can turn this on cluster wide, or just for single transactions using
session variables. In your example, you can specify that this update must be
synchronously replicated, and as such the consequent request will show the
user the updated data.

Read more about it here: [http://www.postgresql.org/docs/9.1/static/warm-
standby.html#...](http://www.postgresql.org/docs/9.1/static/warm-
standby.html#SYNCHRONOUS-REPLICATION)

~~~
recuter
Wait, so you want to block the response until a cross database RPC completes?
That sounds like it would not be very performant and possibly introduce some
other problems. I'm guessing this is best used sparingly with specific
transactional queries.

~~~
fdr
Also, I think that mode does not yet support waiting for _application_ of the
deltas, only their flushing to disk. That will probably be added some-day, so
this is close but no cigar.

In reality, what one could do now is check the 'snapshot' on the follower and
the leader and figure out if a change has been propagated or not. This is
exposed by the function txid_current_snapshot().

Latency of syncrep is poor, as you suggest, but throughput is about the same
-- not everything blocks on acknowledgements from a standby individually, but
rather standbys report their progress through a totally ordered stream of
changes, and the leader will un-block a "COMMIT" (explicit, or implicit when
not using BEGIN) when it sees that a standby has passed the appropriate
transaction number (this is a small fib, because I think it think it thinks in
terms of a different unit known as WAL Records/XLogPosition, but this is quite
close to the truth).

So, for example, one session may only be able to commit a few times a second
on a high-latency link, but thirty parallel sessions may not commit at 1/30th
the throughput each, but given a case of very small commits closer to 30-times
the throughput in aggregate of one client.

Also, 9.2+ has a notion of 'group commit' where this same dynamic (submit
deltas, wait on a number to pass by) is applied for local writes relative to
the on-file-system crash recovery log, the WAL, even though syncrep to another
machine via network was added in 9.1 (i.e. earlier). This is much better at
numerous small writes than older versions of Postgres, where it is likely that
many backends would issue their own sync requests to disk rather than sharing
one.

------
blissofbeing
This is nice, but it would be even better if we could provision a follower in
another AWS Region or at least another AZ.

~~~
hgimenez
Followers are automatically provisioned on a different AZ than their leaders.

~~~
thibaut_barrere
A very nice complement to my ruby fronts hosted at DotCloud.

I really can't wait to use this!

------
denibertovic
this is just so awesome... I have become quite well versed in setting up
streaming replication by myself (postgres 9.1) and have a good system when i
need to provision one but a one click solution is a whole other level!

------
thibaut_barrere
I can't wait to give them my bucks, once they become Safe Harbor compliant!

------
zimbatm
Are their Postgresql offering running on top of EBS instances ?

~~~
thibaut_barrere
Maybe here (but I didn't investigate closely):

<http://enterprisedb.com/>

