[support] restoring drupal site - postgresql database

[Max Pyziur]

> On Fri, 27 May 2011 15:20:39 -0400 (EDT)
> Max Pyziur <pyz at brama.com> wrote:
>
>>
>> Greetings,
>>
>> While restoring one of my drupal-related databases, I get the
>> following error/warning messages:
>>
>> ERROR:  permission denied to set session authorization
>> ERROR:  must be owner of schema public
>
> How did you create the DB?

>From a superuser account
First the user account (using a name that lives on the machine)
createuser --no-createdb drupal1

Login to drupal1 and iisue the following command:
psql drupal1 < drupal1.out

>> The restore process is issued from a command line:
>> > psql drupal1 < drupal1.out
>
> What user are you using to perform this command?

drupal1

> Why didn't you use pg_dump/pg_restore?

I did create drupal1.out using pg_dump

Out of habit, I use psql drupal < drupal1.out; I've never had problems
until now with any other restore like this.

Should I try pg_restore?

> Did you remember to create the DB user on the pg cluster?

You know more than I do. I created the user (per above) by way of one of
the system's postgresql superusers.

> During restore pg is trying to execute
>
> SET SESSION AUTHORIZATION $username;
> the username you're connecting with psql
> if username doesn't have ownership of public schema.
>
> I think that if that command fail you're going to create all the
> object in the DB with the wrong ownership... and that may be a
> problem.

We do want to avoid problems.

;-)

> --
> Ivan Sergio Borgonovo
> http://www.webthatworks.it


Much thanks for your help.

Max

> --
> [ Drupal support list | http://lists.drupal.org/ ]
>