Validate PostgreSQL extension upgrade scripts using pg_validate_extupgrade

A few days ago, I decided to try this new extension called pg_validate_extupgrade written by Julien Rouhaud. This extension looks very interesting for those who are developing PostgreSQL extensions. The point is that when upgrading the extensions that you develop, you have to create upgrade files and, if necessary, downgrade files to go from one installed version to the new one. However, it is equally important to validate the upgrade scripts of a PostgreSQL extension. In this article, we shall discuss how to validate PostgreSQL extension upgrade scripts using pg_validate_extupgrade.

Let me consider the pgtt extension as an example :

$ ls sql/
pgtt--2.0.0.sql  pgtt--2.1.0.sql  pgtt--2.2.0.sql  pgtt--2.3.0.sql  pgtt--2.4.0.sql  pgtt--2.5.0.sql

$ ls updates/
pgtt--2.0.0--2.1.0.sql  pgtt--2.1.0--2.2.0.sql  pgtt--2.2.0--2.3.0.sql  pgtt--2.3.0--2.4.0.sql

I have produced 6 versions with an upgrade file to allow already the installed pgtt extension to be upgraded to a more recent version.

As a developer, if I want to be sure that I haven't forgotten to add, modify or delete an object in the upgrade files, the only solution is a visual check. A regression test is obviously necessary but it usually only tests the latest version produced, but not the upgrade. And if we run the test after a manual upgrade, nothing says that it contains a test set on tiny changes or between several version breaks that I could have made.

The more the number of modifications with the change of versions, the more is the possibility of missing or deprecating an upgrade file. Hopefully, now Julien's extension pg_validate_extupgrade can help us.

Prerequisites

I decide to use a local installation because this extension is in Rust and there are some dependencies that are conflicting with my installation :

$ curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh

when it prompts, Answer 1 to the question or press Enter to install it locally. With this now the Rust Toolchain is installed in $HOME/.cargo/

You can set the environment using the following command, to be able to use the Rust Toolchain without restarting the terminal session :

$ source ~/.cargo/env

If you want to install it from your distribution package, proceed using the following command, for Debian/Ubuntu.

sudo apt install cargo

Similarly for RHEL 7 and RHEL 8, you may use the following command :

sudo yum install rust-toolset-1.31

Or simply use the following command.

sudo yum module install rust-toolset

Installation of pg_validate_extupgrade

We can now proceed to compile the pg_validate_extupgrade extension using the following set of commands :

$ cd pg_validate_extupgrade/
$ cargo build --release
Updating crates.io index
Downloaded async-trait v0.1.50
[...]
Compiling toml v0.5.8
Compiling pg_validate_extupgrade v1.0.0-beta (/home/git/pg_validate_extupgrade)
 Finished release [optimized] target(s) in 57.49s

The first time you run cargo, it will download all of the Rust dependencies and compile them together with the pg_validate_extupgrade extension. But at the end you must see a message like following :

Compiling pg_validate_extupgrade v1.0.0-beta (/home/git/pg_validate_extupgrade)
 Finished release [optimized] target(s) in 33.72s

Now we are ready to test the pgtt's upgrade extension files. The pg_validate_extupgrade binary is now available locally as ./target/release/pg_validate_extupgrade .

Verifying extension's upgrade scripts

I decided to verify all pgtt's upgrade scripts since version 2.0.0 to the current version 2.5.0. To test them, I would first create a dedicated database and then proceed to run the validation command :

$ createdb test_pgtt_scripts
$ ./target/release/pg_validate_extupgrade -d test_pgtt_scripts -e pgtt --from 2.0.0 --to 2.5.0
Connected, server version 130003
ERROR: db error: ERROR: extension "pgtt" has no update path from version "2.0.0" to version "2.5.0"

Damn, looks like I've forgot to include the upgrade script from 2.4.0 to 2.5.0!

$ ls /home/git/pgtt/update/
pgtt--2.0.0--2.1.0.sql  pgtt--2.1.0--2.2.0.sql  pgtt--2.2.0--2.3.0.sql  pgtt--2.3.0--2.4.0.sql

After fixing this, I rerun the validation process :

$ ./target/release/pg_validate_extupgrade -d test_pgtt_scripts -e pgtt --from 2.0.0 --to 2.5.0
Connected, server version 130003
No difference found.

This is a simplistic example, but I was able to notice that an upgrade file was missing. Actually this happens more often than you think.

In real life, I spent my days playing with this extension on a proprietary project, with many extensions and versions. As pg_validate_extupgrade extension was at an early development stage, I was able to take the help of Julien to have some fixes and improvements to cover all these various extensions. The result is really impressive when we see the fruitful results of the extension.

If you want to see a complete example of what this extension can report, see the output example provided in the README file.

Thank you again Julien Rouhaud for this very useful extension and your responsiveness. Since PostgreSQL v10, the new base installation script is no more needed but no doubt that this extension has an interest in regression testing on PostgreSQL extension developments, if you use a version-update script and a new base installation script. Waiting for the first release of this extension. 

By the way, if you are migrating to PostgreSQL and you are stuck due to a feature that only works in Oracle like commercial databases, let us know. MigOps is known for contributing to many extensions around Oracle to PostgreSQL migrations and we are actively contributing to Ora2Pg.  If you are looking for assistance in managing your PostgreSQL databases, Performance Tuning or Migrating to PostgreSQL, please fill the following details to let our Operations team setup a call with an Expert.

Please fill the following form.