pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tim Pizey <tim@paneris.co.uk>
To: pgsql-sql@postgreSQL.org
Cc: bloomsbury_development@messageboards.paneris.org
Subject: Newbie dbadmin out of his league
Date: Fri, 27 Aug 1999 21:12:32 +0100
Message-ID: <3.0.3.32.19990827211232.006f4c40@popmail.dircon.co.uk> (raw)

Hi all, 

I am new to dbadmin, and have to load 2 million records into a db 
every month :(

So far we have found that a naive approach will result in the 
data taking longer to load than it is valid for. 

Initially we were using JDBC to insert or update the table. 
Now we are only using JDBC to insert new category records, such as when a
new format or country are encountered. 
The actual INSERT or UPDATE statements are written out to an SQL file. 

To create the SQL file is estimated at 30 hours, but to updating the 
database with:

psql -f output.sql db 

looks as though it will take forever as it has inserted 177778 records in
43 hours. 

I have just dropped a text index to help things along. 

So two questions: 

1. The primary key is an autoincrement field called id:
   
CREATE SEQUENCE parts_id_seq start 1 increment 1 maxvalue 2147483647
minvalue 1 cache 1 ;
SELECT nextval ( 'parts_id_seq' );
CREATE TABLE "parts" (
	"id" int4 DEFAULT nextval ( 'parts_id_seq' ) NOT NULL,
	"part_no" character(50) NOT NULL,
	"deleted" bool,
	"updatedate" timestamp
);


   The Insert statements do not give the id explicitly.
   Would I be better off dropping the auto-increment and then re-applying it?
   How do I do this?

2. How do I use COPY?

   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


I would be very grateful for any comments and any longer term help 
can be paid for. 

yours
Tim Pizey
http://www.paneris.co.uk/






view thread (6+ messages)  latest in thread

Message-ID: <3.0.3.32.19990827211232.006f4c40@popmail.dircon.co.uk>
Permalink:  ../3.0.3.32.19990827211232.006f4c40@popmail.dircon.co.uk/
Also on:    postgresql.org/message-id/3.0.3.32.19990827211232.006f4c40@popmail.dircon.co.uk

 · 

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-sql@postgresql.org
  Cc: tim@paneris.co.uk, bloomsbury_development@messageboards.paneris.org
  Subject: Re: Newbie dbadmin out of his league
  In-Reply-To: <3.0.3.32.19990827211232.006f4c40@popmail.dircon.co.uk>
  References: <3.0.3.32.19990827211232.006f4c40@popmail.dircon.co.uk>

* 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