Simple PostgreSQL Upgrades in Development
Upgrading a production PostgreSQL server can be a complex and delicate process. Downtime must be minimized. Writes must be prevented while the upgrade is in progress to avoid data loss. For a large database, excessive storage usage during the upgrade may be a problem. Many other guides describe how to handle these and related issues.
But in a development environment downtime is not an issue, writes can be avoided simply by not using the database, and database size is typically fairly small. For these cases performing the same upgrade process as production can be overkill. There is a simpler way.
This guide is not platform specific, but assumes that PostgreSQL was installed with a package manager or you otherwise know how to install and remove it.
pg_dumpall to backup the entire database cluster.
pg_dumpall will write all the SQL necessary to recreate all users and databases in the cluster.
`$ pg_dumpall > pg95.sql`
Next, remove your existing version of PostgreSQL. This could be done with
apt remove for a Debian/Ubuntu or
brew uninstall of MacOS. Strictly speaking, it's not necessary to remove the old version first.
brew will try to do an upgrade and
apt will install multiple versions of PostgreSQL side-by-side. But additional edge cases and complications can arise in these cases.
Once the old PostgreSQL server is uninstalled, install the new version. In Debian/Ubuntu this would be something like
apt install postgresql-9.6 (prefer installing a specific version to the
postgresql metapackage) or
brew install postgresql in MacOS.
Lastly, we need to run
psql -f pg95.sql. However, the newly installed version of PostgreSQL will only have the
postgres user. This means we need to connect to PostgreSQL server as the
On Debian/Ubuntu the user must be authenticated via the OS user. So we need to
sudo to the
$ sudo -u postgres psql -f pg95.sql
On MacOS with homebrew the user can be specificied via argument to the
psql command without any authentication.
$ psql -U postgres -f pg95.sql
You can read more details in the documentation.