From: | Guy Fraser <guy(at)incentre(dot)net> |
---|---|
To: | Janne Blomqvist <janne(dot)blomqvist(at)hansel(dot)fi> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: VS: Backup/dump of huge tables and performance |
Date: | 2000-08-03 03:44:49 |
Message-ID: | 3988EAB1.4C3686C3@incentre.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi
I have been using this method for over a year now.
NOTE: Beware of "\" (escape) characters.
If you have to use them, they must be changed to "\\".
This is a sample of something similar to what I do:
/bin/cat tab-delimited.txt | /usr/bin/psql -c "copy table1 from stdin;"
database1
The "/bin/cat tab-delimited.txt" represents a program that generates the
required
tab delimited text.
Example file.
tab-delimited.txt => 7713857 bytes 69423 lines
Example table.
Table = table1
+----------------------------------+----------------------------------+-------+
| Field | Type |
Length|
+----------------------------------+----------------------------------+-------+
| Time | abstime
| 4 |
| Type | text
| var |
| Id | text
| var |
| Duration | int4
| 4 |
| Server | inet
| var |
| User | text
| var |
| Location | text
| var |
| Port | int4
| 4 |
| Service | text
| var |
| Remote | inet
| var |
| Caller | text
| var |
| Input | int4
| 4 |
| Output | int4
| 4 |
| State | text
| var |
| Delay | int4
| 4 |
+----------------------------------+----------------------------------+-------+
Resources required to import data :
21.82 seconds elapsed
10% CPU utilization
(183 major + 29 minor) page faults
0 swaps
Machine has 128 MB Total memory, 8.5 MB shared memory on a
Pentium Pro 200 with 256 KB L2 cache
and a 9 GB SEAGATE ST19101W Ultra Wide SCSI Drive
running postgresql-6.5.3.
This translates to approximately 3182 records per second,
averaging 111 bytes per record , using only 10% CPU.
From | Date | Subject | |
---|---|---|---|
Next Message | Denis Perchine | 2000-08-03 06:03:08 | Functional indices with const params. |
Previous Message | Thomas Swan | 2000-08-03 03:23:07 | Re: [HACKERS] random() function produces wrong range |