Add PostgreSQL support?

[raj45]

For historic reasons we use PostgreSQL with Drupal. Is it possible to add support for PostgreSQL in Vlad?

[philipnorton42]

I have to admit that I'm somewhat reluctant to add support for a service to support legacy applications. Unless there is a bunch of support for this then it probably won't happen quickly (sorry!).

That said, this is the sort of thing we created custom roles for (see docs at http://vlad-docs.readthedocs.org/en/latest/usage/custom_roles/). The use case here is perfect and there are a few PostgreSQL ansible roles knocking about on github (e.g. https://github.com/ANXS/postgresql)

[raj45]

I totally understand, and I wish it wasn't necessary, since adding support for PostgreSQL probably involves quite a bit of effort, and, like you say, not too many use it anyway.

I actually had a go at adding a custom role via the two links you mention, but couldn't make it work... I think I'll try to convert a DB-dump from PostgreSQL to MySQL in stead.

[philipnorton42]

I'd be interested to know what you couldn't get working with custom roles. Other people are likely to find similar issues and It'd be good to update the documentation with more details.

[raj45]

This is how I do it:

mkdir vlad_test_folder
cd vlad_test_folder
git clone https://github.com/hashbangcode/vlad.git
git clone https://github.com/ANXS/postgresql.git

Creates this structure:

vlad_test_folder/
├── postgresql
│   ├── defaults
│   ├── handlers
│   ├── LICENSE
│   ├── meta
│   ├── README.md
│   ├── tasks
│   ├── templates
│   ├── test.yml
│   ├── Vagrantfile
│   ├── vagrant-inventory
│   └── vars
└── vlad
    ├── ansible.cfg
    ├── docroot
    ├── README.md
    ├── Vagrantfile
    ├── vlad
    └── vlad_aux

and then...

cd vlad/
cp vlad/example.settings.yml vlad/settings.yml
vi vlad/settings.yml 
 - change to "boxname: vladtest", to not overwrite existing Virtualbox machine
 - add "custom_provision: true"
vagrant up
vagrant ssh

In the box:

✝ vladtest ✝ /var/www/site/docroot $ psql --version
The program 'psql' can be found in the following packages:
 * postgresql-client-common
 * postgres-xc-client
Ask your administrator to install one of them
✝ vladtest ✝ /var/www/site/docroot $ postgres -V
The program 'postgres' is currently not installed. To run 'postgres' please ask your administrator to install the package 'postgres-xc'

So it doesn't seem the role is getting detected. Do you know if I need to do some more stuff for it to get picked up by Vagrant?

[dixhuit]

The custom role won't be picked up by Vlad in this situation because:

• it's in the wrong place
• it's not named as Vlad expects

From Vlad's docs:

Vlad expects the custom role to be located at the following path, relative to the root of Vlad's own codebase (e.g. relative to the Vagrantfile): ../vlad_custom

http://vlad-docs.readthedocs.org/en/latest/usage/custom_roles

You need to change your file structure to something more like:

vlad_test_folder/
├── vlad_custom/
│   ├── defaults/
│   ├── handlers/
│   └── rest of role...
└── vlad/
    ├── ansible.cfg
    ├── Vagrantfile
    ├── vlad/
    └── rest of Vlad's codebase...

[raj45]

Thanks Dan. I renamed the folder "postgresql" to "vlad_custom", and it is getting picked up now, after running "vagrant provision":

TASK: [vlad_custom | PostgreSQL | Make sure the CA certificates are available] *** 
ok: [192.168.100.100]
TASK: [vlad_custom | PostgreSQL | Add PostgreSQL repository apt-key] ********** 
failed: [192.168.100.100] => {"cmd": "apt-key add -", "failed": true, "rc": 1}
stdout: ERROR: This command can only be used by root.
FATAL: all hosts have already failed -- aborting
PLAY RECAP ******************************************************************** 
           to retry, use: --limit @/home/raj/site_custom.retry
192.168.100.100            : ok=2    changed=0    unreachable=0    failed=1   
Ansible failed to complete successfully. Any error output should be
visible above. Please fix these errors and try again.

The apt-key error is happening here: vlad_custom/tasks/install.yml

[dixhuit]

TASK: [vlad_custom | PostgreSQL | Add PostgreSQL repository apt-key] ********** 
failed: [192.168.100.100] => {"cmd": "apt-key add -", "failed": true, "rc": 1}
stdout: ERROR: This command can only be used by root.

Sounds like that Ansible task may need to have sudo: true added to it?

[raj45]

That sounds right -- there is an ANXS/postgresql issue waiting to be merged: Error when installing as a user other than 'root'

[raj45]

I tried to change ANXS/postgresql according to the merge pull request above, and it got a little bit further, but not all the way:

TASK: [vlad_custom | PostgreSQL | Make sure the CA certificates are available] *** 
ok: [192.168.100.100]
TASK: [vlad_custom | PostgreSQL | Add PostgreSQL repository apt-key] ********** 
changed: [192.168.100.100]
TASK: [vlad_custom | PostgreSQL | Add PostgreSQL repository] ****************** 
changed: [192.168.100.100]
TASK: [vlad_custom | PostgreSQL | Add PostgreSQL repository preferences] ****** 
failed: [192.168.100.100] => {"failed": true}
msg: Destination /etc/apt/preferences.d not writable
FATAL: all hosts have already failed -- aborting
PLAY RECAP ******************************************************************** 
           to retry, use: --limit @/home/raj/site_custom.retry
192.168.100.100            : ok=4    changed=2    unreachable=0    failed=1   
Ansible failed to complete successfully. Any error output should be
visible above. Please fix these errors and try again.

[philipnorton42]

Looks like the same problem. That is, the user that ansible is using to perform the action doesn't have access to that directory. To solve, you'll need to add 'sudo: true' to any admin tasks.

There are two approaches to sudo actions in ansible. 1) Provide a global 'sudo: true' parameter. This means that every task (unless otherwise indicated) is run via sudo. 2) Provide the sudo: true parameter on a task by task basis.

