Received: from mailhost2.dircon.co.uk (mailhost2.dircon.co.uk [194.112.32.66]) by hub.org (8.9.3/8.9.3) with ESMTP id GAA62885 for ; Thu, 2 Sep 1999 06:36:56 -0400 (EDT) (envelope-from tim@paneris.co.uk) Received: from eric6 (paneris.co.uk [194.112.47.112]) by mailhost2.dircon.co.uk (8.9.3/8.9.3) with SMTP id LAA82824; Thu, 2 Sep 1999 11:36:04 +0100 (BST) Message-Id: <3.0.3.32.19990902113617.00732f1c@popmail.dircon.co.uk> X-Sender: paneris@popmail.dircon.co.uk X-Mailer: QUALCOMM Windows Eudora Light Version 3.0.3 (32) Date: Thu, 02 Sep 1999 11:36:17 +0100 To: Herouth Maoz From: Tim Pizey Subject: Re: [SQL] Newbie dbadmin out of his league Cc: pgsql-sql@postgreSQL.org, bloomsbury_development@messageboards.paneris.org In-Reply-To: References: <7648.935788758@sss.pgh.pa.us> <3.0.3.32.19990827211232.006f4c40@popmail.dircon.co.uk> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" At 18:45 01/09/99 +0300, Herouth Maoz wrote: >At 00:19 +0300 on 28/08/1999, Tom Lane wrote: > > >> > Presumably I again have to take control of the key values, >> > drop the index >> > copy from tab delimited file containing hard ids >> > create id sequence >> > create index >> > modify id definition >> >> Yup, that's about what you need to do. You can leave the "DEFAULT" >> clause where it is, since it won't be invoked during a COPY that's >> supplying non-default values for the ID column. (A good thing too, >> since I don't think we support ALTER TABLE ADD DEFAULT...) > >Hmmm. If it were I, I would have tackled it in a slightly different way: > >COPY the data into a temporary table, that doesn't have the id numbers at >all. Thus you don't have to have a counter on the client side, that knows >the last id that's already on the table, etc, etc., and you also don't have >to transfer several extra bytes per row through the postgres port. > Does the volume of data really matter, I was assuming that it was the indexing that was taking the time. Many of my field sizes are generous and padded with spaces. >Then, when you have a temp table, you can add the values to the main table >with an > >INSERT INTO main_table (field1, field2, field3) >SELECT field1, field2, field3 >FROM temp_table; > >If you don't mention the field that carries the default in this INSERT >statement, it will invoke the default. Dropping the index may still be a >good idea. The temp table shouldn't have an index anyways. > >This would save you at least the three last steps in your "recipe". > Thanks a lot for this, I will try it next. At the moment the job is going much faster, but is getting stuck on the index creation. I am turning off fsync (-o -F) during the copy, which copies all 2000000 records in one COPY. Should I keep it off during the index creation? It looks increasingly as though this process is going to take an un acceptable amount of time and resources on a live server. What is the recommended way of creating a db on another machine and then hot swapping the live and the new dbs? yours Tim Pizey Happy to take part:/ /www.paneris.co.uk/