logical replication Archives

Tech Optimizer

April 24, 2025

TecentDB PostgreSQL will provide full support for PG 17

TencentDB for PostgreSQL has introduced robust security measures including data encryption, Virtual Private Cloud (VPC) isolation, and process monitoring for compliance with international standards. PostgreSQL 17.0 features enhanced logical replication with slot failover, performance optimizations such as batch I/O page read/write and improved Write-Ahead Logging, and operational enhancements like incremental backups and a transaction timeout parameter. Tencent has developed features for resource management, including fine-grained multi-tenant resource management with CPU resource isolation and process-level monitoring, as well as SQL throttling. Cold/hot data separation solutions include the 'cos_fdw' plugin for accessing cold data and the 'starocks_fdw' plugin for integrating with the StarRocks analytics engine. Security auditing has been improved with an Express Edition for basic needs and an Advanced Edition for broader audit coverage. These enhancements aim to balance performance and cost, maximize resource efficiency, and strengthen compliance.

Tech Optimizer

April 21, 2025

Best practices to handle AWS DMS tasks during PostgreSQL upgrades

The AWS Database Migration Service (AWS DMS) provides a managed solution for migrating and replicating databases to AWS, supporting both homogeneous and heterogeneous migrations. It allows seamless data migration from PostgreSQL databases to any supported target and vice versa. Open-source PostgreSQL regularly releases new versions, and Amazon RDS aims to support these within five months. Upgrading PostgreSQL instances is essential for security and compliance. Minor version upgrades focus on security and bug fixes without adding new functionalities, while major upgrades can change system tables and data formats. AWS DMS tasks require careful management during PostgreSQL upgrades. For minor upgrades, ongoing DMS tasks should be stopped and then resumed post-upgrade. For major upgrades, replication slots must be dropped before the upgrade, which will invalidate ongoing DMS tasks. After the upgrade, a new CDC-only task should be created to resume data migration. Minor version upgrades of the target PostgreSQL database do not affect AWS DMS CDC tasks, but major upgrades require stopping the DMS task, performing the upgrade, and ensuring compatibility with the replication instance before resuming the task.

Tech Optimizer

March 18, 2025

Use pgactive for rolling major version upgrades in Amazon RDS for PostgreSQL

pgactive enables rolling major version upgrades for Amazon RDS for PostgreSQL, allowing for a smoother transition with reduced impact on applications. Amazon RDS supports both minor and major version upgrades, with minor upgrades being backward-compatible and major upgrades introducing non-backward-compatible features. Downtime during minor upgrades can be minimized using Amazon RDS Proxy or PgBouncer. For major upgrades, a blue/green deployment option is available, which involves provisioning a new DB instance and validating the upgrade. The pgactive extension allows for active-active replication, enabling all nodes to handle read and write operations simultaneously. It includes features like conflict detection and resolution. Key benefits of using pgactive for database upgrades include multi-tenant support, minimal downtime, and seamless rollback capabilities. To perform a major version upgrade using pgactive, the process involves setting up source and target databases, configuring pgactive parameters, and verifying data replication. Specific commands are provided for creating and modifying databases, setting up user mappings, and initializing the replication group. Limitations of pgactive include handling sequences, materialized views, DDL replication, existing logical replication, and tables without primary keys. Monitoring replication lag is crucial for diagnosing potential issues, and alerts should be set up for active status and pending WAL files. In case of a rollback, the application can be pointed back to the old primary database. After the upgrade, resources should be cleaned up to avoid incurring charges.

Tech Optimizer

February 27, 2025

Upgrade strategies for Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL 12

Standard support for Amazon Aurora PostgreSQL-Compatible Edition and Amazon RDS for PostgreSQL version 12 will end on February 28, 2025. The final update for PostgreSQL 12, version 12.22, was released on November 21, 2024. PostgreSQL follows an annual major version release cycle, supporting each version for five years post-release. Upgrading to newer major versions requires careful planning and testing to ensure application compatibility. New features in higher PostgreSQL versions include performance improvements, enhanced security, and new functionalities. Aurora PostgreSQL now supports AWS Graviton4 instances, which provide up to 40% performance improvement over Graviton3 instances. Recommended upgrades include moving to PostgreSQL 16 or newer, with options for in-place upgrades, blue/green deployments, and out-of-place upgrades. Amazon RDS Extended Support allows continued operation on a major version beyond the standard support end date, available at an additional cost. Extended Support for Aurora PostgreSQL 12.x and Amazon RDS for PostgreSQL 12.x will incur charges starting March 1, 2025, and will cease upon upgrading.

Tech Optimizer

February 21, 2025

PostgreSQL 18: Part 2 or CommitFest 2024-09

September's CommitFest for version 18 has seen a wealth of accepted patches and new features, including: - Conflict detection and logging in logical replication - Removal of the constant 10000000000 in the planner to improve sequential scan cost assessment - Enhanced memory management and usage monitoring for temporary tuple storage - Asynchronous operations support in pg_upgrade for multiple databases - Speed improvements for Hash Joins - Faster text value processing in JSON - Optimized numeric-type multiplication and division - Introduction of ANALYZE ONLY and VACUUM ONLY commands - Improved checkpointer statistics - Normalization of SET commands in pg_stat_statements - Reporting of remote connection status in postgres_fdw_get_connections - Ignoring format conversion errors in file_fdw - New function has_largeobject_privilege - Functions crc32 and crc32c added - Client-server protocol updates to report search_path changes - Support for named prepared statements in psql - Integrity verification for tar backups in pg_verifybackup

