Обсуждение: large dumps won't restore

Поиск
Список
Период
Сортировка

large dumps won't restore

От
Christian Fowler
Дата:
I'm running a large database ( data dir is ~9gigs ) on Fedora Core 2 with
7.4.5-PGDG rpm's

When using the -Fc dump method + pg_restore, I get:

-bash-2.05b$ pg_restore -Fc -d foo -L backup.list
/tmp/02\:43-postgresql_database-foo-backup
pg_restore: ERROR:  invalid input syntax for type timestamp: "52.233334"
CONTEXT:  COPY foo_data, line 42529, column mod_date: "52.233334"
pg_restore: [archiver (db)] error returned by PQendcopy


When using pg_dump + psql:

I get a similar error. In one table that has
about 5.4 million rows, the dump has several incomplete rows in the large
block of copy data. It seems to attempt to fill the copy with data from
the *next* line.

ERROR:  invalid input syntax for type timestamp: "4"
CONTEXT:  COPY foo_data, line 169371, column mod_date: "4"

going to 169371 lines after the start of the copy, indeed there is a short
row (by two), and two fields in on the *next* line is indeed a "4"


Both seem to have the same problem - incomplete COPY row data. any ideas?



[ \ /
[ >X<   Christian Fowler      | spider@viovio.com
[ / \   http://www.viovio.com | http://www.tikipro.org

Re: large dumps won't restore

От
"Joshua D. Drake"
Дата:
> When using pg_dump + psql:
>
> I get a similar error. In one table that has about 5.4 million rows,
> the dump has several incomplete rows in the large block of copy data.
> It seems to attempt to fill the copy with data from the *next* line.
>
> ERROR:  invalid input syntax for type timestamp: "4"
> CONTEXT:  COPY foo_data, line 169371, column mod_date: "4"
>
> going to 169371 lines after the start of the copy, indeed there is a
> short
> row (by two), and two fields in on the *next* line is indeed a "4"
>
>
> Both seem to have the same problem - incomplete COPY row data. any ideas?
>
Well you aren't going to like this but I see two solutions:

1. Cut out the rows listed and reimport after the copy completes.
2. Perform the pg_dump as inserts which will make the restore very slow
but should complete correctly.

Is the dump and restore happening on the same platform?

Sincerely,

Joshua D. Drake



>
>
> [ \ /
> [ >X<   Christian Fowler      | spider@viovio.com
> [ / \   http://www.viovio.com | http://www.tikipro.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings



--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL


Re: large dumps won't restore

От
Weiping
Дата:
Christian Fowler wrote:

>
> I'm running a large database ( data dir is ~9gigs ) on Fedora Core 2
> with 7.4.5-PGDG rpm's
>
> When using the -Fc dump method + pg_restore, I get:
>
> -bash-2.05b$ pg_restore -Fc -d foo -L backup.list
> /tmp/02\:43-postgresql_database-foo-backup
> pg_restore: ERROR:  invalid input syntax for type timestamp: "52.233334"
> CONTEXT:  COPY foo_data, line 42529, column mod_date: "52.233334"
> pg_restore: [archiver (db)] error returned by PQendcopy
>
what's the column type before the  column "mod_date"?
is it's a text type? if so, then have you used any kind of encoding for
your database?
what's it? and what's your PGCLIENCODING setting?

seems like a encoding problem I've ever met, but not sure.

regards

Laser

Re: large dumps won't restore

От
Christian Fowler
Дата:
Joshua -
well, i ran a --inserts dump as you suggested, and it died upteen millions
(and many hours) in at:

INSERT 59235383 1
invalid command \033',
cannot allocate memory for output buffer

Weiping,

db=# \encoding
UNICODE

the last four columns in the table are:

  full_name_nd  | character varying(200)      |
  mod_date      | timestamp without time zone |
  pc_char       | character varying(4)        |
  dim_char      | character varying(16)       |

[root@host root]# env |grep LANG
LANG=en_US.UTF-8


On Wed, 20 Oct 2004, Weiping wrote:

> Christian Fowler wrote:
>
>>
>> I'm running a large database ( data dir is ~9gigs ) on Fedora Core 2 with
>> 7.4.5-PGDG rpm's
>>
>> When using the -Fc dump method + pg_restore, I get:
>>
>> -bash-2.05b$ pg_restore -Fc -d foo -L backup.list
>> /tmp/02\:43-postgresql_database-foo-backup
>> pg_restore: ERROR:  invalid input syntax for type timestamp: "52.233334"
>> CONTEXT:  COPY foo_data, line 42529, column mod_date: "52.233334"
>> pg_restore: [archiver (db)] error returned by PQendcopy
>>
> what's the column type before the  column "mod_date"?
> is it's a text type? if so, then have you used any kind of encoding for your
> database?
> what's it? and what's your PGCLIENCODING setting?
>
> seems like a encoding problem I've ever met, but not sure.
>
> regards
>
> Laser
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>   (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

[ \ /
[ >X<   Christian Fowler      | spider AT viovio.com
[ / \   http://www.viovio.com | http://www.tikipro.org

Re: large dumps won't restore

От
Weiping
Дата:
Christian Fowler wrote:

>
> db=# \encoding
> UNICODE
>
> the last four columns in the table are:
>
>  full_name_nd  | character varying(200)      |
>  mod_date      | timestamp without time zone |
>  pc_char       | character varying(4)        |
>  dim_char      | character varying(16)       |
>
> [root@host root]# env |grep LANG
> LANG=en_US.UTF-8
>
emm. guess you don't have PGCLIENTENCODING setting,
what's the contend of full_name_nd? could it contain some
multibyte character?

try:

export PGCLIENTENCODING=UNICODE

then reload the dump file, see if the error happens again?
I guess there are some problematic character in full_name_nd field.
you may use 'less' or 'more' command to check line 169371, see
if it contain some weird chars.

regards

Laser


unsubscribe

От
"Nikhil Parva"
Дата:
unbsubscribe