Re: replication

On 4/25/25 14:50, Marc Millas wrote: Hello, One of my customers tells me he did a replication with the master on a debian 10 and the slave on a debian 12, both with a pg13.20, one on a pgdg110+1, the other a pgdg120.1 is this a good idea ??? (I don't think so...) Sorry I dont quit

replication

Hello, One of my customers tells me he did a replication with the master on a debian 10 and the slave on a debian 12, both with a pg13.20, one on a pgdg110+1, the other a pgdg120.1 is this a good idea ??? (I don't think so...) thanks, Marc MILLAS Senior Architect +33607850334

Re: find replication slots that "belong" to a publication

12:16 +0200, Willy-Bas Loos wrote: > > My question is not so much about "can i drop a certain replication > slot", > > more about "does this publication still have any replication slots?". > > Or, if you will: "what's the publication for this rep

Re: find replication slots that "belong" to a publication

On Mon, 2025-04-07 at 22:32 +0200, Willy-Bas Loos wrote: > I find it very strange, because the publication is needed to make a > subscription, which makes the slot. Right, but that information is only on the subscriber. Yours, Laurenz Albe

Re: find replication slots that "belong" to a publication

On 4/7/25 13:32, Willy-Bas Loos wrote: Hi Laurenz, Thanks for answering! I find it very strange, because the publication is needed to make a subscription, which makes the slot. From here: https://www.postgresql.org/docs/current/logical-replication-subscription.html "A subscription de

Re: find replication slots that "belong" to a publication

I can't think of a way to link publication to a replication slot I agree using pg_state_activity is the only way to do that however you don't know if the subscriber is momentary disconnected due network error or disconnected due to an error in replication such as duplicated key S

Re: find replication slots that "belong" to a publication

On Mon, 2025-04-07 at 12:16 +0200, Willy-Bas Loos wrote: > My question is not so much about "can i drop a certain replication slot",  > more about "does this publication still have any replication slots?". > Or, if you will: "what's the publication for th

Re: find replication slots that "belong" to a publication

Hi Justin, thanks for your anwer! My question is not so much about "can i drop a certain replication slot", more about "does this publication still have any replication slots?". Or, if you will: "what's the publication for this replication slot?". I've dou

Re: find replication slots that "belong" to a publication

On Fri, Apr 4, 2025 at 4:58 AM Willy-Bas Loos wrote: > Hi! > > I'm looking for a way to find out if there are still replication slots > active for a publication before dropping the publication in an automated > way. The idea is that the publication is thought not to be need

Create replication slot options

I am testing out some streaming logical replication commands and am having trouble specifying options when calling CREATE_REPLICATION_SLOT <https://www.postgresql.org/docs/current/protocol-replication.html#PROTOCOL-REPLICATION-CREATE-REPLICATION-SLOT> . I connect to the database with psql &

Replication slot WAL reservation

I am trying to understand how logical replication slots work, specifically in regard to how WAL is reserved and freed by a replication slot. My current understanding of the WAL (set to wal_level logical) is that: 1. Every DML operation (insert, update, delete, truncate) will have a row in the WAL

Re: find replication slots that "belong" to a publication

postgres 13 BTW On Fri, Apr 4, 2025 at 10:58 AM Willy-Bas Loos wrote: > Hi! > > I'm looking for a way to find out if there are still replication slots > active for a publication before dropping the publication in an automated > way. The idea is that the publication is thou

find replication slots that "belong" to a publication

Hi! I'm looking for a way to find out if there are still replication slots active for a publication before dropping the publication in an automated way. The idea is that the publication is thought not to be needed any longer, but we want to make sure. I'm having trouble finding a link

Re: Replication slot WAL reservation

> On Mar 25, 2025, at 13:58, Phillip Diffley wrote: > > Oh I see! I was conflating the data I see coming out of a replication slot > with the internal organization of the WAL. I think the more specific question > I am trying to answer is, as a consumer of a replication

Re: Replication slot WAL reservation

gt; successfully consumed? > > You're looking for Standby Status Update: > > > https://www.postgresql.org/docs/current/protocol-replication.html#PROTOCOL-REPLICATION-STANDBY-STATUS-UPDATE > > The logical replication protocol is a superset of the binary replication > protocol, s

Re: Replication slot WAL reservation

a logical replica). For a logical replica, that can be confirmed_flush_lsn or any point after, but it can't be before. (Even if the WAL exists, the primary will return an error if the start point provided in START_REPLICATION is before confirmed_flush_lsn for a logical replication slot.) Of

Re: Replication slot WAL reservation

> On Mar 25, 2025, at 20:56, Phillip Diffley wrote: > > Is there a message type that is used to confirm what logs have been > successfully consumed? You're looking for Standby Status Update: https://www.postgresql.org/docs/current/protocol-replication.html#PRO

Re: Replication slot WAL reservation

> You shouldn't need to manually advance the replication slot. > The client is also expected to send back regular messages letting the publisher / primary know that it has successfully consumed up to a particular point I was thinking of these as the same thing, but it sounds li

Re: Replication slot WAL reservation

Oh I see! I was conflating the data I see coming out of a replication slot with the internal organization of the WAL. I think the more specific question I am trying to answer is, as a consumer of a replication slot, how do I reason about what replication records will be made unavailable when I

Re: Replication slot WAL reservation

Missed this question! > On Mar 25, 2025, at 09:56, Phillip Diffley wrote: > But when processing data from a replication slot, we confirm rows that have > been processed and can be deleted from the WAL based on the LSN (eg. with > pg_replication_slot_advance). How does postgres i

Re: Replication slot WAL reservation

> On Mar 25, 2025, at 09:56, Phillip Diffley wrote: > 1. Every DML operation (insert, update, delete, truncate) will have a row in > the WAL and that row will have an LSN assigned to it. > 2. The LSNs are assigned when the operation happens. > 3. Operations within a transaction are written to

Re: Create replication slot options

> On Mar 24, 2025, at 17:31, Phillip Diffley wrote: > > I am testing out some streaming logical replication commands and am having > trouble specifying options when calling CREATE_REPLICATION_SLOT. > > I connect to the database with > psql "dbname=replication_te

PG16 and replication, ensure a clean switchover after a stop of the primary server

Hi, First, I have put all details of this post in this gist https://gist.github.com/flaf/ccedf78d0290d231e79581077fd92dbc (with a little video/demo to see with your eyes ;)). I have 2 PostgreSQL servers version 16.8 on RedHat 8.10, with physical streaming async replication: * postgres-1 the

Re: Records count mismatch with logical replication

On 1/23/25 10:21, Durgamahesh Manne wrote: At subscription side getting this error repeatedly  So Do we have any solution without resync full data again from scratch * |2025-01-23 18:11:46 UTC::@:[507]:DEBUG: logical replication did not find row to be updated in replication target

Re: Records count mismatch with logical replication

On 1/23/25 09:54, Durgamahesh Manne wrote: See comments in line below. Source Publication Side: archiving=> select * from pg_stat_replication; There is missing information here. Am I right in assuming this is for slot cls_eva_msa? And that it going to same client_addr 10.80.0.168? cl

Re: Records count mismatch with logical replication

> But records count varies with difference of more than 10 thousand >> > >> > Have you looked at the I/0 statistics between the Postgres instances? >> > >> > Seems everything looks good with pg replication slots >> >> Except the subscriber is lagging

Re: Records count mismatch with logical replication

res instances? > > > > Seems everything looks good with pg replication slots > > Except the subscriber is lagging behind the publisher. > > '... everything looks good' is an opinion not actual data. > > > > > Does this pg logical slot get changes func

Re: Records count mismatch with logical replication

On 1/22/25 18:53, Durgamahesh Manne wrote: > But records count varies with difference of more than 10 thousand Have you looked at the I/0 statistics between the Postgres instances? Seems everything looks good with pg replication slots Except the subscriber is lagging behind

Re: Records count mismatch with logical replication

I/0 statistics between the Postgres instances? > > > > > How to mitigate this issue in simplest way ? > > Until it is determined what is causing the lag there is no way to deal > with it. > > > > > Regards > > Durga Mahesh > > -- > Adrian Klaver

Re: Records count mismatch with logical replication

On 1/21/25 11:40, Durgamahesh Manne wrote: On Wed, 22 Jan, 2025, 00:22 Adrian Klaver, > wrote: On 1/21/25 10:06 AM, Durgamahesh Manne wrote: > > Hi Adrian Klaver > > 22,906,216 bytes/10,846 rows  works out to 2112 bytes per row.

Re: Records count mismatch with logical replication

On Wed, 22 Jan, 2025, 00:22 Adrian Klaver, wrote: > > > On 1/21/25 10:06 AM, Durgamahesh Manne wrote: > > > > > Hi Adrian Klaver > > > > 22,906,216 bytes/10,846 rows works out to 2112 bytes per row. > > > > Is that a reasonable per row estimate? > > > > Yes sometimes would be vary > > If I am f

Re: Records count mismatch with logical replication

On 1/21/25 10:06 AM, Durgamahesh Manne wrote: Hi Adrian Klaver 22,906,216 bytes/10,846 rows  works out to 2112 bytes per row. Is that a reasonable per row estimate? Yes  sometimes would be vary If I am following the lag went from 350GB behind to 22MB. Is the issue that the lag has stal

Re: Records count mismatch with logical replication

On Tue, Jan 21, 2025 at 11:26 PM Adrian Klaver wrote: > On 1/21/25 09:38, Durgamahesh Manne wrote: > > > > > > > > > Hi Adrian Klaver > > > > Really Thanks for your quick response > > > > This happened during repack lag went to more than 350Gb then gradually > > decreased to minimal lag after run

Re: Records count mismatch with logical replication

On 1/21/25 09:38, Durgamahesh Manne wrote: Hi Adrian Klaver Really Thanks for your quick response This happened during repack lag went to more than 350Gb then gradually decreased to minimal lag after running pg_repack I don't use pg_repack so I don't know what effect it would have on t

Re: Records count mismatch with logical replication

On Tue, Jan 21, 2025 at 9:24 PM Adrian Klaver wrote: > On 1/21/25 04:08, Durgamahesh Manne wrote: > > Hi Team, > > > > I have publication and subscription servers .So seems data replication > > running with minimal lag but records count mismatch with more than 10 &

Re: Records count mismatch with logical replication

On 1/21/25 04:08, Durgamahesh Manne wrote: Hi  Team, I have publication and subscription servers .So seems data replication running with minimal lag but records count mismatch with more than 10 thousand records between source and destination tables Could you please help in resolving this

Records count mismatch with logical replication

Hi Team, I have publication and subscription servers .So seems data replication running with minimal lag but records count mismatch with more than 10 thousand records between source and destination tables Could you please help in resolving this issue? Regards, Durga Mahesh

Re: Different Autovacuum Settings on Master and Replica in Streaming Replication

c a >> écrit : >> >>> Question regarding autovacuum settings in a PostgreSQL streaming >>> replication setup. Specifically, I am curious about whether it is possible >>> (and advisable) to have different values for the following settings on the >>> mas

Re: Different Autovacuum Settings on Master and Replica in Streaming Replication

17, 2025 at 4:34 PM Guillaume Lelarge wrote: > Hi, > > Le ven. 17 janv. 2025 à 15:38, Franjo Stipanovic a > écrit : > >> Question regarding autovacuum settings in a PostgreSQL streaming >> replication setup. Specifically, I am curious about whether it is possible

Re: Different Autovacuum Settings on Master and Replica in Streaming Replication

Hi, Le ven. 17 janv. 2025 à 15:38, Franjo Stipanovic a écrit : > Question regarding autovacuum settings in a PostgreSQL streaming > replication setup. Specifically, I am curious about whether it is possible > (and advisable) to have different values for the following settings on the

Re: Different Autovacuum Settings on Master and Replica in Streaming Replication

Franjo Stipanovic writes: > Question regarding autovacuum settings in a PostgreSQL streaming > replication setup. Specifically, I am curious about whether it is possible > (and advisable) to have different values for the following settings on the > master and repli

Different Autovacuum Settings on Master and Replica in Streaming Replication

Question regarding autovacuum settings in a PostgreSQL streaming replication setup. Specifically, I am curious about whether it is possible (and advisable) to have different values for the following settings on the master and replica databases: - autovacuum_vacuum_scale_factor

Re: Data Out of Sync with Physical Streaming Replication

e784e70250","\\x019344723f7778b6b34305157fb9a339","\\x01934472b0527ce29c7d35a809bcb3f6"} (5 rows) STANDBY: issuedb=# SELECT ctid, xmin, xmax, troubleshoot_issue_id as issue_id from troubleshoot_issue; ctid | xmin | xmax | troubleshoot_issue_id ---+-

Re: Data Out of Sync with Physical Streaming Replication

On 1/15/25 19:50, Tim Gerber wrote: > Hi All, > >... > > Archive mode is on.   I ran pg_amcheck and everything came back clean. I > know synchronous replication is an option, but it feels like something > else is going on and would like to get to the bottom of it. > >

Data Out of Sync with Physical Streaming Replication

Hi All, We have run into an issue where the data for a primary and standby Postgres instances are out of sync. We are using streaming physical replication. We have encountered this issue several times over the last year on different Postgres Clusters. Postgres indicates everything is in sync

Re: Need help in logical replication

criber table empty?? > > Thank you, > > On Mon, Jan 13, 2025 at 7:30 AM Divyansh Gupta JNsThMAudy < > ag1567...@gmail.com> wrote: > >> Hii PostgreSQL Community, >> >> I am setting up logical replication between two clusters in the same >> subnet g

Re: Need help in logical replication

> Hii PostgreSQL Community, > > I am setting up logical replication between two clusters in the same > subnet group. I’ve created a publication on the primary and a subscription > on the secondary, and the replication slot has been created. However, the > slot remains inactive, and chan

Re: Need help in logical replication

> On Jan 13, 2025, at 5:30 AM, Divyansh Gupta JNsThMAudy > wrote: > >  > Hii PostgreSQL Community, > > I am setting up logical replication between two clusters in the same subnet > group. I’ve created a publication on the primary and a subscription on the > sec

Need help in logical replication

Hii PostgreSQL Community, I am setting up logical replication between two clusters in the same subnet group. I’ve created a publication on the primary and a subscription on the secondary, and the replication slot has been created. However, the slot remains inactive, and changes aren’t being

Re: Query related to Logical Replication using test_decoding and unchanged-toast-datum

On Mon, Jan 6, 2025 at 7:21 AM Kiran K V wrote: > could you please tell me whether PostgreSQL will truly log these values to > WAL or not ? If not, what will be entered into WAL for the JSON column if > it remains unchanged? > > Not quite sure what you are looking for but: https://www.postgresql

Query related to Logical Replication using test_decoding and unchanged-toast-datum

Hi, I am currently using PostgreSQL version 16 and the test_decoding plugin to perform logical replication (using replication slots). I have a simple table with integer column and JSON column. When a non-JSON column is updated, the value "unchanged-toast-datum" for the JSON column i

Re: Starting logical replication at arbitrary point that's available in WAL

On 12/27/24 13:20, Zhijie Hou (Fujitsu) wrote: On Friday, December 27, 2024 7:39 PM Alexander Uvizhev wrote: Hi, I'm doing a logical replication using streaming replication protocol and I'm trying to start a stream from a certain arbitrary point that's available in WAL

RE: Starting logical replication at arbitrary point that's available in WAL

On Friday, December 27, 2024 7:39 PM Alexander Uvizhev wrote: Hi, > I'm doing a logical replication using streaming replication protocol and > I'm trying to start a stream from a certain arbitrary point that's > available in WAL. However, both CRE

Starting logical replication at arbitrary point that's available in WAL

Hi, I'm doing a logical replication using streaming replication protocol and I'm trying to start a stream from a certain arbitrary point that's available in WAL. However, both CREATE_REPLICATION_SLOT and pg_create_logical_replication_slot() create slot with nearly last L

Re: Streaming replication problem with collation

Ekaterina Amez Gonzalez writes: > I tried what was suggested: reindexing and running "refresh collation" > alter after that and everything seems to work ok so this looks like an easy > wat to migrate from one server to another. Plus I feel more comfortable > using stre

Streaming replication problem with collation

different glibc version between systems before I started my tests, but I tried it anyway. I first upgraded CentOS version to 15 and then made a streaming replication to the other server (Rocky). After that I encountered the next warning when connecting to new hot standby database: WARNING: datab

Continue Logical Replication After Master Became Slave and then Became Master Again

Hi All, Postgres 15.2 We have Patroni cluster with one master and two replicas. The master is publisher (logical replication) to some subscriptions running on other clusters. When we have a failover, the master becomes replica and one of the replicas assume the role of master. In such a

Re: pg_upgrade vs. logical replication

On Mon, Dec 9, 2024 at 6:43 AM Joe Wildish wrote: Overall, your solution seems okay, but: > a fix has gone in to PG17 that sorts this problem. > > However, we can't go to 17 yet, so need a solution for 15 and 16. Honestly, this would seem like a really, really strong reason to push for v17.

pg_upgrade vs. logical replication

y, and the associated replication origin has no LSN information in pg_replication_origin_status. I found a thread on the hackers list that seems to call out this problem: https://www.postgresql.org/message-id/flat/20230217075433.u5mjly4d5cr4hcfe%40jrouhaud It is a very long thread. TLDR; Julien d

Re: Qsn on Setting replication using " set session_replication_role = "

On Wednesday, December 4, 2024, Bharani SV-forum wrote: > > > Team > Need help on clarification on using > " set session_replication_role " > > Assume i am loggging into > psql > and issue > \o output.lst > set session_replication_role=default > update > This seems like a bad idea. Why wou

Qsn on Setting replication using " set session_replication_role = "

  TeamNeed help on clarification on using" set  session_replication_role " Assume i am loggging intopsql and issue\o output.lstset session_replication_role=defaultupdate coming out of \psql command and verifying log fileand assume i come out of the psql session without doing set session_r

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

primary server and a standby > >server. The database is currently running on PostgreSQL 15.0, and I > plan > >to upgrade both servers to 15.9. > > > >I have the following questions regarding the upgrade and replication > >process: > > > >

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

servers to 15.9. > >I have the following questions regarding the upgrade and replication >process: > > 1. Upgrade and Replication Compatibility: > > * My plan is to perform a failover, promote the standby server > (currently 15.0) to pr

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

<mailto:ronljohnso...@gmail.com>> wrote: > > > > > > Doesn't the existence of a replication slot force PG to retain > > WAL files when replication is broken? > > > > It does. I don't recall if the OP said that they were using a > >

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

On 11/24/24 13:00, Ron Johnson wrote: On Sun, Nov 24, 2024 at 2:55 PM Christophe Pettus <mailto:x...@thebuild.com>> wrote: > On Nov 24, 2024, at 09:15, Ron Johnson mailto:ronljohnso...@gmail.com>> wrote: > > Doesn't the existence of a replication sl

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

On Sun, Nov 24, 2024 at 2:55 PM Christophe Pettus wrote: > > On Nov 24, 2024, at 09:15, Ron Johnson wrote: > > > > Doesn't the existence of a replication slot force PG to retain WAL files > when replication is broken? > > It does. I don't recall if the OP s

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

> On Nov 24, 2024, at 09:15, Ron Johnson wrote: > > Doesn't the existence of a replication slot force PG to retain WAL files when > replication is broken? It does. I don't recall if the OP said that they were using a persistent replication slot or not; it'

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

Pettus wrote: > > > > On Nov 24, 2024, at 09:03, Subhash Udata wrote: > > When we shut down the standby, upgrade it, and then start it back up, > will the replication automatically resume from the primary to the standby? > > Assuming that the standby has access to a

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

> On Nov 24, 2024, at 09:03, Subhash Udata wrote: > When we shut down the standby, upgrade it, and then start it back up, will > the replication automatically resume from the primary to the standby? Assuming that the standby has access to any WAL generated during the shutdown (eit

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

On Sun, Nov 24, 2024 at 12:06 PM Christophe Pettus wrote: > > On Nov 24, 2024, at 09:03, Subhash Udata wrote: > > When we shut down the standby, upgrade it, and then start it back up, > will the replication automatically resume from the primary to the standby? > > Assuming

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

> On Nov 24, 2024, at 08:51, Subhash Udata wrote: > However, my concern lies in the fact that we are working with production > servers, where downtime is not acceptable. There is no way to upgrade community PostgreSQL, either to a new minor version or a new major version, with absolute zero

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

Thank you for your valuable suggestion! I have a question regarding the process: When we shut down the standby, upgrade it, and then start it back up, will the replication automatically resume from the primary to the standby? Looking forward to your clarification. 2) What do you mean by

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

ng with production > servers, where downtime is not acceptable. > > Additionally, if a failover occurs due to a network issue or any other > disaster, setting up replication again requires running the pg_basebackup > command. For large databases, this process becomes a significant chal

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

failover occurs due to a network issue or any other disaster, setting up replication again requires running the pg_basebackup command. For large databases, this process becomes a significant challenge, as running pg_basebackup for the entire cluster can be time-consuming and resource-intensive. On Sun

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

On Sun, Nov 24, 2024 at 11:41 AM Adrian Klaver wrote: > On 11/24/24 08:36, Subhash Udata wrote: > > The reason to upgrade from 15.0 to 15.9 is this > > https://www.postgresql.org/support/security/CVE-2024-10979/ > > > > > > Here it is

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

I have the following questions regarding the upgrade and replication > process: > >1. > >*Upgrade and Replication Compatibility*: >- My plan is to perform a failover, promote the standby server > (currently 15.0) to primary, and then upgrade the old primary

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

gt; server. The database is currently running on *PostgreSQL 15.0*, and I > plan to upgrade both servers to *15.9*. > > I have the following questions regarding the upgrade and replication > process: > >  1. > >     *Upgrade and Replicatio

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

> I have the following questions regarding the upgrade and replication > > process: > > > > 1. > > > > *Upgrade and Replication Compatibility*: > > > > * My plan is to perform a failover, promote the standby server > > (cu

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

the upgrade and replication process: 1. *Upgrade and Replication Compatibility*: * My plan is to perform a failover, promote the standby server (currently 15.0) to primary, and then upgrade the old primary server to version 15.9. * After upgrading the old primary

Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

Dear PostgreSQL Community, I have a production database setup with a primary server and a standby server. The database is currently running on *PostgreSQL 15.0*, and I plan to upgrade both servers to *15.9*. I have the following questions regarding the upgrade and replication process: 1

Version upgrades and replication

g the user on the primary (``CREATE ROLE replicauser WITH REPLICATION PASSWORD 'password' LOGIN``) and allowing connections in ``pg_hba.conf`` Everything looks fine, changes on the primary can be seen on the replica. First question: what's the name of this replication mode? Every ti

Re: PostgreSQL logical replication

On Tue, Nov 12, 2024 at 7:47 PM Ron Johnson wrote: > The documentation tells you, down in the Notes section. > > https://www.postgresql.org/docs/16/sql-createpublication.html > Thank you.

Re: PostgreSQL logical replication

The documentation tells you, down in the Notes section. https://www.postgresql.org/docs/16/sql-createpublication.html On Tue, Nov 12, 2024 at 5:46 AM Jayadevan M wrote: > Hello all, > > I am using PostgreSQL 16.4. I tried to set up logical replication with > "postgres" us

PostgreSQL logical replication

Hello all, I am using PostgreSQL 16.4. I tried to set up logical replication with "postgres" user and all worked fine. Then I created a new user (my_replication) to be used for the purpose and couldn't figure out which privileges are necessary. Initially I got an error like &quo

Re: Logical replication disabled, recovery not possible because of 1 large transaction with schema changes?

De Groote wrote: > > > > Hello Muhammad, > > > > The problem with my scenario is the changes are written as a single > transaction, with a BEGIN and COMMIT. In that transaction, there are first > inserts, then a schema change, and then inserts on the new schema. > >

Re: Logical replication disabled, recovery not possible because of 1 large transaction with schema changes?

on the new schema. I guess until logical replication of DDL is available you’re out of luck. The best you can do is to have a separate table for recording and replaying schema changes. Create triggers that perform actual DDL operations based on DML in this table. Publish this table on the

Re: Logical replication disabled, recovery not possible because of 1 large transaction with schema changes?

table from publication. On Thu, Oct 17, 2024 at 5:49 AM Muhammad Usman Khan wrote: > Hi, > When you execute schema-altering operations on the publisher, these > changes are not automatically replicated to the subscriber which causes > the following error > logical replication

Re: Logical replication disabled, recovery not possible because of 1 large transaction with schema changes?

Hi, When you execute schema-altering operations on the publisher, these changes are not automatically replicated to the subscriber which causes the following error logical replication target relation "public.dummy_table" is missing replicated columns: "contact_email", &qu

Logical replication disabled, recovery not possible because of 1 large transaction with schema changes?

ut this runs into the same error. I remove the table from the publication and retry. Same error. Even with the table no longer in the publication, and the table on the subscriber side dropped and re-created, I'm still getting the exact same errors of "logical replication target relation "p

WAL replication standby server: query

archive_command= ' cp %p /data/archive/%f ' pg_hba.conf I have host replication all 10.255.10.0/24 md5 My doubt is whether the standby Postgres server(EDB16) reads WAL files by itself from the primary server's explicit WAL archive folder /data/a

Re: Logical Replication Delay

Hi Ram, 29 set 2024, 12:29 Ramakrishna m : *We are planning to set up logical replication from a standby to another > server. When the primary goes down, there is no issue as the standby > becomes the primary and the logical slots are already present. However, > when the standby

Re: Logical Replication Delay

up logical replication from a standby to another server. When the primary goes down, there is no issue as the standby becomes the primary and the logical slots are already present. However, when the standby goes down, these slots are not copied to the third node or the primary by Patroni. Is there

Re: MError after replication in postgresql

etting error after doing replication on posrgresql 16.3 (OS is HP > UX) > error is regarding Libpq library, we are unable to start sync getting error > > FATAL: could not connect to the primary server : libpq is incorrectly > linked to backend functions. > LOG: waiting for WAL to

Re: Logical Replication Delay

On Sat, Sep 21, 2024 at 3:08 PM Ramakrishna m wrote: > I would greatly appreciate any suggestions you may have to help avoid > logical replication delays, whether through tuning database or operating > system parameters, or any other recommendations > In addition to the things alre

Re: MError after replication in postgresql

On Wed, 2024-09-25 at 10:57 +0530, Danish Hajwane wrote: > We are getting error after doing replication on posrgresql 16.3 (OS is HP UX) > error is regarding Libpq library, we are unable to start sync getting error > > FATAL: could not connect to the primary server : libpq is incorr

MError after replication in postgresql

Hi Team, We are getting error after doing replication on posrgresql 16.3 (OS is HP UX) error is regarding Libpq library, we are unable to start sync getting error FATAL: could not connect to the primary server : libpq is incorrectly linked to backend functions. LOG: waiting for WAL to become

Re: Logical Replication Delay

Hi Ramakrishna, I am not following the reasoning on not separating the tables into different publications and subscriptions. I set up logical replication all the time in many different environments, one of the audits I perform before deploying LR is looking at pg_stat_all_tables and WAL creation

Re: Logical Replication Delay

isher and subscriber has to do. > > Hope this helps. > > > > On Sat, Sep 21, 2024 at 3:08 PM Ramakrishna m wrote: > >> Hi Team, >> >> We have configured bidirectional replication (but traffic can only flow >> in one direction) between two data centers (dist

Re: Logical Replication Delay

bidirectional replication (but traffic can only flow in > one direction) between two data centers (distance: 1000 km, maximum Network > latency: 100 ms) with an application TPS (transactions per second) of 700 > at maximum. > > We are fine with handling up to 500 TPS without observing a

Logical Replication Delay

Hi Team, We have configured bidirectional replication (but traffic can only flow in one direction) between two data centers (distance: 1000 km, maximum Network latency: 100 ms) with an application TPS (transactions per second) of 700 at maximum. We are fine with handling up to 500 TPS without

  1   2   3   4   5   6   7   8   9   10   >