Tech Optimizer

February 4, 2025

5 new features in EDB Postgres AI

The Oracle Estate Migration Assessment is a strategic tool for enterprise migrations that focuses on low-complexity migrations to achieve early successes. Key benefits include cost efficiency with up to 80% savings on Oracle Database licensing, minimized downtime through live sync capabilities, and complete control over the migration process. EDB Postgres AI introduces the Hybrid Control Plane for cloud-like agility and unified governance, enabling real-time insights up to 30 times faster than traditional methods. The AI Accelerator allows for the creation of AI applications with five lines of SQL. Recent enhancements from PostgreSQL 17 include a 100x faster sub-transaction cache, support for incremental backups, and improvements in logical replication, which aid in executing complex queries, high availability, and disaster recovery.

Tech Optimizer

December 13, 2024

Understand the benefits of physical replication in Amazon RDS for PostgreSQL Blue/Green Deployments

Amazon RDS introduced Blue/Green Deployments for PostgreSQL in October 2023 to facilitate major version upgrades with minimal downtime. This feature uses community logical replication for data replication between different PostgreSQL versions, but has limitations such as inability to replicate schema changes. Physical replication is now an option for RDS Blue/Green Deployments, addressing constraints of logical replication and benefiting minor version upgrades, schema changes, and storage adjustments. Physical replication is the default method for blue/green deployments unless a major version upgrade is specified, in which case logical replication is used. Key benefits of physical replication include broad engine version support, no dependency on static parameter configurations, scalability for multi-tenant databases, simplified storage adjustments, no constraints on DDL operations, faster switchover without sequence sync delays, and efficient handling of large objects. To implement physical replication, users should ensure their deployment does not require a major version upgrade and follow specific steps for creating a blue/green deployment. For PostgreSQL versions 15.3 and lower, blue/green deployments default to physical replication, as major version upgrades are not supported. Users can perform a major version upgrade by first creating a deployment with a minor version upgrade, enabling logical replication, and then executing the switchover.

Tech Optimizer

December 10, 2024

EnterpriseDB expands AI development features in PostgreSQL

EnterpriseDB Corp. has enhanced its EDB Postgres AI platform with hybrid features for artificial intelligence development and processing. The company reports that 56% of enterprises are using hybrid infrastructures that combine public and private cloud resources. New features allow rapid application deployment and management from a single control point, extending cloud-managed experiences as packaged software for seamless connectivity between on-premises and cloud databases. The platform now includes an AI Accelerator that integrates EDB’s Pipelines Extension and pgvector, enabling efficient testing and deployment of generative AI applications. This setup allows users to manage data capture, embedding generation, and retrieval within the PostgreSQL environment without leaving it. Additionally, multimodel data management features provide near real-time online analytical processing, offering 18 times greater cost efficiency and 30 times faster performance than baseline PostgreSQL. This approach supports various data types, reducing the need for multiple specialty databases. An Analytics Accelerator has also been introduced, allowing users to query columnar data in external object storage using SQL and supporting tiered tables functions for efficient data movement without performance loss.

Tech Optimizer

November 23, 2024

What happens when you install Postgres 16 on Ubuntu

- The installation of PostgreSQL 16 on Ubuntu using the apt package manager involves several key steps: updating package lists, resolving dependencies, downloading packages, unpacking and installing, setting up the Postgres service, and cleaning up. - The command `sudo apt update` fetches the latest package lists from configured repositories. - The command `sudo apt install postgresql-16` triggers apt to search for the package and resolve dependencies. - Necessary .deb package files are downloaded to the `/var/cache/apt/archives/` directory. - Each downloaded .deb package is extracted, files are placed in appropriate locations, pre-installation and post-installation scripts are run, and the dpkg package database is updated. - Post-installation scripts create a dedicated postgres Linux user and group, initialize the data directory, generate default configuration files, set up system catalog tables, configure PostgreSQL to start automatically on boot, and start the PostgreSQL server. - The PostgreSQL server is configured to start automatically on system boot using `systemctl enable postgresql.service`. - The PostgreSQL server is started with `systemctl start postgresql.service`. - After installation, several processes are actively running: postmaster, checkpointer, background writer, walwriter, autovacuum launcher, stats collector, and logical replication launcher. - PostgreSQL is ready to accept client connections through the local socket and will start automatically on future system reboots.

Tech Optimizer

November 16, 2024

MySQL vs. PostgreSQL: Compare popular open source databases

MySQL and PostgreSQL are prominent open-source SQL databases, each with distinct strengths. MySQL, now partially commercial under Oracle, has a fork called MariaDB to maintain its open-source status. MySQL supports various storage engines like InnoDB, MyISAM, and in-memory, with InnoDB being optimal for data integrity and speed. PostgreSQL, developed by Michael Stonebraker and open-sourced in 1996, is known for handling complex queries and large datasets effectively. MySQL complies with the SQL:2003 standard, while PostgreSQL is fully compliant with SQL:2011 and offers extensive custom data types. MySQL's replication includes asynchronous, semisynchronous, and group replication, whereas PostgreSQL features streaming, synchronous, logical replication, and hot standby. Both databases provide robust security features, including various authentication methods, role-based access control, and encryption. MySQL is often chosen for web and transactional applications, while PostgreSQL is preferred for data-intensive and analytical systems. David "Walker" Aldridge is a programmer with 40 years of experience in multiple languages, a systems admin, and an infosec blue team member with an interest in retrocomputing.