PostGres support

Drupal 6.11 with postgreSQL and usage of postgres schema

Text moved to drupal core issues, now here: #459134: Contribution: Drupal 6.11 with postgreSQL and usage of postgres schema

@Norbert Poellmann: This issue is for reviewing PostGres support in the contributed migrate module. To submit patches for core, please use the Drupal project: http://drupal.org/project/drupal

When migrating data from external PostGres database to default Drupal's Postgres database, I get the following error:
ERROR: schema "drup" does not exist query: SELECT document.document_id AS document_id, div_document.document_id AS div_document_document_id, document.date_created AS document_date_created, document.website_id AS document_website_id, document.date_rel AS document_date_rel, document.locale AS document_locale, document.date_modified AS document_date_modified, document.seq AS document_seq, document.status AS document_status, document.head AS document_head, document.subhead AS document_subhead, document.author AS document_author, document.annotation AS document_annotation, document.ref_email AS document_ref_email, document.picture AS document_picture, document.ref_url AS document_ref_url, document.announce AS document_announce, document.created_by AS document_created_by, document.modified_by AS document_modified_by, document.doc_alias AS document_doc_alias, source.source_id AS source_source_id, document.priority AS document_priority, document.introduction AS document_introduction, graphics.filename AS graphics_filename, textdata.text AS textdata_text FROM document document LEFT JOIN div_document div_document ON document.document_id = div_document.document_id LEFT JOIN source source ON document.source_id = source.source_id LEFT JOIN graphics graphics ON document.picture = graphics.filename LEFT JOIN textdata textdata ON document.document_id = textdata.document_id LEFT JOIN drup.document_map document_map ON document.document_id = document_map.sourceid LEFT JOIN drup.document_msgs document_msgs ON document.document_id = document_msgs.sourceid WHERE (document_map.sourceid IS NULL) AND (document_msgs.sourceid IS NULL) LIMIT 5 OFFSET 0 in /var/www/dru.polit/htdocs/sites/all/modules/migrate/migrate.module on line 400.
AFAICS Postgres does not support at all queries involving multiple databases. I HAVE TO use a different schema in the default database to achieve what I wanted. Also, I think one can hit this error when trying to import data from Postgres to a mysql-based Drupal installation. I suppose the right way to deal with it is to use just one database per query, probably doing these joins in PHP (yes I understand this is cruel) or first importing data to default database and then doing the joins.

Do you have Schema 6.x-1.5-beta1, which adds support for Postgres schemas? I'm not sure if that will help, but it's worth a shot.

Apart from that, it's hard to see what the migrate module can do here - if Postgres can't join between two dbs (or schemas, or whatever), then you have to put your source tables into the "drup" schema, right?

Postgres can join across schemas within a database. But Migrate module, for example, can't import data from postgres to mysql or vice versa (not only between two Postgres databases) because of such statement usage. So, I think this is worth fixing or at least noticing in the documentation.

OK, I didn't follow - you're trying to migrate directly from a Postgres database into a MySQL Drupal installation, correct? Yes, that definitely is not workable, you need to copy the data into MySQL first, then apply the Table Wizard to the MySQL table copies to migrate from there into Drupal objects - this is what we're doing on my current project coming from Oracle.

Changing to a documentation task.