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

Environments Linux
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>
1 Like
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
1 Like
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.

1 Like
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.

1 Like