0

I am trying to replace an existing database with pg_restore and am running into issues. I apologize if this issue is elementary.

Specifically my Postgres install is set up like this:

  • database postgres owned by root
  • database d owned by u
  • database restore owned by u (more on this later)

I created a dump like pg_dump --format=custom .... I am now trying to replace d with the contents of that dump. Specifically I am running a command like pg_restore -cC --dbname=$DB --exit-on-error --format=custom --username=$USER.

But it seems no matter what combination of $DB and $USER, Postgres is unable to restore. From the error messages (below) Postgres seems to want the user to simultaneously be root and the owner of d. I have read the man pages and tried various combinations to no avail.

What am I doing wrong?


Specifically, if $DB is d, regardless of $USER, I get

ERROR: cannot drop the currently open database

Command was: DROP DATABASE d;

Fair enough. So let's try $DB=postgres and $USER=root:

ERROR: must be owner of database d

Command was: DROP DATABASE d;

Okay, so let's try $DB=postgres and $USER=u:

ERROR: must be member of role "root"

Command was: ALTER DATABASE d OWNER TO root;

Huh? Maybe that has to do with logging into postgres, which is owned by root? Let's try $DB=restore (created specifically for this purpose) and $USER=u:

ERROR: must be member of role "root"

Command was: ALTER DATABASE d OWNER TO root;

1

Evidently, "root" is not a superuser (which is a bit odd, given the normal implications of the word "root"). The pg_restore command you wish to run must be run as a superuser. Only a superuser can drop someone else's database, and then create a new database owned by someone else.

Although it looks like the restored database is going to be owned by "root", not "u". I guess that is not what you are expecting, but it is what was encoded in the pg_dump output.

  • Yes you are right. I never would have thought root is not a super user. Thanks! – Solomonoff's Secret Aug 31 '18 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.