Flyway clean foreign tables

VickyR

Hello! After migrating the scripts to the database, and subsequently attempting to clean them, the clean function is not dropping foreign tables.

Using flyway 8.5.13 and attempting again with flyway 10.18.1 (latest version currently), running the clean function does not drop foreign tables. Is this a limitation or is it possible to drop foreign tables using flyway clean?

The following .bat file is used via flyway CLI:

@REM Runs flyway migrate with some predefined settings

@Echo off

@REM get common configs

call flyway-local-conf.bat

set URL="jdbc:postgresql://localhost:5432/database"

SET SCHEMAS="control,schema1,schema2_fs"

SET LOCATIONS="filesystem:%GIT_LOCAL_REPO_PATH%\postgres\database\migrations"

flyway -url=%URL% -user=%USER% -password=%PWD% -schemas=%SCHEMAS% -locations=%LOCATIONS% clean

Control and schema1 get cleaned (tables and views) whereas schema2_fs does not get cleaned as it only contains foreign tables setup with a foreign server as follows:

CREATE EXTENSION postgres_fdw;

CREATE SERVER "local-database-fs"

   FOREIGN DATA WRAPPER postgres_fdw

   OPTIONS(host '127.0.0.1', dbname 'database2', port '5432');

Any help would be appreciated, as we are currently running migrations using "IF NOT EXISTS" when creating foreign tables and DROP when we could be using ALTER.

AlistairW

The PostgreSQL documentation mentions this:

• 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

Admittedly, this just confirms you are doing this as expected but not why this limitation exists.