3 ms·

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 CREA

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;

manigandham 10y ago
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.
- anarazel 10y ago
  > 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.
  - manigandham 10y ago
    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.

manigandham 10y ago

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.

anarazel 10y ago
> 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.
- manigandham 10y ago
  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.