From: Benjamin Krajmalnik <kraj@illumen.com>
To: Ivo Rossacher <rossacher@bluewin.ch>
To: pgsql-admin@postgresql.org
Subject: Re: Restoring dtabase problem
Date: Fri, 4 Aug 2006 13:48:54 -0600
Message-ID: <BF337097BDD9D849A2F4B818DDB2798725258C@stash.stackdump.local> (raw)
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.
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [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
>
> 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
> > UTF-8 encoding.
> > All of these rows are in a specific table.
> > Is there something I can run against the table which will
> validate the
> > rows which have the bad data and delete the rows?
>
> Typically this issue is related to a low number of invalid
> characters comming up a lot of time in the file. So I would
> replace the invalid codes with the correct unicode character
> by a text editor (take care to choose the correct encoding in
> the editor). In the context of the rest of the text it
> becomes mostly clear what the correct unicode character should be.
>
> >
> > The database is quite large (8GB) and I was informed that
> it would be
> > much faster to restore using the COPY method as opposed to
> the INSERT
> > method.
>
> This is correct, but has to be done when dumping (see man
> pg_dump -> options -d -D). 8GB, well this will take quite
> some time with inserts ...
>
> Regards
> Ivo
>
> > Any and all assistance will be deeply appreciated.
> >
> >
> > Regards,
> >
> > Benjamin
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org
> so that your
> message can get through to the mailing list cleanly
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: kraj@illumen.com, rossacher@bluewin.ch
Subject: Re: Restoring dtabase problem
In-Reply-To: <BF337097BDD9D849A2F4B818DDB2798725258C@stash.stackdump.local>
References: <BF337097BDD9D849A2F4B818DDB2798725258C@stash.stackdump.local>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox