PostgreSQL upgrades and support

The choice between the three documented routes comes down to how long the database can stay offline and how much change you can tolerate during the move. Dump and restore is the simplest path. pg_dumpall writes every object and every row out to a logical dump, and the new cluster rebuilds all of it, so downtime grows with data volume and object count. pg_upgrade converts the system catalogs and reuses the existing data files, which is usually much faster for large clusters, but the source cluster must be stopped while it runs. Logical replication is the third option. Before you choose a route, read the upgrading documentation for your exact source and target major versions, because the steps and limits differ between them.

Logical replication is the route to evaluate when the outage window must be short. It works between different major versions, so the new cluster can stay in sync with the old one until you are ready to cut over. The PostgreSQL documentation describes the resulting downtime as only several seconds. That figure depends on how quickly your team can stop writes, let the subscriber catch up, sequence the values and repoint applications, so the cutover must be rehearsed on a copy of production before it is trusted. Treat the several seconds as a documented possibility to test, not as a promise about your environment.

Logical replication has prerequisites that need checking before you choose it. Every table you replicate needs a replication identity, which is normally a primary key or an explicit REPLICA IDENTITY setting, because updates and deletes cannot be applied on the subscriber without one. Sequence values are not carried across by replication and must be synchronized at cutover. Schema changes made during the sync are not replicated automatically, so any DDL applied to the publisher must be applied to the subscriber in the same order. Finally, review your schema for object types that logical replication does not carry, such as large objects, and plan a separate migration step for each one. Run this inventory against the target version and record the results in your plan.

PostgreSQL 18 changes the pg_upgrade picture. The release announcement states that pg_upgrade retains planner statistics across major upgrades, so the new cluster does not start with missing statistics that can produce poor query plans immediately after cutover. The same release adds parallel jobs and a --swap option. Confirm which pg_upgrade flags your target version supports by reading the reference page for that exact release, and do not copy flags from older runbooks. Test the chosen flags on a copy of production. Parallel jobs shorten the window only if your storage and CPU can sustain the extra load without slowing the upgrade itself.

The recurring cost of running PostgreSQL after an upgrade is mostly people and infrastructure. PostgreSQL is released under the PostgreSQL License, a permissive open source license, so the community edition has no license fee to renew each year. Support is a separate line item. You can buy it from a partner, staff it internally, or combine both. Before comparing options, separate one-time costs, such as the upgrade project and the test environments built for it, from costs that recur every year, because the recurring share determines how a support model affects your budget over the next several years.

The main recurring items are hosting, release work and DBA time. Standby servers and a non-production environment that is close enough to production to rehearse changes run continuously, whether they sit in your cloud accounts or consume capacity in your data center. PostgreSQL publishes minor releases at least once every three months. Each one needs a test run, a change record, a restart window and rollback notes, so the staff time for that cadence repeats every quarter. In a quote, ask how many release windows are included, who performs the rehearsal, and who signs off before production changes.

Next, decide how DBA coverage will be bought and kept. A retainer reserves a set amount of expert time and defined response expectations each month. It suits teams whose database changes arrive steadily and who need a known contact for incidents. Ad hoc support is billed as work arrives. It can work for a stable estate, but response depends on availability when the problem occurs. Internal skills carry a cost either way. Someone must read release notes, check extension compatibility, review authentication changes such as those introduced in PostgreSQL 18, and run backup restore tests. Count the hours current staff already spend and identify the gaps, especially in failover tooling and recovery testing.

Several factors move the recurring load up or down. The number of clusters matters most, because each cluster adds standby servers, backups, monitoring, restore tests and release work. Change frequency matters next: schema changes, new extensions and application releases all require regression checks. Compliance evidence is the third driver. If an assessor expects access reviews, patch records or restore results on a schedule, someone must collect and retain them. DeployOpen prepares that evidence, but independent auditors decide whether it meets the standard. Also note the end-of-life dates. PostgreSQL 14 is supported until November 12, 2026, so the next major upgrade will change the recurring workload when it happens.

DeployOpen does not publish prices. A quote follows from the written scope, so the scope should state the clusters and environments covered, the release windows per year, the support model and response expectations, the evidence to be produced and who holds on-call responsibility. Ask for the quote with recurring and one-time items shown separately. That layout lets you compare a partner retainer with the internal hours and hosting you would otherwise carry, using the same assumptions for both.

Your team's inputs decide whether a PostgreSQL upgrade stays on schedule. Before work starts, we need read access to every cluster in scope, including the postgresql.conf and pg_hba.conf files, the current major and minor version of each server, and the replication topology if standbys exist. We also need an inventory of installed extensions with their versions, and a list of client applications, drivers, ORMs and connection poolers that connect to each database. Extensions and drivers are the most common source of surprises during a major upgrade, because a server can start on the new version while a contrib module or an older client library does not.

A non-production environment matters more than most teams expect. It should run the same operating system family and PostgreSQL major version as production, and it should hold a copy of production data that is recent enough to reflect real table sizes, index bloat and workload shape. A schema-only copy will not show how long pg_upgrade takes to relink objects, or how long a logical replication initial sync runs. Confirm with your data owners whether a masked copy is acceptable under your data handling rules, and confirm who can approve its refresh date.

Name three people before scheduling. The maintenance window owner sets the start and end times, notifies dependent teams and holds the change record. The rollback decision-maker is the person who can say "go back to the old cluster" during the window, and that person must be reachable for the whole window, not only at the start. The named application owner runs the regression checks against their own workflows after the upgrade and signs off that the application behaves correctly. If one person holds two of these roles, write that down, because it changes who has to be available.

Ask for backup and restore evidence before the upgrade, not after. That means the most recent base backup and WAL archive coverage for each cluster, plus the result of a restore test into a separate host. If your recovery objectives depend on point-in-time recovery, confirm that the archive covers the upgrade window. The rollback plan for pg_upgrade and for logical replication both depend on the old cluster remaining intact until the new one is accepted, so confirm the disk space and retention settings that will keep it available.

Settle four decisions early, because each one changes the plan and the effort. The first is the target version. Compare the end-of-life dates in the PostgreSQL versioning policy against your own support calendar, and remember that PostgreSQL 18 retains planner statistics across major upgrades, which affects post-upgrade performance checks. The second is the method: pg_upgrade is simpler but needs a restart window, while logical replication can reduce downtime to a few seconds at the cost of more setup and per-table validation. The third is downtime tolerance, stated as a number of minutes per cluster and agreed by the business owner. The fourth is the support hours for the window, including who is on call after it closes. Each sign-off should be recorded in writing by the people named above.