postgresql backend support

for what it's worth, the new backend allows for this to be developed.

i don't know if i want to make a point of it though. The API gets better by having multiple backends, but it increases our testing requirements.

Fast forward about a year... I thought I'd attach a non-working/semi-working patch.

Start of a pgsql service patch for provision.

It would work much better if the db type didn't get magically changed to mysql halfway through the process. I was able to force things in by brute forcing db_type, db_user, db_passwd, db_host, and db_port in db/db.drush.inc in several class methods of ProvisionService_db and ProvisionService_db_pdo. It still fails on some other assumptions.

Drush command that I'm using:

drush --verbose hostmaster-install --db_host=localhost --db_port=5432 --db_user=aegir --db_passwd=mypassword --version=0.4-alpha12 --master_db=pgsql://aegir:mypassword@localhost:5432 --db_service_type=pgsql --remote_host=localhost

I needed all those options because it defaults to mysql, mysql default port, etc... (I haven't been able to figure out where). Halfway through the process it will default to mysql, mysql default port, and dsn again as mentioned above.

I'm also running into an issue where I need aegir db_user and aegir db created to begin with, but then it wants to create them again later on in the install. This may be some mix up in instructions that I'm not understanding. Any help on those two issues would be appreciated.

Load alias @self                                                                                [notice]
Load alias @server_master                                                                       [notice]
Loading pgsql driver for the db service                                                         [notice]
Loading apache driver for the http service                                                      [notice]
Loading pgsql driver for the db service                                                         [notice]
Aegir domain name [aegir.example.com]: 127.0.0.1
Load alias @self                                                                                [notice]
Load alias @server_master                                                                       [notice]
Loading mysql driver for the db service                                                         [notice]
Loading apache driver for the http service                                                      [notice]
Loading mysql driver for the db service                                                         [notice]
Loading apache driver for the http service                                                      [notice]

Oh wait, I see now. It's getting set to mysql in drush_provision_hostmaster_install.

I have it running better now until it explodes. At some point the $_SERVER variable turns up empty and its keys are set to empty values. I'm not sure where but drupal_get_option('db_type') etc... is returning null at some points, but not at others?

This might be a good headline feature to get into 6.x-2.x, bumping priority.

Has there been any progress on a Postgresql backend with Aegir? Anyone know?

Cheers

Hmm, it seems like efforts here have tried to both add a pgsql service, and install/run the Aegir front-end on said service. The latter should probably be handled separately in #548882: PostgreSQL frontend support. I think efforts in this issue should focus on the former.

Essentially, this should just involve copying /db/Provision/Service/db/mysql.php to /db/Provision/Service/db/pgsql.php and adapting the syntax for PostgreSQL. The patch in #2 looks like a good start in that direction.

We'd also need to add a hostingService_db_pgsql class added to Hosting in db_server/hosting_db_server.service.inc, again modelled off of hostingService_db_mysql.

Oh... and testing, of course :)

I believe the version for this feature was accidentally changed.

I would also like to provision sites on a second postgres database.

Thanks for the great work thus far. Great part of my day dealing with Aegir.

No, this won't make it into 2.x.

FYI, using the attached patches (one for provision, the other for hosting), I've been able to successfully add a pgsql server in Aegir and install a site on this pgsql server... (using Aegir 6.x-2.0). Note that the dumps are not working however...

Considering benchmarks such as these http://posulliv.github.io/2012/06/29/mysql-postgres-bench/ I am fairly excited to see pgsql support in the process of becoming a reality.

This is a small update to make sure that valid connections to the pgsql server that return a failure message of psql: FATAL: role "intntnllyInvalid" does not exist message will not result in a "Connection failed to fail" result.

Here is a new patch which adds backup and restore support. I've been able to successfully migrate a site running on pgsql using this patch, however:

• It is not secure (it displays the password in the command line by exporting it as the PGPASSWORD variable)
• In order to make it work, you will need to first update your pgsql tables to use the "drupal" schema. I had to do this because my pgsql database uses postgis, and so I needed a way to import the database without importing the postgis extension. Here is how to do it:
  1. First, create the drupal schema in your database: CREATE SCHEMA drupal;
  2. Alter the search path: ALTER DATABASE xxx SET search_path="$user",drupal,public;
  3. Alter the drupal tables to make them use the drupal schema: ALTER TABLE xxx SET schema drupal;
  4. In your settings.php, add the "drupal." prefix: $databases['default']['default']['prefix'] = 'drupal.';

Thanks for bumping this to the top of the heap.

We should probably add a section on PostgreSQL support to the documentation. Currently 'server' documentation is somewhat mixed in with sites and platform, or in 'advanced usage'. But we'd definitely want a place to document installation procedures, OS-specific configuration, etc.

As for the patch itself, in #14:

It seems like a number of methods are duplicated verbatim (can_create_database(), import_site_database(), etc.) Maybe those should be migrated to Provision_Service_db_pdo.

generate_site_credentials() still makes reference to 'mysqli' and so will need a cleanup.

This should definitely be secure by default. For MySQL, we pass credentials without exposing them on the command-line: Provision_Service_db_mysql::safe_shell_exec(). Basically we generate a string containing the creds, and pass them to a new process as STDIN. I'm not very familiar with PostgreSQL, so I don't know whether the same mechanics would work there. That code is included here, though. So we should either use it, or remove it. However, proc_open() allows for setting environment variables, so that might be another option.

create_postgis() seems more specific than we should really have here. I may be wrong, of course. But it seems to me that we should be generic in this class, and allow more specific configuration to happen in separate extensions, via pre- or post-hooks. I'd prefer calling a new hook here, rather than including extension-specific code.