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
********************************************************