Beyond logical replication: pg_easy_replicate Supports Tracking DDL Changes
pg_easy_replicate now supports tracking schema changes in PostgreSQL, allowing replication of DDL changes like adding columns. It enhances flexibility and ensures schema synchronization during database migrations and upgrades.
Read original article
pg_easy_replicate has introduced support for tracking schema changes in PostgreSQL, addressing a significant limitation of the database's native logical replication. This enhancement allows for the replication of common Data Definition Language (DDL) changes, such as adding columns or creating indexes, which were previously not automatically applied to target databases during replication. The tool, designed to simplify logical replication management, ensures zero data loss and minimal downtime, making it essential for database migrations and upgrades. The new feature utilizes PostgreSQL's event triggers to capture DDL events, including ddl_command_end, sql_drop, and table_rewrite, storing the details in an audit table. Users can review and apply these changes to the target database using specific commands, ensuring schema synchronization. However, certain operations, such as changes to roles and privileges or database-level operations, are not captured. This development enhances the flexibility of pg_easy_replicate, allowing for dynamic schema management during ongoing replication processes.
- pg_easy_replicate now tracks DDL changes, enhancing PostgreSQL replication.
- The tool captures DDL events using PostgreSQL's event triggers.
- Users can review and apply DDL changes to maintain schema synchronization.
- Certain operations, like role changes, are not tracked by the tool.
- This feature improves flexibility in managing database schemas during replication.
Related
Schema changes and the Postgres lock queue
Schema changes in Postgres can cause downtime due to locking issues. Tools like pgroll help manage migrations by handling lock acquisition failures, preventing application unavailability. Setting lock_timeout on DDL statements is crucial for smooth schema changes.
Postgres Schema Changes and Locking
Schema changes in Postgres can cause downtime by locking out reads and writes. Migration tools help mitigate issues. Breakages during migrations can affect client apps or lock database objects, leading to unavailability. Long queries with DDL statements can block operations. Setting lock_timeout on DDL statements can prevent queuing. Tools like pgroll offer backoff and retry strategies for lock acquisition failures. Understanding schema changes and DDL impact helps ensure smoother migrations and less downtime.
ClickHouse acquires PeerDB to expand its Postgres support
ClickHouse has acquired PeerDB to enhance Postgres support, improving speed and capabilities for enterprise customers. PeerDB's team will expand change data capture, while existing services remain available until July 2025.
Does PostgreSQL respond to the challenge of analytical queries?
PostgreSQL has advanced in handling analytical queries with foreign data wrappers and partitioning, improving efficiency through optimizer enhancements, while facing challenges in pruning and statistical data. Ongoing community discussions aim for further improvements.
Show HN: Pg_replicate – Build Postgres replication applications in Rust
pg_replicate is a Rust crate for PostgreSQL data replication, supporting logical streaming replication. It offers easy integration, a quickstart guide, and plans for future enhancements and additional data sinks.
3 commentsIt’s like the perfect match between event triggers and logical replication. Would love to hear any feedback and/or other ideas folks have tried in this space.
Related
Schema changes and the Postgres lock queue
Schema changes in Postgres can cause downtime due to locking issues. Tools like pgroll help manage migrations by handling lock acquisition failures, preventing application unavailability. Setting lock_timeout on DDL statements is crucial for smooth schema changes.
Postgres Schema Changes and Locking
Schema changes in Postgres can cause downtime by locking out reads and writes. Migration tools help mitigate issues. Breakages during migrations can affect client apps or lock database objects, leading to unavailability. Long queries with DDL statements can block operations. Setting lock_timeout on DDL statements can prevent queuing. Tools like pgroll offer backoff and retry strategies for lock acquisition failures. Understanding schema changes and DDL impact helps ensure smoother migrations and less downtime.
ClickHouse acquires PeerDB to expand its Postgres support
ClickHouse has acquired PeerDB to enhance Postgres support, improving speed and capabilities for enterprise customers. PeerDB's team will expand change data capture, while existing services remain available until July 2025.
Does PostgreSQL respond to the challenge of analytical queries?
PostgreSQL has advanced in handling analytical queries with foreign data wrappers and partitioning, improving efficiency through optimizer enhancements, while facing challenges in pruning and statistical data. Ongoing community discussions aim for further improvements.
Show HN: Pg_replicate – Build Postgres replication applications in Rust
pg_replicate is a Rust crate for PostgreSQL data replication, supporting logical streaming replication. It offers easy integration, a quickstart guide, and plans for future enhancements and additional data sinks.