[8.x] Fix issues with dumping PostgreSQL databases that contain multiple schemata (#36046)

cbj4074 · web-flow · commit 306826fb60df · 2021-01-27T10:58:28.000-06:00
* Eliminate the need for search_path logic The PostgreSQL search_path logic that this commit removes was added with the intention of enabling support for a schema named anything other than "public". While the theory was sound, the implementation didn't take into account the behavior in databases in which *multiple* schemas exist. In multi-schema databases, the list of tables for which data should not be dumped was incorrect, leading to unexpected behavior. This revised approach takes advantage of PostgreSQL's support for pattern-based object references when specifying the list of tables for which data should not be dumped, and eliminates the need to perform complex search_path parsing altogether. The attendant Pull Request documentation explains how this technique works in detail. * Re-implement pg_restore fix that was reverted #36054 was fixed in a previous commit, but appears to have been lost during subsequent edits in 7be50a5 . This commit restores the changes made in 502e75b . Fixes #36054 * Fix PostgreSQL object reference pattern quoting While not required in a psql interactive terminal, this pattern requires outer double-quotes to function as intended when passed as a CLI argument. While simple in this specific instance, pattern quoting can grow complicated (depending on the pattern), but is well explained in the PostgreSQL manual: https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-PATTERNS
diff --git a/src/Illuminate/Database/Schema/PostgresSchemaState.php b/src/Illuminate/Database/Schema/PostgresSchemaState.php
@@ -16,16 +16,12 @@ class PostgresSchemaState extends SchemaState
      */
     public function dump(Connection $connection, $path)
     {
-        $schema = $connection->getConfig('schema', 'public');
-
-        $schema = $schema === 'public' ? '' : $schema.'.';
-
         $excludedTables = collect($connection->getSchemaBuilder()->getAllTables())
                         ->map->tablename
                         ->reject(function ($table) {
                             return $table === $this->migrationTable;
-                        })->map(function ($table) use ($schema) {
-                            return '--exclude-table-data='.$schema.$table;
+                        })->map(function ($table) {
+                            return '--exclude-table-data="*.'.$table.'"';
                         })->implode(' ');
 
         $this->makeProcess(
@@ -43,7 +39,7 @@ public function dump(Connection $connection, $path)
      */
     public function load($path)
     {
-        $command = 'PGPASSWORD=$LARAVEL_LOAD_PASSWORD pg_restore --no-owner --no-acl --host=$LARAVEL_LOAD_HOST --port=$LARAVEL_LOAD_PORT --username=$LARAVEL_LOAD_USER --dbname=$LARAVEL_LOAD_DATABASE $LARAVEL_LOAD_PATH';
+        $command = 'PGPASSWORD=$LARAVEL_LOAD_PASSWORD pg_restore --no-owner --no-acl --clean --if-exists --host=$LARAVEL_LOAD_HOST --port=$LARAVEL_LOAD_PORT --username=$LARAVEL_LOAD_USER --dbname=$LARAVEL_LOAD_DATABASE $LARAVEL_LOAD_PATH';
 
         if (Str::endsWith($path, '.sql')) {
             $command = 'PGPASSWORD=$LARAVEL_LOAD_PASSWORD psql --file=$LARAVEL_LOAD_PATH --host=$LARAVEL_LOAD_HOST --port=$LARAVEL_LOAD_PORT --username=$LARAVEL_LOAD_USER --dbname=$LARAVEL_LOAD_DATABASE';
