liquibase

PGAdapter and Liquibase

PGAdapter has Pilot Support for Liquibase. This sample shows the command line arguments and configuration that is needed in order to use Liquibase with PGAdapter.

Liquibase Requirements

• The JDBC Connection URL must include options=-c%20spanner.ddl_transaction_mode=AutocommitExplicitTransaction. See liquibase.properties for an example URL. Cloud Spanner does not support DDL transactions, and this will ensure that DDL transactions are automatically converted to DDL batches. See DDL options for more information.
• The databasechangeloglock and databasechangelog tables must be created manually, as the DDL script that is automatically generated by Liquibase will try to use the data type timestamp without time zone, which is not supported by Cloud Spanner. The DDL script to create these tables manually can be found in create_database_change_log.sql.

Running the Sample

All the steps below assume that the commands are executed from this directory.

1. Start PGAdapter.

wget https://storage.googleapis.com/pgadapter-jar-releases/pgadapter.tar.gz && tar -xzvf pgadapter.tar.gz
java -jar pgadapter.jar -p my-project -i my-instance

2. Manually create the databasechangeloglock and databasechangelog tables. These need to be manually created because the default table definition that is generated by Liquibase use timestamp without time zone instead of timestamp with time zone, and it would create a table without a primary key. Replace my-database with the actual name of your database.

psql -h localhost -d my-database -f create_database_change_log.sql

3. Modify the liquibase.properties file in this directory to point to your database:

changeLogFile: dbchangelog.xml
url: jdbc:postgresql://localhost:5432/my-database?options=-c%20spanner.ddl_transaction_mode=AutocommitExplicitTransaction

4. Update the database to according to the changes in dbchangelog.xml by executing:

mvn liquibase:update

Frequently Asked Questions

See frequently asked questions for a list of common issues and questions.

Supported Change Types

The following change types are supported by PGAdapter (see the dbchangelog.xml for examples). Note that some change types have some limitations. See limitations for a specific list.

Misc

• sql: https://docs.liquibase.com/change-types/sql.html
• tagDatabase: https://docs.liquibase.com/change-types/tag-database.html

Entities

• createTable: https://docs.liquibase.com/change-types/create-table.html
• createIndex: https://docs.liquibase.com/change-types/create-index.html
• createView: https://docs.liquibase.com/change-types/create-view.html
• addColumn: https://docs.liquibase.com/change-types/add-column.html
• dropColumn: https://docs.liquibase.com/change-types/drop-column.html
• dropIndex: https://docs.liquibase.com/change-types/drop-index.html
• dropTable: https://docs.liquibase.com/change-types/drop-table.html
• dropView: https://docs.liquibase.com/change-types/drop-view.html

Constraints

• addDefaultValue: https://docs.liquibase.com/change-types/add-default-value.html
• addForeignKeyConstraint: https://docs.liquibase.com/change-types/add-foreign-key-constraint.html
• addNotNullConstraint: https://docs.liquibase.com/change-types/add-not-null-constraint.html
• dropDefaultValue: https://docs.liquibase.com/change-types/drop-default-value.html
• dropForeignKeyConstraint: https://docs.liquibase.com/change-types/drop-foreign-key-constraint.html
• dropNotNullConstraint: https://docs.liquibase.com/change-types/drop-foreign-key-constraint.html

Data

• loadData: https://docs.liquibase.com/change-types/load-data.html
• insert: https://docs.liquibase.com/change-types/insert.html
• update: https://docs.liquibase.com/change-types/update.html
• delete: https://docs.liquibase.com/change-types/update.html

Limitations

The Cloud Spanner PostgreSQL dialect supports a subset of the PostgreSQL DDL dialect. This means that not all change types that are supported by Liquibase for PostgreSQL can be used with Cloud Spanner.

Supported Change Types with Limitations

The following change types are supported, but with limitations:

