Below is what the beginning of the log looks like. There area a total of 21,733
lines of errors. Please let me know if I should provide the complete error log
file.
2011-12-20 12:10:58 CST LOG: database system was shut down at 2011-12-20
12:10:56 CST
2011-12-20 12:10:58 CST LOG: database system is ready to accept connections
2011-12-20 12:10:58 CST LOG: autovacuum launcher started
I believe this is where I would have ran psql...
2011-12-20 12:33:48 CST ERROR: role "postgres" already exists
2011-12-20 12:33:48 CST STATEMENT: CREATE ROLE postgres;
2011-12-20 12:33:48 CST ERROR: database "postgis" already exists
2011-12-20 12:33:48 CST STATEMENT: CREATE DATABASE postgis WITH TEMPLATE =
template0 OWNER = postgres;
2011-12-20 12:33:48 CST ERROR: database "template_postgis" already exists
2011-12-20 12:33:48 CST STATEMENT: CREATE DATABASE template_postgis WITH
TEMPLATE = template0 OWNER = postgres;
2011-12-20 12:33:51 CST ERROR: language "plpgsql" already exists
2011-12-20 12:33:51 CST STATEMENT: CREATE PROCEDURAL LANGUAGE plpgsql;
2011-12-20 12:33:51 CST ERROR: type "box2d" already exists
2011-12-20 12:33:51 CST STATEMENT: CREATE TYPE box2d;
2011-12-20 12:33:51 CST ERROR: function "st_box2d_in" already exists with
same argument types
And here is a summary of more of the errors in the beginning of the log... it
goes through each function with errors that the function already exists
(similar to above) and then the same errors that the aggregate already exists,
2011-12-20 12:33:56 CST STATEMENT: CREATE AGGREGATE st_union(geometry) (
SFUNC = pgis_geometry_accum_transfn,
STYPE = pgis_abs,
FINALFUNC = pgis_geometry_union_finalfn
);
2011-12-20 12:33:56 CST ERROR: operator && already exists
...then these two tables already exist...
2011-12-20 12:34:03 CST ERROR: relation "geometry_columns" already exists
2011-12-20 12:34:04 CST ERROR: relation "spatial_ref_sys" already exists
...then a bunch of checkpoint errors...
2011-12-20 12:34:11 CST LOG: checkpoints are occurring too frequently (22
seconds apart)
2011-12-20 12:34:11 CST HINT: Consider increasing the configuration
parameter "checkpoint_segments".
2011-12-20 12:34:18 CST LOG: checkpoints are occurring too frequently (7
seconds apart)
2011-12-20 12:34:18 CST HINT: Consider increasing the configuration
parameter "checkpoint_segments".
...
2011-12-20 12:44:16 CST ERROR: duplicate key value violates unique
constraint "spatial_ref_sys_pkey"
2011-12-20 12:44:16 CST CONTEXT: COPY spatial_ref_sys, line 1: "3819 EPSG
3819 GEOGCS["HD1909",DATUM["Hungarian_Datum_1909",SPHEROID["Bessel
1841",6377397.155,299.1..."
2011-12-20 12:44:16 CST STATEMENT: COPY spatial_ref_sys (srid, auth_name,
auth_srid, srtext, proj4text) FROM stdin;
2011-12-20 12:44:22 CST LOG: checkpoints are occurring too frequently (7
seconds apart)
2011-12-20 12:44:22 CST HINT: Consider increasing the configuration
parameter "checkpoint_segments".
2011-12-20 12:44:29 CST LOG: checkpoints are occurring too frequently (7
seconds apart)
...and then more checkpoint_segment errors and then...
2011-12-20 12:45:55 CST ERROR: canceling autovacuum task
2011-12-20 12:45:55 CST CONTEXT: automatic analyze of table
"postgis.hydrography.rivers_mn"
2011-12-20 12:45:57 CST ERROR: canceling autovacuum task
2011-12-20 12:45:57 CST CONTEXT: automatic analyze of table
"postgis.hydrography.rivers_wi"
2011-12-20 12:45:59 CST ERROR: canceling autovacuum task
2011-12-20 12:45:59 CST CONTEXT: automatic analyze of table
"postgis.hydrography.wi_potentially_restorable_wetlands"
2011-12-20 12:46:00 CST ERROR: canceling autovacuum task
2011-12-20 12:46:00 CST CONTEXT: automatic analyze of table
"postgis.hydrography.wi_roi_areas"
2011-12-20 12:46:01 CST ERROR: multiple primary keys for table
"geometry_columns" are not allowed
2011-12-20 12:46:01 CST STATEMENT: ALTER TABLE ONLY geometry_columns
ADD CONSTRAINT geometry_columns_pk PRIMARY KEY (f_table_catalog,
f_table_schema, f_table_name, f_geometry_column);
2011-12-20 12:46:01 CST ERROR: multiple primary keys for table
"spatial_ref_sys" are not allowed
2011-12-20 12:46:01 CST STATEMENT: ALTER TABLE ONLY spatial_ref_sys
ADD CONSTRAINT spatial_ref_sys_pkey PRIMARY KEY (srid);
This is the first 1/3 of the errors, so hopefully this will help diagnose where
my problem may be. Any help would be greatly appreciated.
Thank you in advance.
Dara
----- Original Message -----
From: Tom Lane
To: Dara Olson
Cc: pgsql-general@postgresql.org
Sent: Tuesday, December 20, 2011 7:16 PM
Subject: Re: [GENERAL] out of memory error with loading pg_dumpall
"Dara Olson" <dol...@glifwc.org> writes:
> I am attempting to create an exact copy of our production database/cluster
on a different server for development. I created a dumpall file which is
8.7GB. When I attempt to run this in psql on the new server it seems okay and
then I got a string of "invalid command \N" lines" and then "out of memory" in
the command prompt and then in the postgres log it states at the end,
> CST LOG: could not receive data from client: Unknown winsock error 10061
> CST LOG: unexpected EOF on client connection
I'd suggest you need to look at the *first* message not the last one.
What it sounds like is that psql is failing on some line of COPY data
and then trying to interpret the rest of the data as SQL commands.
Why that's happening is likely to be revealed by the first few messages.
regards, tom lane
