New! – Spin up instant copies of your dev databases in the cloud for free Learn more

Flyway Documentation

Poll

PostgreSQL

Supported Versions

Support Level

Compatible
Certified
Guaranteed Flyway Teams

Support Level determines the degree of support available for this database (learn more).

Driver

URL format jdbc:postgresql://host:port/database
SSL support Yes - add ?ssl=true
Ships with Flyway Command-line Yes
Maven Central coordinates org.postgresql:postgresql:42.2.14
Supported versions 9.3-1104-jdbc4 and later
Default Java class org.postgresql.Driver

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');

-- COPY ... FROM STDIN
COPY copy_test (c1, c2, c3) FROM stdin;
1	utf8: ümlaute: äüß	NaN
2	\N	123
3	text	123.234444444444449
\.

Authentication

Flyway supports the following PostgreSQL authentication methods:

  • URL authentication
  • SCRAM
  • pgpass

URL authentication

The user and password can be provided in the JDBC URL, in the form

jdbc:postgresql://<host>:<port>/<database>?user=myUsername&password=myPassword&<key1>=<value1>&<key2>=<value2>...

In this case, they do not need to be passed separately in configuration and the Flyway commandline will not prompt for them.

SCRAM

SCRAM authentication encryption is supported transparently using the current driver (42.2.14) - note that .jre6 and .jre7 versions of the driver for older JREs do not support it.

pgpass

Flyway Teams

Authentication can be done with a pgpass file to retrieve the password for a connection, in which case it does not need to be supplied in configuration. If the path to a pgpass file is set in the environment variable PGPASSFILE, it will be read from here. If not, then in Windows the file will be read from the location %APPDATA%\postgresql\pgpass.conf, otherwise it is read from~/.pgpass. You can read more about pgpass files and their structure here.

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 schema(s) 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

Aurora PostgreSQL