• createTable: The createTable change set must include a primary key constraint, and the name of the primary key constraint must be pk_<table_name>. See the examples in dbchangelog.xml.
• createView: The create view statement must include a sql security invoker. Set fullDefinition="true" in the change set and include create view <view_name> sql security invoker as ... as thew view definition.
• dropColumn: Only one column can be dropped per change set.
• dropTable: Cascade constraints is not supported. All secondary indexes on the table must be dropped before the table can be dropped.
• add*Constraint: The "disabled" and "validate" properties are not supported. Constraints are always enabled and validated.
• addForeignKeyConstraint: The "deferrable", "initiallyDeferred", "onDelete", "onUpdate" and "validate" properties are not supported. Foreign key constraints are always validated, non-deferrable and do not support any cascading options.
• insert, update, delete, loadData: The number of mutations may not exceed the mutation limit (https://cloud.google.com/spanner/quotas#limits_for_creating_reading_updating_and_deleting_data)

Unsupported Change Types

The following change types are not supported:

Entities

• addAutoIncrement: https://docs.liquibase.com/change-types/add-auto-increment.html
• alterSequence: https://docs.liquibase.com/change-types/alter-sequence.html
• createFunction: https://docs.liquibase.com/change-types/create-function.html
• createPackage: https://docs.liquibase.com/change-types/create-package.html
• createPackageBody: https://docs.liquibase.com/change-types/create-package-body.html
• createProcedure: https://docs.liquibase.com/change-types/create-procedure.html
• createSequence: https://docs.liquibase.com/change-types/create-sequence.html
• createSynonym: https://docs.liquibase.com/change-types/create-synonym.html
• createTrigger: https://docs.liquibase.com/change-types/create-trigger.html
• disableTrigger: https://docs.liquibase.com/change-types/disable-trigger.html
• dropFunction: https://docs.liquibase.com/change-types/drop-function.html
• dropPackage: https://docs.liquibase.com/change-types/drop-package.html
• dropPackageBody: https://docs.liquibase.com/change-types/drop-package-body.html
• dropProcedure: https://docs.liquibase.com/change-types/drop-procedure.html
• dropSequence: https://docs.liquibase.com/change-types/drop-sequence.html
• dropSynonym: https://docs.liquibase.com/change-types/drop-synonym.html
• dropTrigger: https://docs.liquibase.com/change-types/drop-trigger.html
• enableTrigger: https://docs.liquibase.com/change-types/enable-trigger.html
• renameColumn: https://docs.liquibase.com/change-types/rename-column.html
• renameSequence: https://docs.liquibase.com/change-types/rename-sequence.html
• renameTable: https://docs.liquibase.com/change-types/rename-table.html
• renameTrigger: https://docs.liquibase.com/change-types/rename-trigger.html
• renameView: https://docs.liquibase.com/change-types/rename-view.html
• setColumnRemarks: https://docs.liquibase.com/change-types/set-column-remarks.html
• setTableRemarks: https://docs.liquibase.com/change-types/set-table-remarks.html

Constraints

• addNotNullConstraint: https://docs.liquibase.com/change-types/add-not-null-constraint.html
• addPrimaryKey: https://docs.liquibase.com/change-types/add-primary-key.html
• addUniqueConstraint: https://docs.liquibase.com/change-types/add-unique-constraint.html
• disableCheckConstraint: https://docs.liquibase.com/change-types/disable-check-constraint.html
• dropAllForeignKeyConstraints: https://docs.liquibase.com/change-types/drop-all-foreign-key-constraints.html
• dropNotNull: https://docs.liquibase.com/change-types/drop-not-null-constraint.html
• dropPrimaryKey: https://docs.liquibase.com/change-types/drop-primary-key.html
• dropUniqueConstraint: https://docs.liquibase.com/change-types/drop-unique-constraint.html
• enableCheckConstraint: https://docs.liquibase.com/change-types/enable-check-constraint.html

Data

• addLookupTable: https://docs.liquibase.com/change-types/add-lookup-table.html
• loadUpdateData: https://docs.liquibase.com/change-types/load-update-data.html
• modifyDataType: https://docs.liquibase.com/change-types/modify-data-type.html