PostgreSQL
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.
First, use 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 postgres user.
On Debian/Ubuntu the user must be authenticated via the OS user. So we need to sudo to the postgres user.
$ 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.
