Use PSQL and runWith on PostgreSQL

Prerequisites

• Use Liquibase 4.13 or later
• Specify how Liquibase can find PSQL by adding PSQL to your PATH. Alternatively, pass its location in the liquibase.psql.conf file or from the command prompt during runtime. See liquibase.psql.path in PSQL integration arguments.

Note: Liquibase searches the PSQL location in the following order: runtime arguments, .conf file values, your PATH.

Using the PSQL integration

1. Add the runWith attribute to the needed changesets in the changelog you use:
• runWith:psql for an SQL changelog
• runWith="psql" for an XML changelog
• "runWith": "psql" for a JSON changelog
• runWith: psql for a YAML changelog

Note: See runWith and psql examples.

2. Specify the PSQL integration arguments in one of the following ways:
• Add the values in liquibase.psql.conf or the Liquibase property file.

Note: You can use the liquibase.psql.conf file along with the Liquibase properties file. If you use Liquibase Pro and PSQL in automation, set arguments in the liquibase.psql.conf file instead of running them every time at the command prompt.

• Set the values as environment variables
• Run the values as Java system properties (JAVA_OPTS) along with any command at the command prompt:
Windows

set JAVA_OPTS=-Dliquibase.psql.<option>=<value> && liquibase <command> --changelog-file=psql_script.sql

macOS/Linux

export JAVA_OPTS=-Dliquibase.psql.<option>=<value> && liquibase <command> --changelog-file=psql_script.sql

3. Run a Liquibase command:

Example: liquibase update --changelog-file=psql_script.sql

Note: If the command fails, you will receive an error message. However, if you add a property that is not used in Liquibase to the liquibase.psql.conf file, no error will occur. Liquibase only ignores it.

PSQL integration arguments

Note: Syntax for each parameter is specified in kebab-case (CLI), camelCase (properties file), and ENVAR_CASE (environment variable).

--psql-args
liquibase.psql.args
LIQUIBASE_PSQL_ARGS

--psql-executor
liquibase.psql.executor
LIQUIBASE_PSQL_EXECUTOR

--psql-keep-temp
liquibase.psql.keep.temp
LIQUIBASE_PSQL_KEEP_TEMP

--psql-keep-temp-name
liquibase.psql.keep.temp.name
LIQUIBASE_PSQL_KEEP_TEMP_NAME

--psql-keep-temp-path
liquibase.psql.keep.temp.path
LIQUIBASE_PSQL_KEEP_TEMP_PATH

--psql-log-file
liquibase.psql.logFile
LIQUIBASE_PSQL_LOG_FILE

--psql-path
liquibase.psql.path
LIQUIBASE_PSQL_PATH

--psql-timeout
liquibase.psql.timeout
LIQUIBASE_PSQL_TIMEOUT

runWith and psql examples

Formatted SQL changeset using the runWith attribute

-- liquibase formatted sql
				
-- changeset myauthorname:2314 runWith:psql
			
	DECLARE l_emp_name VARCHAR2(250);	
	l_emp_no NUMBER;	
	l_salary NUMBER;	
			
	l_manager VARCHAR2(250);	
	BEGIN	
	INSERT INTO emp(emp_name,emp_no,salary,manager) VALUES('BBB',1000,25000,'AAA');
	
	...	
	END;		
	/

XML changelog with the inline SQL changeset using the runWith attribute

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:pro="http://www.liquibase.org/xml/ns/pro"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
        http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">

<changeSet id="2" author="myauthorname" runWith="psql"> 
<sql>
	DECLARE l_emp_name VARCHAR2(250);
	l_emp_no NUMBER;
	l_salary NUMBER;
	l_manager VARCHAR2(250);
       BEGIN
	INSERT INTO emp(emp_name,emp_no,salary,manager) VALUES('BBB',1000,25000,'AAA');
	...
	END;
	/
</sql> 

</changeSet>

XML changelog pointing to an SQL file with raw SQL in it

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:pro="http://www.liquibase.org/xml/ns/pro"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
        http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">					

<changeSet id="2315" author="myauthorname" runWith="psql">  
	<sqlFile
	path="my/path/file.sql"
... > 
</changeSet>

YAML changelog pointing to an SQL file with raw SQL in it

databaseChangeLog:
   - changeSet:
       id: 1-yaml
       author: myauthorname
       runWith: psql
       changes:
       - sqlFile:
           path: person.sql
           relativeToChangelogFile: true

JSON changelog pointing to an SQL file with raw SQL in it

{
	"databaseChangeLog": [
	   {
		"changeSet": {
		  "id": "1-json",
		  "author": "myauthorname",
		  "runWith": "psql",
		  "changes": [
			{
			      "sqlFile":{
				      "path": "person.sql",
				      "relativeToChangelogFile": "true"
				}
			 }
		   ]
	     }
       }

PSQL best practices

• Do not set the endDelimiter or splitStatements=true property on PSQL changesets. PSQL handles delimiters and statement splitting natively.
• Prevent hanging queries by configuring the PSQL timeout. In your liquibase.psql.config file, add liquibase.psql.timeout=nn, where nn is time in seconds to wait before stopping the process.
• Save the output of your PSQL spool files to your temp directory by adding liquibase.psql.keep.temp to the liquibase.psql.config file.

Related links

• runWith
• modifyChangeSets
• Using Liquibase with PostgreSQL