Обсуждение: problems with moving a database
Hi,
since quite some time I'm wrestling with the following,
seemingly very basic problem:
I have a database, call it DB1, on my laptop (size of DB ~ 6 GB; OS Suse Linux 8.0).
Now I need to move DB1 to another computer (laptop got too small).
So I "just" create a dump, and create the database on the
pc (call the new database DB2; same version of PostgreSQL, also Suse Linux 8.0).
The first time I tried it was not possible to recreate the
table structure. So well, I use
pg_dump -a
Still problems with the owner structure (user1 on the laptop
is different from user2 on the pc). Since I do not care at
all about these different names, I used
pg_dump -a -R -O -x DB1
Now I get
> psql -dDB2 -U oliver < DB1.dmp
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
FATAL 2: copy: line 6606854, write of log file 3, segment 143, offset 8716288 failed: No space left on device
lost synchronization with server, resetting connection
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: copy: line 6602698, cannot write block 34375 of b_unit_reductions_b_info_id_key: No space left on device
lost synchronization with server, resetting connection
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
ERROR: pg_class: Permission denied.
setval
--------
2
(1 row)
setval
--------
1
(1 row)
setval
--------
1
(1 row)
setval
--------
4
(1 row)
setval
--------
8
(1 row)
setval
--------
1
(1 row)
setval
--------
1
(1 row)
setval
--------
1
(1 row)
setval
--------
230
(1 row)
setval
--------
178
(1 row)
setval
----------
11647053
(1 row)
setval
--------
150
(1 row)
setval
----------
11648603
(1 row)
As you can see, first I get these pg_class errors:
I guess still the systems tries to copy something
of the old structure (in which I'm not interested
--- I just want to get the data copied, *nothing else*).
So well, perhaps these errors can be ignored, but the
other errors seem to be serious, and the database
is not consistent anymore: For example I get NULL
entries for attributes of type SERIAL.
On the pc I still have 12 GB free (the dump-file is
a bit more than 1 GB). What else can I do??
I would be glad for any help!
Thanks in any case.
Oliver
> pg_dump -a -R -O -x DB1 Here pg_dump with -a, dumps only the data. Is it what you want? Does the database exist in machine 2? If not, you should not use -a option. Rather, use $ pg_dump dbname -uf dbname.sql which is enough to dump entire database. > > 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. 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. regards, bhuvaneswaran
On Wed, May 14, 2003 at 11:00:51AM +0530, A.Bhuvaneswaran wrote:
> Date: Wed, 14 May 2003 11:00:51 +0530 (IST)
> From: "A.Bhuvaneswaran" <bhuvansql@myrealbox.com>
> X-X-Sender: bhuvan@Bhuvan.bksys.co.in
> To: Oliver Kullmann <O.Kullmann@Swansea.ac.uk>
> cc: pgsql-admin@postgresql.org
> In-Reply-To: <20030513180758.GB5534@swan.ac.uk>
> X-Reverse-DNS-Failure: 61.3.64.10
> Subject: Re: [ADMIN] problems with moving a database
> X-Spam-Status: No, hits=-9.7 required=8.0
> tests=IN_REP_TO,QUOTE_TWICE_1,USER_AGENT_PINE
> version=2.53
> X-Spam-Level:
> X-Spam-Checker-Version: SpamAssassin 2.53 (1.174.2.15-2003-03-30-exp)
>
> > pg_dump -a -R -O -x DB1
>
> Here pg_dump with -a, dumps only the data. Is it what you want?
yes; dumping the whole database does not work (lots of error messages)
> Does the
> database exist in machine 2?
yes, I recreated it from scratch;
> If not, you should not use -a option. Rather,
> use
>
> $ pg_dump dbname -uf dbname.sql
>
> which is enough to dump entire database.
>
> >
> > 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
--
-- Selected TOC Entries:
--
--
-- Data for TOC Entry ID 14 (OID 19638375)
--
-- Name: frames Type: TABLE DATA Owner: kullmann
--
-- Disable triggers
UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" = 'frames';
COPY "frames" FROM stdin;
1 {3} 1
2 {2,3} 2
\.
-- Enable triggers
UPDATE pg_class SET reltriggers = (SELECT count(*) FROM pg_trigger where pg_class.oid =
tgrelid) WHERE relname = 'frames';
What has this trigger bussiness to do with my database?
Is it not possible to back up a database as a normal user??
Any help is really appreciated (I definitely need to move
the database)!
Oliver
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
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