Enhancement: Add support for postgreSQL databases

[agapiospanos]

Hello again! Thank you for keeping the repo up-to-date.

I would like to suggest if it is not a big burden for you to add support for PostgreSQL. Similar to MySQL we could use pgdump for PostgreSQL.

It would be great to have this option since VestaCP also supports PostgreSQL.

Thank you again! Cheers!

[joshbmarshall]

I'm sure this is possible. The v-list-databases command outputs whether the database is MySQL or PostgreSQL so it would filter off that. The database save and restore recently changed (except for archived users process) to use a borg repo per database and using uncompressed data stream to maximise the deduplicator. So the pgdump would probably be best to use the plain text format. I haven't used PGSQL for ages so don't have much experience with it, especially for backup and restore. We could use the VestaCP's scripts as a reference. Are you willing to do some testing if code was written to add Postgres support?

[agapiospanos]

Thank you for the quick response. For sure I am willing to test the Postgres support. Just ping me when you are ready for some testing! :)

[joshbmarshall]

I've got some code on my fork https://github.com/joshbmarshall/vestacp-borg-incremental-backups that you can try. I tried to install postgres on my VestaCP test system (I have a custom build inside docker) and it didn't want to play nicely. I've limited the postgres code to the archive-user.sh and restore-archived-user.sh scripts. If you can test those with a test server and test account and report back would be great, I am flying a bit blind with the exact command to do the backup and restore

[joshbmarshall]

@agapiospanos I managed to get PostgreSQL working on my VestaCP server, and have added the code for full backup and restore via user archives and via borg. If you can run tests to ensure it works nicely for you also I will create a pull request into the official git repo.

[joshbmarshall]

@agapiospanos This is merged now it works for me on my test but it is not a large database if you can confirm it works for you this issue can be closed

[agapiospanos]

@joshbmarshall Thank you for the quick implementation of the requested feature. I will test it tomorrow with a database of mine and come back.

[agapiospanos]

Hello again. I tried to perform the daily backup using the backup-execute.sh but I have a problem with the postgres db backup. The message I got is the following:

pg_dump: [archiver (db)] connection to database "admin_postgres_demo" failed: FATAL: Peer authentication failed for user "postgres"

[joshbmarshall]

Can you please do some checks. As root, can you run

pg_dump -U postgres admin_postgres_demo

If that gives the same error can you look at settings in pg_hba.conf - there are plenty of advice if you do a search. The vestacp backup scripts extract the user name and password from the configuration. I don't have my VestaCP install that has postgres on it now to test their more complex method. I do recall making a change to pg_hba.conf and it allowing root to access the database but not other users.

[agapiospanos]

Hello again. Well, it took some time for me to figure this out. It seems that the vestacp default backup mechanism implements a different method to perform the pg_dump. As you can see in the db.sh in this link the steps that they follow are the following:

STEP 1: They connect to the database using the psql_connect function before attempting to dump the db.

dump_pgsql_database() {
    psql_connect $HOST

    psql_dump $dump $database

    query="SELECT rolpassword FROM pg_authid WHERE rolname='$DBUSER';"
    md5=$(psql_query "$query" | head -n1 | cut -f 2 -d \ )
    pw_str="UPDATE pg_authid SET rolpassword='$md5' WHERE rolname='$DBUSER';"
    gr_str="GRANT ALL PRIVILEGES ON DATABASE $database to '$DBUSER'"
    echo -e "$pw_str\n$gr_str" >> $grants
}

The key point here is when they export the PGPASSWORD from the pgsql.conf file. This file is located here: /usr/local/vesta/conf/pgsql.conf As you can see in this link when you export PGPASSWORD it behaves as you are providing the password after running the pg_dump command.

If you open the pgsql.conf file, you can see that it contains the password for the user postgres that you need to provide if you run the pg_dump function. So it seems that vesta stores this password when the user is created to be able to use this for the dumping. Whatever database you want to dump you should use this password instead of the one that you created when you added the database from the vestacp interface.

