SOLVED [CPANEL-29288] Non-Public Schemas in PostgreSQL Databases Do Not Restore

[plague]

Hello.

Last week I had to move some server to new hardware and I noticed that postgres databases were transfered with empty data. The database was created in the new server, the user was created, but the database was totally empty. Data was lost.
The transfer and restore log shows no error, and the database data was not left in the user's homedir for manual restoration.

I also noticed, too late, that cpanel backups are not backing up any postgres databases at all.

Here is the transfer log:

[2019-08-28 01:31:05 -0300] Performing “Postgresql” component....
[2019-08-28 01:31:05 -0300] Grabbing PostgreSQL databases...
[2019-08-28 01:31:05 -0300] .........
[2019-08-28 01:31:06 -0300] Done
[2019-08-28 01:31:06 -0300] Grabbing PostgreSQL privileges...
[2019-08-28 01:31:06 -0300] Done
[2019-08-28 01:31:06 -0300] Completed “Postgresql” component.

And here the restore log:

Postgres
Preparing PostgreSQL restore …
Restoring PostgreSQL users …
Creating PostgreSQL databases …
Updating PostgreSQL privileges …
Restoring PostgreSQL databases …
Restoring the database “user_new” …
Restoring the database “user_db” …
Restoring PostgreSQL grants …
Postgres

Both databases user_new and user_db are empty after the transfer. Both databases had it's data inside schemas other then "public" and the "public" schema was empty. The "public" schema was the only one restored.

Databases whose data was inside the "public" schema were transfered and restored correctly.

Now for the daily backups, there is no log at all for postgres databases, nothing about postgres is mentioned in the logs.

I already have a ticket about this issue, just wanted to post it here to share my issue.

 

[plague]

One more thing about the account backups. If I make a backup using /scripts/pkgacct script, the postgres databases are included in the backup file.
Only the daily backup is skipping postgres

/scripts/pkgacct user /backup/

.....

[2019-09-05 14:25:54 -0300] Performing âPostgresqlâ

                                                   [2019-09-05 14:25:54 -0300] Grabbing PostgreSQL databases............

[2019-09-05 14:25:55 -0300] Done

[2019-09-05 14:25:55 -0300] Grabbing PostgreSQL privileges...[2019-09-05 14:25:55 -0300] Done

[2019-09-05 14:25:55 -0300] Completed âPostgresqlâ

.....

tar -tf cpmove-user.tar.gz cpmove-user/psql

cpmove-user/psql/

cpmove-user/psql/user_dbname.tar

cpmove-user/psql/user_dbname2.tar

 

[plague]

Bug confirmed in the transfer tool:

I was able to confirm that the schemas outside of public were lost during both the transfer and the back up process.

I have submitted a case to our developers at #CPANEL-29288. While I do not have an ETA regarding this you can follow our change log to see updates regarding this case.

======
https://documentation.cpanel.net/display/CL/Change+Logs
======

Until this has been resolved, I would advise taking manaul backups with /scripts/pkgacct as from my testing this keeps the psql database in tact.
...

 

[plague]

Problem in the daily backup confirmed too

 In my testing forcing the backup process to run resulted in the database not being backed up.

========
/usr/local/cpanel/bin/backup --force
========

The backup script does still utilize pkgacct, and should hypothetically work the same as when pkgacct is run by itself. This does not seem to be the case, unfortunately.

The issue also did not appear to be exclusive to Centos, or Cloudlinux as they both had the same result.

So, manually backup your accounts using postgres if you want to save your data.

 

[cPanelMichael]

Hello @plague,

Thank you for sharing the case number and workaround. I'm monitoring internal case CPANEL-29288 and will update this thread with more information on it's status as it becomes available.

Thank you.

 

[cPanelLauren]

Hello,


The internal case associated with this thread was marked as completed and by design. The comments indicated that we only restore the public schema for postgres databases due to an issue with restoring postgres databases on versions higher than 9

Restore psql databases when using PSQL 9.0 or later

