Hi guys,
Problems ...
OS = RedHat Linux 7.2 (kernel 2.4.9-21)
Build options: --prefix=/home/postgres --with-perl --with-tcl --with-python
Postgres Version 7.2 (Final) for loading data, 7.1.3 for dumping data
I have a database with a few smaller tables and one large one (23
Million records). I tried to dump and load, using pg_dump -fc ... but
I never got pg_restore to load the dump file back - I didn't try hard
enough, I know but ...
I then wrote a script which copies all the data into flat files (copy
xxx to '<file>';) and dumped the schema info separate (pg_dump -s).
With an editor, I split the schema file into two, one to define the
tables, one to define the indexes, I don't want to load data with the
indexes active, it'll take for ever.
Dumping the big table was messy, because Postgres always complained
that the file is too large. I'm running this on RedHat 7.2 which can
create files as big as the disk. and yes, I have ulimit set to
unlimited. Eventually I got it done by using "copy phonelog to
stdout;" and redirected the output into the file I wanted. I then
went ahead and split the file into three parts, using "split
-10000000" and now got three ~ 1GB files called xaa xab and xac.
I set up the new environment (7.2), did an initdb, created the users
and empty databases and loaded the first part of the schema info.
Then I proceeded to load the raw data using "copy". All went well!
I then went ahead to create the indexes and this is where the fun begins:
psql:/tmp/phones.schema.2:136: ERROR: tuplesort: unexpected end of data
CREATE
CREATE
CREATE
psql:/tmp/phones.schema.2:168: pqReadData() -- backend closed the
channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
psql:/tmp/phones.schema.2:168: connection to server was lost
Line 136 reads:
CREATE INDEX "i_pl_date_line" on "phonelog" using btree ( "entity"
"varchar_ops", "cdate" "date_ops", "frline" "int4_ops" );
Nothing wrong with this, right?
I then went, and said, ok, try again, of course, there are some
errors for indexes which are already there - ignore those:
psql:/tmp/phones.schema.2:128: ERROR: Cannot create index:
'i_pc_countrycode' already exists
psql:/tmp/phones.schema.2:136: ERROR: Relation 0 does not exist
psql:/tmp/phones.schema.2:144: ERROR: Cannot create index:
'i_pl_entity_date' already exists
psql:/tmp/phones.schema.2:152: ERROR: Cannot create index:
'i_pl_loadtimestamp' already exists
psql:/tmp/phones.schema.2:160: ERROR: Cannot create index:
'i_pl_prefix' already exists
psql:/tmp/phones.schema.2:168: pqReadData() -- backend closed the
channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
psql:/tmp/phones.schema.2:168: connection to server was lost
At this point, I don't know any further. As this is my production db,
I will revert back to 7.1.3 for the time being. Any input is welcome.
Best regards,
Chris
--
Chris Ruprecht
Network grunt and bit pusher extraordinaíre
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
