How can we help you today? How can we help you today?

Flyway clean foreign tables

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.
VickyR
0

Comments

2 comments

Sort by
  • 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.
    AlistairW
    0
  • robertcollins

    From what you've shared, this is most likely expected behavior rather than a configuration issue. Flyway's clean command is designed to remove objects it manages, but foreign tables created through postgres_fdw aren't always handled the same way because they depend on external server definitions and object dependencies.

    If your migration process creates those foreign tables, you may need to explicitly drop them in a separate migration or pre-clean script before running clean. That's generally a safer approach than expecting Flyway to remove every dependency automatically.

    I usually think of it as any other clean up process. Whether you're cleaning up a database or dealing with something like mold clean up, it's important to understand what's connected to the underlying source before removing objects. Otherwise, you can end up with leftover dependencies or create new issues that are harder to troubleshoot later.

    One question: are the foreign tables created entirely by your migrations, or were they already present before Flyway started managing the database? That detail will help determine the best approach.

    robertcollins
    -1

Add comment

Please sign in to leave a comment.