This error occurs when attempting to create a replication origin with a name that already exists in the pg_replication_origin catalog, typically during logical replication setup or when manually creating origins.
This error is raised when PostgreSQL's logical replication system detects an attempt to create a replication origin that already exists. Replication origins are used to track the progress of logical replication from remote servers and are stored in the pg_replication_origin system catalog. Each replication origin must have a unique name. When you create a logical replication subscription, PostgreSQL automatically creates a corresponding replication origin. The error typically occurs when trying to manually create an origin with a name that's already in use, or when attempting to recreate a subscription that wasn't properly cleaned up. Replication origins are created using pg_replication_origin_create() and store metadata about replication progress, including the LSN (Log Sequence Number) position. If an origin with the same name already exists, PostgreSQL prevents creating a duplicate to avoid conflicts in replication progress tracking.
First, identify all existing replication origins in your database:
-- View all replication origins
SELECT * FROM pg_replication_origin;
-- View origins with their current replication status
SELECT * FROM pg_replication_origin_status;This shows you which origins exist and their current LSN positions. Look for the origin name causing the conflict.
Verify if there are any active subscriptions that might be using the origin:
-- List all subscriptions
SELECT subname, subenabled, subconninfo
FROM pg_subscription;
-- Check subscription slots
SELECT slot_name, plugin, slot_type, active
FROM pg_replication_slots;If a subscription exists but should be removed, drop it first before dealing with the origin.
If the replication origin is no longer needed or needs to be recreated, drop it:
-- Drop a specific replication origin by name
SELECT pg_replication_origin_drop('origin_name');Replace 'origin_name' with the actual name of the conflicting origin. Note that this requires superuser privileges and will remove all associated progress tracking.
If the origin belongs to a subscription, drop the subscription completely:
-- Drop subscription (this should also clean up the origin)
DROP SUBSCRIPTION subscription_name;In some cases, if the publisher is unavailable, you may need to force the drop:
-- Force drop when publisher is unreachable
ALTER SUBSCRIPTION subscription_name DISABLE;
ALTER SUBSCRIPTION subscription_name SET (slot_name = NONE);
DROP SUBSCRIPTION subscription_name;After cleanup, you can now recreate the subscription or origin:
-- Create new subscription (automatically creates origin)
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=source_host dbname=source_db user=repl_user password=xxx'
PUBLICATION my_publication;Or manually create an origin if needed:
-- Manually create replication origin
SELECT pg_replication_origin_create('my_origin_name');Replication Origin Naming: When PostgreSQL creates a replication origin for a subscription, it typically uses the format "pg_" followed by the subscription's OID. However, origins can also be created with custom names for manual replication setups.
Origin Persistence: Replication origins persist even after subscription drops in some failure scenarios, particularly if the connection to the publisher is lost during the drop operation. Always verify origins are cleaned up using pg_replication_origin catalog queries.
LSN Position Preservation: If you drop and recreate a replication origin, you lose the tracked LSN position. For production replication, consider using pg_replication_origin_advance() to manually adjust positions rather than dropping and recreating origins.
Bi-directional Replication: In bi-directional replication setups using origin filtering, origin names are critical for preventing replication loops. Ensure origin names are unique across all nodes and match your replication topology design.
Permissions: Both pg_replication_origin_drop() and pg_replication_origin_create() require superuser privileges or the pg_read_all_settings and pg_write_all_settings roles in newer PostgreSQL versions.
Catalog Queries: The pg_replication_origin system catalog uses an exclusive lock during origin creation/deletion. This is generally fast but can cause brief waits if multiple replication operations occur simultaneously.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL