Add PostgreSQL support

[geerlingguy]

With https://www.drupal.org/node/2157455 finally having been completed, it would be nice if users could switch from MySQL/MariaDB/Percona to PostgreSQL (at least for Drupal 8) optionally.

I don't currently have a Postgres role, but could add one or find a good one in Galaxy. Besides also installing php-mysql, there's not much else that needs to be conditionally changed besides swapping out Posgres for MySQL.

[gitoutthere]

This actually what I need at the moment, being able to add geerlingguy.pgsl to playbook along side with the PHP connector

[gitoutthere]

I used ANXS.postgresql seems to be working ok

[geerlingguy]

@gitoutthere - Could you leave some instructions for what changes you made in this issue, so I can point others interested in trying out Postgres with Drupal here? I'm still planning on adding 'official' support sometime, but would be glad to 'unofficially' support it as best I can for now.

[gitoutthere]

@geerlingguy cool I am still trying to wrestle and get my Envs setup. But here is the steps I took to get Postgres working, in your vagrant folder:

1. Add 'ANXS.postgresql' to '/provisioning/requirements.txt'
2. Run sudo ansible-galaxy install -r provisioning/requirements.txt --force to install the new plugin.
3. Modify your '/provisioning/playbook.yml' and add - ANXS.postgresql to the roles.
4. Override settings on '/config.yml'. Config can be found on https://github.com/ANXS/postgresql

[JorisVanEijden]

ANXS.postgresql seems to be Ubuntu only.
I've been using patrik.uytterhoeven.PostgreSQL-For-RHEL6x for CentOS 5,6 and 7

Would love to see these combined into a geerlingguy.postgresql role 👍

[geerlingguy]

@JorisVanEijden - heh, always the optimist! I'll see what I can do, now that everything in Drupal 8 is running and tested with Postgres.

[JorisVanEijden]

Well, 13 of the 14 galaxy roles in use here at oneshoe are geerlingguy roles. I'd say our infra has been pretty much geerlinged since buying your book half a year ago 😉

[gitoutthere]

Any of you guys are here at DrupalCon? I would love to catch up with you.

On 23 September 2015 at 09:48, JvE [email protected] wrote:

Well, 13 of the 14 galaxy roles in use here at oneshoe are geerlingguy
roles. I'd say our infra has been pretty much geerlinged since buying your
book half a year ago [image: 😉]

—
Reply to this email directly or view it on GitHub
#146 (comment)
.

[geerlingguy]

@gitoutthere - I wish! Watching presentations remotely, and participating in Twitter, but not much else.

[kevinquillen]

I am going to give this a shot this weekend.

[oxyc]

It seems ANXS.postgresql supports Centos now: ANXS/postgresql#141 (comment)

[oxyc]

I've got a testable PR for this #490.

[DougDavenport]

Greetings,

Wondering what is the status of PostgreSQL?

[geerlingguy]

@DougDavenport - This is not a high priority for me right now, though I still do want to add at least minimal support.

I currently don't have any Drupal sites using Postgres, and know very few people who do, so just like with HHVM, it's kind of a niche use case that is more on the back burner for this project.

[DougDavenport]

Thanks, we are new to using Drupal and have good experience with Postgres on other projects. I have noticed comments that some Drupal modules are said not to support Postgres. We are flexible, what are the pros/cons of the other DB choices? What do you advise?

[geerlingguy]

The Drupal community (and most other PHP CMS communities) has traditionally only supported MySQL (and by extension, MariaDB). While other databases have had varying levels of support, none are as widely used and supported as My/Maria, so some modules and even some older Drupal core components had queries that were either optimized for MySQL or would fail in interesting ways on other relational databases.

With Drupal 8, Postgres support is a lot better, but it's still very slow in the uptake in the Drupal community, so I'm putting this on the backburner, but I definitely want to get to it soon so it's easier for people to compare the same site under Postgres vs. MySQL vs. MariaDB.

[laceysanderson]

For what it's worth, PostgreSQL support is very important for those of us who use Tripal. Tripal provides support for storing data related to biology/agriculture research in a Drupal website and facilitates storing, organization and analysis of that data. However, it requires PostgreSQL because the community developed schema (used by a host of open-source analysis/viewer programs, not just Tripal) has PostgreSQL-specific features.

As a Tripal developer, I would really like to use and encourage others to use the Drupal VM since most Tripal/Drupal sites (currently 91 instances) will need to develop extensions and customizations but won't have the resources to use/own a dedicated development server.

I'm going to try to get PostgreSQL working as it seems others have earlier in the queue but I have no vagrant experience so any help or guidance would be VERY much appreciated!

[oxyc]

@laceysanderson at the moment there's no easy way to add it :/

You would have to cancel and redo a lot of the stuff Drupal VM does. Something like this (untested so only usable as a starting point).

