Re: out of memory error with loading pg_dumpall

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

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

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

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

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)

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);

----- 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" <dolson@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