Resetting connection errors and incomplete restores.
Resetting connection errors and incomplete restores.
От:
Sanjay Bhatia <sbix@yahoo.com>
Дата:
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
Re: Resetting connection errors and incomplete restores.
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