Questions tagged [pg-logical]
pglogical is a PostgreSQL extension that implements a publish-subscribe replication model. Use this tag specifically when asking questions about that extension; do not use it for questions about the PostgreSQL native logical replication, available since version 10. Include also the generic "postgresql" tag, along with the version-specific Postgres tag.
27
questions
1
vote
1
answer
38
views
Postgres Logical replication data missing in Slave - Out of sync
The issue started with the replication lag between Master and slave (Logical Replication Server) upto 30 GB lag and the lag wasn't coming down for a proper sync and was in catchup state.
The ...
0
votes
1
answer
36
views
Question about PostgreSQL Logical replication behaviour
Generally when a transaction commits in PostgreSQL, then immediately the logical decoding process will start and convert the WAL data into steam of data for consumers. My understanding is
Commit » WAL ...
- 1,810
1
vote
1
answer
197
views
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 ...
- 111
0
votes
0
answers
214
views
PostgreSQL Logical Replication Lag
I am using pglogical extension for logical replication between a PostgreSQL version 10 and 13.
I have multiple databases in the primary which are replicating data to three databases in logical replica....
- 504
0
votes
1
answer
330
views
Sync Schema changes automatically with Logical Replication Postgresql
I know there is limitation for logical replication to publish schema change to the subscriber. And for example if you add a column in publisher you have to manually add the same column to the ...
- 1
0
votes
0
answers
1k
views
How to activate replication slot
I have 2 logical replication slots in Postgres 12. Suddenly one of them (100 fields, 16K records) failed couple of days ago. Obviously, the replication slot is inactive. Then I check a couple of ...
- 137
0
votes
1
answer
157
views
slave on logical replication does not get update real time
I successfully started logical replication on a testing environment. I generate insert on the master for some 60K records using procedure. While the insert process is running, i monitor the slave ...
- 137
-2
votes
1
answer
123
views
Can min_wal_size bigger than max_wal_size?
is it possible if min_wal_size bigger than max_wal_size ? What happen if it does ? What are the circumstances when min_wal is bigger than max_wal ?
I am using PG 12.
Thanks
- 137
0
votes
0
answers
83
views
I there a way to decode pglogical transaction to find table name in trouble?
I've set up pglogical replication between postgresql 9.x and 13.x and it was replicating (initially syncing the 200G database) just fine for quite some time.
Then it got stuck at some point, showing ...
- 208
0
votes
0
answers
20
views
Limiting subscription to just one table only
Icreate a Publication using :
CREATE PUBLICATION TEST_publication2 FOR TABLE public.Table1;
Then I create a Subscription using this
CREATE SUBSCRIPTION test_mysub
CONNECTION 'host=10.100.5.55 port=...
- 137
1
vote
1
answer
480
views
Read Postgres CDC data with Logical Replication via Pgoutput plugin and decode it into textual format
Background: We are running Postgres v13.4 and have enabled logical replication for reading CDC data for a select few tables. We are using the pgoutput plugin and the basic setup is working fine.
Query:...
- 11
0
votes
1
answer
182
views
Upgrading Logical Replica of PostgreSQL
I am trying to upgrade a logical replica from PostgreSQL V10 to V13. We are using pglogical for the logical replication.
Though the upgrade process of the replica can be done, however the replication ...
- 504
1
vote
1
answer
429
views
Installing pglogical: Find postgres include folder, add all include folders to C_INCLUDE_PATH
I am trying to install the pglogical extension in my postgres database, which I installed on my macOS with homebrew. I am following instructions provided on the GCP blog as I am preparing to migrate ...
0
votes
1
answer
200
views
pglogical: list Schema/table part of the replication sets?
Is there a way to list all the Schemas which have been added to the replication_set?
# SELECT * FROM pglogical.replication_set;
set_id | set_nodeid | set_name | replicate_insert | ...
2
votes
1
answer
476
views
Why can't I setup replication with pglogical? It says I need a primary key even though I have unique constraints
I'm trying to enable replication with pglogical on a database running Postgres 9.6.22, however I can't add one of my tables even though it has a unique constraint:
db=> SELECT pglogical....
- 31
2
votes
1
answer
776
views
Postgres major version upgrade using replication server
I am planning to upgrade my database from Postgres 10 to Posgres 14. To reduce the downtime, I plan to use the replication for the same. Plan (in short) is to:
Dump the data using pg_dumpall
Kill the ...
- 121
0
votes
0
answers
749
views
Postgresql pg_logical sequence replication not synchronized
I'm testing pg_logical extension for Postgresql because we need to replicate some clusters with Postgresql 9.6 before upgrading them to newer version, where logical replication is a core feature.
I've ...
- 125
3
votes
1
answer
912
views
Postgres difference between restart_lsn and confirmed_flush
According to the documentation, restart_lsn is
The address (LSN) of oldest WAL which still might be required by the consumer of this slot and thus won't be automatically removed during checkpoints...
...
- 135
0
votes
1
answer
462
views
Show more details on Postgres logical replication errors
Wonder if there is a way to add more details (like the column name, database) to logical replication errors in case of missing columns. I got general log entries like this:
2021-09-16 14:47:37.149 CDT ...
0
votes
1
answer
507
views
Limit size of WAL files for PostgreSQL database
I'm using AWS RDS PostgreSQL 13 database in a master-slave configuration using logical replication with pglogical extension.
Recently, one of the application migrations has updated the schema of the ...
- 101
1
vote
0
answers
92
views
Create logical replication along with normal replication [closed]
I need to replicate only some of the tables from my Master Postgres.
With Google search, I got to know that it is possible with logical replication from Postgres10.
I already have a read-only replica ...
0
votes
0
answers
400
views
Unable to replicate Postgres tables using logical replication in RDS
Tables in the destination database are empty even after subscription is successful.
I am extremely new to logical replication within Postgres. I am trying to replicate three tables from the source ...
0
votes
2
answers
122
views
Error when passing variable to function
I've created a function in PostgreSQL 11.10 to handle DDL change in pglogical to apply it into subscriber. Here is the function:
CREATE OR REPLACE FUNCTION public.intercept_ddl()
RETURNS ...
0
votes
1
answer
208
views
2ndQuadrant/Pglogical multi-master cluster installation
I'm looking for a way to make a multi-master cluster with pglogical.
When I say multi-master, it's means 3 nodes, where each of them is a provider and a subscriber.
Currently I have two nodes that ...
- 1
2
votes
1
answer
222
views
Pglogical - multiple database tables to single table
I have 4 databases, each database is having 2 tables. The table structure on all the databases are same. So i want to replicate these two tables from all 4 databases to a single database.
So my ...
- 1,810
0
votes
1
answer
160
views
Dump pglogical Config to files
We use the tool etckeeper to keep track of the configuration of servers.
I would like to dump the configuration of pglogical to files for this purpose.
I tried this:
db@host:~$ pg_dump -t '...
- 1,281
0
votes
1
answer
358
views
How to resolve the error for "Alter Subscription disable"?
I have the following subscription for logical replication:
pctest1=> SELECT subscription_name, status FROM pglogical.show_subscription_status();
subscription_name | status
----...
- 113