Issues with postgresql

[colindaven]

Hi,

I am attempting GKS on a Debian Jessie system. I have ironed out a few issues so far, most notably setting allow_unauthenticated=True in the following to get ansible-apt working.

nano /galaxy_local/GalaxyKickStart/roles/galaxyprojectdotorg.galaxy-os/tasks/debian/packages.yml Add allow_unauthenticated=True to all

TASK [ensure_postgresql_up : service] *** ok: [debiantest]

TASK [ensure_postgresql_up : supervisorctl] ***** fatal: [debiantest]: FAILED! => {"changed": false, "failed": true, "msg": "ERROR: no such process/group: postgresql\n", "name": "postgresql", "state": "present"} ...ignoring

TASK [ensure_postgresql_up : supervisorctl] ***** skipping: [debiantest]

TASK [ensure_postgresql_up : service] *** ok: [debiantest]

TASK [ensure_postgresql_up : command] *** FAILED - RETRYING: ensure_postgresql_up : command (10 retries left). FAILED - RETRYING: ensure_postgresql_up : command (9 retries left). FAILED - RETRYING: ensure_postgresql_up : command (8 retries left). FAILED - RETRYING: ensure_postgresql_up : command (7 retries left). FAILED - RETRYING: ensure_postgresql_up : command (6 retries left). FAILED - RETRYING: ensure_postgresql_up : command (5 retries left). FAILED - RETRYING: ensure_postgresql_up : command (4 retries left). FAILED - RETRYING: ensure_postgresql_up : command (3 retries left). FAILED - RETRYING: ensure_postgresql_up : command (2 retries left). FAILED - RETRYING: ensure_postgresql_up : command (1 retries left). fatal: [debiantest]: FAILED! => {"attempts": 10, "changed": true, "cmd": "/usr/bin/pg_isready", "delta": "0:00:00.069895", "end": "2017-05-11 13:46:17.246481", "failed": true, "rc": 0, "start": "2017-05-11 13:46:17.176586", "stderr": "", "stderr_lines": [], "stdout": "/var/run/postgresql:5432 - Verbindungen werden angenommen", "stdout_lines": ["/var/run/postgresql:5432 - Verbindungen werden angenommen"]} to retry, use: --limit @/galaxy_local/GalaxyKickStart/galaxy.retry

PLAY RECAP ** debiantest : ok=24 changed=4 unreachable=0 failed=1

Postgre seems to be running, so I am not sure what supervisorctl is checking.

ps -aux | grep postg root 7955 0.0 0.0 12748 2256 pts/0 S+ 13:49 0:00 grep postg postgres 30379 0.0 0.3 227444 22344 ? S 11:34 0:00 /usr/lib/postgresql/9.4/bin/postgres -D /var/lib/postgresql/9.4/main -c config_file=/etc/postgresql/9.4/main/postgresql.conf postgres 30381 0.0 0.0 227444 4120 ? Ss 11:34 0:00 postgres: checkpointer process
postgres 30382 0.0 0.0 227444 5848 ? Ss 11:34 0:00 postgres: writer process
postgres 30383 0.0 0.0 227444 4120 ? Ss 11:34 0:00 postgres: wal writer process
postgres 30384 0.0 0.1 227844 6940 ? Ss 11:34 0:00 postgres: autovacuum launcher process
postgres 30385 0.0 0.0 82724 4492 ? Ss 11:34 0:00 postgres: stats collector process

Any ideas ? Thanks, Colin

[drosofff]

Just quick thoughts