Add your own ansible playbook that runs after Drupal VM (docs):

Create a Vagrantfile.local with:

config.vm.provision 'ansible' do |ansible|
  ansible.playbook = "#{host_config_dir}/local.playbook.yml"
  ansible.galaxy_role_file = "#{host_config_dir}/local.requirements.yml"
end

Create a local.requirements.yml next to your config.yml with:

---
- src: ANXS.postgresql

Then create a local.playbook.yml with:

---
- hosts: all
  become: yes

  vars_files:
    - default.config.yml # Not entirely sure these are the paths...
    - config.yml

  pre_tasks:
    - name: Install PHP PostgreSQL dependencies.
      # if you're using Ubuntu 14.04, might be `php7.0-pgsql` or `php-pgsql`, not sure
      apt: name=php5-pgsql state=installed
      when: ansible_os_family == 'Debian'
    - name: Install PHP PostgreSQL dependencies.
      yum: name=php-pgsql state=installed
      when: ansible_os_family == 'RedHat'

  roles:
    - ANXS.postgresql

  tasks:
    - name: Check if site is already installed.
      command: >
        /usr/local/bin/drush status bootstrap
        chdir={{ drupal_core_path }}
      register: drupal_site_installed
      failed_when: false
      changed_when: false
      become: no

    - name: Install Drupal with drush.
      command: >
        /usr/local/bin/drush site-install {{ drupal_install_profile | default('standard') }} -y
        --site-name="{{ drupal_site_name }}"
        --account-name={{ drupal_account_name }}
        --account-pass={{ drupal_account_pass }}
        --db-url=pgsql://{{ drupal_mysql_user }}:{{ drupal_mysql_password }}@localhost/{{ postgresql_databases[0].name }}
        {{ drupal_site_install_extra_args | default([]) | join(" ") }}
        chdir={{ drupal_core_path }}
      notify: restart webserver
      when: "'Successful' not in drupal_site_installed.stdout"
      become: no

    - name: Install configured modules with drush.
      command: >
        /usr/local/bin/drush pm-enable -y {{ drupal_enable_modules | join(" ") }}
        chdir={{ drupal_core_path }}
      when: "'Successful' not in drupal_site_installed.stdout"
      become: no

And finally add these to your config.yml:

postgresql_databases:
  - name: "{{ drupal_mysql_database }}"
    owner: "{{ drupal_mysql_user }}"

postgresql_users:
  - name: "{{ drupal_mysql_user }}"
    pass: "{{ drupal_mysql_password }}"
    encrypted: no

postgresql_user_privileges:
  - name: "{{ drupal_mysql_user }}"
    db: "{{ drupal_mysql_user }}"
    priv: "ALL"

# We have to re-define these tasks as mysql is hardcoded.
install_site: false

[laceysanderson]

