Managing data changes to your PostgreSQL Database with Liquibase
Speaking for myself, rarely do I spend time thinking about the effort involved in tracking, logging, managing, and recovering from unintended data changes. Not referring to backups nor restores here, talking specifically about changes to tuples in tables that occur many times a day.
The thought had not even crossed my mind until recently when a client asked: “Is managing, tracking, and versioning database changes in a PostgreSQL database possible?” The answer: “Yes, you can. It is achievable with Liquibase.”
What is Liquibase?
Liquibase lets you track and version database schema changes, and deploy database code with the velocity to match your application code. Liquibase is available in multiple editions: community (free), pro, business, and enterprise.
An analogy for Liquibase would be that it is the equivalent of a source control management system for data.
Features and capabilities of Liquibase
Features and capabilities vary by edition, the ones listed below are available across all editions:
- Change preview/dry run
- Database state comparison
- Command line & properties file
- Community support
Creating a Project in Liquibase Hub
The first step for working with Liquibase is to create a project, such a project will be used to attach all data changes with the registerChangeLog subcommand.
A Liquibase project is created by performing the steps below:
- Navigate, and Login to Liquibase Hub - an registered account should be created
- Click on Projects Link
- Click on Create Project Link
- Type in Project
- Click Create Project Button
Downloading your Liquibase jar file
Navigate to Liquibase download location, review the requirements, assess your requirements and download the file that matches your environment.
Creating your liquibase.properties file
A liquibase.properties file contains the information required in order to authenticate and connect via the JDBC driver. Before proceeding to fill in the details for this file, it is recommended to have the values readily available prior to commencing this step.
The classpath location should indicate the path for its location if the Liquibase jar file is not located alongside the liquibase.properties file.
changeLogFile: dbchangelog.xml url: jdbc:postgresql://<db_url>:<port>/<database> username: <username> password: <password> classpath: postgresql-42.3.0.jar liquibaseProLicenseKey: <licensekey>
The subcommand to generate the change log file is:
The generateChangeLog subcommand creates a script file containing all of the objects within the database represented as a changeset (unit of change that can be grouped and tagged) at the moment of generateChangeLog execution. The content of this file can help perform a full rollback should the database suffer a catastrophic failure.
A successful generateChangelog will create a file with the name indicated in the changeLogFile parameter of ‘liquibase.properties’. If successful the resulting output of the command line will display:
Liquibase command ‘generateChangelog’ was executed successfully.
Creating the changeset file
Create a template file containing all the data changes to be pushed towards the database. Keep in mind that Liquibase Pro Edition allows for rollbacks per changeset, which is the format provided as an example. Each data change should follow the convention below:
<changeSet author="<user> (generated)" id="<id>-<numberofchange>"> <insert catalogName="<databasename>" dbms="postgresql" schemaName="public" tableName="departments"> <column name="deptno" value="40"/> <column name="dname" value="Sales"/> <column name="location" value="USA"/> </insert> <rollback> DELETE FROM public.departments WHERE deptno=40; </rollback> </changeSet>
ChangeLog files are supported in multiple formats: SQL, XML, JSON, YAML, and other formats.
Update dbchangelog.xml file
Before proceeding to execute the registerChangeLog subcommand, the steps below must be completed:
- Create a copy of the generated dbchangelog.xml file
- Remove all the of the ‘<changeSet>’ tags except the first one
- Copy all of the data changes to be applied after the first ‘<changeSet>’ tag
- Copy ID from generated ID in generated dbchangelog.xml
- Apply ID along with dash and number of data change:
- Remove the first ‘<changeSet>’ tag
Registering the Changelog
The liquibase subcommand to register the changeLog is:
A prompt will appear listing the Liquibase projects available as choices from which you should:
- Select the desired project to be attached towards
A successful registerChangelog will be shown
Liquibase command 'registerChangelog' was executed successfully.
Pushing changes to the DB
At this point, we are ready to push our data changes to the database with the liquibase subcommand:
A successful update will look like to this
Liquibase command 'update' was executed successfully.
Rollback changes to the DB
Rolling back specific changesets is only available in the Liquibase Pro version. The changeset number is what becomes useful when targeting to rollback a specific changeset as illustrated next:
liquibase rollbackCount <count>
A successful rollbackCount will be displayed
Liquibase command 'rollbackCount' was executed successfully.
In this blog we:
- Explained what Liquibase is
- Glanced at the features and capabilities of Liquibase
- Learned how to
- Download Liquibase
- Generate a changelog
- Register a changelog
- Push updates to the DB
- Perform rollbacks to db changes
Liquibase is an extremely useful tool as we have shown in this post. Should you have questions feel free to reach out and contact us.
See you in the next blog post!