|
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 -----
Sent: Tuesday, December 20, 2011 7:16
PM
Subject: Re: out of memory
error with loading pg_dumpall
"Dara Olson" <dolson@xxxxxxxxxx> 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
| 