PostGreSQL may be on the cards again.

Post #241,074 by static 1/11/06 10:10:00 PM Reply	PostGreSQL may be on the cards again. It depends on the state of PgCluster. I've decided I Am Going To Find Time To Test This. Clustering is a Requirement on The Checklist, don-cha-know. Currently, PostGreSQL is being glacial at importing my data. :-/ But I shall perservere. Wade. "Insert crowbar. Apply force."
Post #241,086 by admin 1/11/06 10:44:37 PM Reply	For faster COPYs Turn off all indexes, triggers, and constraints on the table. They will all slow down the import. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #241,096 by static 1/11/06 11:36:35 PM Reply	Been trying to figure out how to do that. Now that I've got a whole table in, I can look at pg_dump's output and see how it does it. Ah... that's how to use COPY... I think I'll be writing my own dump tool to do the data migration. Wade. "Insert crowbar. Apply force."
Post #241,090 by broomberg 1/11/06 10:59:58 PM Reply	Copy is slow? From the same box - in direct backend mode? I found it pretty damn fast - about 1/2 as slow as Oracle's SQL/Loader in direct path mode, and Oracle was pushing hardware limits. Or are you doing a copy from the client, which is pretty bad.
Post #241,093 by static 1/11/06 11:24:58 PM Reply	Well, I'm doing a psql < filename.sql My only reference point is MySQL, remember. And I'm still wrangling with data conversion... I've made it quite a bit faster by turning off fsync and giving it more memory (raised work_mem from 1000 to 4096). Wade. "Insert crowbar. Apply force."
Post #241,095 by broomberg 1/11/06 11:32:05 PM Reply	Explain the copy command you are using Show me exactly what you are doing. I would hope you are staying away from CSV and using tab delimited data. And study this page: [link\|http://www.postgresql.org/docs/8.1/interactive/sql-copy.html\|http://www.postgresq...ive/sql-copy.html] Note this text: Do not confuse COPY with the psql instruction \\copy. \\copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \\copy is used.
Post #241,097 by static 1/11/06 11:38:57 PM Reply	I wasn't using COPY at all. I was using INSERT. After all, that's the portable format, isn't it? :-) Besides, that's what MySQL uses for dump-n-restore. I'll be building a migration tool, I think. Wade. "Insert crowbar. Apply force."
Post #241,098 by broomberg 1/11/06 11:50:35 PM Reply	Huh? It provides an excellant tool for very quick data migration. And you whine about the speed of not using it. Makes no sense.
Post #241,100 by static 1/12/06 12:02:20 AM Reply	I didn't know about it. :-) ... until the last few hours. I discovered it literally minutes after the initial post about how slow the import was. Wade. "Insert crowbar. Apply force."
Post #241,101 by broomberg 1/12/06 12:22:59 AM Reply	Oh, OK Learn it. Use it. Let me know if you neeed any help in working with it. The key issue is let the Postgres backend see the files, do not load through the network.

Welcome to IWETHEY!