The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'Database Discussions' started by TomKu, Mar 4, 2014.

  1. TomKu

    TomKu Member

    Joined:
    Oct 10, 2011
    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Chicago
    cPanel Access Level:
    Root Administrator
    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.
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,762
    Likes Received:
    662
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    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.
     
  3. TomKu

    TomKu Member

    Joined:
    Oct 10, 2011
    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Chicago
    cPanel Access Level:
    Root Administrator
    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
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,762
    Likes Received:
    662
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    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.
     
Loading...

Share This Page