Postgres not working after fresh Virtualmin install

Submitted by Brook on Mon, 08/06/2018 - 08:28

I've installed Postgres and initialised the database using /usr/pgsql-9.3/bin/postgresql93-setup initdb (and if I try it again says Data directory is not empty!, however Virtualmin reports:

The PostgreSQL host configuration file /var/lib/pgsql/data/pg_hba.conf was not found on your system, indicating that the database has not been initialized yet. Click the button below to setup PostgreSQL.

Clicking on the button, says: sh: postgresql-setup: command not found

Going to the modules tab then clicking on Postgres says the same as above. Also trying to run this command from terminal doesn't do anything but show usage (and I can't see anything from this that would help) virtualmin config-system PostgreSQL.

Could this be a bug?

Status: 
Closed (works as designed)

Comments

Submitted by Brook on Mon, 08/06/2018 - 12:34

Ok so the file exists at /var/lib/pgsql/9.3/data/pg_hba.conf and from memory (on my previous installs), I was able to enter that filename manually in the Postgres webmin module page - but that page no longer lets you make any changes since it reports The PostgreSQL host configuration file /var/lib/pgsql/data/pg_hba.conf was not found on your system, indicating that the database has not been initialized yet. Click the button below to setup PostgreSQL.

Can I edit the webmin module file manually via the terminal?

Submitted by andreychek on Mon, 08/06/2018 - 21:20

Thanks for sharing your solution!

Jamie, the info they used to fix it is the following:

There was a cog icon in the top left/right corner which I hadn't spotted earlier.

It needs to be updated with the following info (presuming you are using pg 9.3)


Path to psql command:
/usr/pgsql-9.3/bin/psql
Command to start PostgreSQL:
systemctl start postgresql-9.3
Command to stop PostgreSQL:
systemctl stop postgresql-9.3
Command to initialize PostgreSQ:
/usr/pgsql-9.3/bin/postgresql93-setup initdb
Path to postmaster PID file:
/var/lib/pgsql/9.3/data/postmaster.pid
Paths to host access config file:
/var/lib/pgsql/9.3/data/pg_hba.conf
Path to pg_dump command:
/usr/pgsql-9.3/bin/pg_dump
Path to pg_restore command:
/usr/pgsql-9.3/bin/pg_restore
Default backup repository directory:
/home/postgres_backups

Submitted by Brook on Mon, 08/06/2018 - 21:30

Also if it's any help, this is how I installed PG:

CentOS 7
(From: http://www.liquidweb.com/kb/how-to-install-and-connect-to-postgresql-on-centos-7/)

First remove the old postgres if one has been installed:
psql --version
yum list postgres*
yum remove postgresql

yum install uuid
rpm -iUvh https://yum.postgresql.org//9.3/redhat/rhel-7-x86_64/pgdg-centos93-9.3-2.noarch.rpm
yum -y update
yum -y install postgresql93 postgresql93-server postgresql93-contrib postgresql93-libs --disablerepo=* --enablerepo=pgdg93

systemctl enable postgresql-9.3
/usr/pgsql-9.3/bin/postgresql93-setup initdb
systemctl start postgresql-9.3

Submitted by andreychek on Mon, 08/06/2018 - 21:33

Oh I see, you're using a third party Postgres version there. That would explain the issue you're seeing :-)

Yeah unfortunately, when using software versions that don't come with the distro, there may indeed be some manual steps that need to be taken to get things working.

That's not actually a bug though, that's expected, as we can't modify Virtualmin to be able to look for th package names and file names for all the different third party repos out there.

Note that CentOS 7 does come with Postgres 9.2, which we do support out of the box.

Sorry! But unfortunately there's not much we'll be able to do regarding this one.

Submitted by Brook on Mon, 08/06/2018 - 21:40

I think what stumped me was what seemed like not being able to manually configure - that's until I found the cog icon.

On my previous server I had the standard CentOS PG installed initially, so webmin was working fine with it - and then later installed the newer version of PG (it's frightening how old the centos version is!) and so all I needed to do was update the settings.

On this server I did not bother installing the distro's available version as I knew it would not be sufficient, so went straight to 9.3, hence on Virtualmin's install I couldn't get to the config easily.

Perhaps, you could add a note to say they can manually configure and then provide a link - that cog icon was hard to spot, haha!