I am creating a new database on a brand new server (P4, 1GB RAM,
postgres 7.3.3, debian 3.0) and trying to populate one of the tables
with the COPY command. I split a large file with 20 million records into
20 files, but when I run COPY I usually get the following message:
analytics=# COPY tbl555 FROM '/usr/local/pgsql/xaa' WITH NULL AS '';
ERROR: copy: line 167641, Query was cancelled.
The line number varies each time I run it, and occasionally succeeds. I
split up the data into even smaller files (100,000 rows) with the same
results. Then I tried it on one of our older debian boxes (version ?)
with postgres 7.3 and the COPY's succeed. I turned up logging on the new
server but the messages returned are:
Jul 16 09:04:43 imp postgres[31180]: [72-2] Free/Avail. Space 0/0;
EndEmpty/Avail. Pages 0/0.
Jul 16 09:04:43 imp postgres[31180]: [72-3] ^ICPU 0.00s/0.00u sec
elapsed 0.00 sec.
Jul 16 09:04:43 imp postgres[31180]: [73-1] DEBUG: Index
pg_toast_4070343_index: Pages 1; Tuples 0.
Jul 16 09:04:43 imp postgres[31180]: [73-2] ^ICPU 0.00s/0.00u sec
elapsed 0.00 sec.
Jul 16 09:04:43 imp postgres[31180]: [74] DEBUG: CommitTransactionCommand
Jul 16 09:04:57 imp postgres[31180]: [75] DEBUG: StartTransactionCommand
Jul 16 09:04:57 imp postgres[31180]: [76] DEBUG: ProcessUtility
analytics=# COPY tc555 FROM '/usr/local/pgsql/xaa' WITH NULL AS '';
DEBUG: StartTransactionCommand
DEBUG: ProcessUtility
ERROR: copy: line 218765, Query was cancelled.
DEBUG: AbortCurrentTransaction
ERROR: copy: line 218765, Query was cancelled.
I set the following values in postgresql.conf:
tcpip_socket = true
shared_buffers = 1000 # min max_connections*2 or 16, 8KB each
max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes
sort_mem = 32168 # min 64, size in KB
vacuum_mem = 64336 # min 1024, size in KB
fsync = false
effective_cache_size = 32768 # typically 8KB each
geqo_threshold = 25
server_min_messages = debug5 # Values, in order of decreasing detail:
syslog = 1 # range 0-2
LC_MESSAGES = 'C'
LC_MONETARY = 'C'
LC_NUMERIC = 'C'
LC_TIME = 'C'
If someone has any ideas what the problem may be, please let me know. I
am thinking that it may involve one of the following, but am not sure which:
- my configuration file changes
- ram
- driver issues - mother board = asus p4 p800
- driver issues - hard drive (IDE)
- ?
Thanks in advance
Ron