471,316 Members | 1,026 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,316 software developers and data experts.

problem with pg_dump and restoring sequences


postgresql 7.4beta4 on linux
and postgresql 7.3.4 on linux

We have a database that we routinely backup using "pg_dump -a"
We have to do this (dump the data only) because of various characteristics of
the database (e.g. the dump file tries to create fks to tables that it hasn't
created yet if we export schema + data).

When we come to do a restore, the schema is created by running through our
*.sql files, and then we use psql to import the backed up data. However,
there is a problem with only 2 of the many sequences that psql tries to

here is a snippet of the lines that fail in the dump file:

-- start quote--
SET search_path = signal, pg_catalog;

-- TOC entry 2 (OID 169712)
-- Name: type_id_seq; Type: SEQUENCE SET; Schema: signal; Owner: martin
SELECT pg_catalog.setval('type_id_seq', 1, false);

-- TOC entry 3 (OID 169728)
-- Name: auto_id_seq; Type: SEQUENCE SET; Schema: signal; Owner: martin

SELECT pg_catalog.setval('auto_id_seq', 19, true); <------ FAILS
-- TOC entry 4 (OID 169745)
-- Name: instance_at_seq; Type: SEQUENCE SET; Schema: signal; Owner: martin

SELECT pg_catalog.setval('instance_at_seq', 41, true); <----- FAILS
-- end quote--

the failure messages are:
psql:db.backup:42041: ERROR: relation "auto_id_seq" does not exist
psql:db.backup:42049: ERROR: relation "instance_at_seq" does not exist

Now - to make it work all I have to do is edit the dump file and prepend the
schema name to the setval call for these 2 failing cases:

select pg_catalog.setval('signal.auto_id_seq', 19, true); <----- WORKS

I initially thought that the schema name might have been the cause of the
problem, but changing it has no effect - so maybe it's the search path (but
notice that the first sequence [type_id_seq] works). I do create all tables
"without oids", maybe that's related?

It is most likely that the problem is with my code - but I can't see it. Here
is a \d of the two tables taken immediately before trying to import the data:

db=# \d signal.instance
Table "signal.instance"
Column | Type | Modifiers
at | bigint | not null default nextval('signal.instance_at_seq'::text)
type_id | bigint | not null
f0 | text |
f1 | text |
f2 | text |
f3 | text |
f4 | text |
"instance_pkey" primary key, btree ("at")
"instance_f0" btree (f0)
"instance_type_id" btree (type_id)
Foreign-key constraints:
"$1" FOREIGN KEY (type_id) REFERENCES signal."type"(id)

db=# \d signal.auto
Table "signal.auto"
Column | Type | Modifiers
id | bigint | not null default nextval('signal.auto_id_seq'::text)
type_id | bigint | not null
schema | text | not null
relation | text | not null
fields | text |
criteria | text |
is_insert | boolean | not null default false
is_update | boolean | not null default false
is_delete | boolean | not null default false
"auto_pkey" primary key, btree (id)
Foreign-key constraints:
"$1" FOREIGN KEY (type_id) REFERENCES signal."type"(id) ON DELETE CASCADE

also, both sequences do actually exist at this point :-)

What I do not understand is why it is only these 2 cases out of lots of
similar sequence setting that fail. I understand that I am probably not
providing enough information here for a diagnostic - this email is just to
test the water and see if anybody else has experienced similar problems.

I will start work on a test case to see if I can reproduce the behaviour with
a smaller input set. If so I'll post it - but I would be interested to hear
from anybody with similar experiences. At the moment this problem is
preventing us offering an "automatic backup and restore" :-(

Martin Hart
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #1
0 4572

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Sam | last post: by
6 posts views Thread by Rajesh Kumar Mallah | last post: by
3 posts views Thread by mike | last post: by
1 post views Thread by Ben-Nes Michael | last post: by
1 post views Thread by Plant Thomas | last post: by
reply views Thread by Stefano Bonnin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.