Learn effective strategies for database migrations including version upgrades, platform migrations, data transformations, and zero-downtime approaches.
Learn effective strategies for database migrations including version upgrades, platform migrations, data transformations, and zero-downtime approaches.
Database migrations are complex operations that require careful planning and execution.
Whether you're upgrading to a new version, transitioning between different database
platforms, or restructuring your data model, effective migration strategies are
essential to minimize risks and downtime.
This lesson covers comprehensive approaches to database migration, focusing on PostgreSQL
but with principles applicable to other database systems. You'll learn strategies for
version upgrades, cross-platform migrations, data model changes, and techniques to
achieve near-zero downtime.
Version Upgrades
Cross-Platform
Data Transformations
Zero-Downtime
Major and minor version upgrades in PostgreSQL require different approaches. While minor
version upgrades are usually straightforward, major version upgrades require more careful planning.
Approaches for PostgreSQL Version Upgrades
pg_upgrade (Fast Upgrade)
pg_upgrade is a utility that performs a fast upgrade between PostgreSQL major versions
by reusing database files rather than transferring data with dump and reload.
Advantages:
Much faster than dump and restore, especially for large databases
Minimal disk space requirements (when using hard links)
Preserves most database objects and settings
Limitations:
Only works for consecutive major versions (e.g., 14 to 15, not 13 to 15)
Requires database to be offline during the upgrade
Some extensions might need manual handling
Example Usage:
Logical Replication
Logical replication uses a publisher-subscriber model to replicate data changes from
one PostgreSQL instance to another, allowing for cross-version migrations with minimal downtime.
Advantages:
Works across multiple major versions
Minimal downtime (only during final switchover)
Can filter tables or data during migration
Allows schema changes during migration
Limitations:
More complex to set up and monitor
Requires more disk space during migration
DDL changes need separate handling
Some object types aren't replicated (sequences, large objects)
Implementation Steps:
Set up a new PostgreSQL instance with the target version
Create identical schema on the target database
Configure logical replication publications on the source
Set up subscriptions on the target
Monitor replication until synchronization is complete
Switch applications to the new database
Example Configuration:
Dump and Restore
The most traditional upgrade method using pg_dump to export data and pg_restore to
import it into the new version. Though slower, it's the most reliable method.
Advantages:
Works across any PostgreSQL version
Most reliable method with fewest compatibility issues
Provides opportunity to clean up database during migration
Allows restructuring schema during migration
Limitations:
Requires significant downtime for large databases
Requires approximately twice the disk space
Slower than other methods
Example Usage:
Best Practices for Version Upgrades
Test thoroughly:
Always perform test migrations with a copy of your production
data before upgrading the actual production database.
Check extension compatibility:
Ensure all extensions are compatible with the
new PostgreSQL version or have upgrade paths available.
Review release notes:
Check for deprecated features, behavior changes, or removed
functionality in the target PostgreSQL version.
Validate applications:
Test applications against the new database version to
identify any compatibility issues.
Maintain a rollback plan:
Document the steps needed to revert to the previous
version if problems occur.
Perform post-upgrade validation:
Run VACUUM ANALYZE after upgrading to update
statistics, and verify that all functions, views, and procedures work correctly.
1# Basic usage
2pg_upgrade \
3 --old-datadir=/var/lib/postgresql/14/main \
4 --new-datadir=/var/lib/postgresql/15/main \
5 --old-bindir=/usr/lib/postgresql/14/bin \
6 --new-bindir=/usr/lib/postgresql/15/bin \
7 --old-options='-c config_file=/etc/postgresql/14/main/postgresql.conf' \
8 --new-options='-c config_file=/etc/postgresql/15/main/postgresql.conf'
9
10# Check-only mode (no actual upgrade)
11pg_upgrade --check ... (same options as above)
12
13# Using link mode (faster, less disk space)
14pg_upgrade --link ... (same options as above)1-- On source database (old version)
2CREATE PUBLICATION my_publication FOR ALL TABLES;
3
4-- On target database (new version)
5CREATE SUBSCRIPTION my_subscription
6 CONNECTION 'host=source_db dbname=mydb user=repl_user password=secret'
7 PUBLICATION my_publication;
8
9-- Monitor replication lag
10SELECT * FROM pg_stat_replication;
11SELECT * FROM pg_replication_slots;1# Custom format dump (most flexible)
2pg_dump -Fc -v -f database_backup.dump dbname
3
4# Restore to new version
5pg_restore -v -d new_dbname database_backup.dump
6
7# Plain SQL dump (for smaller databases)
8pg_dump -v -f database_backup.sql dbname
9
10# Restore SQL dump
11psql -d new_dbname -f database_backup.sql
12
13# Parallel dump and restore for large databases
14pg_dump -Fd -j 8 -v -f dump_directory dbname
15pg_restore -j 8 -v -d new_dbname dump_directoryMigrating between different database platforms (e.g., MySQL to PostgreSQL, Oracle to PostgreSQL)
presents unique challenges beyond version upgrades. These migrations require careful handling
of SQL dialect differences, data type mappings, and platform-specific features.
Key Challenges in Cross-Platform Migrations
SQL Dialect Differences
Each database platform has its own SQL dialect with unique syntax, functions, and operators.
Migrations must address these differences.
Common Dialect Differences:
Date/time functions and formats
String manipulation functions
Transaction handling
Aggregate and window functions
Procedural language syntax
Example Dialect Conversions (MySQL to PostgreSQL):
Migration Tools:
pgLoader - Specialized tool for migrating to PostgreSQL
AWS Schema Conversion Tool - For cloud migrations
Ora2Pg - Oracle to PostgreSQL migration
Data Type Mapping
Different database systems support different data types with varying
semantics, precision, and behavior. Proper data type mapping is critical for successful migrations.
Common Data Type Mapping Challenges:
Integer types and their size limits
Floating-point precision differences
Date/time type handling and time zones
BLOB/TEXT types and size limits
Boolean representation
Specialized types (UUID, JSONB, Geometry)
Example Data Type Mapping (Oracle to PostgreSQL):
Validation Strategy:
After migration, validate data integrity by
comparing row counts, sums of numerical columns, and samples of converted data between
source and target databases.
Platform-Specific Features
Each database platform offers unique features that may not have direct equivalents
in the target platform. These require rethinking implementation strategies.
Features Requiring Special Handling:
Stored procedures and functions
Triggers and event handling
Materialized views
Partitioning schemes
Indexing strategies (e.g., MySQL FULLTEXT vs. PostgreSQL tsvector)
Auto-increment implementation
Role and permission systems
Example: Replacing Oracle Packages in PostgreSQL
Migration Strategy:
Identify critical platform-specific features early,
develop alternative approaches for the target platform, and test thoroughly before migrating production data.
Cross-Platform Migration Process
Assessment and planning:
Evaluate source database size, complexity, and dependencies
Identify critical features that need special handling
Develop a detailed migration strategy and timeline
Set up test environments for both source and target platforms
Schema conversion:
Convert table definitions with appropriate data type mappings
Recreate indexes, constraints, and foreign keys
Transform stored procedures, functions, and triggers
Establish equivalent security and permission models
Data migration:
Extract data from source database in manageable chunks
Transform data to match target platform requirements
Load data into target database while preserving integrity
Validate migrated data through consistency checks
Application adaptation:
Update connection strings and configuration
Modify SQL queries to work with the target platform's dialect
Update ORM mappings and database-specific code
Implement and test replacement solutions for platform-specific features
Validation and cutover:
Perform thorough testing of the migrated system
Conduct performance benchmarking
Execute a practice cutover to identify potential issues
Plan the production cutover with a detailed rollback strategy
1-- MySQL
2SELECT CONCAT(first_name, ' ', last_name) AS full_name,
3 DATE_FORMAT(birth_date, '%Y-%m-%d') AS formatted_date
4FROM users
5WHERE IFNULL(status, 'active') = 'active'
6LIMIT 10;
7
8-- Converted to PostgreSQL
9SELECT first_name || ' ' || last_name AS full_name,
10 TO_CHAR(birth_date, 'YYYY-MM-DD') AS formatted_date
11FROM users
12WHERE COALESCE(status, 'active') = 'active'
13LIMIT 10;1-- Oracle data types
2CREATE TABLE example (
3 id NUMBER(10) PRIMARY KEY,
4 name VARCHAR2(100) NOT NULL,
5 description CLOB,
6 binary_data BLOB,
7 created_date DATE,
8 status NUMBER(1),
9 price NUMBER(10,2)
10);
11
12-- PostgreSQL equivalent
13CREATE TABLE example (
14 id INTEGER PRIMARY KEY,
15 name VARCHAR(100) NOT NULL,
16 description TEXT,
17 binary_data BYTEA,
18 created_date TIMESTAMP,
19 status BOOLEAN,
20 price NUMERIC(10,2)
21);1-- Oracle package
2CREATE OR REPLACE PACKAGE user_mgmt AS
3 PROCEDURE create_user(p_username VARCHAR2, p_email VARCHAR2);
4 FUNCTION get_user_id(p_username VARCHAR2) RETURN NUMBER;
5END user_mgmt;
6
7-- PostgreSQL schema-based approach
8CREATE SCHEMA user_mgmt;
9
10CREATE OR REPLACE FUNCTION user_mgmt.create_user(
11 p_username VARCHAR, p_email VARCHAR
12) RETURNS VOID AS $$
13BEGIN
14 -- Implementation
15END;
16$$ LANGUAGE plpgsql;
17
18CREATE OR REPLACE FUNCTION user_mgmt.get_user_id(
19 p_username VARCHAR
20) RETURNS INTEGER AS $$
21BEGIN
22 -- Implementation
23END;
24$$ LANGUAGE plpgsql;Database migrations often involve data model changes, including schema evolution,
data restructuring, and normalization/denormalization. These transformations require
careful planning and execution to maintain data integrity and application functionality.
Types of Data Model Transformations
Schema Evolution
Schema evolution involves modifying the database structure while preserving existing
data. Common changes include adding/removing columns, changing column types,
and modifying constraints.
Key Techniques:
Schema migrations with version control
Forward and backward compatibility
Safe column type conversions
Staged constraint additions
Example: Adding a NOT NULL Column Safely
Tools for Schema Evolution:
Flyway - Version-controlled database migrations
Liquibase - Database schema change management
Sqitch - Database change management application
Django/Rails migrations - Framework-integrated migration tools
Data Restructuring
Data restructuring involves transforming data between different representations while
preserving its semantic meaning. This includes splitting/merging tables, changing data
formats, and implementing new data models.
Common Data Restructuring Scenarios:
Splitting one table into multiple tables (normalization)
Combining multiple tables into one (denormalization)
Converting between different data representations (e.g., JSON to relational)
Implementing slowly changing dimensions
Moving from EAV (Entity-Attribute-Value) to relational structure
Example: Splitting a Table
Migration Strategy:
Implement data restructuring in phases, maintaining
dual-write capability during transition periods to ensure consistency between old and new structures.
Data Validation and Cleansing
Data migrations provide an opportunity to validate, cleanse, and enhance data quality.
This process involves identifying and correcting data inconsistencies, filling gaps,
and enforcing new data integrity rules.
Data Validation Techniques:
Format validation (e.g., email, phone numbers)
Reference data integrity checks
Business rule validation
Deduplication and merger of records
Data enrichment from external sources
Example: Data Cleansing During Migration
Best Practices:
Generate detailed reports of all data changes during
cleansing, maintain an audit trail of transformations, and create validation scripts
to verify the results after migration.
Data Transformation Implementation Strategies
Implementing data transformations requires choosing the right approach based on database
size, complexity, and downtime constraints.
In-place transformations:
Modify the existing database directly, suitable
for simple changes or when working with smaller datasets.
Staging-based transformations:
Create a staging area to prepare and validate
data before moving it to the target structure.
ETL-based transformations:
Use extract-transform-load processes for complex
transformations, especially with large datasets.
Dual-write approaches:
Write to both old and new structures during a transition
period to ensure consistency and enable gradual cutover.
Incremental transformations:
Break large transformations into smaller, manageable
steps that can be applied separately.
Data Transformation Case Study: E-commerce Platform
An e-commerce platform needed to transform its product catalog from a simple relational model
to a more sophisticated structure supporting multiple variants, attributes, and hierarchical categories.
Created a complete design of the new data model with relationships
Developed a staging area to transform and validate data
Implemented an ETL process to populate the new structure
Built a dual-write mechanism for the transition period
Created data validation scripts to ensure consistency
Implemented application changes to work with both models
Performed incremental cutover by functionality
Key Success Factors:
Comprehensive data validation at each step
Maintaining backward compatibility during the transition
Incremental approach to minimize risk
Thorough testing with production-like data volumes
1-- Step 1: Add the column allowing nulls
2ALTER TABLE users ADD COLUMN email VARCHAR(255);
3
4-- Step 2: Update existing records
5UPDATE users SET email = '[email protected]' WHERE email IS NULL;
6
7-- Step 3: Add the NOT NULL constraint
8ALTER TABLE users ALTER COLUMN email SET NOT NULL;
9
10-- Step 4: Add validation constraint
11ALTER TABLE users ADD CONSTRAINT valid_email
12 CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$');1-- Original table with mixed concerns
2CREATE TABLE orders (
3 id SERIAL PRIMARY KEY,
4 customer_name VARCHAR(100),
5 customer_email VARCHAR(255),
6 customer_address TEXT,
7 product_id INTEGER,
8 product_name VARCHAR(100),
9 quantity INTEGER,
10 price NUMERIC(10,2),
11 order_date TIMESTAMP
12);
13
14-- Step 1: Create the new tables
15CREATE TABLE customers (
16 id SERIAL PRIMARY KEY,
17 name VARCHAR(100),
18 email VARCHAR(255),
19 address TEXT
20);
21
22CREATE TABLE products (
23 id SERIAL PRIMARY KEY,
24 name VARCHAR(100),
25 price NUMERIC(10,2)
26);
27
28CREATE TABLE orders_new (
29 id SERIAL PRIMARY KEY,
30 customer_id INTEGER REFERENCES customers(id),
31 order_date TIMESTAMP
32);
33
34CREATE TABLE order_items (
35 id SERIAL PRIMARY KEY,
36 order_id INTEGER REFERENCES orders_new(id),
37 product_id INTEGER REFERENCES products(id),
38 quantity INTEGER,
39 unit_price NUMERIC(10,2)
40);
41
42-- Step 2: Migrate the data
43INSERT INTO customers (name, email, address)
44SELECT DISTINCT customer_name, customer_email, customer_address
45FROM orders;
46
47INSERT INTO products (name, price)
48SELECT DISTINCT product_name, price
49FROM orders;
50
51-- Step 3: Link the data
52INSERT INTO orders_new (customer_id, order_date)
53SELECT
54 (SELECT c.id FROM customers c WHERE c.name = o.customer_name AND c.email = o.customer_email),
55 o.order_date
56FROM orders o;
57
58INSERT INTO order_items (order_id, product_id, quantity, unit_price)
59SELECT
60 (SELECT on.id FROM orders_new on
61 JOIN customers c ON on.customer_id = c.id
62 WHERE c.name = o.customer_name AND c.email = o.customer_email AND on.order_date = o.order_date),
63 (SELECT p.id FROM products p WHERE p.name = o.product_name),
64 o.quantity,
65 o.price
66FROM orders o;1-- Identify data quality issues
2SELECT
3 COUNT(*) AS total_records,
4 COUNT(*) FILTER (WHERE email IS NULL) AS missing_email,
5 COUNT(*) FILTER (WHERE email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$') AS invalid_email,
6 COUNT(*) FILTER (WHERE phone IS NULL) AS missing_phone,
7 COUNT(*) FILTER (WHERE phone !~ '^\\+?[0-9\\s\\-\\(\\)]{10,20}$') AS invalid_phone
8FROM customers;
9
10-- Clean up data during migration
11INSERT INTO customers_new (
12 id, name, email, phone, address, created_at
13)
14SELECT
15 id,
16 TRIM(name), -- Remove leading/trailing whitespace
17 CASE
18 WHEN email IS NULL THEN '[email protected]'
19 WHEN email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$' THEN 'invalid-' || email
20 ELSE LOWER(email) -- Normalize to lowercase
21 END AS email,
22 CASE
23 WHEN phone IS NULL THEN NULL
24 WHEN phone !~ '^\\+?[0-9\\s\\-\\(\\)]{10,20}$' THEN NULL
25 ELSE REGEXP_REPLACE(phone, '[\\s\\-\\(\\)]', '', 'g') -- Standardize format
26 END AS phone,
27 COALESCE(address, 'Unknown') AS address,
28 COALESCE(created_at, CURRENT_TIMESTAMP) AS created_at
29FROM customers;
30
31-- Create a report of cleansed records
32CREATE TABLE data_cleansing_report AS
33SELECT
34 id,
35 name,
36 CASE WHEN customers.email != customers_new.email
37 THEN 'Changed from: ' || customers.email || ' to: ' || customers_new.email
38 ELSE 'Unchanged'
39 END AS email_changes,
40 CASE WHEN customers.phone != customers_new.phone
41 THEN 'Changed from: ' || COALESCE(customers.phone, 'NULL') || ' to: ' || COALESCE(customers_new.phone, 'NULL')
42 ELSE 'Unchanged'
43 END AS phone_changes
44FROM customers
45JOIN customers_new USING (id)
46WHERE customers.email != customers_new.email
47 OR customers.phone != customers_new.phone
48 OR (customers.phone IS NULL AND customers_new.phone IS NOT NULL)
49 OR (customers.phone IS NOT NULL AND customers_new.phone IS NULL);Zero-downtime (or near-zero-downtime) migrations are essential for high-availability
systems where traditional maintenance windows are not feasible. These techniques minimize
or eliminate service disruptions during database migrations.
Foundations of Zero-Downtime Migrations
Logical Replication with Switchover
Logical replication maintains a continuously updated copy of the source database
on the target system. When the target is fully synchronized, a brief switchover
redirects traffic to the new database.
Implementation Steps:
Set up logical replication from source to target database
Monitor replication lag and ensure synchronization
Prepare applications for the switchover (connection string updates)
During a brief maintenance window (seconds to minutes):
Stop writes to the source database
Wait for replication to fully catch up
Switch application connections to the target database
Resume normal operations
PostgreSQL Logical Replication Example:
Advantages:
Minimal downtime (seconds), works across major versions
and platforms, allows verification before switching.
Challenges:
More complex setup, requires sufficient disk space for
both databases, DDL changes need special handling.
Blue-Green Deployment
Blue-green deployment involves maintaining two identical production environments (blue and green).
While one environment is active, the other is updated. The traffic is switched when the inactive
environment is ready.
Database Migration Process:
Set up a new database environment (green) alongside the existing one (blue)
Implement data synchronization mechanism between blue and green
Apply schema changes and upgrades to the green environment
Test the green environment thoroughly
Switch traffic to the green environment
Keep the blue environment as a fallback option
Implementation Diagram:
┌───────────────┐ ┌───────────────┐ │ │ │ │ │ BLUE │ │ GREEN │ │ Database │◄──────►│ Database │ │ (Current) │ Sync │ (New) │ │ │ │ │ └───────┬───────┘ └───────┬───────┘ │ │ │ │ ┌───────▼───────┐ ┌───────▼───────┐ │ Load │ │ Load │ │ Balancer │───┐ │ Balancer │ │ (Active) │ │ │ (Standby) │ └───────────────┘ │ └───────────────┘ ▲ │ ▲ │ │ │ │ ┌───────▼───────┐ │ └────┤ ├────┘ │ Application │ │ │ └───────────────┘
Traffic Switching Strategies:
DNS switch - Update DNS records to point to the new environment
Load balancer reconfiguration - Redirect traffic at the load balancer
Connection proxy - Use a database proxy to redirect connections
Application config update - Change connection settings in applications
Advantages:
Complete testing before switchover, easy rollback if issues occur,
works with any database platform.
Challenges:
Requires duplicate infrastructure, data synchronization complexity,
potential data drift during extended migrations.
Dual-Write Pattern
The dual-write pattern involves writing data to both old and new database systems
simultaneously during the migration period. This ensures data consistency while
allowing for gradual cutover of read operations.
Implementation Approaches:
Application-level dual-write:
Modify the application to write to both databases
Database triggers:
Use database triggers to replicate writes to the target database
Change data capture (CDC):
Capture changes from transaction logs and apply them to the target
Message queue:
Use a queue to ensure writes are applied to both databases
Example: Application-Level Dual Write
Migration Process:
Implement dual-write mechanism
Validate data consistency between old and new databases
Gradually shift read operations to the new database
Monitor for any issues or inconsistencies
Once confidence is established, complete the migration by:
Directing all reads to the new database
Removing dual-write mechanism
Decommissioning the old database
Advantages:
Gradual transition with minimal risk, continuous operation,
excellent fallback options.
Challenges:
Increased application complexity, potential performance impact,
handling data inconsistencies.
Expand-Contract Pattern
Also known as the "parallel change" pattern, this approach evolves the database schema
in stages without breaking existing functionality. It's particularly useful for complex
schema changes within the same database.
Implementation Phases:
Expand phase:
Add new tables/columns while keeping the old structure
Migration phase:
Populate new structures and maintain both in parallel
Contract phase:
Remove old structures when no longer needed
Example: Table Splitting with Expand-Contract
Advantages:
Gradual changes with no downtime, backward compatibility
throughout the process, clear phases with verification steps.
Challenges:
Temporary data duplication, increased complexity during
transition, coordination with application changes.
Zero-Downtime Migration Best Practices
Comprehensive testing:
Test the entire migration process in environments
that closely mirror production, including realistic data volumes and traffic patterns.
Monitoring and observability:
Implement thorough monitoring of database
performance, replication lag, data consistency, and application behavior during migration.
Rollback plan:
Develop and test a detailed rollback strategy that can be
executed quickly if issues arise during the migration.
Incremental approach:
Break complex migrations into smaller, manageable
steps that can be implemented and verified independently.
Feature flags:
Use feature flags to control the migration process and
quickly disable new functionality if problems occur.
Data validation:
Implement automated validation to ensure data integrity
and consistency throughout the migration process.
Communication plan:
Establish clear communication channels and protocols
for the migration team, including decision-making authority for critical situations.
Zero-Downtime Migration Case Study: Financial Services Platform
A financial services platform needed to migrate its core transaction database to a new version
while maintaining 24/7 availability for global customers. Any downtime would result in
significant financial and reputational damage.
Implemented logical replication between the old and new database instances
Used database proxy to manage connection routing transparently
Developed comprehensive monitoring for replication lag and data consistency
Implemented automated validation checks for transaction integrity
Created a phased cutover approach:
Read-only operations moved to new database first
Write operations moved in batches by transaction type
Final switchover performed during lowest traffic period
Successful migration with zero customer-facing downtime
Total switchover time reduced to under 10 seconds
No data loss or integrity issues
Improved performance on the new platform
1-- On source database
2CREATE PUBLICATION migration_pub FOR ALL TABLES;
3
4-- On target database
5CREATE SUBSCRIPTION migration_sub
6 CONNECTION 'host=source_db dbname=mydb user=repl_user password=secret'
7 PUBLICATION migration_pub;
8
9-- Monitor replication lag
10SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;
11
12-- Prepare for switchover
13-- 1. Set source database read-only
14SET default_transaction_read_only = on; -- On source DB
15
16-- 2. Check that replication is complete
17SELECT count(*) FROM pg_stat_replication; -- Should show active connection
18SELECT * FROM pg_replication_slots; -- Should show active slot
19
20-- 3. Perform application switchover
21-- Update connection strings in application configuration
22
23-- 4. Post-switchover
24-- On source database once switchover is confirmed
25DROP PUBLICATION migration_pub;
26
27-- On target database once switchover is confirmed
28DROP SUBSCRIPTION migration_sub;1// Service abstraction for dual-write pattern
2class UserRepository {
3 constructor(
4 private oldDbClient: DatabaseClient,
5 private newDbClient: DatabaseClient,
6 private featureFlags: FeatureFlags
7 ) {}
8
9 async createUser(userData: UserData): Promise<User> {
10 // Always write to the old database
11 const oldDbUser = await this.oldDbClient.users.create(userData);
12
13 // Conditionally write to the new database
14 if (this.featureFlags.isDualWriteEnabled()) {
15 try {
16 await this.newDbClient.users.create({
17 ...userData,
18 id: oldDbUser.id // Ensure ID consistency
19 });
20 } catch (error) {
21 // Log error but don't fail the operation
22 console.error('Failed to write to new database:', error);
23 // Consider alerting or retrying mechanism
24 }
25 }
26
27 return oldDbUser;
28 }
29
30 async getUser(userId: string): Promise<User> {
31 // Read strategy based on feature flags
32 if (this.featureFlags.isReadFromNewDbEnabled()) {
33 try {
34 return await this.newDbClient.users.findUnique({ where: { id: userId } });
35 } catch (error) {
36 // Fallback to old database on error
37 console.warn('Failed to read from new database, falling back:', error);
38 }
39 }
40
41 // Default to reading from old database
42 return await this.oldDbClient.users.findUnique({ where: { id: userId } });
43 }
44
45 // Similar patterns for update and delete operations
46}1-- Initial schema
2CREATE TABLE users (
3 id SERIAL PRIMARY KEY,
4 username VARCHAR(50) NOT NULL,
5 email VARCHAR(100) NOT NULL,
6 address TEXT,
7 city VARCHAR(100),
8 state VARCHAR(2),
9 zip VARCHAR(10),
10 created_at TIMESTAMP DEFAULT NOW()
11);
12
13-- EXPAND phase: Create new tables
14CREATE TABLE user_profiles (
15 id SERIAL PRIMARY KEY,
16 user_id INTEGER UNIQUE REFERENCES users(id),
17 address TEXT,
18 city VARCHAR(100),
19 state VARCHAR(2),
20 zip VARCHAR(10)
21);
22
23-- Create trigger to keep both tables in sync (new writes)
24CREATE OR REPLACE FUNCTION sync_user_address() RETURNS TRIGGER AS $$
25BEGIN
26 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
27 -- When inserting or updating users table
28 INSERT INTO user_profiles (user_id, address, city, state, zip)
29 VALUES (NEW.id, NEW.address, NEW.city, NEW.state, NEW.zip)
30 ON CONFLICT (user_id)
31 DO UPDATE SET
32 address = NEW.address,
33 city = NEW.city,
34 state = NEW.state,
35 zip = NEW.zip;
36 END IF;
37 RETURN NEW;
38END;
39$$ LANGUAGE plpgsql;
40
41CREATE TRIGGER users_address_sync
42AFTER INSERT OR UPDATE ON users
43FOR EACH ROW
44EXECUTE FUNCTION sync_user_address();
45
46-- MIGRATION phase: Populate new table with existing data
47INSERT INTO user_profiles (user_id, address, city, state, zip)
48SELECT id, address, city, state, zip FROM users
49ON CONFLICT (user_id) DO NOTHING;
50
51-- Update application code to read from user_profiles
52-- Test thoroughly in production environment
53
54-- CONTRACT phase: Remove old columns (after all code is updated)
55ALTER TABLE users DROP COLUMN address;
56ALTER TABLE users DROP COLUMN city;
57ALTER TABLE users DROP COLUMN state;
58ALTER TABLE users DROP COLUMN zip;
59
60-- Remove trigger when no longer needed
61DROP TRIGGER users_address_sync ON users;
62DROP FUNCTION sync_user_address;Regardless of the specific migration scenario, certain considerations are universal for
ensuring successful database migrations:
Performance impact:
Understand how the migration process will affect
database performance, particularly for production systems handling live traffic.
Resource requirements:
Plan for additional CPU, memory, network bandwidth,
and disk space needed during the migration process.
Data integrity:
Implement comprehensive validation procedures to ensure
data consistency and correctness throughout the migration.
Security considerations:
Maintain proper security controls during the
migration, including encryption of data in transit and appropriate access controls.
Documentation:
Maintain detailed documentation of the migration plan,
execution steps, configuration changes, and validation procedures.
User communication:
For customer-facing systems, develop a communication
plan to inform users about potential impacts, even if downtime is minimal.
Database migration strategies require careful planning, appropriate techniques, and thorough
testing to ensure successful outcomes. The approaches covered in this lesson provide a
comprehensive toolkit for handling various migration scenarios:
Version upgrades using pg_upgrade, logical replication, or dump and restore
Cross-platform migrations addressing SQL dialect differences and data type mapping
Data model transformations including schema evolution and restructuring
Zero-downtime techniques like blue-green deployment and dual-write patterns
By selecting the right strategy for your specific requirements and following established
best practices, you can minimize risks and ensure smooth, successful database migrations
that support your organization's evolving needs.
Your progress is saved in this browser, on this device only.