Re: problems with moving a database

On Thu, May 15, 2003 at 09:14:37AM -0400, Robert Treat wrote:
> From: Robert Treat <xzilla@users.sourceforge.net>
> To: Oliver Kullmann <O.Kullmann@Swansea.ac.uk>
> Cc: pgsql-admin@postgresql.org
> In-Reply-To: <20030514102354.GA8077@swan.ac.uk>
> X-Mailer: Ximian Evolution 1.0.8
> Date: 15 May 2003 09:14:37 -0400
> X-Reverse-DNS-Failure: 65.217.53.66
> Subject: Re: [ADMIN] problems with moving a database
> X-Spam-Status: No, hits=-4.9 required=8.0
>     tests=EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT,REFERENCES,
>           REPLY_WITH_QUOTES,USER_AGENT_XIMIAN
>     version=2.54
> X-Spam-Level:
> X-Spam-Checker-Version: SpamAssassin 2.54 (1.174.2.17-2003-05-11-exp)
>
> On Wed, 2003-05-14 at 06:23, Oliver Kullmann wrote:
> > On Wed, May 14, 2003 at 11:00:51AM +0530, A.Bhuvaneswaran wrote:
> > >
> > > >
> > > > Now I get
> > > >
> > > > > psql -dDB2 -U oliver < DB1.dmp
> > > > ERROR:  pg_class: Permission denied.
> > >
> > > I highly suspect that it would be due to the non-super user trying to
> > > access super user privileges.
> >
> > Both on my laptop and on the pc, "normal users" are the owners of the database,
> > and they have all rights.
> >
> > > In your case, it would have happened like in
> > > your laptop, the database owner is the super user and in your pc, the
> > > database owner is not the super user. Check it. Note, pg_dump does update
> > > pg_class to enable/disable the triggers.
> >
> > but I don't use any triggers?! The dump starts with
>
> you do use triggers, you just don't realize it. postgresql is trying to
> disables the foreign key triggers when you reimport the data, but your
> user doesn't have access to the pg_* system tables, and so it is
> generating an error " pg_class: Permission denied.".  You need to either
> make sure your user is a superuser, or drop the -x flag from your dump
> command (note this might cause other issues)
>
> Robert Treat
>

Hi Robert,

thanks for your help, but dropping the -x flag didn't change anything:

Using

pg_dump -a -R -O database > db.dmp

and filling it with

psql -ddatabase -U csoliver < db.dmp

still yields the same errors

ERROR:  pg_class: Permission denied.

Perhaps I can ignore these errors, but more seriously seems to me

ERROR:  copy: line 5624388, cannot extend bcls_info: No space left on device.
        Check free disk space.
lost synchronization with server, resetting connection

However, I have 12 GB free space?!?!

ANYWAY: Meanwhile I tried the simple version

laptop> pg_dump -O database > oliver@ps:database.dmp
pc> createdb database
pc> psql -ddatabase < database.dmp

and, different from my first tries (with an older version of PostgreSQL),
this time it seems to have worked! Hope that's it.

Thanks for your help!

Oliver