pglogical | Logical Replication extension for PostgreSQL | Change Data Capture library

I am trying to upgrade Amazon Aurora PostgreSQL from verion 9.6 to 10.14 via CloudFormation template command aws cloudformation update-stack --stack-name base --template-body file://cloudformation/base.yml but every time update is getting rollback with one of the below messages:

1. Unmodifiable DB Parameter: wal_consistency_checking

2. Unmodifiable DB Parameter: ssl_dh_params_file

Here's the part of base.yml looks like:

NOTE: a similar Q. was asked but was not answered here: How do I replicate a single schema in postgresql?

I am trying to replicate just one schema in a postgresql (13) database using pglogical to another server.

In my test setup on the provider node I have 2 schemas "shared" and "not_shared".

On the subscriber node I have one existing schema called "not_shared".

I want to replicate "shared" from the provider to the subscriber, I have already set up the nodes on both servers.

On the provider I create a replication set for just the "shared" schema (in theory) called "shared_repl":

I'm following instructions from https://github.com/2ndQuadrant/pglogical to install pglogical on postgres 12 on Centos 8. The install seems be successful:

I'm trying to migrate and synchronize a PostgreSQL database using AWS DMS and I'm getting the following error.

I looked at the docs of pglogical.

But I could not find a way to list all replication sets, which I created with create_replication_set().

How to list all replication sets?

I am replicating data from Postgres 10.4 to another Postgres 10.4 instance using logical replication.

The subscriber has several triggers that log events to a single table. This table has a trigger that executes another function (that returns a trigger) to call NOTIFY for a downstream listener.

Trigger on the audit table looks like this:

Is there a way to convert a UUID to a UUID5 in an existing postgres data set?

I have a master/slave setup with pglogical replicating specific tables & columns. I need to give access to this replicated data to a third party, but I want to anonymise it as much as possible.

Every row in a table uses a UUID (generated in code) as primary key, but in order to ensure anonymity I need to ensure that the primary keys of the master & replicated tables are one-way transformed to make back-tracing harder. Some of the replicated tables refer to each other as well, meaning I can't just exclude those columns - values need to match between tables. Hopefully this makes sense so far...

Is there a way of using uuid-ossp and uuid_generate_v5() to essentially use the existing UUIDs as the namespace for a v5 UUID, returning those in the view to the third party (or even to use to transform the specific columns during replication)?

I'm a little stumped. If I can get this sorted entirely in postgres I won't have to muck about with additional tools to transform the data before allowing the third party access, I can just give them real-time data instead of batched. But if I need to involve some additional code then I guess that's not the end of the world...

Thanks in advance!

I'm experimenting with a 2-node bidirectional replication using pglogical-9.6. I'm able to consistently cause a seg fault when I set pglogical.conflict_resolution = 'last_update_wins'.

I'm create a BEFORE DELETE TRIGGER to prevent insertion some values into replicated table.

There are two PostgreSQL 9.6 nodes subscribed to each other via pglogical. If node A inserts a row into the replicated table then node B sees it and vice versa.

However, when I update a row on one node, then subsequent SELECT queries on both nodes will keep returning different results - the current one and some of the previous ones.

Moreover, there are log entries about replication conflicts in the logs of both nodes.

Why does that happen and how do I fix that?

upd: setting pglogical.conflict_resolution to last_update_wins helps. Might consider other options of conflict resolution too