Moving a production database to a new PostgreSQL major version should not be treated as a simple installation task. The database may start normally, existing tables may be available, and basic application queries may work, but that does not prove that the application is ready for the new version.
PostgreSQL 19 can introduce changes in query planning, indexing, extensions, text processing, replication, and other database behavior. These changes can be completely harmless for one application and important for another. The difference usually comes from how the application uses PostgreSQL.
A good upgrade test gives you a safe place to discover these differences before production traffic reaches the new database. The goal is not to test every possible SQL statement. The goal is to test the database features, queries, workloads, and application behavior that your production system actually depends on.
This article explains a practical way to test PostgreSQL 19 before moving a production database to it.
Why Test PostgreSQL 19 Before Production?
A production database contains more than tables and rows. It also contains indexes, constraints, extensions, statistics, permissions, functions, triggers, background jobs, application connections, and workloads.
A major PostgreSQL upgrade can affect some of these areas.
For example, an application might have a search feature that depends on pg_trgm. Another application might depend heavily on complex joins and query planning. A third application might use logical replication or database functions.
The upgrade itself may complete successfully in all three applications, but the testing requirements are different.
This is why copying the production database into a test environment and running realistic application workloads is much more useful than simply checking whether PostgreSQL 19 starts.
What Does PostgreSQL Upgrade Testing Mean?
PostgreSQL upgrade testing means running the new PostgreSQL version against a representative copy of your database and checking whether your application continues to work correctly.
The word representative is important.
A test database should contain enough realistic data and database objects to reproduce the important behavior of production.
You should try to preserve things such as:
- Tables and schemas
- Indexes
- Foreign keys
- Views
- Functions
- Triggers
- Extensions
- Important configuration
- Representative data
- Application queries
You do not necessarily need an exact copy of every production component. You need enough of the environment to test the areas that can affect your application.
Start by Recording the Current PostgreSQL Version
Before changing anything, record the current database environment.
You can check the PostgreSQL version with:
SELECT version();
You can also get the server version number with:
SHOW server_version;
Next, check the installed extensions:
SELECT
extname,
extversion
FROM pg_extension
ORDER BY extname;
These results give you a simple baseline.
When the PostgreSQL 19 test environment is ready, you can compare the two environments and investigate differences.
Create a Test Copy of the Database
The safest approach is to test against a copy of the production database rather than directly changing production.
The exact process depends on how your PostgreSQL environment is managed.
For example, a logical backup can be created with:
pg_dump -Fc -d production_db -f production_db.dump
The custom format can then be restored into a test database:
createdb postgres19_test
pg_restore \
-d postgres19_test \
production_db.dump
The exact commands may need to be adjusted for your environment, authentication method, network configuration, and database hosting platform.
The important idea is that the test database should contain the application structure and representative data needed for upgrade testing.
Do Not Test Only With an Empty Database
One of the most common upgrade-testing mistakes is using a small or empty database.
Database behavior depends on data.
Consider this query:
SELECT *
FROM orders
WHERE customer_id = 1001
ORDER BY created_at DESC;
With a few hundred rows, almost any reasonable execution strategy may appear fast.
With millions of orders, the same query can become much more sensitive to indexes, statistics, sorting, and query planning.
This is why production-like data is important.
If you cannot use an actual production copy because of privacy or security requirements, create a representative dataset with similar table sizes and data distributions.
Check Your Important Queries
After restoring the test database, identify the queries that matter most to your application.
These are usually queries that are:
- Executed frequently
- Used by important user operations
- Accessing large tables
- Joining several tables
- Performing aggregation
- Sorting large result sets
- Searching text
- Used by background workers
- Used by reports
You can inspect a query with:
EXPLAIN (ANALYZE, BUFFERS)
SELECT
o.id,
o.created_at,
o.total
FROM orders o
WHERE o.customer_id = 1001
ORDER BY o.created_at DESC;
The purpose is not to prove that PostgreSQL 19 produces the exact same execution plan.
The purpose is to determine whether the new plan still performs correctly.
Compare Query Plans Carefully
A query plan describes how PostgreSQL intends to execute a SQL statement.
For example, PostgreSQL may decide to use an index scan:
Index Scan
or it may choose a sequential scan:
Seq Scan
Neither choice is automatically good or bad.
The correct choice depends on the amount of data, available indexes, statistics, and query conditions.
After upgrading, you might see a different plan. Before treating that as a problem, compare the actual execution time and other relevant information.
For example:
EXPLAIN (ANALYZE, BUFFERS)
SELECT
c.id,
c.name,
COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
GROUP BY c.id, c.name;
Look at the total execution time, row estimates, actual rows, joins, scans, sorts, and buffer activity.
If the plan changed but the query performs well, there may be nothing to fix.
Test Extensions Separately
Extensions should be part of the PostgreSQL 19 upgrade test.
Check the extensions in your test database:
SELECT
extname,
extversion
FROM pg_extension
ORDER BY extname;
Then identify extensions that your application actually depends on.
For example:
SELECT
extname,
extversion
FROM pg_extension
WHERE extname IN (
'pg_trgm',
'pg_stat_statements',
'fuzzystrmatch'
);
Do not only check whether the extension is installed.
Test the functionality that uses the extension.
For example, if your application uses pg_trgm:
SELECT id, name
FROM customers
WHERE name ILIKE '%rahul%';
Check that the search returns the expected results and that important search queries still perform properly.
Test Full-Text Search
If your application uses PostgreSQL full-text search, include it in your upgrade testing.
For example:
SELECT id, title
FROM documents
WHERE search_vector @@ plainto_tsquery('database upgrade');
Test normal searches as well as searches containing words and characters that are common in your application's real data.
If search vectors are stored or indexed, make sure the corresponding indexes and stored values are also checked.
This is important because a search feature can appear to work while producing different results after an upgrade.
Test Application Connections
Database testing should also include the way your application connects to PostgreSQL.
Check:
- Connection strings
- Authentication
- Connection pooling
- SSL settings
- Connection limits
- Timeouts
- Application startup
- Database migrations
For example, an ASP.NET Core application may have a PostgreSQL connection string similar to:
Host=localhost;Port=5432;Database=postgres19_test;Username=app_user;Password=your_password
The test environment should use the same general connection behavior as production while pointing to the test PostgreSQL 19 database.
Do not copy production secrets into a test environment unnecessarily. Use test credentials and appropriate access controls.
Test Database Migrations
Your application may use Entity Framework Core migrations or another database migration system.
Run your normal migration process against the PostgreSQL 19 test database.
For an Entity Framework Core application, this may look like:
dotnet ef database update
The important part is checking whether your application's normal migration process works with the new PostgreSQL version.
Pay attention to:
- New tables
- Column changes
- Index creation
- Foreign keys
- Default values
- Generated columns
- Database functions
- Migration scripts
A database upgrade and an application schema migration are related but different operations. Both should be tested.
Test Background Jobs and Scheduled Work
Many applications perform database work outside normal HTTP requests.
Examples include:
- Email processing
- Report generation
- Data imports
- Cleanup jobs
- Billing processes
- Queue consumers
- Scheduled database maintenance
These workloads should also be tested against PostgreSQL 19.
A background job may run a query that developers rarely execute manually. That query could behave differently after the upgrade even though the main application appears healthy.
This is why testing only the application's web pages is not enough.
Test Transactions and Concurrency
Production databases rarely execute one query at a time.
Multiple requests can access the same tables simultaneously.
Test important transaction workflows such as:
Begin transaction
Read data
Update data
Commit transaction
Also test workflows that involve multiple users or concurrent background jobs.
Look for:
- Lock waits
- Deadlocks
- Transaction failures
- Timeout problems
- Unexpected rollbacks
You do not need to reproduce every possible production workload, but important concurrent workflows should be tested before the upgrade.
Check Database Statistics
PostgreSQL uses statistics to help the query planner make decisions.
You can update statistics with:
ANALYZE;
For a specific table:
ANALYZE orders;
If a query behaves differently after the upgrade, make sure the test database has reasonable statistics before concluding that PostgreSQL 19 itself caused the problem.
Testing with an incorrectly analyzed database can produce misleading results.
Build a Simple Upgrade Test Checklist
A practical PostgreSQL 19 test can follow this sequence.
1. Prepare the Test Database
Restore a representative database copy into PostgreSQL 19.
2. Verify Database Objects
Check tables, indexes, constraints, views, functions, triggers, and extensions.
3. Test Application Connectivity
Point the application or a test instance of the application to PostgreSQL 19.
4. Test Important SQL
Run critical queries and compare their behavior with the existing PostgreSQL environment.
5. Test Search
Validate pg_trgm and full-text search if your application uses them.
6. Test Migrations
Run the application's normal database migration process.
7. Test Background Work
Run scheduled jobs, workers, imports, reports, and other database-heavy processes.
8. Test Performance
Compare important query execution times and query plans.
9. Test Recovery
Make sure your backup and recovery process works with the new environment.
10. Record Problems
Create a list of differences instead of fixing issues immediately without understanding them.
This makes it easier to separate actual PostgreSQL compatibility problems from normal differences in query planning.
Common Mistakes During Upgrade Testing
Testing Only Whether the Server Starts
A successful PostgreSQL startup is only the first check.
Your application still needs to be tested.
Using a Tiny Dataset
Small datasets can hide query-planning and performance problems.
Use representative data whenever possible.
Ignoring Extensions
An application may depend heavily on an extension without developers realizing how much database functionality comes from it.
Always review installed extensions.
Comparing Only Query Results
Two versions can return exactly the same rows while using very different execution plans.
Check performance as well as correctness.
Testing Only the Main Application
Background jobs, reports, imports, and scheduled tasks can contain important SQL.
Include them in the test.
Making Multiple Changes at Once
If you upgrade PostgreSQL, change indexes, rewrite queries, and modify application code at the same time, it becomes difficult to identify the actual cause of a problem.
Test the PostgreSQL upgrade as an isolated change as much as possible.
What Should You Measure Before Moving to Production?
Before production migration, collect a small set of useful baseline measurements.
For important queries, record:
- Execution time
- Query plan
- Rows returned
- Important indexes used
- Buffer activity
- Error rate
For the application, record:
- Request latency
- Error rate
- Database connection errors
- Background job failures
- Search behavior
- Transaction failures
You do not need a complicated performance-testing platform for every application. Even a well-maintained list of important queries and workflows can provide useful evidence when comparing the old and new PostgreSQL environments.
Advantages and Disadvantages
| Area |
Advantages |
Considerations |
| Safety |
Problems can be found before production |
Requires preparation and testing time |
| Performance |
Query plans can be compared before migration |
Results depend on how realistic the test data is |
| Compatibility |
Extensions and application behavior can be validated |
Some production conditions are difficult to reproduce |
| Troubleshooting |
Problems can be isolated before production |
Large applications may have many dependencies |
| Rollout |
Gives the team more confidence in the upgrade |
Requires a clear migration and recovery plan |
| Application testing |
Database and application can be tested together |
Test environments require additional resources |
Best Practices
Keep the PostgreSQL 19 test environment as close to production as practical.
Use representative data, recreate important indexes, install the required extensions, and configure the application to use the same general connection pattern.
Create a list of critical queries before starting the upgrade test. This prevents the testing process from becoming an unstructured collection of random SQL checks.
Do not focus only on errors. Performance changes are equally important. A query that previously took 100 milliseconds but now takes several seconds deserves investigation even if it returns exactly the correct result.
Test the complete application workflow, including background jobs and database migrations.
Finally, document every issue discovered during testing and its resolution. This gives the production migration team a clear checklist instead of relying on memory during the deployment.
Summary
Testing PostgreSQL 19 before moving a production database is one of the most useful steps in a major PostgreSQL upgrade.
The goal is not to prove that every query will have exactly the same execution plan. The goal is to confirm that the application still produces correct results, performs well, and works correctly with the database features it depends on.
Start by creating a representative PostgreSQL 19 test environment. Restore realistic data, check extensions and database objects, test important queries, compare execution plans, validate search functionality, run application migrations, and test background jobs.
Also test transactions, database connections, and important concurrent workflows. These areas can expose problems that simple application testing may miss.
A successful PostgreSQL 19 upgrade should therefore be based on evidence from testing rather than simply assuming that an existing application will behave the same way on the new database version.
Join the conversation! Your thoughts help the community grow.