Postgres Pro
Downloads
Home   >   mailing lists

Re: out of memory error with loading pg_dumpall - Mailing list pgsql-general

From Dara Olson
Subject Re: out of memory error with loading pg_dumpall
Date
Msg-id 3F9CB68F897846A5BCC4505BACB3FF92@GISWKSTN2
Whole thread Raw
In response to out of memory error with loading pg_dumpall  ("Dara Olson" <dolson@glifwc.org>)
Responses Re: out of memory error with loading pg_dumpall  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
Tree view
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
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

pgsql-general by date:

Previous
From: devrim@gunduz.org
Date:
Subject: Re: Cannot connect to 2nd cluster database remotely
Next
From: Tom Lane
Date:
Subject: Re: can not use the column after rename