X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 9230C9FB248 for ; Fri, 4 Aug 2006 16:49:06 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 41294-09 for ; Fri, 4 Aug 2006 16:48:55 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from stash.illumen.com (unknown [64.207.29.137]) by postgresql.org (Postfix) with ESMTP id 9949D9FB1D2 for ; Fri, 4 Aug 2006 16:48:55 -0300 (ADT) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Subject: Re: Restoring dtabase problem X-MimeOLE: Produced By Microsoft Exchange V6.5 Date: Fri, 4 Aug 2006 13:48:54 -0600 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] Restoring dtabase problem Thread-Index: Aca3/WZHrDRYwrubQ5K8GCbWyKmGXQAAB4/A From: "Benjamin Krajmalnik" To: "Ivo Rossacher" , X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.041 tagged_above=0 required=5 tests=AWL, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/48 X-Sequence-Number: 22592 The problem is that I do not which rows have the problem. Out of a few thousand rows, there are only about 15 rows with the problem. I was wondering if there is some validation which I can run as a script against the table so it wil delete it. How much faster is the copy method as opposed to the insert? The only other idea which I had was to do a pg_dump of the schema, and then individual pg_dumps for each table. That way, I can use the copy method for all of the tables other than the particular table. I looked for an option to exclude a table from the dump, but did not find such an option. I am going to try a long shot right now since I am going from Windows to FreeBSD. I stopped Postgres on the Windows machine and tar'ed the data directory. Now I am going to move it to the FreeBSD box and see what happens. Probably won't work, but what the heck! As an alternative oprion, I may do a table dump of the table with the problems in insert mode, turn on logging, and see if I can identify the culprit rows that way and then delete them. =20 > -----Original Message----- > From: pgsql-admin-owner@postgresql.org=20 > [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Ivo Rossacher > Sent: Friday, August 04, 2006 1:36 PM > To: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Restoring dtabase problem >=20 > Am Freitag, 4. August 2006 19.06 schrieb Benjamin Krajmalnik: > > I am trying to restore a database from a pg_dump. > > I have a problematic issue in that a few rows have a problem with=20 > > UTF-8 encoding. > > All of these rows are in a specific table. > > Is there something I can run against the table which will=20 > validate the=20 > > rows which have the bad data and delete the rows? >=20 > Typically this issue is related to a low number of invalid=20 > characters comming up a lot of time in the file. So I would=20 > replace the invalid codes with the correct unicode character=20 > by a text editor (take care to choose the correct encoding in=20 > the editor). In the context of the rest of the text it=20 > becomes mostly clear what the correct unicode character should be. >=20 > > > > The database is quite large (8GB) and I was informed that=20 > it would be=20 > > much faster to restore using the COPY method as opposed to=20 > the INSERT=20 > > method. >=20 > This is correct, but has to be done when dumping (see man=20 > pg_dump -> options -d -D). 8GB, well this will take quite=20 > some time with inserts ... >=20 > Regards > Ivo >=20 > > Any and all assistance will be deeply appreciated. > > > > > > Regards, > > > > Benjamin >=20 > ---------------------------(end of=20 > broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org=20 > so that your > message can get through to the mailing list cleanly >=20