Re: Resetting connection errors and incomplete restores. - Mailing list pgsql-admin

My suggestion would be to grep out all the userid changes that are going
on here.  You don't need them and they clutter up your output.

Also, in the future use the -f db.out option with psql, you'll get line
numbers on the errors which should help you diagnose the problem.

On Mon, 2001-12-17 at 19:15, Sanjay Bhatia wrote:
> Hello.
>
> I am running into a problem while restoring a database and would really
> appreciate your help to figure out what's going on.
>
> I created a dump file using
>
> pg_dump -a mydb > $WORKING_DIR/db.out
>
> I then copy the db.out file to a new machine and recreate/reinitialize the
> 'mydb' database.  This process creates the database, a user and a group for me.
>  I use the following command to restore the database -
>
> psql -d mydb -U sb < db.out
>
> The sb user is created during the initialization process.
>
> We have a few tables in the database with thousands of rows.  My problem is
> that 2 of the tables don't get restored completely... When the restore command
> completes, No new rows get added to these 2 (app_user and app_group) tables.
> I've included the output of the restore command below.  Can you please let me
> know how to fix this?
>
> I've also included the contents of the logfile below.
>
> Thanks,
> sb
>
> You are now connected as new user sb.
> UPDATE 1
> You are now connected as new user roxadmin.
> You are now connected as new user sb.
> UPDATE 1
> UPDATE 1
> You are now connected as new user roxadmin.
> ERROR:  copy: line 1, Cannot insert a duplicate key into unique index
> rox_permission_pkey
> PQendcopy: resetting connection
> You are now connected as new user sb.
> UPDATE 1
> UPDATE 1
> You are now connected as new user roxadmin.
> ERROR:  copy: line 1, Cannot insert a duplicate key into unique index
> rox_user_pkey
> PQendcopy: resetting connection
> You are now connected as new user sb.
> UPDATE 1
> UPDATE 1
> You are now connected as new user roxadmin.
> ERROR:  copy: line 1, Cannot insert a duplicate key into unique index
> rox_group_pkey
> PQendcopy: resetting connection
> You are now connected as new user sb.
> UPDATE 1
> UPDATE 1
> You are now connected as new user roxadmin.
> ERROR:  copy: line 1, Cannot insert a duplicate key into unique index
> rox_group_member_pkey
> PQendcopy: resetting connection
> You are now connected as new user sb.
> UPDATE 1
> UPDATE 1
> You are now connected as new user roxadmin.
> ERROR:  copy: line 1, Cannot insert a duplicate key into unique index
> rox_parent_child_group_pkey
> PQendcopy: resetting connection
> You are now connected as new user sb.
> UPDATE 1
> UPDATE 1
> You are now connected as new user roxadmin.
> ERROR:  copy: line 1, Cannot insert a duplicate key into unique index
> rox_group_graph_pkey
> PQendcopy: resetting connection
> You are now connected as new user sb.
> UPDATE 1
> UPDATE 1
> You are now connected as new user roxadmin.
> ERROR:  copy: line 1, Cannot insert a duplicate key into unique index
> rox_granted_permission_pkey
> PQendcopy: resetting connection
> You are now connected as new user sb.
> UPDATE 1
> UPDATE 1
> You are now connected as new user roxadmin.
> You are now connected as new user sb.
> UPDATE 1
> UPDATE 1
> You are now connected as new user roxadmin.
> You are now connected as new user sb.
> UPDATE 1
> UPDATE 1
> You are now connected as new user roxadmin.
> You are now connected as new user sb.
> UPDATE 1
> UPDATE 1
> You are now connected as new user roxadmin.
> You are now connected as new user sb.
> UPDATE 1
> UPDATE 1
> You are now connected as new user roxadmin.
> You are now connected as new user sb.
> UPDATE 1
> UPDATE 1
> You are now connected as new user roxadmin.
> You are now connected as new user sb.
> UPDATE 1
> UPDATE 1
> You are now connected as new user roxadmin.
> You are now connected as new user sb.
> UPDATE 1
> UPDATE 1
> You are now connected as new user roxadmin.
> You are now connected as new user sb.
> UPDATE 1
> UPDATE 1
> You are now connected as new user roxadmin.
> You are now connected as new user sb.
> UPDATE 1
> UPDATE 1
> You are now connected as new user roxadmin.
> You are now connected as new user sb.
> UPDATE 1
> UPDATE 1
> You are now connected as new user roxadmin.
> You are now connected as new user sb.
> UPDATE 1
> UPDATE 1
> You are now connected as new user roxadmin.
> You are now connected as new user sb.
> UPDATE 1
> UPDATE 1
> You are now connected as new user roxadmin.
> You are now connected as new user sb.
> UPDATE 1
> UPDATE 1
> You are now connected as new user roxadmin.
> You are now connected as new user sb.
> UPDATE 1
> UPDATE 1
> You are now connected as new user roxadmin.
> You are now connected as new user sb.
> UPDATE 1
>
>
>
>
> LOG FILE CONTENTS :
> DEBUG:  database system was shut down at 2001-12-17 16:06:21 PST
> DEBUG:  CheckPoint record at (0, 2207456)
> DEBUG:  Redo record at (0, 2207456); Undo record at (0, 0); Shutdown TRUE
> DEBUG:  NextTransactionId: 693; NextOid: 19675
> DEBUG:  database system is in production state
> ERROR:  copy: line 1, Cannot insert a duplicate key into unique index
> rox_permission_pkey
> FATAL 1:  Socket command type 0 unknown
> ERROR:  copy: line 1, Cannot insert a duplicate key into unique index
> rox_user_pkey
> pq_flush: send() failed: Broken pipe
> FATAL 1:  Socket command type 1 unknown
> pq_flush: send() failed: Broken pipe
> ERROR:  copy: line 1, Cannot insert a duplicate key into unique index
> rox_group_pkey
> pq_flush: send() failed: Broken pipe
> FATAL 1:  Socket command type 0 unknown
> pq_flush: send() failed: Broken pipe
> ERROR:  copy: line 1, Cannot insert a duplicate key into unique index
> rox_group_member_pkey
> pq_flush: send() failed: Broken pipe
> FATAL 1:  Socket command type \ unknown
> pq_flush: send() failed: Broken pipe
> ERROR:  copy: line 1, Cannot insert a duplicate key into unique index
> rox_parent_child_group_pkey
> pq_flush: send() failed: Broken pipe
> FATAL 1:  Socket command type \ unknown
> pq_flush: send() failed: Broken pipe
> ERROR:  copy: line 1, Cannot insert a duplicate key into unique index
> rox_group_graph_pkey
> FATAL 1:  Socket command type \ unknown
> ERROR:  copy: line 1, Cannot insert a duplicate key into unique index
> rox_granted_permission_pkey
> pq_flush: send() failed: Broken pipe
> FATAL 1:  Socket command type \ unknown
> pq_flush: send() failed: Broken pipe
> DEBUG:  copy: line 584, XLogWrite: new log file created - consider increasing
> WAL_FILES
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Check out Yahoo! Shopping and Yahoo! Auctions for all of
> your unique holiday gifts! Buy at http://shopping.yahoo.com
> or bid at http://auctions.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Andrew G. Hammond     mailto:drew@xyzzy.dhs.org
http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F
613-389-5481
5CD3 62B0 254B DEB1 86E0  8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me