Thread

1. Problem with restoring database from a pg_dump generated script.

   Jerry Davis <jdavis@teamdev.com> — 2001-05-07T19:28:38Z

   In short, when I try to restore a database from a pg_dump
   generated SQL script, then try to insert additional records
   into the restored datbase, it gives me a duplicate key error.
   
   I am using the following version of the PostgreSQL database:
   
   PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
   
   and I am having a problem with dumping and restoring data.
   
   First, I dump an existing database to a text sql file with the
   following command from the shell:
   
   pg_dump -D -n logindb > testdump.sql
   
   It produces the following file:
   
   === start of testdump.sql file
   \connect - jdavis
   CREATE SEQUENCE login_primary_key_seq start 1 increment 1 maxvalue
   2147483647 minvalue 1  cache 1 ;
   SELECT nextval ('login_primary_key_seq');
   CREATE TABLE login (
   	primary_key int4 DEFAULT nextval('login_primary_key_seq'::text) NOT NULL,
   	user_index int4 NOT NULL,
   	hash character varying(25) NOT NULL,
   	last_used timestamp NOT NULL,
   	expired character varying DEFAULT 0 NOT NULL,
   	PRIMARY KEY (primary_key)
   );
   INSERT INTO login (primary_key,user_index,hash,last_used,expired) VALUES
   (1,1,'W5GGk7yEM+0Kw260tDeBaA==','2001-04-25 15:56:43-05','1');
   INSERT INTO login (primary_key,user_index,hash,last_used,expired) VALUES
   (2,1,'ufrmu+395SIfuBC6ombmwA==','2001-04-26 08:45:10-05','1');
   INSERT INTO login (primary_key,user_index,hash,last_used,expired) VALUES
   (3,1,'A7V4Rvo20cqb4CFDnWkOvg==','2001-04-26 11:14:26-05','1');
   INSERT INTO login (primary_key,user_index,hash,last_used,expired) VALUES
   (4,1,'fIlh5Su9iMc2KXt5tqCAEw==','2001-04-26 13:02:38-05','1');
   INSERT INTO login (primary_key,user_index,hash,last_used,expired) VALUES
   (5,1,'9eLdWa6wl/DrqOpq/0QSWA==','2001-04-26 16:39:51-05','1');
   INSERT INTO login (primary_key,user_index,hash,last_used,expired) VALUES
   (6,1,'x9P/sRNj4rFenORY+POq1w==','2001-05-01 08:48:20-05','1');
   INSERT INTO login (primary_key,user_index,hash,last_used,expired) VALUES
   (7,1,'4nT7+E4wBxKChA1BDgW0+Q==','2001-05-03 14:37:27-05','1');
   INSERT INTO login (primary_key,user_index,hash,last_used,expired) VALUES
   (8,1,'H9QDcbiNVLGlMXnngo9ICQ==','2001-05-03 16:51:26-05','1');
   === end of testdump.sql file
   
   Now, I do the following commands from the shell:
   
   dropdb logindb
   createdb logindb
   psql -e logindb < testdump.sql
   
   This works fine.  It recreates the table and inserts the data
   records.  It does give me one notice message:
   
   NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'login_pkey' for
   table 'login'
   
   Then I go into the psql utility to add new records.
   
   psql logindb
   
   This works fine and brings me to the psql prompt.  I enter the
   following command:
   
   insert into login (user_index,hash,last_used,expired) values
   (1,'abcdefghijklmnop','2001-05-07 08:00:00-05','1');
   
   This attempt to insert gives me the following error message:
   
   ERROR: Cannot insert a duplicate key into unique index login_pkey
   
   If I continually try the additional insert, it fails 8 times then
   finally succeeds on the 9th try, and when I do the following
   query:
   
   select * from login;
   
   it shows that the following record has been appended:
   
   9,1,'abcdefghijklmnop','2001-05-07 08:00:00-05','1'
   
   This suprises me, since the SQL in the testdump.sql file is
   created by the pg_dump utility, not by me, so I would have thought
   it would have everything necessary to restore the database without
   a problem.  I also would have expected that the INSERT INTO
   statements in the testdump.sql would have appropriately updated
   the primary key.
   
   Is there something missing from the original database that got
   dumped, or something I have to manually do to the dumped script
   that will allow a no-problem restore?  Or is this a bug in
   PostgreSQL?  I would appreciate any help you can offer.
   
   Jerry Davis
   TEAM Development
   jdavis@teamdev.com
   
   
   
   

2. Re: Problem with restoring database from a pg_dump generated script.

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-11T15:08:48Z

   "Jerry Davis" <jdavis@teamdev.com> writes:
   > CREATE SEQUENCE login_primary_key_seq start 1 increment 1 maxvalue
   > 2147483647 minvalue 1  cache 1 ;
   > SELECT nextval ('login_primary_key_seq');
   
   > Is there something missing from the original database that got
   > dumped, or something I have to manually do to the dumped script
   > that will allow a no-problem restore?
   
   Hm.  Apparently pg_dump should have restored this sequence with an
   initial value of 9, not 1.  Not sure why it didn't.  If I try
   
   create table foo (f1 serial);
   insert into foo default values;
   insert into foo default values;
   insert into foo default values;
   
   and then dump this in 7.0.*, I get
   
   \connect - tgl
   CREATE SEQUENCE "foo_f1_seq" start 3 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
   SELECT nextval ('"foo_f1_seq"');
   CREATE TABLE "foo" (
           "f1" int4 DEFAULT nextval('foo_f1_seq'::text) NOT NULL
   );
   COPY "foo" FROM stdin;
   1
   2
   3
   \.
   CREATE UNIQUE INDEX "foo_f1_key" on "foo" using btree ( "f1" "int4_ops" );
   
   which as you can see initializes the sequence to the right place (the
   next nextval() will produce 4).
   
   Can you recall anything about the history of your table that might have
   caused the sequence to get reset?
   
   Anyway, fixing the database state just requires issuing a setval() to
   set the sequence to the desired place.
   
   			regards, tom lane