Articles

When converting cPanel backup that contains PostgreSQL databases to DirectAdmin, PostgreSQL data are skipped. One can see similar messages:

WARNING! PostgreSQL databases detected in pgsql/, these will not be restored:
username_dbname1.tar
username_dbname2.tar
...
Creating DirectAdmin tarball...

The script included in Directadmin PostgreSQL plugin’s bin directory facilitates import of PostgreSQL parts of the backup.

Put cPanel backup in /home/admin/admin_backups. The file should be named USERNAME.tar.gz where USERNAME is cPanel’s username. Backup made with /scripts/pkgacct USERNAME --backup PATH produces USERNAME.tar.gz file.

Make sure user postgres can access PostgreSQL server using psql command without password prompt. Then run import script:

/usr/local/directadmin/plugins/postgres/bin/cpanel_to_da_postgresql.sh USERNAME

You will see the script listing databases and dbusers available in the backup. It will offer you removing existing databases and dbusers (recommended for clean load). Finally new dbusers will be created and databases loaded.

Note, if the databases to be restored require extensions make sure these are preinstalled.

Simplified script content

Let’s assume username is testing.

U=testing

Make sure /home/admin/admin_backups/$U.tar.gz exists, extract databases, users and grants. Allow access to user postgres.

cd /home/admin/admin_backups
tar -xzf $U.tar.gz $U/psql_grants.sql $U/psql_users.sql $U/psql
chown -R postgres: $U

Cleanup any of the databases, dbusers to be restored to minimize risk of error messages about conflict with existing data.

for tar in `ls /home/admin/admin_backups/$U/psql/*.tar`; do
    role=`basename $tar .tar`
    su - postgres -c "psql -c 'DROP DATABASE IF EXISTS $role' 2>/dev/null";
    su - postgres -c "psql -c 'DROP ROLE IF EXISTS $role' 2>/dev/null";
done

grep "CREATE USER \"" /home/admin/admin_backups/$U/psql_users.sql | awk '{print $3}' | sed 's/"//g' | while read; do
    su - postgres -c "psql -c 'DROP USER IF EXISTS $REPLY' 2>/dev/null";
done

Finally create DB users and roles, restore databases and grant privileges, grant roles to dbusers and remove unneeded files.

su - postgres -c "psql -f /home/admin/admin_backups/$U/psql_users.sql"
for tar in `ls /home/admin/admin_backups/$U/psql/*.tar`; do
    role=`basename $tar .tar`
    su - postgres -c "psql -c 'CREATE ROLE $role;'"
    su - postgres -c "pg_restore --if-exists -v -c -d template1 $tar -C -F t 2>&1 | sed 's/mungakac_/testing_/g'";
    su - postgres -c "psql -c 'GRANT ALL ON DATABASE $role TO $role;'"
done
su - postgres -c "psql -f /home/admin/admin_backups/$U/psql_grants.sql"
rm -rf /home/admin/admin_backups/$U

Optionally you could run ALTER USER ... CONNECTION LIMIT connlimit 20; for the restored dbusers.