In Vlad, the decision was made to go for the task-by-task sudo approach so that it was very clear what was and wasn't being run with sudo. This parameter is applied to install tasks and system tasks, but for everything else it isn't needed and just adds complexity. As a silly example, say we create a .bash_profile file (which we do) using the sudo parameter. We would then need to ensure that the permissions and owner of that file were correct before moving onto the next task. By simply leaving out the sudo parameter we automatically set up the file with the correct permissions.

I hope that makes things clear as to what is going on and why it is happening :)

Also, unless anyone else is desperately looking for Vlad to support postresql I think that adding the custom role should be enough. As a result I'm going to close this ticket. We may need to better support userland ansible roles, but that is another issue :)

[raj45]

Thanks for writing an explanation on the reasoning behind the structural decisions. It is great for people new to Ansible, like myself, to get an understanding of what is happening behind the scene :+1:!

I actually tried just now with another PostgreSQL Ansible script (zenoamaro/ansible-postgresql), and it seems to get quite far on the default settings provided with the role. The commands and the outcome below:

mkdir vlad_postgres
cd vlad_postgres/
git clone https://github.com/hashbangcode/vlad.git
git clone https://github.com/zenoamaro/ansible-postgresql.git
mv ansible-postgresql/ vlad_custom
cp vlad/vlad/example.settings.yml vlad/vlad/settings.yml
vi vlad/vlad/settings.yml # change boxname to "boxname: vladtest" and add "custom_provision: true"
cd vlad
vagrant up
vagrant ssh

Result

TASK: [vlad_custom | Adding APT repository key] ******************************* 
changed: [192.168.100.100]
TASK: [vlad_custom | Add PostgreSQL official APT repository] ****************** 
changed: [192.168.100.100]
TASK: [vlad_custom | Install PostgreSQL] ************************************** 
changed: [192.168.100.100]
TASK: [vlad_custom | Install dependencies for the Ansible module] ************* 
changed: [192.168.100.100] => (item=python-psycopg2)
TASK: [vlad_custom | Install development headers] ***************************** 
skipping: [192.168.100.100]
TASK: [vlad_custom | Install PostgreSQL contribs] ***************************** 
skipping: [192.168.100.100]
TASK: [vlad_custom | Add postgis extensions] ********************************** 
skipping: [192.168.100.100]
TASK: [vlad_custom | Create the necessary directories] ************************ 
changed: [192.168.100.100] => (item=/etc/postgresql/9.4/main)
changed: [192.168.100.100] => (item=/var/lib/postgresql/9.4/main)
ok: [192.168.100.100] => (item=/var/run/postgresql)
changed: [192.168.100.100] => (item=/var/log/postgresql)
TASK: [vlad_custom | Configure PostgreSQL] ************************************ 
changed: [192.168.100.100] => (item=postgresql.conf)
changed: [192.168.100.100] => (item=pg_ctl.conf)
changed: [192.168.100.100] => (item=environment)
TASK: [vlad_custom | Configure PostgreSQL (authentication)] ******************* 
changed: [192.168.100.100]
TASK: [vlad_custom | Configure PostgreSQL (ident)] **************************** 
changed: [192.168.100.100]
NOTIFIED: [vlad_custom | restart postgresql] ********************************** 
failed: [192.168.100.100] => {"failed": true}
msg:  * Stopping PostgreSQL 9.4 database server
   ...done.
 * Starting PostgreSQL 9.4 database server
 * Error: could not exec /usr/lib/postgresql/9.4/bin/pg_ctl /usr/lib/postgresql/9.4/bin/pg_ctl start -D /var/lib/postgresql/9.4/main -l /var/log/postgresql/postgresql-9.4-main.log -s -o  -c config_file="/etc/postgresql/9.4/main/postgresql.conf" : 
   ...fail!
