Обсуждение: out of memory error with loading pg_dumpall
Greetings.
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
CST LOG: unexpected EOF on client connection
I am running it on a Windows 2008 server with 8 GB Ram and dual 2GHz processors. I have the "postgres.conf" file set to 1GB of shared buffers. The production and new server are both running PostgreSQL 8.4 with PostGIS 1.4.
Am I going about this in the correct way? How can I debug to figure out what it happening? Can/should I just dump each database individually and drop and reload each database?
Any help would be greatly appreciated.
Dara
"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
createda 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
astring of "invalid command \N" lines" and then "out of memory" in the command prompt and then in the postgres log it
statesat 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
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 exists2011-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_columnsADD 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_sysADD 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 LaneTo: Dara OlsonSent: Tuesday, December 20, 2011 7:16 PMSubject: 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
"Dara Olson" <dolson@glifwc.org> writes:
> This is the first 1/3 of the errors, so hopefully this will help diagnose where my problem may be. Any help would be
greatlyappreciated.
Well, you didn't show us the error that caused a COPY to fail, but it's
pretty obvious that you're attempting to load the dump into a database
that's already populated. This suggests that the actual problem could
be something like a COPY command that matches the name but not the
column set of an existing table. I'd suggest dropping and recreating
the target database first.
regards, tom lane