PostgreSQL (commonly known as Postgres) is a well-known, solid as a rock database. And yes, from time to time it craves for an upgrade too. Today we will learn something about PostgreSQL Windows upgrade from version 9.6 to 12.
KEEP IN MIND: As always, backup your database before starting any upgrade procedure.
The psql upgrade is based on pg_upgrade command. The first step is to install Postgres version 12 and it is available for download on the PostgreSQL official site (visit PostgreSQL). Install the downloaded file and set a familiar Postgres password (in our example we will use the following password: postgres). Since psql 9.6 is also installed on your server, the port on which version 12 will listen will be 5433.
Before we start with the upgrade, stop both databases. Afterward, open command prompt and type: SET PASSWORD=your password. In our example, it would look like this: SET PASSWORD= postgres.
The next step would be data migration. To perform this task, use the following in your command line:
"c:\Program Files\PostgreSQL\12\bin\pg_upgrade.exe" -d "c:\Program Files\PostgreSQL\9.6\data" -D"c:\Program Files\PostgreSQL\12\data" -b "c:\Program Files\PostgreSQL\9.6\bin" -B "c:\Program Files\PostgreSQL\12\bin" -U postgres
Let's see what these arguments represent:
- -d is data directory for the older psql, version 9.6
- -D is data directory for the newer version 12
- -b is binary folder for the older psql
- -B is binary folder for the newer version 12 psql
- -U postgres would be the user which is performing the migration.
When the migration is complete, you can analyze your databases. In your new postgresql.conf file (located in c:\Program Files\PostgreSQL\12) change port to 5432 and start the Postgres 12 service. Do not forget to disable Postgres 9.6 service and, if you are sure that everything went smoothly, delete Postgres 9.6 as well.And that is all it takes to have your databases running on PostgreSQL 12!