FATAL: all hosts have already failed -- aborting
NOTIFIED: [vlad_custom | restart postgresql] ********************************** 
FATAL: no hosts matched or all hosts have already failed -- aborting
FATAL: all hosts have already failed -- aborting
NOTIFIED: [vlad_custom | restart postgresql] ********************************** 
FATAL: no hosts matched or all hosts have already failed -- aborting
FATAL: all hosts have already failed -- aborting
PLAY RECAP ******************************************************************** 
           to retry, use: --limit @/home/raj/site_custom.retry
192.168.100.100            : ok=9    changed=8    unreachable=0    failed=1   
Ansible failed to complete successfully. Any error output should be
visible above. Please fix these errors and try again.

[philipnorton42]

Looks like it fails on the restart task, although it did cover everything else without failure. Which would point at an issue in the config. I would check the logs, see if anything stands out and report your findings back to the project issue page (https://github.com/zenoamaro/ansible-postgresql/issues).

[raj45]

Thanks Philip, I submitted an issue at the project: Problem getting it to work as a custom role

[raj45]

The solution was to reinstall the language package inside the box, before adding the ansible-postgresql role and running vagrant provision:

sudo apt-get install --reinstall language-pack-en-base

Is it worth considering including this in the Vlad Playbook? EDIT: I have created issue #244 about this.

Before running the commands

✝ vladtest ✝ /var/www/site/docroot $ locale
locale: Cannot set LC_ALL to default locale: No such file or directory
LANG=en_US.UTF-8
LANGUAGE=
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC=en_DK.UTF-8
LC_TIME=en_DK.UTF-8
LC_COLLATE="en_US.UTF-8"
LC_MONETARY=en_DK.UTF-8
LC_MESSAGES="en_US.UTF-8"
LC_PAPER=en_DK.UTF-8
LC_NAME=en_DK.UTF-8
LC_ADDRESS=en_DK.UTF-8
LC_TELEPHONE=en_DK.UTF-8
LC_MEASUREMENT=en_DK.UTF-8
LC_IDENTIFICATION=en_DK.UTF-8
LC_ALL=

After

✝ vladtest ✝ /var/www/site/docroot $ locale
LANG=en_US.UTF-8
LANGUAGE=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=en_US.UTF-8

[raj45]

This should work

git clone https://github.com/hashbangcode/vlad.git vlad_pgtest/vlad
# add custom role postgresql
git clone https://github.com/zenoamaro/ansible-postgresql.git vlad_pgtest/vlad_custom
cd vlad_pgtest/vlad
cp vlad/example.settings.yml vlad/settings.yml
# update vlad boxname
sed -i 's/boxname: "vlad"/boxname: "vladBOXNAME"/' vlad/settings.yml
vagrant up
vagrant ssh
# reinstall language package to insert missing locale settings
sudo apt-get install --reinstall language-pack-en-base
locale # check it works now
# exit the box and udpdate settings
# add "custom_provision: true" in vlad/settings.yml
sed -i '17i # add PostgreSQL role\n  custom_provision: true\n' vlad/settings.yml
vagrant provision
vagrant ssh
# create PostgreSQL user and database
sudo su - postgres
psql template1
CREATE USER vagrant WITH PASSWORD 'wibble';
CREATE DATABASE YOUR_DATABASE_NAME;
GRANT ALL PRIVILEGES ON DATABASE YOUR_DATABASE_NAME to vagrant;
\q
sudo su - postgres
psql vagrant -h 127.0.0.1 -d YOUR_DATABASE_NAME -p wibble # verify access
## install PHP postgres extensions and enable in php.ini
sudo -i
echo -e "\nextension=php_pgsql.dll" >> /etc/php5/apache2/php.ini
apt-get install php5-pgsql
service apache2 restart 
# install Drupal
drush -y si \
 --account-name=admin \
 --account-pass=admin \
 --account-mail=mail@example.com \
 --db-url=pgsql://vagrant:wibble@localhost/YOUR_DATABASE_NAME \
 --site-mail=mail@example.com \
 --locale=en \
 --verbose

[dixhuit]

@raj45 Quick update to say that the new 1.1.4 release of Vlad has replaced custom roles with full custom playbooks (within which you can add as many roles as you like, from wherever you like). This development seemed relevant to this thread so I thought I'd drop by and let you know. More details:

• http://vlad-docs.readthedocs.org/en/latest/project/changelog
• http://vlad-docs.readthedocs.org/en/latest/usage/custom_playbook

Enjoy!

[raj45]

Thanks @danbohea! I am trying to convince my colleagues to start using MySQL with Drupal, and MySQL has in fact just today been chosen as DB for a new project, Hooray!