From: Daniel Rubio <drubior@tinet.org>
To: pgsql-admin@postgresql.org
Subject: Restore problems due to changed table names
Date: Fri, 12 Mar 2004 09:54:51 +0100
Message-ID: <40517ADB.3010902@tinet.org> (raw)
Hi all.
Yesterday I planned the migration from a couple of databases from a
7.3.2 postgres box to a 7.4.1.
I did the pg_dump with the -C command, and substituted on the dump 2
aparitions of $libdir for the path of my libraries ( on the previous
tests, postgres didn't found this variable value, despite i had the
variable set: dynamic_library_path = '/apps/pg/lib', where pg is a
dinamic link)
¿Some idea on how to solve this?
Ok, now I'm on the new host, ready to import my database, via psql -d
template1 -f filename, it works fine, excepting from these errors:
psql:file1:879: ERROR: relation "usuari_codi_seq" does not exist
psql:file1:887: ERROR: relation "idioma_codi_seq" does not exist
psql:file1:895: ERROR: relation "portada_codi_seq" does not exist
psql:file1:903: ERROR: relation "nivell1_codi_seq" does not exist
Looking at the dump, for these lines, I see that postgres is trying to
set the values from these sequences, e.g.
-- Name: nivell1_codi_seq; Type: SEQUENCE SET; Schema: public; Owner:
riudoms
SELECT pg_catalog.setval ('nivell1_codi_seq', 14, true);
The problem resides in that the database user updated the table names
(but it seems that when it occurs, postgres doesn't change the sequence
names associated to them), and when importing, postgres automatically
created the sequences with the new names prefix, and then failed the
value setting :(
Fortunatelly the database had a few tables and sequences, and I could
solve this via PGAdmin, but I think it could be a disaster dumping for
example all databases on the system (we make hosting, and every user can
change the name of a table when he wants ...) or big-structured databases.
Someone has some idea of how this issue could be solved? Is a known
"bug" (If can be considered a bug ...)?
Until one more "collateral damage", I updated the sequences value to the
number the setval was telling, e.g ( 14 in SELECT pg_catalog.setval
('nivell1_codi_seq', 14, true);), but when the used tried to make a
insert it failed because the database told it was duplicated key values
(the second insert worked fine, because the sequence had incremented
this value), why this error happened? I would have to put the value from
setval+1 ? why?
Thanks in advance
--
********************************************************
Daniel Rubio Rodríguez
OASI (Organisme Autònom Per la Societat de la Informació)
c/ Assalt, 12
43003 - Tarragona
Tef.: 977.244.007 - Fax: 977.224.517
e-mail: drubio a oasi.org
********************************************************
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: drubior@tinet.org
Subject: Re: Restore problems due to changed table names
In-Reply-To: <40517ADB.3010902@tinet.org>
References: <40517ADB.3010902@tinet.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox