Logical replication is a feature of PostgreSQL 10. It is a method of replicating data objects and their changes, based upon their replication identity (usually a primary key)
Questions tagged [logical-replication]
113 questions
0
votes
0 answers
Postgres SQL Logical Replication Out of Sync - How to Resume Subscription
I'm worried that unfortunately there's not a great way to handle this.
Here's what happened:
Schema change made in main database
Logically replicated db has issue with subscription given the identity change
Subscription failed
Inactive replication…
jlarks32
- 931
- 8
- 20
0
votes
0 answers
PostgreSQL pgoutput publication and custom subscriber
PostgreSQL has a replication feature which is called logical replication. In earliest versions there is a native extension called pgoutput for logical decoding.
Logical Replication consist of a publisher and a subscriber which result into 1-1…
Stavros Koureas
- 1,126
- 12
- 34
0
votes
0 answers
how to know who tries to connect to a database in postgresql
I have a server with postgresql 13 and several postgresql clients that are mutually synchronized by logical replication. Due to problems with the replication in base A that I couldn't solve, I decided to create another identical base B, pass all the…
vittorio
- 1
- 1
0
votes
0 answers
getting replica identity fields for table
I’m using Postgres, and I want to know all the replica identity fields of table
Meaning -
I can get the replica identity type by
SELECT CASE relreplident
WHEN 'd' THEN 'default'
WHEN 'n' THEN 'nothing'
WHEN 'f' THEN 'full'
…
user3289736
- 11
- 1
0
votes
0 answers
Give One Subscription Priority Over Other Subscriptions
I use logical replication to synchronize one big table + some small tables.
I know that if all tables are part of the same subscription it will be executed in transactional order. In other words, as long as the transaction that updated the big table…
user1409708
- 953
- 2
- 11
- 20
0
votes
1 answer
Cloud SQL Postgres external replication from AWS RDS Postgres - problem with source representation instance
I'm setting up logical replication between Cloud SQL Postgres (as a replica) and AWS RDS Postgres (source). I'm using guideline provided by GCP -> https://cloud.google.com/sql/docs/postgres/replication/configure-replication-from-external.
I was…
Olsson
- 25
- 1
- 4
0
votes
1 answer
postgres logical replication not working. Error says "could not connect to the publisher"
I have a postgres database called salephone_test with 3 tables (smartphones, listings, phone_listings) on my windows pc which I want to replicate to at least one ubuntu droplet on digitalocean. I attempted the following setup to replicate the…
Lase
- 27
- 5
0
votes
0 answers
Logical Replication of table with many fields, functions and indexes
I have a table with 16 millions rows that needs to be replicated. The table has a total of 115 columns. Within those, there are 50 varchars columns and primary key with uuid that has 36 chars. There are 15 indexes,some dependencies and some…
padjee
- 125
- 2
- 12
0
votes
0 answers
Postgres replication stop
My replication suddenly stopped, data are not replicated to the subscriber. I don't really know the cause may be because of ID conflict.
I try a couple of things, but none work :
ALTER SUBSCRIPTION test_mysub REFRESH publication;
ALTER SUBSCRIPTION…
padjee
- 125
- 2
- 12
0
votes
2 answers
How to create a logical replication subscription with SSL certificates in Google Cloud SQL (PostgreSQL)?
I would like to use CDC with PostgreSQL's logical replication feature to gather data from different databases in the same instance for a materialized view. However, I cannot get this to work with SSL.
To test the logical replication, I wrote a zsh…
FOR_SCIENCE
- 386
- 3
- 9
0
votes
1 answer
postgresql subscription not working for schemas other than public
I'm trying to create a logical replication with two local postgresql servers (node1: port 5434, node2: port 5435).
I could successfully create publication and subscription on node1 and node2 for a table in public schema.
Node1:
CREATE PUBLICATION…
Tony
- 9
- 4
0
votes
2 answers
postgres 14 "create publication" stuck for hours
I upgraded from Postgres 10 to Postgres 14 using pg_upgrade --link option. The total databases size is around 10TB. The pg_upgrade was successful and quick and like the tool suggested -
Optimizer statistics are not transferred by pg_upgrade. Once…
nullptr
- 87
- 1
- 9
0
votes
1 answer
Can I configure AWS RDS to only stream INSERT operations to AWS DMS?
My requirement is to stream only INSERTs on a specific table in my db to a Kinesis data stream.
I have configured this pipeline in my AWS environment:
RDS Postgres 13 -> DMS (Database Migration Service) -> KDS (Kinesis Data Stream)
This setup works…
raxaghi
- 3
- 2
0
votes
1 answer
Postgresql Logical Replication is not working for some table
I have setup logical replication between two servers in PostgreSQL 11. For some table replication is not working. I dropped those table from publication and added them back and refreshed subscription .still replication has not happened. I checked…
0
votes
0 answers
How to access a table inside trigger function during the logical replication
Scenario:
We have two tables, first one call main_table. second one call look_up table. we need replicate main_table from other DB instance via Postgres build-in logical replication.
in the meantime, we have to filter some rows based on look_up…
Ben
- 1