Trusty sudo postgresql listening on port 5433, can't resolve why

trusty

#1

For a couple weeks now, one of my Travis-CI jobs have been failing due to the PostgreSQL instance starting on port 5433 instead of 5432. (subsequent code assumes that postgresql is running on port 5432 ie psql command). Anyway, I can not resolve why PostgreSQL is listening on 5433.

A recent build failure where I attempted to debug what was happening.

$ sudo service postgresql status 10
9.2/main (port 5432): down
9.3/main (port 5432): down
9.4/main (port 5432): down
9.5/main (port 5432): down
9.6/main (port 5432): down
10/main (port 5433): online

my .travis.yml

```yml sudo: required

dist: trusty

addons:
artifacts:
target_paths: /$PYTHON_VERSION
paths: “mplresults”
postgresql: “9.5”
apt:
packages:
- postgresql-9.5-postgis-2.3
hosts:
- iemdb
- iemdb-hads
- iemdb2
env:
global:
- MPLLOCALFREETYPE=1
matrix:
- PYTHON_VERSION=2.7
- PYTHON_VERSION=3.6
language: python

before_install:

  • sudo add-apt-repository ppa:ubuntugis/ppa -y
  • sudo apt-get update -qq

install:

Install miniconda

-----------------

Create the basic testing environment

------------------------------------

  • conda config --set always_yes yes --set changeps1 no --set show_channel_urls yes
  • conda update -q conda
  • . $HOME/miniconda/etc/profile.d/conda.sh
  • conda create -n test-environment python=$PYTHON_VERSION
  • conda activate test-environment

Customise the testing environment

---------------------------------

Prioritize conda-forge channel over defaults for best compat

  • conda config --prepend channels conda-forge
  • conda install -q --file conda_requirements.txt
  • pip install -r pip_requirements.txt

before_script:

  • psql -c ‘create database postgis;’ -U postgres
  • psql -c ‘create user nobody;’ -U postgres
  • psql -c ‘create user apache;’ -U postgres
  • psql -c ‘create database portfolio;’ -U postgres
  • psql -c ‘create database iem;’ -U postgres
  • psql -c ‘create database mesosite;’ -U postgres
  • psql -c ‘create database asos;’ -U postgres
  • psql -c ‘create database hads;’ -U postgres
  • psql -c ‘create database mos;’ -U postgres
  • psql -U postgres -f data/schema/iem.sql iem
  • psql -U postgres -f data/schema/postgis.sql postgis
  • psql -U postgres -f data/schema/portfolio.sql portfolio
  • psql -U postgres -f data/schema/mesosite.sql mesosite
  • psql -U postgres -f data/schema/asos.sql asos
  • psql -U postgres -f data/schema/hads.sql hads
  • psql -U postgres -f data/schema/mos.sql mos

script:

  • coverage run --source=pyiem setup.py test --addopts “–mpl --mpl-results-path=mplresults”;

after_success:

  • if [[ “$PYTHON_VERSION” == 2* ]]; then
    coveralls;
    fi
</details>

#2

My initial guess is that the apt package is somehow detecting the use of port 5432, and setting up the configuration file to listen to port 5433.


#3

Thanks. So I guess the issue is with the addons functionality, hmm. I guess I should spin up a trusty instance and try to debug the issue with the apt packages? Unsure what to do otherwise.


#4

You can edit the config file, then restart the server.

before_install:
  - sudo service postgresql stop
  - sed -e 's/^port.*/port = 5432/' /etc/postgresql/10/main/postgresql.conf > postgresql.conf
  - sudo chown postgres postgresql.conf
  - sudo mv postgresql.conf /etc/postgresql/10/main
  - sudo service postgresql start 10

#5

Oh thank you so much! Just for another data point, I stripped my .travis.yml down to just this and got the same port issue, what’s puzzling is that I have another project that uses about the same travis config and it is not failing in this matter.

sudo: required
dist: trusty
addons:
  postgresql: "10"
  apt:
    packages:
      - postgresql-10-postgis-2.4
before_install:
  - sudo service postgresql status 10
  - sudo ls -ltra /tmp
  - sudo netstat -nlp

#6

Thank you, that worked! PS, you are missing a sudo on the chown command.


#7

Good catch. I’ve edited the comment, and marked it as the solution.


#8

FYI Xenial has PostgreSQL 10 pre-installed, and this issue does not apply.


#9

Thanks for all the help here, I did not realize xenial was an option yet :slight_smile:

erases what I previously wrote

it still isn’t happy, now the domain socket is not running.


#10

I am so puzzled… So am trying xenial and attempting to debug what is going on and see things like this:

$ which psql
/usr/lib/postgresql/9.6/bin/psql

another issue is that I am getting an anaconda installed postgresql, which seems to assume sockets are in /tmp whereas the configuration now has this

unix_socket_directories = '/var/run/postgresql' 

even with services: postgresql set, I get this

$ sudo systemctl status postgresql
● postgresql.service - LSB: PostgreSQL RDBMS server
   Loaded: loaded (/etc/init.d/postgresql; bad; vendor preset: enabled)
   Active: inactive (dead)
     Docs: man:systemd-sysv-generator(8)

more thrashing ahoy, I must be missing something obvious.


#11

Sorry to hear about the continued troubles. If you wish, we can enable the debug feature for your repo (if it’s public) so you can have an interactive session.


#12

Thanks for the continued help and offer. Amazingly, my builds are back to green again. I think my blissful ignorance, many moving parts, and changes to the postgresql config conspired against me.

Note that the original issue of postgresql running on TCP:5433 somehow went away without me directly addressing it.

I think the biggest issue was that I was providing a custom postgresql whose psql was in front of my $PATH and assumed that postgresql had a socket file in /tmp. That file is now limited to /var/run/postgresql, which I think was a recent change in xenial packaging?

Also, as the docs denote for xenial, you need to start postgresql via

serivces:
  - postgresql

I was then able to remove all the config file munging and use a basic config for either postgresql 10 or 9.6 and have it work.