Thread: Dump/Transfer Sequence Problems
Hi,
I am using Navicat to transfer data from one database to another. But
it soon gives me an error message like the following:
[Err] [Dtf] Transfer Data [create table "admin"."news" ( "id" int4
not null default nextval('news_id_seq1'::regclass) , "date" date ,
"text_en" text , "text_fr" text , "text_es" text ) WITHOUT OIDS;
ALTER table "admin"."news" SET WITHOUT CLUSTER;;]: ERROR: relation
"news_id_seq1" does not exist
Ok, I understand meanwhile that there is a sequence being created when
using SERIALs.... But why does a dump or that kind of transfer
transfers not the sequence with it? Or is it because I am using 8.1 on
one and 8.2 on another machine? I can't imagine...
Thanks for any suggestions,
Stef
Stefan Schwarzer wrote: > Hi, > > I am using Navicat to transfer data from one database to another. But it > soon gives me an error message like the following: I think you'll probably have to ask the navicat people. If you want to use pg_dump to transfer data from 8.1 to 8.2 though, use the version of pg_dump that ships with 8.2. -- Richard Huxton Archonet Ltd
>> I am using Navicat to transfer data from one database to another.
>> But it soon gives me an error message like the following:
>
> I think you'll probably have to ask the navicat people.
>
> If you want to use pg_dump to transfer data from 8.1 to 8.2 though,
> use the version of pg_dump that ships with 8.2.
Ok, tried that.... Not yet mentioned is the fact that I am trying to
import postgis tables (tables with geographic parameter).
But I get this:
pg_restore: restoring data for table "admin01"
pg_restore: restoring data for table "boundaries_national"
pg_restore: [archiver (db)] error returned by PQputCopyData: server
closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
pg_restore: *** aborted because of error
The log (where can I change the parameters?) says this:
ERROR: syntax error at or near "pg_restore" at character 1
STATEMENT: pg_restore -U ss_admin -d geodataportal -v /Users/
schwarzer/Temp/pg_dump.gridca.2008-04-10.gis.c.sql
There again, when I look into the dump file, it seems that it doesn't
include any statement to create the sequence...
Thanks for any hints,
Stef
Stefan Schwarzer wrote: >>> I am using Navicat to transfer data from one database to another. But >>> it soon gives me an error message like the following: >> >> I think you'll probably have to ask the navicat people. >> >> If you want to use pg_dump to transfer data from 8.1 to 8.2 though, >> use the version of pg_dump that ships with 8.2. > > Ok, tried that.... Not yet mentioned is the fact that I am trying to > import postgis tables (tables with geographic parameter). OK, might well be relevant. Your error below though is nothing to do with a sequence. > But I get this: > > pg_restore: restoring data for table "admin01" > pg_restore: restoring data for table "boundaries_national" > pg_restore: [archiver (db)] error returned by PQputCopyData: server > closed the connection unexpectedly What this indicates is that something in the backend went bang while copying the data into boundaries_national. Shouldn't happen. I'm assuming that table contains postGIS data? Could you try dumping & restoring just that one table? Then, could you make a copy of the table, but with only a few rows in it and dump/restore that? That should tell us whether there is a particular value that is causing the problem. > This probably means the server terminated abnormally > before or while processing the request. > pg_restore: *** aborted because of error > > The log (where can I change the parameters?) says this: > > ERROR: syntax error at or near "pg_restore" at character 1 > STATEMENT: pg_restore -U ss_admin -d geodataportal -v > /Users/schwarzer/Temp/pg_dump.gridca.2008-04-10.gis.c.sql > > There again, when I look into the dump file, it seems that it doesn't > include any statement to create the sequence... This is a separate problem. If you run the pg_restore above without the "-d geodataportal" but with --schema-only it should print to stdout all the schema-related stuff. -- Richard Huxton Archonet Ltd
>>>> I am using Navicat to transfer data from one database to another. >>>> But it soon gives me an error message like the following: >>> >>> I think you'll probably have to ask the navicat people. >>> >>> If you want to use pg_dump to transfer data from 8.1 to 8.2 >>> though, use the version of pg_dump that ships with 8.2. >> Ok, tried that.... Not yet mentioned is the fact that I am trying >> to import postgis tables (tables with geographic parameter). > > OK, might well be relevant. Your error below though is nothing to do > with a sequence. > >> But I get this: >> pg_restore: restoring data for table "admin01" >> pg_restore: restoring data for table "boundaries_national" >> pg_restore: [archiver (db)] error returned by PQputCopyData: server >> closed the connection unexpectedly > > What this indicates is that something in the backend went bang while > copying the data into boundaries_national. Shouldn't happen. I'm > assuming that table contains postGIS data? Yes. > Could you try dumping & restoring just that one table? I tried that one too. But same thing. > Then, could you make a copy of the table, but with only a few rows > in it and dump/restore that? That should tell us whether there is a > particular value that is causing the problem. > >> This probably means the server terminated abnormally >> before or while processing the request. >> pg_restore: *** aborted because of error >> The log (where can I change the parameters?) says this: >> ERROR: syntax error at or near "pg_restore" at character 1 >> STATEMENT: pg_restore -U ss_admin -d geodataportal -v /Users/ >> schwarzer/Temp/pg_dump.gridca.2008-04-10.gis.c.sql >> There again, when I look into the dump file, it seems that it >> doesn't include any statement to create the sequence... > > This is a separate problem. > > If you run the pg_restore above without the "-d geodataportal" but > with --schema-only it should print to stdout all the schema-related > stuff. Don't know if the problem occured because I hadn't dumped and restored my data as postgres, but as another postgres-user. Now, it works. But I re-installed/compiled postgres/postgis, so, can't really say why it works now... Thanks a lot for your help.
Stefan Schwarzer wrote: > > Don't know if the problem occured because I hadn't dumped and restored > my data as postgres, but as another postgres-user. Now, it works. But I > re-installed/compiled postgres/postgis, so, can't really say why it > works now... Since you were getting backend crashes, I'd guess you had an old version of a postGIS library (or something it depends on) being picked up in your new installation. -- Richard Huxton Archonet Ltd