PostgreSQL 10 had added built-in logical replication which tackles some of the limitations of physical replication and opens up the possibility of promising new areas of replication. In this webinar, we introduce the concept of logical replication and demonstrate how one can configure a logical replication in no time.
Highlights include:
- Basic architecture, including the publisher and subscriber model
- Configuration, administration, and monitoring
- Limitations and future plans
Webinar video:
Webinar slides:
Webinar Q&A:
- What could become handy is the possibilty to modify data on the fly - say by adding/changing a value to mark the publisher (if fetching from many pubs). what about this or adding or changing columns?
- You can create triggers on the subscriber.
- How can one use logical replication by upgrading EDB major release from 10 to 12?
- Yes, assuming you make sure to replicate everything.
- Is wal retained on the publisher until all updates are published to the subscriber?
- Yes. An offline subscriber can cause retention of WAL.
- If the subscriber server is down for the weekend, what happens with the wal on the publisher?
- It says there until it is consumed by the subscriber.
- Will the wal be retained until the subscriber consumes it, potentially running out of storage on the publisher? How about performance on a high load cluster and monitoring the replication lag?
- Yes, it will be retained. Not sure about performance overhread.
- What about PostGIS tables including geometry columns?
- Sure, no problem, as long as PostGIS is installed on the subscriber too.
- Does Logical Replication support multiple subscribers? What about sequences?
- Yes. It does not replicate sequence tables, but does replicated tables that use sequences.
- Is it difficult to convert two servers from streaming replication to logical replication?
- Uh, yes, because you need to replicate each object.
- Is it difficult to convert a server from version 9.6 to version 12.1?
- No, pg_upgrade makes that easy.
- How can you tell monitory replication status based on table base?
- Tablespace? Not sure why you would want that.
- Conversion of the data type from publisher to subscriber - does it support data-masking or it can be done on subscriber cluster OR how we can define data conversion?
- You might be able to do that with triggers on the subscriber.
- Can operations be performed on the replicated data before it is written at the replicate side?
- Perhaps using triggers on the subscriber.
- Do I need the same major versions for logical replication?
- PG 10+ can replicate to each other major version.
- Can we replicate based on a query rather than having an entire table?
- No.
- How is access to the published data is controlled? Is it published for everybody with superuser in subscriber database? It seems to be able to create the subscription - I didn't understand how the published data is protected from unallowed access.
- I think it is super-user only and you have to enable replication in pg_hba.conf for each host.
- Hello, can we use streaming replication as full failover and logical replication for dedicated data from one master server at the same time?
- Yes.
- Since Logical Replication is WAL based, does DDL also replicates to the Subscribers?
- No.
- Is bidirectional Replication possible? How about writable subscribers?
- It is only possible with 3rd party products like EDB Replication Server.
- Can we add columns to the subscriber that will be populated by the replication process? such as adding a datetimestamp when the row was replicated?
- Uh, you can add triggers to the subscriber.
- Can logical rep support a table row logging, so the replication side only does inserts, no deletes?
- Yes, he showed you can control what is replicated.
- Can the Logical side have SHARD tables?
- I don’t know what shard tables are.
- Is there a cloud service for logical replication for non-dba guys?
- Yes, EDB has a product that is cloud-based. I don’t know what logical replication features it supports.
- What about conflicts/errors in replication when subscribing from multiple publishers?
- You usually don’t replicate from multiple publishers into the same table, but into perhaps a partition tree, so there should be no conflicts.
- Can I created Index manually on logical replication side?
- Yes, indexes are independent of the publisher.
- Does two-way replications exists?
- Yes, in EDB Replicateion Server, and other 3rd party products.
- What if the subscription database is down? Does replication slot send the records when database is up?
- Yes.
- Tables without primary key are not candidate for logical replication, correct?
- Well, there are several modes, one is primary key, or you can specify a unique index, or you can ask it to compare each old row. This was covered.
- In logical replication can we replicate only certain columns?
- No.
- I use streaming replication to have a standby up to date, that's why logical replication doesn't work for me, right?
- Yes, you can’t write to a binary standby since it must remain identical to the master.
- How are column defaults handled when defined on both publisher & subscriber or if defined only on subscriber?
- The defaults are set on the publisher and the values just copied to the subscriber.