In this blog post, I’ll guide you on how to perform a migration of databases (and upgrade during that process since the major version is different) from PostgreSQL 10.4 to the newest (11.5 at time of writing) on Windows. These guidelines are applicable for 9.x to 10.x, possibly even from 9.x to 11.x directly (though I haven’t tested it).

Quick introduction

In my case, I had two VM’s at my disposal

• 1st (older one) is currently running an older version of PostgreSQL (in my case it was 10.4), which also runs few additional services, and we want to take off a bit of load from that VM
• 2nd (running on newer hardware) just has fresh Windows installed, on which you need to migrate the DB’s to.

In my case, I had to migrate 620~ish databases, the largest being around 25GB, so manual dumping and restoring through PgAdmin was not an option. Instead of creating one large backup file using pg_dumpall which would be easier, I had a bit different approach, to speed the migration up (explained bit more in step Dumping databases contents).

Process

Here is an general overview of the steps required to perform a successful DB migration:

• obviously, you need to install and configure PostgreSQL to the new machine per your liking (probably you’ll want to go with the newest version). For configuring the PostgreSQL, we’re often using PgTune.
• to avoid new data being written to the old server, reconfigure it to allow connections ONLY from the new machine (pg_hba.config).
• set up roles on a new server - you can do this manually, or by dumping all users through PSQL command (command line). I’m doing this using the latter way (more info below).
• dump database names.
• dump databases.
• restore databases.
• verify behavior with new server.

With the exception of reconfiguring the old machine (step 2 above), all steps below should be done on new (target) machine which should have newer PostgreSQL version installed at this point.

Dumping the roles

As mentioned, I’m using command-line script for dumping the roles on the old server, and it looks like this:

SET PGPASSWORD=yourPassword
"C:\PostgreSQL\11\bin\pg_dumpall" --roles-only -h yourHost --username=yourUsername --file=D:\BackupDBPostgres10\roles.backup

Open generated file (roles.backup), and you’ll see all roles from the old server. Now run the generated scripts through PgAdmin while connected to new PostgreSQL server. This way you’ll have the same roles and passwords on the new server as they were on old one.

Dumping database names

For automation purposes in the upcoming steps we need to retrieve database names from the old server, using the following command-line script:

SET PGPASSWORD=yourPassword
"C:\PostgreSQL\11\bin\psql" -h yourOldHost --username=yourUsername --dbname=postgres -c "SELECT datname FROM pg_database where datname not in ('template0', 'template1', 'postgres') ORDER BY datname ASC" --output=D:\BackupDBPostgres10\databases.txt

Verify that you have entries in generated databases.txt file. You’ll need to remove leading space in each row, as well as “column header” rows - resulting file needs to be a newline-separated list of database names.

Dumping databases contents (creating backup)

Since I had a lot of databases, I decided to create 3 separate text-files, each of them containing around 200 database names from the original list. This allows rudimentary parallelism and speeding up of dumping database contents (running 3 dumps in parallel in three separate command prompt windows instead of running them sequentially in one command line window).

Once I prepared 3 text files, named databases-chunk1.txt, databases-chunk2.txt and databases-chunk3.txt, I’ve prepared 3 command-line script files, each handling one chunk of databases. I also created new folder “Dumps” in which database backups will be stored.

for /f "usebackq delims=" %%F in (databases-chunk1.txt) DO (	
SET PGPASSWORD=yourPassword
"C:\PostgreSQL\11\bin\pg_dump" -h yourOldHost --create --username=yourUsername --no-owner --file="D:\BackupDBPostgres10\Dumps\%%F.backup" "%%F"
)

Following the example above, just change chunk1 with chunkX depending on the number of chunks you are working with.

This is also time-consuming process and it can take a while (around 2h in my case).

Restoring database contents and reindexing

Once you have backups created, verify that number of created backup files is matching the number of databases on the old server (minus one for system “postgres” database)

If everything is ok, you can proceed with restoring backups to the new server.

Again, I prepared 3 command-line script files, each handling one chunk of databases.

for /f "usebackq delims=" %%F in (databases-chunk1.txt) DO (	
SET PGPASSWORD=yourPassword
"C:\PostgreSQL\11\bin\psql" -p 5432 --username=yourUsername --dbname=postgres --file="D:\BackupDBPostgres10\Dumps\%%F.backup" >> log1.txt
)

This can also take a while (also around 2h in total in my case), so be patient.

Once restoration of backups is done, there is an additional step that I like to perform, and that is Reindexing. Since index data is lost (actual indexes are preserved), we need to reindex to gain performance from indexes across databases.

Luckily, this is also simple:

SET PGPASSWORD=yourPassword
"C:\PostgreSQL\11\bin\reindexdb" -p 5432 --username=yourUsername --all >> logReindex.txt

Once reindexing is complete (1h in my case for all 600 DBs), you are done with migration and upgrade, congratulations! Now you can update your connection strings and verify the runtime application(s) behavior on the new server.

In-place upgrade

For future lurkers, if you need to do an in-place upgrade (on same Windows machine), the process is somewhat different but isn’t hard. As above, make sure that you’re using PostgreSQL binaries from the newer version.

• shut down currently running instance.
• install targeted PostgreSQL server version and configure it.
• reconfigure old instance to use port 5433 (5432 is the default) and to allow connections from localhost only and start it (pg_hba.config).
• for steps “Dumping the roles”, “Dumping database names” and “Dumping databases contents (creating backup)” add additional command-line switch -p 5433. This will instruct commands to connect to the old server running on port 5433.
• for restoring databases, use the port of targeted (new) server (5432).

And that’s it!

I sincerely hope this guide will help someone who encounters this task as I did. Do you have a simpler solution? Let me know!