Did you check in line the command /usr/bin/pg_isready ? (I can't read the german warning ;-) This is maybe related to the postgresql version that is not the one the task ensure_postgresql_up : command is expecting

[mvdbeek]

Yes, we're checking that the stdout of pg_isready contains the line result.stdout.find("accepting connections"), but that doesn't work with the german localization. I'll see what we can do.

[colindaven]

I saw this

"FYI, I remember that setting the postgresql_version variable to 9.5 or something in group_vars/all helps for Ubuntu 16.04"

and tried changing the group_vars/all from postgresql_version: "{{ '9.3' if ansible_distribution_version | version_compare('15.04', '<=') else '9.5 }}" to ``postgresql_version: "{{ '9.3' if ansible_distribution_version | version_compare('15.04', '<=') else '9.4' }}"

(I have 9.4 as seen in the grep above)

However, this did not help

[colindaven]

Yes, it shouldn't really be german, I had a student install this system. Apologies.

/usr/bin/pg_isready

/var/run/postgresql:5432 - Verbindungen werden angenommen

This means - connections are being awaited

[mvdbeek]

I just pushed a fix in https://github.com/mvdbeek/ensure_postgresql_up/commit/54bb616cc9a8dfb0e09b43d3d26544cd6ceebce6, I think if you remove the ensure_postgresql_up role and reinstall it you should get past that point, but note that we haven't tested Debian 8 at all. If there are issues let us know and we'll address them.

[colindaven]

Great, thanks for this @mvdbeek

This has helped get past this stage, and I am happy to test on Debian 8.

I am not sure if you missed the previous error, of if it is not really problematic?

TASK [ensure_postgresql_up : supervisorctl] ***** fatal: [debiantest]: FAILED! => {"changed": false, "failed": true, "msg": "ERROR: no such process/group: postgresql\n", "name": "postgresql", "state": "present"}

[mvdbeek]

Yes, this is not critical, the postgresql group in supervisor will be defined in another role, that's why this is annotated with ignore_errors: yes. It's just that we should stop postgresql if it is already running.

[colindaven]

So I had further issues with postgresql further downstream (following the miniconda and galaxy steps).

` TASK [ensure_postgresql_up : supervisorctl] ***** fatal: [debiantest]: FAILED! => {"changed": false, "failed": true, "msg": "postgresql: ERROR (no such file)\n"} ...ignoring

TASK [ensure_postgresql_up : service] *** skipping: [debiantest]

TASK [ensure_postgresql_up : command] *** FAILED - RETRYING: ensure_postgresql_up : command (10 retries left). .. FAILED - RETRYING: ensure_postgresql_up : command (1 retries left). fatal: [debiantest]: FAILED! => {"attempts": 10, "changed": true, "cmd": "/usr/bin/pg_isready", "delta": "0:00:00.066518", "end": "2017-05-11 17:23:07.594100", "failed": true, "rc": 2, "start": "2017-05-11 17:23:07.527582", "stderr": "", "stderr_lines": [], "stdout": "/var/run/postgresql:5432 - keine Antwort", "stdout_lines": ["/var/run/postgresql:5432 - keine Antwort"]}

RUNNING HANDLER [galaxyprojectdotorg.galaxy-extras : restart nginx] ***** to retry, use: --limit @/galaxy_local/GalaxyKickStart/galaxy.retry

PLAY RECAP ** debiantest : ok=96 changed=59 unreachable=0 failed=1
`

The german - keine Antwort - means no answer.

Also, postgresql does not seem to be running.

ps -aux | grep postgre root 1882 0.0 0.0 12748 2276 pts/0 S+ 17:29 0:00 grep postgre

Best, Colin

[mvdbeek]

Can you check if you have sth. like this in /etc/supervisor/conf.d/galaxy ?:

[program:postgresql]
user            = postgres
command         = /usr/lib/postgresql/9.5/bin/postmaster -D /var/lib/postgresql/9.5/main -c "config_file=/etc/postgresql/9.5/main/postgresql.conf"
process_name    = %(program_name)s
stopsignal      = INT
autostart       = true
autorestart     = true
redirect_stderr = true
priority        = 100

If that is the case, what is the error you see if you launch:

/usr/lib/postgresql/9.5/bin/postmaster -D /var/lib/postgresql/9.5/main -c "config_file=/etc/postgresql/9.5/main/postgresql.conf

as the postgres user? Do these paths exist ? It's quite likely that either the binaries or the config files are stored in different paths.

[colindaven]

It seems to be a version number issue. I had attempted to set the Postgresql version to 9.4 in global_vars/all, yet it is looking for 9.3 here.

less /etc/supervisor/conf.d/galaxy.conf

[program:pre_postgresql]
user            = root
startsecs       = 0
command         = /bin/bash -c "install -d -m 2775 -o postgres -g postgres /var/run/postgresql"

[program:postgresql]
user            = postgres
command         = /usr/lib/postgresql/9.3/bin/postmaster -D /var/lib/postgresql/9.3/main -c "config_file=/etc/postgresql/9.3/main/postgresql.conf"
process_name    = %(program_name)s
stopsignal      = INT
autostart       = True
autorestart     = true
redirect_stderr = true
priority        = 100

If I start postgres as user postgres with this command (changed 9.3 to 9.4), then all is good.

sudo su postgres
$ whoami
postgres
$ 
$ 
$ 
$ /usr/lib/postgresql/9.4/bin/postmaster -D /var/lib/postgresql/9.4/main -c "config_file=/etc/postgresql/9.4/main/postgresql.conf"
2017-05-12 14:04:41 CEST [5373-1] LOG:  Datenbanksystem wurde am 2017-05-11 17:17:50 CEST heruntergefahren
2017-05-12 14:04:41 CEST [5373-2] LOG:  MultiXact-Member-Wraparound-Schutz ist jetzt aktiviert
2017-05-12 14:04:41 CEST [5372-1] LOG:  Datenbanksystem ist bereit, um Verbindungen anzunehmen
2017-05-12 14:04:41 CEST [5377-1] LOG:  Autovacuum-Launcher startet

This means it is awaiting connections once more.

I will further hack the versions to be used in the settings file. It seems the version_compare 15.04 did not work for this Debian system. previously: postgresql_version: "{{ '9.3' if ansible_distribution_version | version_compare('15.04', '<=') else '9.4' }}" adjusted to: postgresql_version: "{{ '9.4' if ansible_distribution_version | version_compare('15.04', '<=') else '9.4' }}"

Cheers, Colin

[mvdbeek]

Yeah, we'll have to check the distribution as well. This just assumed Ubuntu version numbering.

[drosofff]

@colindaven in the meantime you can try in group_vars/all:

postgresql_version: 9.4

[colindaven]

Hey @drosofff , essentially I have trying that today, but failing to completely "reset" ansible.

I've been trying --flush-cache and --step

but nothing seems to be working to completely reset all the (already established) postgresql settings.

Do you guys use any other "reset" commands ?

Therefore, I am also attempting an install on Ubuntu 16.04 .... In a couple of months I should have access to more VMs so can be more flexible.

[drosofff]

@colindaven Yeah I must confess that when ansible starts to suck, I trash the VM and start another one. have you tried to uninstall & purge postgres manually ?

[drosofff]

@colindaven please give a shot to the PR #238 that I will merge to the master. postgresql version is now set automatically with Debian Jessie (8) to 9.4 Other versions can be further conditionally set in the future. best

[drosofff]

Hi @colindaven Is this issue fixed at the end Cheers

[colindaven]

Yep, cheers