Attempts to create Postgres migrations table after updating to Laravel 9 but it already exists (Postgres+PGBouncer)

[AlbinoDrought]

After upgrading from Laravel 8 to Laravel 9 (including changing schema to search_path in config/databases.php), php artisan migrate fails because it attempts to create the migrations table, but it already exists:

SQLSTATE[42P07]: Duplicate table: 7 ERROR:  relation "migrations" already exists (SQL: create table "migrations" ("id" serial primary key not null, "migration" varchar(191) not null, "batch" integer not null))

\Illuminate\Database\MIgrations\DatabaseMigrationRepository->repositoryExists() is used to determine if the table already exists. In our case, it runs this: DB::connection('pgsql')->getSchemaBuilder()->hasTable('migrations')

In Laravel 8, we get the expected result:

>>> DB::connection('pgsql')->getSchemaBuilder()->hasTable('migrations')
=> true

But in Laravel 9, we get the unexpected result:

>>> DB::connection('pgsql')->getSchemaBuilder()->hasTable('migrations')
=> false

Laravel 9 is able to access everything in the tables though. Here's Laravel 9 listing a row from the migrations table (it exists and is accessible!)

>>> DB::connection('pgsql')->table('migrations')->limit(1)->get()
=> Illuminate\Support\Collection {#3850
     all: [
       {#3838
         +"id": 1,
         +"migration": "2014_10_12_000000_create_users_table",
         +"batch": 1,
       },
     ],
   }

Thoughts?

---

Here is the query executed in Laravel 8:

>>> $c = DB::connection('pgsql');
=> Illuminate\Database\PostgresConnection {#3833}
>>> $c->enableQueryLog();
=> null
>>> $c->getSchemaBuilder()->hasTable('migrations')
=> true
>>> $c->getQueryLog()
=> [
     [
       "query" => "select * from information_schema.tables where table_schema = ? and table_name = ? and table_type = 'BASE TABLE'",
       "bindings" => [
         "public",
         "migrations",
       ],
       "time" => 5.14,
     ],
   ]

Here is the query executed in Laravel 9:

>>> $c = DB::connection('pgsql');
=> Illuminate\Database\PostgresConnection {#3847}

>>> $c->enableQueryLog()
=> null

>>> $c->getSchemaBuilder()->hasTable('migrations')
=> false

>>> $c->getQueryLog()
=> [
     [
       "query" => "select * from information_schema.tables where table_catalog = ? and table_schema = ? and table_name = ? and table_type = 'BASE TABLE'",
       "bindings" => [
         "homestead_web",
         "public",
         "migrations",
       ],
       "time" => 17.06,
     ],
   ]

The specified table_catalog value is the pgbouncer connection pool we're connecting to, but not the correct database name:

DB_CONNECTION=pgsql
DB_DATABASE=homestead_web
DB_HOST=pgbouncer
DB_PORT=25061
DB_USERNAME=rw_homestead

---

table_catalog parameter was added in #35530 . The same database config value is used when connecting to the database and when generating the hasTable statement.

[AlbinoDrought]

This is a hack, but I can force parseSearchPath to return the values I want in this scenario by changing config('database.migrations') to the fully qualified {database}.{schema}.{table} format like homestead.public.migrations. Alternatively, migrations can be performed directly without a connection pool.

[AlbinoDrought]

However, the configured database name is also used in other calls like getColumns(). A better solution may be to pass the pool name only during createConnection() (as part of the DSN).

[AlbinoDrought]

See here for a workaround to only specify the PGBouncer pool name as part of the DSN: https://gist.github.com/AlbinoDrought/852d0f52ee57907bdaeae16eba39afab

Specify your real database at database, and your PGBouncer pool name at pgbouncer_poolname

[AlbinoDrought]

See #43542

[dennis-koster]

@AlbinoDrought Thank you so much for this, I ran into the exact same issue after an upgrade with DigitalOcean being the database host with a pgbouncer, but didn't find the added configuration option documented.

Just so I understand correctly, I use the database configuration value for the actual database name (for instance defaultdb) and then use the connect_via_database configuration value for the pgbouncer, correct? And this will still utilise the connection pooler?

[Silassiai]

I encountered the same issue where database migrations stopped working after switching to DigitalOcean's connection pooling for PostgreSQL.

After some research, I discovered that Laravel 10 actually provides a solution for this, though it’s not mentioned in their official documentation. The fix is located in the PostgresConnector.php file under the following path: vendor/laravel/framework/src/Illuminate/Database/Connectors/PostgresConnector.php within the getDns method.

To resolve this issue, all I had to do was add the following configuration to my database.php file under the relevant database connection settings:

'connect_via_database' => env('DB_DATABASE_VIA'),
 'connect_via_port' => env('DB_PORT_VIA'),