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

plague

Well-Known Member
Sep 22, 2006
74
17
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
74
17
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
74
17
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
74
17
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

Administrator
Staff member
Apr 11, 2011
47,914
2,200
363
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

cPanelLauren

Technical Support Community Manager
Staff member
Nov 14, 2017
13,004
1,200
313
Houston
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
 

cPanelLauren

Technical Support Community Manager
Staff member
Nov 14, 2017
13,004
1,200
313
Houston
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

Well-Known Member
Sep 22, 2006
74
17
158
Brasil
cPanel Access Level
Root Administrator
Twitter
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.