How to test a PostgreSQL Database's schema on a new version
The goal of this guide is to test the schema of an existing Database against another Database version in order to see if it's compatible with the desired version. The primary reason to do this is to ensure a Database's schema is compatible with a higher version before upgrading.
Install the Aptible CLI and PostgreSQL Client Tools. This guide uses the
psql client tools.
Collect information on the Database you'd like to test and store it in the following environment variables for use later in the guide:
SOURCE_HANDLE- The handle (i.e. name) of the Database.
SOURCE_ENVIRONMENT- The handle of the environment theDatabase belongs to.
Collect information on the target Database and store it in in the following environment variables:
TARGET_HANDLE- The handle (i.e. name) for the Database.
TARGET_VERSION- The target PostgreSQL version. Run
aptible db:versionsto see a full list of options.
TARGET_ENVIRONMENT- The handle of the environment to create the Database in.
TARGET_HANDLE='schema-test' TARGET_VERSION='14' TARGET_ENVIRONMENT='test-environment'
Create the target Database
Create a new Database running the desired version. Assuming the environment variables above are set, this command can be copied and pasted as-is to create the Database.
aptible db:create "$TARGET_HANDLE" --type postgresql --version "$TARGET_VERSION" --environment "$TARGET_ENVIRONMENT"
aptible db:create creates a Database with a 1 GB of memory and 10 GB of disk space. This should be sufficient for most schema tests but, if more memory or disk is required, the
--disk-size arguments can be used.
Dump the schema
Create a Database Tunnel to the source Database using the Aptible CLI.
aptible db:tunnel "$SOURCE_HANDLE" --environment "$SOURCE_ENVIRONMENT"
The tunnel will block the current terminal until it's stopped. In another terminal, collect the tunnel's information, which is printed by
aptible db:tunnel, and store it in the following environment variables:
SOURCE_URL- The full URL of the Database tunnel.
SOURCE_PASSWORD- The Database's password.
SOURCE_URL='postgresql://aptible:[email protected]:5432/db' SOURCE_PASSWORD='pa$$word'
Dump the schema into a file.
schema.sql in this case.
PGPASSWORD="$SOURCE_PASSWORD" pg_dumpall -d "$SOURCE_URL" --schema-only --no-password \ | grep -E -i -v 'ALTER ROLE aptible .*PASSWORD' > schema.sql
The output of
pg_dumpall is piped into
grep in order to remove any SQL commands that may change the default
aptible user's password. If these commands were to run on the target Database, it would be updated to match the source Database. This would result in the target Database's password no longer matching what's displayed in the Aptible Dashboard or printed by commands like
aptible db:url or
aptible db:tunnel which could cause problems down the road.
You now have a copy of your Database's schema in
schema.sql! The Database Tunnel can be closed by following the instructions that
aptible db:tunnel printed when the tunnel started.
Restore the schema
Create a Database Tunnel to the target Database using the Aptible CLI.
aptible db:tunnel "$TARGET_HANDLE" --environment "$TARGET_ENVIRONMENT"
Again, the tunnel will block the current terminal until it's stopped. In another terminal, store the tunnel's full URL, which is printed by
aptible db:tunnel, in the
TARGET_URL environment variable.
Apply the schema to the target Database.
psql $TARGET_URL -f schema.sql > /dev/null
The output of
psql can be noisy depending on the complexity of the source Database's schema. In order to reduce the noise, the output is redirected to
/dev/null so that only error messages are displayed.
The following errors may come up when restoring the schema:
ERROR: role "aptible" already exists ERROR: role "postgres" already exists ERROR: database "db" already exists
These errors are expected because Aptible creates these resources on all PostgreSQL Databases when they are created. The errors are a result of the schema dump attempting to re-create the existing resources. If these are the only errors, the upgrade was successful!
If there are additional errors, they will need to be addressed in order to be able to upgrade the source Database to the desired version. Consult the PostgreSQL Documentation for details about the errors you encounter.
Once you've updated the source Database's schema you can test the changes by deprovisioning the target Database, see the Cleanup section, and starting from the Create the target Database step.
Deprovision the target Database
aptible db:deprovision "$TARGET_HANDLE" --environment "$TARGET_ENVIRONMENT"
$TARGET_ENVIRONMENT is configured to retain final Database Backups, which is enabled by default, you may want to delete the final backups for all target Databases you created for this test.
You can obtain a list of final backups by running:
aptible backup:orphaned --environment "$TARGET_ENVIRONMENT"
Then, delete the backup(s) by ID using the
aptible backup:purge command.
Updated 11 months ago