A PostgreSQL major version upgrade is not something that should be validated only after production has already been changed.
The application may continue to start normally while database behavior changes underneath it. Queries can choose different execution plans, extensions may behave differently, configuration defaults can change, and application code that worked against the previous PostgreSQL version may expose problems after the upgrade.
This is why a production database should be tested before the actual migration.
The goal is not simply to prove that PostgreSQL starts on the new version. The real goal is to determine whether the application, database schema, queries, extensions, jobs, connections, and operational processes continue to work correctly.
A reliable upgrade test should reproduce production conditions as closely as practical without putting the production system at risk.
Why Test Before a PostgreSQL Major Upgrade?
PostgreSQL major upgrades can introduce changes that are not visible through a simple backup and restore test.
A database may contain:
- millions of rows
- large indexes
- materialized views
- custom extensions
- stored procedures
- scheduled jobs
- application-specific SQL
- connection pooling
- replication
- reporting workloads
- background workers
A development database rarely represents all of these conditions.
For example, a query may run in 20 milliseconds against a development database containing 10,000 rows.
The same query against production may involve 100 million rows and choose a very different execution plan after the upgrade.
Testing therefore needs to cover both database compatibility and application behavior.
What Should Be Tested?
A useful PostgreSQL upgrade test should cover several layers.
Production Database
|
+-- Schema
+-- Data
+-- Extensions
+-- Queries
+-- Indexes
+-- Functions
+-- Application
+-- Background Jobs
+-- Monitoring
+-- BackupsEach layer can expose a different problem.
For example:
| Area | What to Check |
|---|---|
| Schema | Tables, indexes, constraints, functions |
| Data | Row counts, important records, data types |
| Extensions | Version and compatibility |
| Queries | Errors and execution plans |
| Application | CRUD operations and transactions |
| Jobs | Background processing and scheduled tasks |
| Connections | Pooling and connection behavior |
| Backups | Backup and restore process |
| Monitoring | Metrics, alerts, and logs |
| Performance | CPU, memory, I/O, latency |
Testing all of these gives a much better picture than checking only whether the database starts.
Start With a Production Inventory
Before creating the test environment, document the current production database.
Collect information such as:
SELECT version();List databases:
SELECT datname
FROM pg_database
WHERE datistemplate = false;List extensions:
SELECT extname, extversion
FROM pg_extension
ORDER BY extname;List schemas:
SELECT schema_name
FROM information_schema.schemata
ORDER BY schema_name;Also identify:
- database size
- largest tables
- largest indexes
- active connections
- long-running queries
- replication configuration
- scheduled jobs
- custom functions
- triggers
- foreign data wrappers
- logical replication objects
The inventory becomes the baseline for the migration test.
Measure the Current Production Baseline
A test environment is much more useful when you know what normal production behavior looks like.
Record important metrics before the upgrade.
Examples include:
Average API latency
Database CPU usage
Database memory usage
Connection count
Transaction rate
Slow query count
Read/write throughput
Replication lag
Cache hit ratio
Disk usageFor important queries, record execution plans as well.
For example:
EXPLAIN (ANALYZE, BUFFERS)
SELECT
customer_id,
created_at,
total_amount
FROM orders
WHERE customer_id = 1001
ORDER BY created_at DESC
LIMIT 50;Save the output.
After upgrading the test database, execute the same query again.
The goal is not to make every execution plan identical.
The goal is to identify meaningful changes.
Create a Realistic Test Environment
The best upgrade test uses a database that resembles production.
A practical setup is:
Production PostgreSQL
|
| Backup / Clone
v
Test PostgreSQL
|
| Upgrade
v
Target PostgreSQL VersionThe test database should contain enough real-world structure and data to expose the problems you care about.
If copying production data is restricted because of privacy or compliance requirements, create a representative dataset with the same characteristics.
For example, if production contains:
200 million orders
15 million customers
3 million active subscriptionstesting against 50,000 synthetic orders will not provide enough confidence for query and index behavior.
Protect Sensitive Production Data
A production copy may contain:
- customer information
- email addresses
- phone numbers
- payment-related data
- internal identifiers
- access information
Do not copy sensitive production data into an environment without considering access controls and data protection requirements.
When practical, anonymize or pseudonymize sensitive fields.
For example:
Production:
[email protected]
Test:
[email protected]The structure and distribution should remain realistic enough for testing while reducing exposure.
Test the Upgrade Path
Do not assume that the final target version is the only thing worth testing.
The actual upgrade method matters.
For example, if the migration will use a dump and restore process, test that exact approach.
If the production environment will use a logical replication migration, test the replication process.
If the organization uses a supported in-place upgrade tool, validate that workflow in the test environment.
The test should answer:
Can we perform the same migration process
without unexpected application or database failures?A successful restore using one method does not automatically prove that another migration method will work.
Test Schema Compatibility
Start by validating the schema.
Compare:
- tables
- columns
- data types
- constraints
- indexes
- sequences
- views
- materialized views
- functions
- procedures
- triggers
- permissions
A useful approach is to generate schema definitions from both environments and compare them.
For example:
pg_dump --schema-only production_db > production-schema.sqland:
pg_dump --schema-only upgraded_db > upgraded-schema.sqlThen compare the resulting files.
The exact differences depend on the upgrade method, so not every difference represents a problem.
The purpose is to identify unexpected changes.
Test PostgreSQL Extensions
Extensions deserve special attention during a major upgrade.
Check the production list:
SELECT extname, extversion
FROM pg_extension
ORDER BY extname;Then verify that the same extensions are available and supported in the target PostgreSQL environment.
Common examples include:
PostGIS
pgcrypto
uuid-ossp
pg_stat_statements
citextThe actual extension list depends on the application.
Do not assume that an extension installed in production will automatically be available in the target environment.
An application may start successfully while a feature that depends on an extension fails later.
Test Application Connections
After upgrading the test database, connect the real application to it.
This is one of the most important parts of the process.
Test:
Application startup
Database connection
Authentication
Connection pooling
Transactions
Reads
Writes
Updates
Deletes
Background jobs
Reports
Scheduled tasksFor a .NET application using Npgsql, for example, the connection configuration should be tested against the upgraded database.
var connectionString =
"Host=postgres-test;Database=orders;Username=app;Password=***";
await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync();
await using var command =
new NpgsqlCommand("SELECT 1", connection);
var result = await command.ExecuteScalarAsync();A successful SELECT 1 only proves connectivity.
It does not prove application compatibility.
Real application operations must be tested.
Test Transactions
Transaction behavior should be included in the upgrade test.
For example:
await using var transaction =
await connection.BeginTransactionAsync();
try
{
// Perform related database operations.
await transaction.CommitAsync();
}
catch
{
await transaction.RollbackAsync();
throw;
}Test scenarios involving:
- commit
- rollback
- concurrent transactions
- deadlocks
- transaction timeouts
- retry behavior
- connection failures
A database upgrade should not change application assumptions about transaction behavior without being detected during testing.
Test Your Important Queries
Do not try to test every query manually.
Instead, identify the queries that matter most to production.
Good candidates include:
- authentication queries
- customer lookup
- order creation
- payment-related queries
- search queries
- dashboard queries
- reporting queries
- batch processing
- queue consumers
- scheduled jobs
Record their baseline behavior.
For example:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT 20;Then compare the same query on the upgraded database.
Look for changes in:
- execution time
- row estimates
- index usage
- sequential scans
- joins
- sort operations
- buffer usage
- temporary file usage
This is especially important because a query can remain logically correct while becoming slower.
Check Execution Plan Changes
Suppose the old database uses:
Index Scanwhile the upgraded database chooses:
Seq ScanThat is not automatically wrong.
The optimizer may have determined that the sequential scan is cheaper.
The important question is whether the decision makes sense for the actual workload.
For example:
Before:
Index Scan
Execution Time: 15 ms
After:
Seq Scan
Execution Time: 2,400 msThat difference requires investigation.
Potential causes include:
- changed statistics
- stale statistics
- different cost estimates
- changed data distribution
- missing indexes
- changed configuration
- query planner changes
Do not fix a plan difference simply because it looks different. Fix it when the resulting behavior is problematic.
Refresh Statistics
After restoring or migrating a database, statistics may need attention.
Run:
ANALYZE;For a specific table:
Join the conversation! Your thoughts help the community grow.