CiviCRM Community Forums (archive)

I am not sure if this is the correct place to ask but I thought I start here.


I would like to do a non-standard install of civicrm. I have several drupal sites running on localhost because I am doing some custom development. I would like to setup civicrm using Postgresql. I know you don't currently support this, and the newest post on any support was in 2010.

Would it be possible to do a install of civicrm using a mysql database for the civicrm database and a postgresql database for the drupal database? I figure since there is already going to be two databases, I try that.

The reason I even need postgresql is I am working with spatial data and the best database for it is postgis. And since there is already an postgis drupal module, I would really like to keep working with drupal. Your software does pretty much everything I want on the crm part but I need to integrate it with the drupal postgis.

Anyway, any suggestions on how to install civicrm so that is allows drupal to keep using the postgresql database would most welcome!

To install civi on mysql with drupal + postgres I disabled 1 string in civi install file (line 237), which disables drupal db config check:
if ($installType == 'drupal') {
    //$dbReq->checkdatabase($drupalConfig, 'Drupal');
  }

check the file:

install/index.php

at the top level of your civi install

lobo

After getting Drupal (7) installed on Postgresql and CiviCRM installed on Mysql, I am having problems querying contacts in Views (3). I suspect I do not have the correct code in settings.php...

I tried to follow the instructions, http://wiki.civicrm.org/confluence/display/CRMDOC40/Views3+Integration , but I think this assumes both databases are of the same type(?)

In Views, I have the option to create a new view based on CiviCRM Contacts, but no results are shown. It shows the following error:

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "`" LINE 5: `ac_civicrm`.civicrm_contact civicrm_contact) subquery ^

I have tried to require a relationship between CiviCRM Contacts : Drupal ID, but it does not seem to change anything.

I SUSPECT that the problem is that I have Drupal installed in a Postgresql database and CiviCRM in a Mysql database and Views is not connecting properly to the Mysql database. I do have a user with the same username and password for both databases.

Here is the relevant portion of my settings.php file:

$databases['default']['default'] = array(
  'database' => 'ac_drupal',
  'username' => 'ac',
  'password' => 'drupal',
  'host' => 'localhost',
  'port' => '',
  'driver' => 'pgsql',
  'prefix' => '',
);
$databases['default']['default']['prefix']= array(       
  'civicrm_acl'                              => '`ac_civicrm`.',
  'civicrm_acl_cache'                        => '`ac_civicrm`.',
  ...
);

Does anybody see any obvious mistakes?

Thanks.

Thanks for the response Lobo. But does this mean I am barking up the wrong tree? Is it not possible to query CiviCRM Contact info in Views with Drupal installed on Postgresql?

The reason I need to have Drupal installed on Postgresql (PostGIS) is for better mapping functionality. Can you suggest of another way to accomplish this (considering I am not a programmer).

Thanks for your time.

Aaron

Well, I think I found a solution, in case anybody else has the same issue.

I found in Views (under Query Settings) an option to 'Use Slave Server', which I selected.

Then in the settings.php file I changed the 'default' to 'slave' as follows:

$databases['default']['slave'] = array(
  'database' => 'ac_civicrm',
  ......

And it seems to work! Thanks for the nudge Lobo. Very happy to find this solution.

Lobo, asuttonsd,

I also want to run CiviCRM on PostgreSQL due to PostGIS and mapping (MappingDC and OpenStreetMap).  My drupal instance already has views, OpenLayers, and a bunch of other modules installed.

I started with a CiviCRM 4.2.7 download.  I converted the mysql sql files (schema and demo data) to PG SQL.  I manually ran that.  I didn't expect install.php to run.  I manually created a civicrm_settings.php file and put that into place in the defaults folder.

I was getting all sorts of Page Not Found issues.  Since install.php wasn't run, i placed a call to rebuild_menu() in the drupal bootstrap file and hit the website once, before taking that out.  This populated the drupal menu_router table and fixed that issue.

Just about every CiviCRM page is giving me errors because its queries are MySQL specific.  First i ran into TIMESTAMPDIFF.  Then i ran into while ( 1 ).  That should be while true.    Also ran into select foo as `foo`.  Oh and PEAR DB doesn't guess the sequence names right.  Anyway... i'm slogging through these pages one by one getting them to load without generating tons of errors.  I manipulated CRM/Core/Error.php to product better error messages.

asuttonsd, In MySQL you can JOIN across databases.  In PG you can not.  Though PG has schemas (MySQL does not), and you can JOIN across those.  Drupal supports schemas, CiviCRM does not.  For now i'm using two PG databases.  So theoretically CiviCRM may try to JOIN across databases, but i don't think it does, because it  asks for separate credentials to each database.  For now i'm using 2 PG databases and later i will move them into a single database.

I could use some support on this project, because i can tell already it's going to be a LOT of work.  My MySQL isn't great.  For example, i know they support LIMIT offset, limit.  But do they also support LIMIT limit OFFSET offset?  In this effort to get CiviCRM to support PG, of course, i will try to maintain support for MySQL.

If there are enough people willing to support this effort, i'm willing to stand up a dev server and SVN repository to track changes.  I prefer working with stable code, but i could be convinced to use HEAD.

Brian

In 1998 i did my own analysis between PostgreSQL and MySQL.  I picked PG due to it being standards based and having more features over MySQL (triggers, viewes, stored procedures, sequences).  I'm a PG expert and a MySQL newb.  So there's that.  And it just annoys me that a project locks itself into one database (e.g. WordPress).  I've already helped the PHPList project add support for PostgreSQL.

Finally, i think there can be some synergy with using a single database.  Wouldn't it be nice to know the location of constituents and be able to perform a spatial query on them like, find me all people in a 12 mile radius of Chicago.  Granted with less than 10,000 constituents there's not much benefit of a spatial database.

I'm still learning the features of CiviCRM, but surely there can be an advantage to a spatial database.  I'm thinking of CiviGeo.

Brian