Multiple schema and DB migrations with Doctrine2 and PostgreSQL

Question

I have a PG database containing several schemas.

I have a symfony2 application that needs to be connected to only one of them.

If I do not configure anything, by default Doctrine2 searches in all the schemas, so it tries to do SELECT in a table where it has no right.

I followed this answer: symfony2 + doctrine2 @postgresql sets the schema

Now it seems to go only to the shema that I pointed out, but he is still looking for some tables that are not in my schema, but in others.

When I do something ldoctrine: migrations: diff

 [Doctrine\DBAL\DBALException] An exception occurred while executing 'SELECT min_value, increment_by FROM "pgstatspacknameid"': SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "pgstatspacknameid" does not exist LINE 1: SELECT min_value, increment_by FROM "pgstatspacknameid" ^ [PDOException] SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "pgstatspacknameid" does not exist LINE 1: SELECT min_value, increment_by FROM "pgstatspacknameid" ^ 

So, following the documentation for the doctrine documentation, I added this line to the connection configuration:

  schema_filter: ~^(?!pgstats)~ 

He still always tries to do SELECT in these tables ...

Answer 1

You need the -db-configuration option for the migrate command. A configuration file is required as an argument. Try with the following contents of the configuration file.

 <?php return array( "driverClass"=>"AppBundle\Driver", "host" => "localhost", "user" => "test", "password" => "test", "dbname" => "test" ); ?> 

In addition, I changed vendor / doctrine / dbal / lib / Doctrine / DBAL / SchemaPostgreSqlSchemaManager.php to always add the schema to the sequence names.

like this:

 /** * {@inheritdoc} */ protected function _getPortableSequencesList($sequences) { $sequenceDefinitions = array(); foreach ($sequences as $sequence) {//+ || true if ($sequence['schemaname'] != 'public' || true) { $sequenceName = $sequence['schemaname'] . "." . $sequence['relname']; } else { $sequenceName = $sequence['relname']; } $sequenceDefinitions[$sequenceName] = $sequence; } $list = array(); foreach ($this->filterAssetNames(array_keys($sequenceDefinitions)) as $sequenceName) { $list[] = $this->_getPortableSequenceDefinition($sequenceDefinitions[$sequenceName]); } return $list; } /** * {@inheritdoc} */ protected function getPortableNamespaceDefinition(array $namespace) { return $namespace['nspname']; } /** * {@inheritdoc} */ protected function _getPortableSequenceDefinition($sequence) {//+ || true if ($sequence['schemaname'] != 'public' || true) { $sequenceName = $sequence['schemaname'] . "." . $sequence['relname']; } else { $sequenceName = $sequence['relname']; } $data = $this->_conn->fetchAll('SELECT min_value, increment_by FROM ' . $this->_platform->quoteIdentifier($sequenceName)); return new Sequence($sequenceName, $data[0]['increment_by'], $data[0]['min_value']); } 

(added or true both places where it is assumed that the schema name! = 'public')