[Case 83089] postgres: restore cpmove and dbuser with the same name as database

[TomKu]

In Postgres < 9.3 I can create database and dbuser with the same name.
This stopped to work in Postgres 9.3 as after creating database username_db
cPanel reports that such user already exists when I try to create username_db user.

Consequently pkgacct archives done on cPanel with postgres < 9.3
where dbusername is the same as dbname do not
restore on cPanel with postgres 9.3 and the following can be seen in restorepkg log:

Restoring PostgreSQL databases....ALTER ROLE
CREATE ROLE
Restoring database username_db......
The database “username_db” cannot be added because a user with the same name already exists.
pg_restore: [archiver (db)] connection to database "username_db" failed:
FATAL: database "username_db" does not exist
...

Please share your ideas.

 

[cPanelMichael]

Hello

The new behavior is actually by design. There is an internal case open discussing the possibility of allowing restores/transfers in instances where the names are the same, but no specific time frame is available on when such a solution might become available. For reference, the case number is 83089.

Thank you.

 

[TomKu]

I think you should first discuss and then apply changes and not vice versa.
Additionally there should be some announcement and guide on how to overcome this problem.
Leaving such change for admins to discover and then have them waste time for designing a helper solution is bad.

Now, for anyone looking for solution this is what I had to use manually.
After failure to restore db with the same name as dbuser for USERNAME

1. The restorepkg only creates dbusers then fails

2. Now we need to restore the databases that cPanel fails at. Repeat for each DBNAMESUFFIX

tar -C /tmp -xzf /home/cpmove-USERNAME.tar.gz cpmove-USERNAME/psql/USERNAME_DBNAMESUFFIX.tar
pg_restore -v -d template1 /tmp/cpmove-USERNAME/psql/USERNAME_DBNAMESUFFIX.tar -e -C -F t
rm -rf /tmp/cpmove-USERNAME

3. Restore grants
tar -O -xzf /home/cpmove-USERNAME.tar.gz cpmove-USERNAME/psql_grants.sql | psql

 

[cPanelMichael]

Additionally there should be some announcement and guide on how to overcome this problem..

I agree. Part of the discussion in the internal case includes suggestions on adding a warning about this to the interface. I have linked this thread in the case so that our developers can review your feedback.

Thanks.