Case 183837: Cannot restore postgresql databases
on a CentOS 7 server (postgresql-server-9.2.7)

When restoring a postgresql database with /usr/bin/pg_restore
we need to set an extra option to set the public schema
when the server is using version 9.0 or later.

Ultimately this is due to the fact that postgres was not permitting transfers of non-public schema data and can cause a loss of data. The workaround for this is as you noted as well, to manually back up postgres dbs

 

[plague]

Hello guys.

It's been 1 year since my post here about postgres backups. The daily backup is still not backing up postgres databases at all.

In 1 year, we still do not have any updates about this?

 

[cPanelLauren]

Hello guys.

It's been 1 year since my post here about postgres backups. The daily backup is still not backing up postgres databases at all.

In 1 year, we still do not have any updates about this?

This was marked as by design, please see my update directly above your post.

 

[plague]

Hey @cPanelLauren

Your post was about the tranfer and restoring of non-public schemas.

The backup problem still exists and is not related to non-public schemas. The daily backup is ignoring postgres databases completely, while manual backups using pkgacct script are fine. This error was confirmed in the same ticket as quoted in my post on " Sep 5, 2019 ", but I guess no internal case was open about it?

Thank you.

 

[cPanelLauren]

This is addressed in the case @plague as well as in the workaround I noted. The transfer tool uses the same backup mechanism the daily backups use, and the reason they are not restored is due to the exact same issue. They are not included in the daily backups due to the issues with restoration.

Hello,


The internal case associated with this thread was marked as completed and by design. The comments indicated that we only restore the public schema for postgres databases due to an issue with restoring postgres databases on versions higher than 9



Ultimately this is due to the fact that postgres was not permitting transfers of non-public schema data and can cause a loss of data. The workaround for this is as you noted as well, to manually back up postgres dbs

The case text was as follows:

Non-Public Schemas in PostGres Databases Do Not Restore When Accounts are Transferred or through automated backups

Details
Non-Public Schemas in PostGres Databases Do Not Restore When Accounts are Transferred or through automated backups.
However, when manually running pkgacct, they are included in the backup.

 

[plague]

@cPanelLauren you still did not understand. Forget about the transfer tool and restoration. The error I am refering to is in the daily backup process.

The daily backup is not backing up any postgres databases, even those using public schemas.
This was reported and confirmed in the same support ticket on Sep 5, 2019

As a workaround I had to create a script to check the accounts that have created postgres databases and rerun the backup for those accounts with /scripts/pkgacct after the daily backup is done:

#!/bin/sh
DIA=`date +%Y-%m-%d`
cd /var/cpanel/databases && for user in `grep -l '"PGSQL":{"' *json | cut -d'.' -f1` ; do /scripts/pkgacct --incremental --skiphomedir --backup $user /backup/$DIA/accounts/ ; done

Manually invoking /scripts/pkgacct does include postgres databases in the backup. The automatic daily backup does not. That's the problem.

On Sep 5, 2019 the operator wrote in the ticket:

The backup script does still utilize pkgacct, and should hypothetically work the same as when pkgacct is run by itself. This does not seem to be the case, unfortunately.

 

[TA]

@cPanelLauren I have also just discovered that my postgres database has not been included in my daily backups - no public schema, no nothing, the psql folder is empty. However running the cPanel Backup Wizard for the user does include the postgres database, although no indication is given in the wizard - it only mentions the MySQL db.

@plague thanks for the script - will use that to do the daily backup until this is sorted.

 

[cPRex]

@Thornhill Associates - I wasn't able to reproduce this issue on a test server. I created a cPanel account, ensured it had a postgres database, forced the daily backups to run, and confirmed the psql directory had the tar files included. Would it be possible for you to try running this command on the account:

/scripts/pkgacct username

to see if that includes the psql databases on the system? That will create a cpmove file in /home on the system that you can unzip. You'll also see the following section scroll by on the command line as it creates the backup:

