pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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
> 



view thread (4+ messages)  latest in thread

Message-ID: <BF337097BDD9D849A2F4B818DDB2798725258C@stash.stackdump.local>
Permalink:  ../BF337097BDD9D849A2F4B818DDB2798725258C@stash.stackdump.local/
Also on:    postgresql.org/message-id/BF337097BDD9D849A2F4B818DDB2798725258C@stash.stackdump.local

 · 

reply

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