psql_connect() {
    host_str=$(grep "HOST='$1'" $VESTA/conf/pgsql.conf)
    eval $host_str
    export PGPASSWORD="$PASSWORD"
    if [ -z $HOST ] || [ -z $USER ] || [ -z $PASSWORD ] || [ -z $TPL ]; then
        echo "Error: postgresql config parsing failed"
        log_event "$E_PARSING" "$ARGUMENTS"
        exit $E_PARSING
    fi

    psql -h $HOST -U $USER -c "SELECT VERSION()" > /dev/null 2>/tmp/e.psql
    if [ '0' -ne "$?" ]; then
        if [ "$notify" != 'no' ]; then
            echo -e "Can't connect to PostgreSQL $HOST\n$(cat /tmp/e.psql)" |\
                $SENDMAIL -s "$subj" $email
        fi
        echo "Error: Connection to $HOST failed"
        log_event  "$E_CONNECT" "$ARGUMENTS"
        exit $E_CONNECT
    fi
}

STEP 2: After getting the password from this file you should attempt the dumping but you should include the host in the command. The v-list-databases also includes the host in the output. So you can get it from there and the pg_dump has to be changed to pg_dump -h $HOST -U postgres $DATABASE

I hope my explanation is clear. Using this method, it does not require to make changes to pg_hba.conf which I think is better because it provides a plug 'n' play experience. Do you think you can implement these changes in your code?

Thank you for your time and effort on this! :)

[joshbmarshall]

This makes sense. I reinstalled postgres on a server and made appropriate adjustments, it works for me without changing the pg_hba.conf file. Can you please download from my fork the pgsql branch and test? https://github.com/joshbmarshall/vestacp-borg-incremental-backups/tree/pgsql If all good I'll make a pull request

[joshbmarshall]

On second thoughts please use the pgsql2 branch, it is a much cleaner patch and wont have future issues since it doesn't inject the Vesta database variables into the script. https://github.com/joshbmarshall/vestacp-borg-incremental-backups/tree/pgsql2

[joshbmarshall]

Hi @agapiospanos did you get a chance to test it?

[agapiospanos]

Hi @joshbmarshall. Thank you for the effort in this. I just tested the functionality. It seems that the backup is working now. Congratulations. There is only 1 side-effect. The date is not saved correctly to the repo. I get the following output.

-- Creating new backup archive /demobackup/borg/db/admin::admin_postgres_demo-
Creating archive at "/demobackup/borg/db/admin::admin_postgres_demo-"
------------------------------------------------------------------------------
Archive name: admin_postgres_demo-
Archive fingerprint: d553f114db8884a03b63583e78603458c48b844ea678781ee38dc3873ee0d916
Time (start): Fri, 2020-02-21 14:13:44
Time (end):   Fri, 2020-02-21 14:13:44
Duration: 0.01 seconds
Number of files: 1
Utilization of max. archive size: 0%
------------------------------------------------------------------------------
                       Original size      Compressed size    Deduplicated size
This archive:               43.94 kB             11.50 kB             11.50 kB
All archives:               29.43 MB              7.55 MB              7.55 MB

                       Unique chunks         Total chunks
Chunk index:                      38                   38

As you can see above the database is extracted using the following name admin::admin_postgres_demo- Thus the restoration does not work. Even though I used the correct DB name and date.

 ./restore-db.sh 2020-02-21 admin admin_postgres_demo  
!!!!! Backup archive 2020-02-21 not found, the following are available:
admin_postgres_demo-                 Fri, 2020-02-21 14:13:44 [d553f114db8884a03b63583e78603458c48b844ea678781ee38dc3873ee0d916]
Usage example:
restore-db.sh 2018-03-25 user database

[joshbmarshall]

Hi @agapiospanos I can see your issue. The dump-databases.sh script needs a date parameter. I've updated the README.md in the git branch to reflect this. so e.g. dump-databases.sh 2020-02-22 for today. The backup-execute.sh auto-fills the date parameter.

[joshbmarshall]

I've just added to the git branch to auto-set the date to the current date if the parameter is not specified, so it will work for you as you were using it before. Let me know how it goes, if all good I'll do a PR

[agapiospanos]

After the last changes, it works fine! I tested it on a small database that I backed up, made some changes to the values and restored it. It restored successfully the backed up data.

Good Job!! Thank you for your time and effort!!

[joshbmarshall]

Thanks @agapiospanos ! @ramirojoaquin I have created a PR #14 to bring this into the main code