Many applications start with a PostgreSQL database running directly on a developer’s machine. This setup works well during the early stages of development because it is simple, fast, and easy to reset.
As the application grows, teams may want to test their PostgreSQL workloads in an environment that is closer to their target database platform. AlloyDB Express can be used as another development option while keeping the application’s PostgreSQL-based development workflow.
Moving from an existing local PostgreSQL setup should not be treated as simply changing the database connection string. Database versions, extensions, roles, schemas, configuration, test data, migrations, and application assumptions can all affect the transition.
This article explains a practical approach for moving an existing local PostgreSQL development setup to AlloyDB Express while keeping the migration controlled and easy to validate.
Understand the Current PostgreSQL Setup
Before moving anything, document how the existing local PostgreSQL environment is being used.
Start by identifying:
- PostgreSQL version
- Database names
- Schemas
- Tables and indexes
- Extensions
- Database users and roles
- Permissions
- Stored procedures and functions
- Triggers
- Views
- Sequences
- Seed data
- Migration tools
- Connection settings
- Application environment variables
- Test dependencies
For example, an application might currently use:
Application
|
v
Local PostgreSQL
|
+-- application database
+-- public schema
+-- extensions
+-- test data
The goal is to understand exactly what the application expects from PostgreSQL before changing the database environment.
Compare the PostgreSQL Environments
AlloyDB Express is PostgreSQL-compatible, but compatibility does not mean that every local setup will behave identically.
Check the PostgreSQL version used by the existing application and compare it with the version supported by the AlloyDB Express environment you plan to use.
Pay particular attention to:
- SQL features used by the application
- Extensions
- Data types
- Index types
- Functions
- Authentication
- Database permissions
- Migration behavior
- Connection configuration
- Development tooling
A small compatibility checklist can prevent problems later.
Area | Existing Local PostgreSQL | AlloyDB Express |
|---|
Database engine | PostgreSQL | PostgreSQL-compatible AlloyDB environment |
Application SQL | Existing queries | Must be validated |
Extensions | Current extensions | Verify availability |
Users | Local roles | Recreate required roles |
Schema | Existing schema | Import and validate |
Data | Local development data | Export and import |
Migrations | Existing migration history | Run and validate |
Connection | Local connection | New connection configuration |
Do not assume that an application is compatible simply because both environments use PostgreSQL-compatible SQL.
Create a Migration Inventory
Create a simple inventory before moving the database.
For example:
Database: orders_dev
Schemas:
public
reporting
Tables:
customers
orders
order_items
payments
Extensions:
extension_a
extension_b
Functions:
calculate_order_total()
Roles:
application_user
reporting_user
The exact list will depend on the application.
This inventory becomes a checklist during migration. Every important database component should have a clear answer: migrate it, recreate it, replace it, or remove it.
Export the Existing Database
The next step is to create a backup or export of the existing development database.
For a PostgreSQL environment, logical database export tools can be used to capture schema and data.
A typical workflow might look like this:
pg_dump -h localhost -U postgres -d orders_dev -f orders_dev.sql
For a separate data export, the process can be split into schema and data.
pg_dump \
-h localhost \
-U postgres \
-d orders_dev \
--schema-only \
-f schema.sql
And:
pg_dump \
-h localhost \
-U postgres \
-d orders_dev \
--data-only \
-f data.sql
The exact options should be adjusted to match the database structure and migration strategy.
The important point is to keep a known-good copy of the existing local database before making changes.
Review the Export Before Importing
Do not immediately import the exported database into the new environment.
Inspect the export and look for:
- Unsupported extensions
- Role-specific statements
- Ownership statements
- Environment-specific configuration
- Hard-coded database names
- Development-only objects
- Unexpected schemas
- Large test data
- Functions or procedures that need validation
This is especially important when moving from a personal local PostgreSQL installation to a shared or team-oriented development environment.
Some objects belong to the local PostgreSQL installation rather than the application itself.
Prepare the AlloyDB Express Database
Create the target development database according to the application's requirements.
The database structure should be planned before importing application data.
For example:
AlloyDB Express
|
+-- application database
|
+-- application schema
|
+-- application tables
|
+-- indexes
|
+-- functions
|
+-- development data
Avoid copying unnecessary databases from the old machine.
The target environment should contain what the application actually needs.
Move the Schema First
A useful migration approach is to move the schema before moving the complete dataset.
For example:
Step 1: Create target database
Step 2: Import schema
Step 3: Validate tables
Step 4: Validate indexes
Step 5: Validate functions and triggers
Step 6: Import development data
Step 7: Run application tests
This makes troubleshooting easier.
If the schema import fails, there is no need to investigate application data at the same time.
Move Development Data
After validating the schema, import the development data.
Development databases often contain:
- Test customers
- Sample orders
- Fake users
- Test transactions
- Seed configuration
- Reference data
Do not automatically move every record from the old environment.
A large local database may contain temporary data that is no longer useful.
For example:
Old local database
|
+-- Required reference data
+-- Useful test records
+-- Old test data
+-- Temporary debugging data
Only migrate the data that provides value to the development workflow.
Update the Application Connection
The biggest application change is usually the database connection configuration.
Instead of hard-coding the local PostgreSQL connection, keep the database connection in environment-specific configuration.
For example:
DATABASE_HOST=target-host
DATABASE_PORT=5432
DATABASE_NAME=orders_dev
DATABASE_USER=application_user
DATABASE_PASSWORD=development-password
The application should read these values from its existing configuration mechanism.
The application code should not need to know whether the database is running on a developer's machine or in the new development environment.
This separation makes future environment changes easier.
Keep the Old Configuration Temporarily
Do not immediately delete the old PostgreSQL setup.
For the first migration cycle, keep both configurations available.
For example:
Local PostgreSQL
|
+---- Existing development configuration
AlloyDB Express
|
+---- New development configuration
This provides a simple rollback path.
If a migration issue is discovered, developers can temporarily return to the previous environment while the problem is investigated.
Once the new setup has been validated, the old configuration can be removed from the team's normal workflow.
Update Database Migration Configuration
Most modern applications use a migration system to manage database changes.
Examples include:
- Entity Framework migrations
- Flyway
- Liquibase
- Rails migrations
- Custom SQL migration systems
- Application-specific migration frameworks
The migration configuration should point to the new database.
For example, a .NET application may read its database connection from configuration:
var connectionString =
configuration.GetConnectionString("DefaultConnection");
The migration system should then use the same environment-specific connection configuration.
This avoids maintaining separate database definitions inside application code.
Validate Existing Migrations
Do not assume that an existing database is healthy simply because the application works locally.
Run the migration process against a clean AlloyDB Express database.
A useful validation process is:
Create empty database
|
v
Run all migrations
|
v
Verify schema
|
v
Load seed data
|
v
Run application tests
This can reveal migration problems that were hidden in the existing local database.
For example, a developer may have manually changed a table during development. The local database works, but the migration files do not contain that change.
A clean migration exposes the problem.
Validate Extensions Carefully
Extensions deserve special attention during a PostgreSQL migration.
An application may depend on an extension for:
- Specialized data types
- Full-text search
- Cryptographic operations
- UUID generation
- Vector operations
- Additional indexing capabilities
Do not migrate extension statements blindly.
First confirm that the required extension is supported and behaves as expected in the target environment.
If an extension is unavailable, identify an alternative before switching the application.
Check Database Roles and Permissions
Local PostgreSQL environments often use broad permissions because the database is running on a developer's machine.
A production-like development environment should use more realistic permissions.
For example:
application_user
SELECT
INSERT
UPDATE
DELETE
migration_user
Schema modification
reporting_user
SELECT
The exact permission model depends on the application.
Avoid using an administrative database account for normal application traffic.
Testing with realistic permissions can expose permission problems before deployment.
Validate Application Behavior
After the migration, test the application instead of only checking whether the database connection works.
Test important operations such as:
- User registration
- Login
- Creating records
- Updating records
- Deleting records
- Searching
- Reporting
- Transactions
- Background jobs
- Database migrations
Also test application startup.
A successful connection does not prove that every SQL operation behaves as expected.
Test Transaction Behavior
Transactions should be part of the migration validation.
For example:
Begin transaction
|
+-- Insert order
+-- Insert order items
+-- Update inventory
|
Commit
Then test failure handling:
Begin transaction
|
+-- Insert order
+-- Insert order items
+-- Simulated failure
|
Rollback
Verify that the application does not leave partially written data.
This is especially important for applications with multiple related database operations.
Check Query Behavior
Run the application's important queries against the new environment.
Pay attention to:
- Slow queries
- Missing indexes
- Unexpected execution behavior
- Large result sets
- Sorting
- Joins
- Aggregations
- Pagination
- Connection usage
The migration should not be considered complete simply because all tables were copied successfully.
The database needs to support the application's actual workload.
Update Seed and Test Data
Seed scripts often contain assumptions about the local environment.
For example, a seed script may assume:
localhost
default database
local administrator
fixed database user
Remove environment-specific assumptions.
A better approach is to make seed data depend only on application configuration and database schema.
This allows the same seed process to work across different development environments.
Update Team Documentation
A database migration affects every developer who works on the project.
Document:
- How to start AlloyDB Express
- How to configure the application
- Required environment variables
- How to run migrations
- How to load seed data
- How to reset development data
- How to troubleshoot connection problems
- How to switch back to the old setup during migration
A technically correct migration can still fail at the team level if developers do not know how to use the new environment.
Common Migration Problems
Connection Works but Application Fails
Check the database name, user, password, port, SSL requirements, and connection settings.
Also verify that the application is actually reading the new configuration.
Tables Exist but Queries Fail
Compare the schema carefully.
Check:
- Column types
- Constraints
- Indexes
- Functions
- Extensions
- Views
- Triggers
A table existing does not mean the complete database behavior was migrated.
Migration Fails on a Clean Database
This usually indicates that the migration files do not fully describe the current local database.
Compare the local schema with the schema produced by the migration process.
Extension Errors Appear
Check whether the extension is supported in the target environment and whether the application depends on extension-specific behavior.
Do not remove an extension without checking the application code and queries that use it.
Data Looks Correct but Tests Fail
Check environment-specific assumptions in:
- Seed scripts
- Test fixtures
- Stored procedures
- Database configuration
- Permissions
- Default values
Tests can reveal differences that are not visible during manual inspection.
Common Mistakes to Avoid
Changing the Connection String and Stopping There
A database migration is more than a connection change.
Schema, extensions, roles, migrations, data, and application behavior must also be validated.
Migrating All Local Data
Old debugging data and temporary records can make the new environment unnecessarily large and difficult to maintain.
Move only useful development data.
Ignoring Database Roles
A local administrator account may hide permission problems.
Use application-specific credentials where practical.
Skipping Clean Migration Testing
If the application only works because the existing local database has been manually modified, the migration process will eventually expose the problem.
Test the database from an empty state.
Removing the Old Setup Too Early
Keep the previous local environment available until the new workflow has been validated by the team.
Best Practices
- Inventory the existing PostgreSQL environment before migration.
- Compare PostgreSQL versions and supported features.
- Export the existing schema and data before making changes.
- Validate extensions before importing them.
- Move the schema before the full dataset.
- Keep connection settings outside application code.
- Test migrations against a clean database.
- Recreate required roles with appropriate permissions.
- Keep the old environment temporarily as a rollback option.
- Test real application workflows after migration.
- Document the new developer setup.
- Automate migrations and seed data where practical.
- Treat development database configuration as environment-specific.
- Remove obsolete local data instead of migrating everything.
Advantages of Moving to AlloyDB Express
- Developers can work with a database environment closer to the intended AlloyDB workflow.
- Database configuration becomes more consistent across development environments.
- The migration process exposes hidden dependencies in the existing PostgreSQL setup.
- Teams can standardize database initialization and testing.
- Application database configuration becomes easier to separate from application code.
Disadvantages and Considerations
- Migration requires initial setup and validation work.
- Existing PostgreSQL extensions may require compatibility checks.
- Team members need to update their local development workflow.
- Development data needs to be exported and imported carefully.
- Existing scripts may contain assumptions about local PostgreSQL.
- Maintaining two database environments during the transition adds temporary complexity.
When Should You Move?
Moving to AlloyDB Express makes sense when the team wants to validate an existing PostgreSQL application against an AlloyDB-oriented development environment or standardize development around a more consistent database setup.
It may not be necessary for every small application. If the existing local PostgreSQL environment already provides everything the team needs, moving databases can introduce unnecessary complexity.
The decision should be based on the application's database requirements and the team's development workflow.
Summary
Moving an existing local PostgreSQL setup to AlloyDB Express is a migration project, not just a connection-string change.
Start by inventorying the current database, comparing versions and features, and creating a reliable backup. Move the schema first, validate extensions and roles, and then migrate useful development data.
Next, update the application's environment-specific connection settings and verify the migration system against a clean database. Test real application workflows, transactions, queries, permissions, and seed data before making the new environment the team's default.
Keeping the old PostgreSQL setup temporarily provides a useful rollback option during the transition. Once the new environment is stable and documented, the team can gradually retire the previous local setup.
A controlled migration gives developers a cleaner database workflow while reducing the risk of discovering compatibility or configuration problems later in the development lifecycle.
Join the conversation! Your thoughts help the community grow.