Virtualmin backup not backing up postgres databases

Virtualmin... Backup and Restore... Backup Virtual Servers is failing on the PG backup, but Webmin... PostgreSQL Database Server... Backup databases works. (See attached screenshots.)

I have upgraded Postgres to 9.3 and updated the Postgres settings in the module (see screenshot) so it seems as though the Virtualmin backup doesn't honour these settings (as the backup from the postgres server page seems to work fine).

Status: 
Closed (fixed)

Comments

It looks like the error Postgres is giving is as follows:

Connection to database XXX failed: fe_sendauth: no password supplied

Jamie, do you have any thoughts as to why the password wouldn't be seen in this newer Postgres version (9.3)?

Newer distros use that version, so it should be possible for that version to work.

The issue may be that Virtualmin is using the domain's PostgreSQL login and password to perform the backups, rather than the root login. If you go to Webmin -> Servers -> PostgreSQL Database, do all your databases appear?

Yes they appear (that is the page where I can back-up all databases).

Ok .. and can you login to your PostgreSQL database from the command line, using the psql command?

If I type in psql it asks for a password (which I can't remember or remember setting)

psql: FATAL: password authentication failed for user "root"

What if you use a command like psql -U domainuser -W (replacing domainuser with the admin user for the domain)

...I'm able to log in fine.

So Virtualmin uses a .pgpass file to set the password for logging in - I wonder if perhaps in PostgreSQL 9.3 the semantics or format of this file changed.

Which Linux distribution are you running there, and how did you do the upgrade to 9.3 ?

/Post removed as the details were incorrect/

Hi Jamie, sorry I posted the wrong instructions - the one in the previous post are for CentOS 7, but the error is actually on my CentOS 6 server - sorry!

Here's how I installed it on CentOS 6:

========================
How to upgrade Postgres
========================
 
First remove the old postgres if one has been installed:
    psql --version
    yum list postgres*
    yum remove postgresql
 
Then exclude CentOS Postgres Packages:
    nano /etc/yum.repos.d/CentOS-Base.repo
    - in this file add: exclude=postgresql*
    - to the end of the [base] and [updates] sections
 
Then get the url of the latest version for your distro from here:
    http://yum.postgresql.org/repopackages.php (right click to get url)
    Then wget it (the url below is what right clicking above gets you), install rpm, and list:
    wget http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm
    rpm -ivh pgdg*
    yum list postgres*
 
Then we install postgres:
    yum install postgresql93-server
 
Then initialise db and set it up to start automatically:
    service postgresql-9.3 initdb
    chkconfig postgresql-9.3 on
    service postgresql-9.3 start
 
 
HOW TO LOG INTO THE POSTGRES DATABASE
-------------------------------------
 
    su - postgres
    psql
    \q
    exit
 
SOURCE: https://www.digitalocean.com/community/articles/how-to-install-and-use-postgresql-on-a-centos-vps
 
 
HOW TO UPDATE VIRTUALMIN SO THAT IT HAS DETAILS OF THE CORRECT PG VERSION
-------------------------------------------------------------------------
 
    Webmin > servers > postgres > module config > system config
 
    Path to psql command: 
        /usr/pgsql-9.3/bin/psql
    Command to start PostgreSQL:
        if [ -r /etc/rc.d/init.d/rhdb ]; then /etc/rc.d/init.d/rhdb start; else /etc/rc.d/init.d/postgresql-9.3 start; fi
    Command to stop PostgreSQL:
        if [ -r /etc/rc.d/init.d/rhdb ]; then /etc/rc.d/init.d/rhdb stop; else /etc/rc.d/init.d/postgresql-9.3 stop; fi
    Command to initialize PostgreSQ:
        if [ -r /etc/rc.d/init.d/rhdb ]; then /etc/rc.d/init.d/rhdb start; else /etc/rc.d/init.d/postgresql-9.3 initdb ; /etc/rc.d/init.d/postgresql-9.3 start; fi
    Path to postmaster PID file:
        /var/run/postmaster-9.3.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:
        /var/lib/pgsql/9.3/backups
 

So I just installed the exact same PostgreSQL version on a CentOS 6 system, and with both Virtualmin 4.17 and 4.18 I was able to backup a domain with a PostgreSQL database just fine.

I wonder, are you using hashed passwords on your system (so that they PostgreSQL login is different from the admin login for the domain) ?

Do you mean in the post-installation set-up page Jamie? If so, then yes I always choose hashed passwords.

Also, do you have PostgreSQL setup to allow connections from the Unix user with the same name as the DB login, without a password being required?

Also, do you have PostgreSQL setup to allow connections from the Unix user with the same name as the DB login, without a password being required?

Do you mean what's in the attached screen?

No, I was referring to the "Allowed Hosts" page.

Ok... MD5 password - see attached screen grab:

Hi Jamie,

I have changed the settings for 'Local connection' and '127.0.0.1/32' to no authentication required (see attached image) and it is working now ...is that the correct settings?

What about '::1/128' do I need to change that too?

Ok, I found the bug that causes this - it happens only when MD5 password authenticate is in use. Your change is a suitable work-around - a proper fix will be in the 4.19 Virtualmin release.

Thanks Jamie.

Will I need to make any further changes when 4.19 is out, or will the update take care of reverting/updating the settings?

If you want to revert to MD5 password authentication, you will have to do that manually after 4.19 comes out.

Are there any negatives to leaving it as it is?

I've just found the guide I used to install it, so it's actually more involved than the details in the post above (sorry, those were in my notes) http://www.davidghedini.com/pg/entry/install_postgresql_9_on_centos

Here is my set-up notes for installing it on CentOS 7, looking at the guide in the link above, and the instructions below, do you feel these are adequate - or would you recommend following all or more of the guide in the link?

[quote] How to upgrade Postgres

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

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

rpm -iUvh http://yum.postgresql.org/9.3/redhat/rhel-7-x86_64/pgdg-centos93-9.3-1.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

HOW TO LOG INTO THE POSTGRES DATABASE

su - postgres
psql
\q
exit

HOW TO UPDATE VIRTUALMIN SO THAT IT HAS DETAILS OF THE CORRECT PG VERSION

Webmin > servers > postgres > module config > system config

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


Webmin > servers > postgres > allowed hosts
- make sure 'Local connection' and the 127.0.0.1/32 settings have:
'All users' and 'no auth required' checked
restart postgres
//This should not be needed in 4.19 Virtualmin release//

[/quote]

I don't think there are any negatives ... in fact, on my system when I installed PostgreSQL 9.3 it was using ident-based authentication by default.

Thanks Jamie. FYI, I think I had to change it on the CentOS 7 install too (to no authentication required) as it wouldn't create databases otherwise.

Automatically closed -- issue fixed for 2 weeks with no activity.