After 3 days of running an import routine written on RoR to move some 269,000 records from a legacy database via csv files the routine finally finished. The very first thing I attempt to do is to backup the database using pg_dump before doing anything else.
This resulted in the following
pg_dump -Ft hsb > hsb_after_full_import.tar
pg_dump: Dumping the contents of table "companies" failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR: invalid memory alloc request size 18446744073709551613
pg_dump: The command was: COPY public.companies (id, category_id, city_id, name, address, town, neighbourhood, post_code, lon, lat, contact, email, facebook, twitter, listing_url, image_url, phone, website, listing_cid, promotion, description, rating, reviews, wheelchair_accessible, merchant_verified, uploaded, created_at, updated_at, server_id, opening_hours, multinational, places_id, featured_image_file_name, featured_image_content_type, featured_image_file_size, featured_image_updated_at, places_lat, places_lon, source, sanitised) TO stdout;
I am unable to find a tool to verify and fix any potential corruption. Indeed I have encountered many suggestions that Postgresql doesn't get corrupted (I find that impossible to believe) and therefore needs no repair/verification tools.
The import went smoothly and many tests of a few thousand records were undertaken before the full run to determine the suitability of the import script.
There is no point re-creating the database and re-importing if Postgresql is unable to handle this amount of data and just corrupt again.
I would really appreciate some guidance in the best way to proceed? Maybe an alternative backup tool?
The data appears to be OK. The app that is using the data seems to behave itself properly, maybe it's just the pure size of the dump and maybe an issue with the amount of ram (4gb) I have on my machine?
PostgreSQL version
pg_config --version
PostgreSQL 9.3.12
The database was created using Rails db rake task
rake db:create
From PGAdmin III the create script looks like this
-- Table: companies
-- DROP TABLE companies;
CREATE TABLE companies
(
id serial NOT NULL,
category_id integer,
city_id integer,
name character varying(255),
address character varying(255),
town character varying(255),
neighbourhood character varying(255),
post_code character varying(255),
lon character varying(255),
lat character varying(255),
contact character varying(255),
email character varying(255),
facebook text,
twitter text,
listing_url character varying(255),
image_url character varying(255),
phone character varying(255),
website text,
listing_cid character varying(255),
promotion text,
description text,
rating character varying(255),
reviews integer,
wheelchair_accessible boolean NOT NULL DEFAULT true,
merchant_verified boolean NOT NULL DEFAULT true,
uploaded boolean NOT NULL DEFAULT true,
created_at timestamp without time zone NOT NULL,
updated_at timestamp without time zone NOT NULL,
server_id integer,
opening_hours text,
multinational boolean DEFAULT false,
places_id character varying(255),
featured_image_file_name character varying(255),
featured_image_content_type character varying(255),
featured_image_file_size integer,
featured_image_updated_at timestamp without time zone,
places_lat double precision,
places_lon double precision,
source character varying(255),
sanitised boolean DEFAULT false,
CONSTRAINT companies_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE companies
OWNER TO wpsmiler;
I'm running Linux mint 17.3
select pg_total_relation_size('companies') gives me 113459200
If I run the query manually I get some records output (I assume 100 because of the message) then the same error
Query returned more than 100 copy rows, discarding the rest...
ERROR: invalid memory alloc request size 18446744073709551613
********** Error **********
ERROR: invalid memory alloc request size 18446744073709551613
SQL state: XX000
