3 ms·

Logical replication. Please let that be true. I didn't know live data migration from one database version to another had a name, but I have been waiting for it

by bostik 13y ago

Logical replication. Please let that be true. I didn't know live data migration from one database version to another had a name, but I have been waiting for it for years. (Since 7.3, in fact.) And if I'm interpreting this right, that feature could also allow for a fairly straightforward way to move data from a running instance to a new one. Perhaps not yet in the first release, but eventually that too could work.

jeltz 13y ago
The plans for logical replication and the amount of work put into it seems serious so I would expect logical replication to land sooner or later (my guess is in 9.5, but I may be optimistic here). Several patches of the ground work have already been committed with a bunch of other patches being worked on.
twic 13y ago
> that feature could also allow for a fairly straightforward way to move data from a running instance to a new one That's already relatively straightforward with PostgreSQL's existing replication. This has worked since 9.1: http://www.rassoc.com/gregr/weblog/2013/02/16/zero-to-postgresql-streaming-replication-in-10-mins/ http://www.rassoc.com/gregr/weblog/2013/02/16/zero-to-postgr... It involves bouncing the master, but it doesn't involve extended downtime. Or is that not what you meant? However, the 'logical log streaming replication' that is being implemented looks even better. A small but useful thing is that it works at database granularity, rather than having to replicate the whole installation. The huge thing is that because it works with logical rather than physical representations of changes, it allows replication into a database which is also being updated locally - slaves can be read/write rather than read-only. That itself is not immediately exciting, but the logical consequence of this is that two databases can both accept changes and replicate to each other - 'bi-directional replication', or what in MySQL is called 'multi-master replication'. The holy grail of database clustering!
- bostik 13y ago
  > The holy grail of database clustering! Thank you for pointing this out, I would have missed it.In fact, thank you everyone who has answered with helpful technical details. I have a couple of use-cases in mind where logical streaming replication could solve a number of problems. A personal project would benefit from a database-level setup due to easier data migration, a professional one would benefit from bi-directionality and logically straightforward clustering. (I also have some future designs in mind where replicating only subsets of a database would be VERY useful.) Owing thanks to the replies, I now have some extra reading to do. :)
einhverfr 13y ago
Well, let's be clear about what's being worked on. The logical replication framework as I understand it is a framework for logical replication frameworks, not for the end user/administrator. I.e. it is there to allow some centralized logic between implementations like Bucardo and Slony, rather than be usable directly. Logical replication, if you need it, is not some far-off feature from a sysadmin's perspective but something where there are currently at least three major implementations of it to date, namely Slony, Bucardo, and Londiste. Take your pick, set it up, and use it. These three have very different focuses and design decisions associated with them and so what's good for one environment may not be good for another. So instead of continuing to wait for it, get out and try these solutions. Just to note, the two main uses of logical replication (as opposed to physical replication which is what the streaming replication offers) is: 1. Live migrations between versions. 2. Replicating only a subset of a database (for example, into a system in a DMZ). Every major implementation currently supports both of these approaches, but supporting #2 makes using it for #1 significantly harder. It might be nice to see a framework for #1 be built on top of an existing logical replication framework.

by bostik 13y ago

jeltz 13y ago

The plans for logical replication and the amount of work put into it seems serious so I would expect logical replication to land sooner or later (my guess is in 9.5, but I may be optimistic here). Several patches of the ground work have already been committed with a bunch of other patches being worked on.

twic 13y ago

> that feature could also allow for a fairly straightforward way to move data from a running instance to a new one That's already relatively straightforward with PostgreSQL's existing replication. This has worked since 9.1: http://www.rassoc.com/gregr/weblog/2013/02/16/zero-to-postgresql-streaming-replication-in-10-mins/ http://www.rassoc.com/gregr/weblog/2013/02/16/zero-to-postgr... It involves bouncing the master, but it doesn't involve extended downtime. Or is that not what you meant? However, the 'logical log streaming replication' that is being implemented looks even better. A small but useful thing is that it works at database granularity, rather than having to replicate the whole installation. The huge thing is that because it works with logical rather than physical representations of changes, it allows replication into a database which is also being updated locally - slaves can be read/write rather than read-only. That itself is not immediately exciting, but the logical consequence of this is that two databases can both accept changes and replicate to each other - 'bi-directional replication', or what in MySQL is called 'multi-master replication'. The holy grail of database clustering!

bostik 13y ago
> The holy grail of database clustering! Thank you for pointing this out, I would have missed it.In fact, thank you everyone who has answered with helpful technical details. I have a couple of use-cases in mind where logical streaming replication could solve a number of problems. A personal project would benefit from a database-level setup due to easier data migration, a professional one would benefit from bi-directionality and logically straightforward clustering. (I also have some future designs in mind where replicating only subsets of a database would be VERY useful.) Owing thanks to the replies, I now have some extra reading to do. :)

einhverfr 13y ago

Well, let's be clear about what's being worked on. The logical replication framework as I understand it is a framework for logical replication frameworks, not for the end user/administrator. I.e. it is there to allow some centralized logic between implementations like Bucardo and Slony, rather than be usable directly. Logical replication, if you need it, is not some far-off feature from a sysadmin's perspective but something where there are currently at least three major implementations of it to date, namely Slony, Bucardo, and Londiste. Take your pick, set it up, and use it. These three have very different focuses and design decisions associated with them and so what's good for one environment may not be good for another. So instead of continuing to wait for it, get out and try these solutions. Just to note, the two main uses of logical replication (as opposed to physical replication which is what the streaming replication offers) is: 1. Live migrations between versions. 2. Replicating only a subset of a database (for example, into a system in a DMZ). Every major implementation currently supports both of these approaches, but supporting #2 makes using it for #1 significantly harder. It might be nice to see a framework for #1 be built on top of an existing logical replication framework.