• Verified Versions: 9.2, 18
• Maintainer: Redgate

Supported Versions and Support Levels

• For information regarding the supported version and support levels available, please see Supported Databases for Flyway
• For information regarding the Flyway features available, please see Flyway feature summary

Driver

Related database-specific configuration

PostgreSQL-specific configuration can be found here.

Java Usage

PostgreSQL support is a separate dependency for Flyway and will need to be added to your Java project to access these features. PostgreSQL is found within the flyway-database-postgresql plugin module.

Maven

Redgate

<dependency>
    <groupId>com.redgate.flyway</groupId>
    <artifactId>flyway-database-postgresql</artifactId>
</dependency>

Open Source

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-database-postgresql</artifactId>
</dependency>

Gradle

Redgate

buildscript {
    dependencies {
        implementation "com.redgate.flyway:flyway-database-postgresql"
    }
}

Open Source

buildscript {
    dependencies {
        implementation "org.flywaydb:flyway-database-postgresql"
    }
}

SQL Script Syntax

• Standard SQL syntax with statement delimiter ;
• Stored procedures (CREATE FUNCTION with $$ escapes, as generated by pg_dump)
• COPY ... FROM STDIN (as generated by pg_dump)

Compatibility

• DDL exported by pg_dump can be used unchanged in a Flyway migration.
• Any PostgreSQL sql script executed by Flyway, can be executed by the PostgreSQL command-line tool and other PostgreSQL-compatible tools (after the placeholders have been replaced).

Example

/* Single line comment */
CREATE TABLE test_data (
 value VARCHAR(25) NOT NULL PRIMARY KEY
);


/*
Multi-line
comment
*/

-- Multi-statement PostgreSQL function
CREATE FUNCTION AddData() RETURNS INTEGER
AS $$
   BEGIN
    INSERT INTO test_data (value) VALUES ('Hello');
    RETURN 1;
  END;
$$ LANGUAGE plpgsql;

SELECT *  INTO TEMP adddata_temp_table FROM AddData() ;

-- Single-statement PostgreSQL function
CREATE FUNCTION add(integer, integer) RETURNS integer
   LANGUAGE sql IMMUTABLE STRICT
   AS $_$select $1 + $2;$_$;

-- Placeholder
INSERT INTO ${tableName} (name) VALUES ('Mr. T');

Authentication

PostgreSQL supports username/password authentication (including via JDBC URL), SCRAM, and pgpass credential retrieval.

SCRAM authentication encryption is supported transparently using the current JDBC driver. Note that .jre6 and .jre7 versions of the driver for older JREs do not support it.

For how to use authentication methods, see Connecting to environments. For credential storage and retrieval, see Storing and retrieving credentials.

Lock Types

By default Flyway uses a transactional lock with PostgreSQL, however this can cause issues with certain SQL statements, most notably CREATE INDEX CONCURRENTLY. In this scenario, transactional locks can be replaced with session-level locks by setting flyway.postgresql.transactional.lock=false in your configuration.

Limitations

• No support for PSQL meta-commands with no JDBC equivalent like \set
• Clean does not remove objects created by extensions. It is therefore highly recommended to create your extensions using CREATE EXTENSION IF NOT EXISTS in order to be able to clean and (re-)migrate your schemas at will
• No support for cleaning referenced large objects in pg_largeobject (Issue 1934)
• No support for the passfile or hostaddr parameter when using pgpass as there is no JDBC equivalent

Feature support for check command

Support for the check command on PostgreSQL databases is currently ongoing. Basic support for the following object types has been implemented.

• Aggregates
• Domains
• Enumerations
• Foreign Keys
• Functions
• Indexes
• Materialized Views
• Procedures
• Schemas
• Sequences
• Tables
• Triggers
• Views