473,378 Members | 1,496 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

problem with pg_dump and restoring sequences


Hi,

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
restore:

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 |
Indexes:
"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
Indexes:
"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" :-(

TIA
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 4820

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Sam | last post by:
I'm having trouble restoring databases that have to lo type installed in /contrib/lo. The dump seems to work just fine, I get no errors when I execute the following command #pg_dump -Fc -o -b...
6
by: Rajesh Kumar Mallah | last post by:
Hi, I face the following problem in transferring data from pgsql 7.3.4 to pgsql 7.4 RC1 . I am piping the output of pg_dumpall from 7.3 to 7.4 running on different port. The problem is there...
0
by: Wind Wood | last post by:
Hello ŁĄ I can dump data from old dababase(Postgresql 6.5.3), but new problem came out when I restore the data to postgresql 7.4. It went out during the COPY command, if I do it in such command...
3
by: mike | last post by:
I have just upgraded froom Fedora test1 to test2 which has upgraded postgres version from 7.4.2 to 7.4.5 (frrom rpm -q) I have used pg_dumpall to dump my database, the restore appeared to go OK,...
3
by: Sean Shanny | last post by:
To all, We dumped our instance from a 7.4.2 instance compiled with a block size of 32k running on OSX 10.3.3 server.. We loaded this dump on Fedora 2.6.5-1.358smp running postgresql 7.4.2...
1
by: Ben-Nes Michael | last post by:
Hello Im getting strange errors when restoring data from pg_dump files: ERROR: duplicate key violates unique constraint "products_pkey" CONTEXT: COPY products, line 1: "98 Super Pak Ruby...
6
by: Soeren Gerlach | last post by:
Hi, some weeks ago I started to develop an application using Postgresql the first time. I'm running 7.4.3 on a Linux box with a plain 2.6.7 kernel, the storage is handled by 5 SATA disks,...
1
by: Plant Thomas | last post by:
Hi, I have a problem restoring a database from Postgresql 7.3 ( backup created with 'pg_dump -Ft -b > test.tar' ) to a new database on PostgreSQL 7.4.3 on Cygwin. I get the following error: ...
0
by: Stefano Bonnin | last post by:
Hi, I'm a Postgis user, and I have a problem restoring data from 7.4 to 8.0..0beta2. I use the postgis_restore.pl script that comes with postgis distribution. I do the following for the dump:...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

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.