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

plague

Well-Known Member
Sep 22, 2006
75
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
75
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
75
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
75
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,913
2,202
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

Product Owner
Staff member
Nov 14, 2017
13,304
1,252
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

Product Owner
Staff member
Nov 14, 2017
13,304
1,252
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
75
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.
 

cPanelLauren

Product Owner
Staff member
Nov 14, 2017
13,304
1,252
313
Houston
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:

CPANEL-29288 said:
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

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

Code:
#!/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:

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