Re: VS: Backup/dump of huge tables and performance

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.

In response to

Browse pgsql-general by date

  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