CODEWITHSUNDEEP
postgresqlreplicationlogical-replication
Humberto Barrera
Humberto Barrera

Reputation: 23

PostgreSQL 13.3 Logical Replication doesn't create the replication slot in the master

I'm new in PostgreSQL. I'm trying to create a logical replication of 2 databases that are in the same localhost and port (I don't know if that's part of the problem that I'm having). The point is, that I create the publication with no problem, but, the moment that I create the subscription, the replication slot that must be created in the master won't create. I have the wal_level = logical, but I don't know if I need another configuration in the postgres.conf or pg_hba

Another detail is that I can't create the subscription by code, Postgres just simply doesn't respond, like it got stuck right there, doesn't show any error message or confirmation. The only way I can create the subscription is in pgAdmin 4, but here occurs the error that the replication slot it doesn't create.

Any suggestion of help is accepted. And btw, sorry if my english is kind of bad, I hope I have explained myself.

Upvotes: 2

Views: 3949

Answers (1)

Nikhil B
Nikhil B

Reputation: 393

You are not able to create a subscription from code because when you are creating a replication slot (the default behavior) with CREATE SUBSCRIPTION cannot be executed inside a transaction block.

Now while creating a subscription that connects to the same database cluster (for example, to replicate between databases in the same cluster or to replicate within the same database) will only succeed if the replication slot is not created as part of the same command. Otherwise, the CREATE SUBSCRIPTION call will hang. To make this work, create the replication slot separately (using the function pg_create_logical_replication_slot with the plugin name pgoutput) and create the subscription using the parameter create_slot = false. This is an implementation restriction that might be lifted in a future release.

please refer to Notes section of https://www.postgresql.org/docs/current/sql-createsubscription.html

Upvotes: 2

Related Questions