Can't push migrations to Atlas Cloud

[KostyaZgara]

Hi there! Recently I had to enable postgis extension and it's time to push my changes to Atlas Cloud and apply changes to my cloud database, but it's failed with error message

failed to push directory: Error: executing statement: pq: extension "postgis" already exists

I tried to use different params but nothing helped me. So here is my current configuration

• I use GitHub action and my workflow looks in following way

  atlas:
    needs:
      - build
    services:
      postgres:
        image: postgis/postgis:15-3.4
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: example
          POSTGRES_DB: moji
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
    runs-on: ubuntu-latest
    steps:
      - name: Setup PSQL
        uses: tj-actions/install-postgresql@v3
        with:
          postgresql-version: 15
      # Let atlas migrations to enable postgis extension again
      - name: Drop postgis extension
        run: psql "postgres://postgres:example@localhost:5432/moji?sslmode=disable" -c "drop extension postgis cascade"
      - uses: actions/checkout@v3
      - uses: ariga/setup-atlas@v0
        with:
          cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
      - uses: ariga/atlas-action/migrate/push@v1
        with:
          dir-name: moji
          tag: ${{ github.ref_name }}
          env: ci
        env:
          DATABASE_HOST: localhost
          DATABASE_NAME: moji

My atlas.hcl config is following

variable "host" {
  type = string
  default = getenv("DATABASE_HOST") != "" ? getenv("DATABASE_HOST") : "localhost"
}

variable "port" {
  type = string
  default = getenv("DATABASE_PORT") != "" ? getenv("DATABASE_PORT") : "5432"
}

variable "username" {
  type = string
  default = getenv("DATABASE_USERNAME") != "" ? getenv("DATABASE_USERNAME") : "postgres"
}

variable "password" {
  type = string
  default = getenv("DATABASE_PASSWORD") != "" ? getenv("DATABASE_PASSWORD") : "example"
}

variable "database" {
  type = string
  default = getenv("DATABASE_NAME") != "" ? getenv("DATABASE_NAME") : "moji"
}

# Need for running postgis extension correctly
docker "postgres" "dev" {
  image  = "postgis/postgis:15-3.4-alpine"
  schema = "public"
  baseline = <<SQL
    drop extension postgis cascade;
  SQL
}

env "ci" {
  url = "postgres://${var.username}:${var.password}@${var.host}:${var.port}/${var.database}?search_path=public&sslmode=disable"
  dev = docker.postgres.dev.url

  migration {
    dir    = "file://db/migrations"
    format = atlas
  }
}

And migration directory looks in following way:

This configuration works well locally and in CI when I run my integration tests where I use action ariga/atlas-action/migrate/apply@v1. I have no idea what to try next, cuz I even don't know in what place this error appears exactly, cuz I tried to drop postgis extension in dev database defined in atlas.hcl and drop extension defined directly in CI postgres. Could you please point me out where I'm wrong?

P.S. I tried to follow recommendation suggested here #2578 but so far it doesn't work to me

[giautm]

@KostyaZgara I saw my co-worker's response here. #2578 (comment)

Please use a bare docker image for PG (without Postgis installed on it). postgis/postgis:15-3.4 replace this image with regular docker image, and remove the baseline for drop extension.

[masseelch]

atlas migrate push is computing some intermediate states for the migration files. I have a feeling that there might be something off inside there. I will investigate and come back to you.

[masseelch]

Okay, we managed to figure this out:

Extensions in PostgreSQL are database objects, they cannot be managed in schema level. This means your migrations should work in database scope (without search_path set).

In your example, if you remove the schema = "public" form the docker block in the atlas.hcl it should work in CI.

Please note, that by changing the scope (schema bound to database bound) your migrations will have schema qualifiers.

[KostyaZgara]

Yeap, it works! Solution with removing search_path and schema = "public" from docker block in atlas.hcl works and now I can be able to run extensions everywhere locally, in CI, and in production. Moreover Atlas can compute now diff and install extension automatically that's great news as well, cuz now I can remove manipulations with baseline scrips everywhere. I'm closing the issue now, but I think it would be great to add such mention in documentation, cuz it's not obvious behavior in my opinion. Thanks!

[masseelch]

Yes. We will improve the documentation regarding this. Glad we could help.