Skip site navigation (1) Skip section navigation (2)

Newbie dbadmin out of his league

From: Tim Pizey <tim(at)paneris(dot)co(dot)uk>
To: pgsql-sql(at)postgreSQL(dot)org
Cc: bloomsbury_development(at)messageboards(dot)paneris(dot)org
Subject: Newbie dbadmin out of his league
Date: 1999-08-27 20:12:32
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
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. 

Tim Pizey

pgsql-sql by date

Next:From: Tom LaneDate: 1999-08-27 21:19:18
Subject: Re: [SQL] Newbie dbadmin out of his league
Previous:From: Frank JoerdensDate: 1999-08-27 17:56:52
Subject: Re: [SQL] vacuum fails

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group