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

plague

Well-Known Member
Sep 22, 2006
67
14
158
Brasil
cPanel Access Level
Root Administrator
Twitter
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:
Code:
[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:
Code:
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

Well-Known Member
Sep 22, 2006
67
14
158
Brasil
cPanel Access Level
Root Administrator
Twitter
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/
Code:
.....

[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â

.....
Code:
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

Well-Known Member
Sep 22, 2006
67
14
158
Brasil
cPanel Access Level
Root Administrator
Twitter
Bug confirmed in the transfer tool:

Code:
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.
...
 
  • Like
Reactions: cPanelMichael

plague

Well-Known Member
Sep 22, 2006
67
14
158
Brasil
cPanel Access Level
Root Administrator
Twitter
Problem in the daily backup confirmed too

Code:
 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

Technical Support Community Manager
Staff member
Apr 11, 2011
47,911
2,233
363
cPanel Access Level
DataCenter Provider
Twitter
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.
 
  • Like
Reactions: plague