[2020-12-02 15:37:53 -0500] Performing “Postgresql” component....
[2020-12-02 15:37:53 -0500] Grabbing PostgreSQL databases............
[2020-12-02 15:37:54 -0500] Done
[2020-12-02 15:37:54 -0500] Grabbing PostgreSQL privileges...[2020-12-02 15:37:54 -0500] Done
[2020-12-02 15:37:54 -0500] Completed “Postgresql” component.

If that doesn't work, it may provide a helpful error as to why that isn't being included on your machine.

 

[TA]

If I run pkgacct the postgres database is backed up correctly. But it isn't backed up when running the daily backup configured in WHM Backup->Backup Configuration

 

[cPRex]

Do you see anything in the backup log? These logs would be located in /usr/local/cpanel/logs/cpbackup and are sorted according to date.

 

[TA]

Below the logfile - user1 has a postgres db as well as a mysql db. I don't see any error messages, it just seems to ignore the postgres db altogether

[2020-12-01 02:00:02 +0200] info [backup] Setting I/O priority to reduce system load: best-effort: prio 6
[2020-12-01 02:00:02 +0200] info [backup] Pruning metadata for backup at /backup/2020-12-01
[2020-12-01 02:00:02 +0200] info [backup] Creating metadata index for backup at /backup/2020-12-01
[2020-12-01 02:00:02 +0200] info [backup] Hard Linking available on "/backup"
[2020-12-01 02:00:02 +0200] info [backup] Starting full MySQL database backups
/usr/bin/gtar: Removing leading `/' from member names
/usr/bin/gtar: /var/lib/mysql/mysql.sock: socket ignored
[2020-12-01 02:00:08 +0200] info [backup] Running dir & file backup with target : /backup/2020-12-01/system
/usr/bin/gtar: Removing leading `/' from member names
/usr/bin/gtar: Removing leading `/' from member names
/usr/bin/gtar: Removing leading `/' from member names
/usr/bin/gtar: Removing leading `/' from member names
/usr/bin/gtar: Removing leading `/' from member names
/usr/bin/gtar: Removing leading `/' from member names
/usr/bin/gtar: Removing leading `/' from member names
/usr/bin/gtar: Removing leading `/' from member names
/usr/bin/gtar: Removing leading `/' from member names
/usr/bin/gtar: Removing leading `/' from member names
/usr/bin/gtar: /var/cpanel/userhomes/cpanelconnecttrack/p0f.socket: socket ignored
/usr/bin/gtar: /var/cpanel/dnsadmin/sock: socket ignored
/usr/bin/gtar: Removing leading `/' from hard link targets
/usr/bin/gtar: Removing leading `/' from member names
/usr/bin/gtar: Removing leading `/' from member names
/usr/bin/gtar: Removing leading `/' from member names
/usr/bin/gtar: Removing leading `/' from member names
[2020-12-01 02:00:32 +0200] info [backup] Queuing transport of file: /backup/2020-12-01/system_files.tar
[2020-12-01 02:00:32 +0200] info [backup] no_transport = 0 .. and queueid = TQ:TaskQueue:6254
[2020-12-01 02:00:32 +0200] info [backup] leaving queue_backup_transport_item
[2020-12-01 02:00:32 +0200] info [backup] Queuing deletion of file: /backup/2020-12-01/system_files.tar
[2020-12-01 02:00:32 +0200] info [backup] no_transport = 0 .. and queueid = TQ:TaskQueue:6255
[2020-12-01 02:00:32 +0200] info [backup] leaving queue_backup_transport_item
[2020-12-01 02:00:32 +0200] info [backup] Running account backup with target : /backup/2020-12-01/accounts
[2020-12-01 02:00:32 +0200] info [backup] checking backup for user1
[2020-12-01 02:00:32 +0200] info [backup] Backups ARE enabled for user1
[2020-12-01 02:00:32 +0200] info [backup] Calling pkgacct under cpuwatch to backup user “user1”
[2020-12-01 02:00:33 +0200] pkgacct started.
[2020-12-01 02:00:33 +0200] pkgacct version 10 - user : user1 - tarball: 1 - target mysql : default - split: 0 - incremental: 0 - homedir: 1 - mailman: 1 - backup: 1 - archive version: 4 - running with uid 0
[2020-12-01 02:00:33 +0200] pkgacct using '/usr/local/cpanel/3rdparty/bin/pigz -6 --processes 1 --blocksize 4096 --rsyncable' to compress archives
[2020-12-01 02:00:33 +0200] pkgacct working dir : /backup/2020-12-01/accounts/user1
[2020-12-01 02:00:33 +0200] Copying Reseller Config...[2020-12-01 02:00:33 +0200] Done
[2020-12-01 02:00:33 +0200] Copying Suspension Info (if needed)...[2020-12-01 02:00:33 +0200] Done
[2020-12-01 02:00:33 +0200] Copying installed SSL certificates and keys...[2020-12-01 02:00:33 +0200] Performing “ApacheTLS” component....
[2020-12-01 02:00:33 +0200] Completed “ApacheTLS” component.
[2020-12-01 02:00:33 +0200] Done
[2020-12-01 02:00:33 +0200] Copying Domain Keys....[2020-12-01 02:00:33 +0200] Done
[2020-12-01 02:00:33 +0200] Copying Bandwidth Data....[2020-12-01 02:00:33 +0200] Performing “Bandwidth” component....
Summary databases … done!
[2020-12-01 02:00:34 +0200] Completed “Bandwidth” component.
[2020-12-01 02:00:34 +0200] Done
[2020-12-01 02:00:34 +0200] Copying Dns Zones.......233.174.0.154.in-addr.arpa......xxxx...[2020-12-01 02:00:34 +0200] Done
[2020-12-01 02:00:34 +0200] Copying Mail files....[2020-12-01 02:00:34 +0200] Done
[2020-12-01 02:00:34 +0200] Copying proftpd file....[2020-12-01 02:00:34 +0200] Done
[2020-12-01 02:00:34 +0200] Performing “Logs” component....
...log file sizes [5441448 byte(s)]......$
[2020-12-01 02:00:34 +0200] Copy userdata...[2020-12-01 02:00:34 +0200] Done
[2020-12-01 02:00:34 +0200] Copy custom virtualhost templates...[2020-12-01 02:00:34 +0200] Done
[2020-12-01 02:00:34 +0200] Copying mailman lists and archives....Done copying mailman lists and archives.
[2020-12-01 02:00:34 +0200] Copying homedir.............
.........
[2020-12-01 02:00:40 +0200] Done
[2020-12-01 02:00:40 +0200] Fixing up EA4 .htaccess blocks: /home/user1/xxxx/.htaccess  /home/user1/xxxx/.htaccess  Done.
[2020-12-01 02:00:40 +0200] Performing “Mysql” component....
Load watching suspended due to SIGUSR1
[2020-12-01 02:00:40 +0200] Determining mysql dbs...[2020-12-01 02:00:40 +0200] ...mysqldump version: 5.7.32...[2020-12-01 02:00:40 +0200] ...mysql version: 5.7...[2020-12-01 02:00:40 +0200] Saving mysql privs.$
[2020-12-01 02:00:40 +0200] Saving mysql authentication information...[2020-12-01 02:00:40 +0200] Done
[2020-12-01 02:00:40 +0200] Storing MySQL databases...[2020-12-01 02:00:40 +0200] Storing database xxxx
[2020-12-01 02:00:41 +0200] Done
[2020-12-01 02:00:41 +0200] ...Done
[2020-12-01 02:00:41 +0200] Storing Roundcube’s data............
roundcube.users,roundcube.identities,roundcube.contacts,roundcube.contactgroups[2020-12-01 02:00:41 +0200] (7179 bytes)
roundcube.contactgroupmembers[2020-12-01 02:00:41 +0200] (2405 bytes)
[2020-12-01 02:00:41 +0200] ...Done
Load watching resumed due to SIGUSR2
[2020-12-01 02:00:41 +0200] Completed “Mysql” component.
[2020-12-01 02:00:41 +0200] Performing “MysqlRemoteNotes” component....
[2020-12-01 02:00:41 +0200] Completed “MysqlRemoteNotes” component.
[2020-12-01 02:00:41 +0200] Performing “CpUserFile” component....
[2020-12-01 02:00:41 +0200] Completed “CpUserFile” component.
[2020-12-01 02:00:41 +0200] Copying crontab file.......[2020-12-01 02:00:41 +0200] Done
[2020-12-01 02:00:41 +0200] Performing “Quota” component....
[2020-12-01 02:00:41 +0200] Completed “Quota” component.
[2020-12-01 02:00:41 +0200] Performing “Integration” component....
[2020-12-01 02:00:41 +0200] Completed “Integration” component.
[2020-12-01 02:00:41 +0200] Performing “AuthnLinks” component....
[2020-12-01 02:00:41 +0200] Completed “AuthnLinks” component.
[2020-12-01 02:00:41 +0200] Performing “APITokens” component....
[2020-12-01 02:00:41 +0200] Completed “APITokens” component.
[2020-12-01 02:00:41 +0200] Performing “DNSSEC” component....
[2020-12-01 02:00:41 +0200] Completed “DNSSEC” component.
[2020-12-01 02:00:41 +0200] Performing “Custom” component....
[2020-12-01 02:00:41 +0200] No custom components to perform.
[2020-12-01 02:00:41 +0200] Completed “Custom” component.
[2020-12-01 02:00:41 +0200] Performing “AutoSSL” component....
[2020-12-01 02:00:41 +0200] Completed “AutoSSL” component.
[2020-12-01 02:00:41 +0200] Storing Subdomains....
[2020-12-01 02:00:41 +0200] Done
[2020-12-01 02:00:41 +0200] Storing Parked Domains....
[2020-12-01 02:00:41 +0200] Done
[2020-12-01 02:00:41 +0200] Storing Addon Domains....
[2020-12-01 02:00:41 +0200] Performing “Password” component....
[2020-12-01 02:00:41 +0200] Completed “Password” component.
[2020-12-01 02:00:41 +0200] Performing “DigestShadow” component....
[2020-12-01 02:00:41 +0200] Completed “DigestShadow” component.
[2020-12-01 02:00:41 +0200] Copying shell.......[2020-12-01 02:00:41 +0200] Done
[2020-12-01 02:00:41 +0200] Performing “PublicContact” component....
[2020-12-01 02:00:41 +0200] Completed “PublicContact” component.
[2020-12-01 02:00:41 +0200] Performing “MailLimits” component....
[2020-12-01 02:00:41 +0200] Completed “MailLimits” component.
[2020-12-01 02:00:41 +0200] Performing “LinkedNodes” component....
[2020-12-01 02:00:41 +0200] Completed “LinkedNodes” component.
[2020-12-01 02:00:41 +0200] Creating Archive ....................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
..............................
[2020-12-01 02:21:58 +0200] Done
[2020-12-01 02:21:58 +0200] pkgacctfile is: /backup/2020-12-01/accounts/user1.tar.gz
[2020-12-01 02:21:58 +0200]
[2020-12-01 02:21:58 +0200] size is: 16274170600
[2020-12-01 02:21:58 +0200]
[2020-12-01 02:21:58 +0200] homesize is: 38614073344
[2020-12-01 02:21:58 +0200]
[2020-12-01 02:21:58 +0200] homefiles is: 61049
[2020-12-01 02:21:58 +0200]
[2020-12-01 02:21:58 +0200] mysqlsize is: 23196057
[2020-12-01 02:21:58 +0200] pkgacct completed
[2020-12-01 02:21:59 +0200] info [backup] Successfully backed up account “user1” to “/backup/2020-12-01/accounts”
[2020-12-01 02:21:59 +0200] info [backup] Adding metadata information for user1 to backup at /backup/2020-12-01
[2020-12-01 02:21:59 +0200] info [backup] Queuing daily backup copy of “user1” for transport of “/backup/2020-12-01/accounts/user1.tar.gz” to “2020-12-01/accounts/user1.tar.gz”
[2020-12-01 02:21:59 +0200] info [backup] This particular transport will be queued with keep_local = 0 , based on the need to copy weekly () and/or monthly () copies as well.
[2020-12-01 02:22:49 +0200] info [backup] Queuing transport of file: /backup/2020-12-01/accounts/user1.tar.gz
[2020-12-01 02:22:49 +0200] info [backup] no_transport = 0 .. and queueid = TQ:TaskQueue:6256
[2020-12-01 02:22:49 +0200] info [backup] leaving queue_backup_transport_item
[2020-12-01 02:22:49 +0200] info [backup] checking backup for devel
[2020-12-01 02:22:49 +0200] info [backup] Queuing transport of meta file: /backup/2020-12-01/accounts/.master.meta
[2020-12-01 02:22:49 +0200] info [backup] no_transport = 0 .. and queueid = TQ:TaskQueue:6257
[2020-12-01 02:22:49 +0200] info [backup] leaving queue_backup_transport_item
[2020-12-01 02:22:49 +0200] info [backup] Queuing prune operation for remote destination daily backups
[2020-12-01 02:22:49 +0200] info [backup] no_transport = 0 .. and queueid = TQ:TaskQueue:6258
[2020-12-01 02:22:49 +0200] info [backup] Queuing prune operation for remote destination daily backups
[2020-12-01 02:22:49 +0200] info [backup] no_transport = 0 .. and queueid = TQ:TaskQueue:6258
[2020-12-01 02:22:49 +0200] info [backup] leaving queue_backup_transport_item
[2020-12-01 02:22:49 +0200] info [backup] Queuing removal of staging directories since KEEPLOCAL is disabled.
[2020-12-01 02:22:49 +0200] info [backup] no_transport = 0 .. and queueid = TQ:TaskQueue:6259
[2020-12-01 02:22:49 +0200] info [backup] leaving queue_backup_transport_item
[2020-12-01 02:22:49 +0200] info [backup] Queuing transport reporter
[2020-12-01 02:22:49 +0200] info [backup] no_transport = 0 .. and queueid = TQ:TaskQueue:6260
[2020-12-01 02:22:49 +0200] info [backup] leaving queue_backup_transport_item
[2020-12-01 02:22:49 +0200] info [backup] Completed at Tue Dec  1 02:22:49 2020
[2020-12-01 02:22:49 +0200] info [backup] Final state is Backup::Success (0)
[2020-12-01 02:22:49 +0200] info [backup] Sent Backup::Success notification.

 

[cPRex]

@Thornhill Associates - it might be best if we took a look at that. Could you open a ticket using the link in my signature so we can check out the system? If you do that, please post the number here so I can update the community with our findings.

 

[TA]

Thanks @cPRex. When I entered the subject for the ticket the following link was suggested:

cPanel

support.cpanel.net

I followed the workaround given there (set PSQLBACKUP to 'yes' in /var/cpanel/backups/config) and the postgres database is now included in the daily backup.

I am happy with the workaround - however, it would be useful to have this documented somewhere (the only reference made to PSQLBACKUP in the cPanel docs is a change log for v82.0.4), and even more useful if this option was automatically set to 'yes' when PostgreSQL is configured on WHM.

 

[cPRex]

I completely agree - I've notified our documentation team about that so we can make some adjustments :D

 

[plague]

I have so many questions now....
Why this options is missing from the WHM interface and when will it be available ?
Why is this disable by default since it does not make any sense on doing incomplete backups?
Why no one from cpanel replied here with this workaround before?

I reported this a year ago, I had a ticket opened about this and even the support staff didn't know what to do.