Awesome!! That worked :-) I had managed to get partway there following @gitoutthere's comment on Aug 4, 2015 but I was running into problems forcing the new requirements plus that wouldn't have gotten me the php-pg link. Your recipe @oxyc worked perfectly! I did have to add a local ansible.cfg in order to get past an issue with Ansible 2.1 and the unpriviledged postgres user (issue: ansible/ansible#16048):

Created a ansible.cfg with:

# This needs to be set in order for postgresql to be installed.
# postgresql installation requires becoming the unpriviledged postres
# use which is known to be a security risk:
#  https://docs.ansible.com/ansible/become.html#becoming-an-unprivileged-user
# As of Jun 2016 it is uncertain how to get around this as determined
# in this discussion: https://github.com/ansible/ansible/issues/16048
[defaults]
allow_world_readable_tmpfiles = TRUE

Once I added that and re-provisioned I was able to get a fully installed Drupal site with postgresql backend :-D Thanks!!

[fubarhouse]

Off the back of this, I am seriously considering creating a role to suit this purpose.

As far as I'm aware, are these the requirements you're seeking, and if I was to engage in this task would I have your support? I'm a MySQL man, so I wouldn't actually be using this one myself.

Must support

• Support for Debian/Ubuntu
• Support for Arch
• Support for RHEL/CentOS

Role purpose

• Installs PostgreSQL
• Passes tests via Travis
• Must be available to the galaxy

[geerlingguy]

@fubarhouse - Support for Arch would be optional.

But yes, it would need to be available on Galaxy, and it also should support easy addition of databases / database users (like the mysql_databases and mysql_users vars in the MySQL role).

[fubarhouse]

@geerlingguy You only have Ubuntu and CentOS available via the Hashicorp Atlas, are you able to recommend a box for Arch - perhaps the one used to test support for DrupalVM?

Also, I've tested ANXS.postgresq on your ubuntu and centos boxes and they pass perfectly fine - besides the optional arch support I can't see much more benefit in rewriting that role. What're your thoughts on this matter?

The way I see it, as is you could tack on support for this - though I'm uncertain of the extent of which Drupal VM supports Arch.

[geerlingguy]

I don't currently do any Arch testing—it's not currently supported as a base box for the VM (though some users run it as their host OS).

[fubarhouse]

There's a rundown of all the postgresql specific modules on the following tasks file, as long as tasks are called independently it should be an easy integration!

https://github.com/gulcin/2ndQuadrantBlog/blob/master/AnsibleLovesPostgreSQL/main.yml

[geerlingguy]

Note that I'm working on a simpler PostgreSQL role here: https://galaxy.ansible.com/geerlingguy/postgresql/ (most of the other roles I've tried are either crazy-complex, only work on one or two OSes, etc.

Just tagged version 1.0.0, working on Debian, Ubuntu, and CentOS (haven't tested Fedora or Arch). I'll work on a couple other supporting roles and see if I can adapt the awesome work @oxyc has done so far.

The main reasons I'm interested are:

1. I want to do some fulltext search comparisons between MySQL/Solr/PostgreSQL (Solr should win... but if Postgres is as good as some people say, it could be a good middle solution when Solr is overkill).
2. I want to do some benchmarking between different RDBMSes for Drupal and other projects.

[geerlingguy]

Current status:

1. For automated builds, everything works up to drush site-install; at that point, with the Composer-based installation, we get the error:

Starting Drupal installation. This takes a while. Consider using the [ok]
--notify global option.
Error: Undefined class constant 'MYSQL_ATTR_USE_BUFFERED_QUERY' in /var/www/drupalvm/drupal/web/core/lib/Drupal/Core/Database/Driver/mysql/Connection.php on line 128
Drush command terminated abnormally due to an unrecoverable error.   [error]

1. If I wipe out the Composer-based installation and download Drupal 8.x core manually, then run the GUI installer at http://drupalvm.dev/core/install.php, then I hit the error message:

The bytea_output setting is currently set to 'binary', but needs to be 'escape'

I found this drupal.org issue (and see this forum topic, which tells me I have to run the following query to get the Drupal pgsql database in working order:

ALTER DATABASE drupal SET bytea_output = 'escape';

(To run that manually, vagrant ssh in, enter sudo su - postgres, then run psql, then run the query.)

After doing that, I get a successful GUI install, and the following in the status report:

• Database system: PostgreSQL
• Database system version: 9.5.4

So now I'm going to work on making sure the database is adjusted correctly (but why?), and also why Drush + Composer-based install is failing (whereas downloading manually works).

[Edit:] From pgsnake's comment, it looks like we can set the bytea_output option globally inside postgresql.conf, so I'm going to do that and see if it works...

[geerlingguy]

See also: [meta] Remaining Drupal 8 PostgreSQL issues. It seems that PostgreSQL support in D8 is similar to Nginx... most people use the old stalwart (e.g. Apache, MySQL), and the smaller percentage that do use the alternative (Nginx, PostgreSQL) are forced to work through a lot of problems independently :)

[geerlingguy]

Well, would you look at that!

Now just waiting on build #1009 to succeed...

[geerlingguy]

The build failed with:

InstallerException: Resolve all issues below to continue the
installation. For help configuring your database server, see the
installation handbook</a>, or contact your hosting provider.

The bytea_output setting is currently set to 'hex', but needs to
be 'escape'. Change this by running the following query:
ALTER DATABASE "drupal" SET bytea_output = 'escape';
in /var/www/drupalvm/drupal/web/core/includes/install.core.inc:911

But it worked locally. Re-running locally to see if I can reproduce the error :/

[geerlingguy]

Hmm... getting that locally too.

[geerlingguy]

It seems that global config change won't take effect until a restart of the server, and it feels like a hacky way of going about it anyways; if I want to use that, I'll have to do a meta: flush_handlers somewhere in a role, and I hate having to do that.

So instead, I'll add a task right before site install:

# See: https://www.drupal.org/node/2569365#comment-11680807
- name: Configure database correctly if using PostgreSQL.
  command: psql -c "ALTER DATABASE {{ drupal_db_name }} SET bytea_output = 'escape';"
  when: "'Successful' not in drupal_site_installed.stdout"
  become: yes
  become_user: "{{ postgresql_user }}"
  # See: https://github.com/ansible/ansible/issues/16048#issuecomment-229012509
  vars:
    ansible_ssh_pipelining: true

Running local tests again.

[geerlingguy]

@laceysanderson - FYI, I am using a workaround for the Ansible 2.1+ issue you mentioned earlier (ansible/ansible#16048) in the PostgreSQL role, so you won't need to maintain that specialized ansible.cfg file (which makes things slightly less secure overall!) anymore.

[geerlingguy]

Whee!