Using the PSQL integration and runWith attribute with Liquibase Pro and 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.

  1. 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:
  1. Run a Liquibase command:
  2. 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

Property Environment variable CLI Description
liquibase.psql.args LIQUIBASE_PSQL_ARGS --psql-args [Optional] Extra arguments to pass to the psql executable. For more information about PSQL arguments, see PSQL Documentation.

Note: The delimiter for arguments is a space " ". Do not use a comma "," or semicolon ";".

liquibase.psql.keep.temp LIQUIBASE_PSQL_KEEP_TEMP --psql-keep-temp Flag to indicate whether or not to keep a temporary SQL file after the execution of PSQL. If true, the file is not deleted. Default: false.
liquibase.psql.keep.temp.name LIQUIBASE_PSQL_KEEP_TEMP_NAME --psql-keep-temp-name [Optional] Flag to indicate the name of a temporary SQL file after the execution of PSQL.
liquibase.psql.keep.temp.overwrite LIQUIBASE_PSQL_KEEP_TEMP_OVERWRITE --psql-keep-temp-overwrite Flag to overwrite any files in the specified directory with the same name. Default: true.
liquibase.psql.keep.temp.path LIQUIBASE_PSQL_KEEP_TEMP_PATH --psql-keep-temp-path [Optional] Flag to indicate the location to store a temporary SQL file after the execution of PSQL. If not specified, the files are stored in the system's temp directory.
liquibase.psql.path LIQUIBASE_PSQL_PATH --psql-path Path to the psql executable.
liquibase.psql.timeout LIQUIBASE_PSQL_TIMEOUT --psql-timeout

Seconds to wait for the psql timeout:

  • "-1" disables the timeout
  • "0" returns an error
  • 1800 seconds (30 minutes) is the default value

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