Hacker News Reader

Comment by manigandham | Hacker News Reader

We use MemSQL and it has the best replication setup process for any relational database with 1 line:

  REPLICATE DATABASE db_name FROM master_user[:master_password]@master_host[:master_port][/master_db_name]

Why is it in 2017 we still don't have any other database that can come close to this? Basic replication is very well understood and used everywhere but it seems like database creators just don't understand what should be prioritized.

View on HN

The stuff in postgres 10 isn't that much different (for logical replication). On the master you do

  CREATE PUBLICATION all_tables FOR ALL TABLES;
  or
  CREATE PUBLICATION important_tables FOR TABLE transactions, users;

and then on the node you want to replicate to

  CREATE SUBSCRIPTION backup_important CONNECTION 'dbname=foo host=bar user=repuser' PUBLICATION important_tables;

What about editing the postgresql.conf, pg_hba.conf, recovery.conf files, setting up archiving and doing the first base backup transfer? That's the actual hard part.

Logical replication doesn't replicate DDL commands either from what I know so far so that's another major problem.

> What about editing the postgresql.conf, pg_hba.conf, recovery.conf files, setting up archiving and doing the first base backup transfer? That's the actual hard part.

I talked about logical replication here... But the defaults for those are going to be a lot saner too in 10. We're not going to expose postgres to the world by default, so you'll have to change listen_address - but I think everything else would be a really bad deal security wise.

> setting up archiving

You don't really need that anymore in the more basic cases. Unless you want/need point-in-time recovery to older states, replication slots make that a lot easier.

> doing the first base backup transfer?

    psql -h master "SELECT pg_create_physical_replication_slot('my_standby');
    pg_basebackup --write-recovery-conf --slot my_standby -D targetdirectory

I'm not saying things are perfect, but they're clearly improving. And some of the complexity (e.g. not allowing remote connections by default) are pretty basic security vs. simplicity tradeoffs.

Default remote access off is perfectly fine, we don't need the mongo style security problems.

However the rest just seems to be a overbuilt mess. Why not start simple with barely and options and work up from there instead of trying to build in so many different configurations and features?

90% of use cases would be satisfied with a simple async replica on a different server started with any user that has read access to that database and a single command, along with the ability to become a standalone master anytime.

Redis (yes its a different database) has another nice and easy setup with a simple "slaveof" command.

When you issue that command in MemSQL, what does it actually do?

Physical replication in PostgreSQL is a bit more work to set up, but its behaviour is rather straightforward. As far as I understand it, all it really does is the same as a standalone database, except that instead of generating WAL segments in response to user queries that are then applied to the database, it gets them by streaming them from the master.

It's easy to see how the system behaves when nodes fail.

Similarly, a PITR is achieved by telling PostgreSQL to (optionally) retrieve WAL at startup from an archive and apply it until the desired point. The mechanism is identical to streaming replication, except non-continuous.

Referring to your comment below, postgresql.conf, pg_hba.conf and recovery.conf are data that can't really exist in the database, because it's needed before the database can even start up.

With logical replication in PostgreSQL 10, you could feasibly implement a command identical to MemSQL's, but the problem is that its behaviour is no longer quite so straightforward to understand compared to physical replication.

It does everything. There's nothing else to configure. MemSQL is a distributed SQL database so a "cluster" is multiple machines supporting a single logical interface to multiple databases.

Another cluster in another datacenter made up of multiple machines can then issue this command which will automatically contact the master cluster, setup connections in parallel between several machines, create the replica database, ask the master to create a new snapshot, stream the snapshot over, build the database, then subscribe to the change log stream including data + schema changes, all async and very fast.

Yes, this is most commonly referred to as "physical replication" but it covers about 90% of use cases where a database needs a replica somewhere else.

Postgres is great but too often the features keep getting blown out into ridiculous complexity instead of just making the basics work well and without tweaking anything, then building from with optional configuration for greater functionality.

I was actually asking what it actually does, not what it does conceptually. My main issue with these advanced "easy" solutions is that while they're easy to setup, often that comes at the cost of missing understanding of behaviour under load / failure scenarios.

I'm not saying PostgreSQL couldn't improve (it definitely can), but when it comes to data, I advocate simplicity over ease.

Very quick reading gives me the impression that MemSQL's replication is actually per-partition logical replication (might be physical, if partitions are physically separate databases, but they don't seem to be) and uses an algorithm (didn't find which) to decide which node to failover to when something occurs. It also seems to differentiate between metadata nodes and storage/worker nodes.

Reply on news.ycombinator.com