I'm attempting to dump and restore an 8.2.4 database to another (same
architecture) machine also running 8.2.4 with a freshly initialized
database.
I'm using this script to generate the pg_dump file:
#!/bin/sh
DATE=`date +%Y%m%d%H%M%S`
#dump the live wykids database
/usr/local/bin/pg_dumpall -c -h localhost > \
/home/_postgresql/wykids$DATE.sql
#slony
/usr/local/bin/dropdb -h slony.internal wykids
#recreate the development wykids database from the dump file we just made
#first we connect to template1 and set template0 to accept connections
/usr/local/bin/psql -U _postgresql -h slony.internal -t -c \
"update pg_database set datallowconn = 't' where datname = 'template0';"
template1
#template0 is stock database--no additions whatsoever
/usr/local/bin/psql -h slony.internal template0 -f \
/home/_postgresql/wykids$DATE.sql
#now we connect to template1 again and set template0 to not accept
connections
/usr/local/bin/psql -U _postgresql -h slony.internal -t -c \
"update pg_database set datallowconn = 'f' where datname = 'template0';"
template1
#vacuum analyze
/usr/local/bin/psql -U _postgresql -h slony.internal -t -c \
"vacuum analyze;" wykids
When restoring to slony, psql begins spewing errors and eventually stops.
I've narrowed the problem table to a specific table and tried doing a
pg_dump on just that table. Using psql to load that one table gives me
the same error, which comes at the last line of the create table
statement. Here is the first part of the dump file:
File:
/tmp/people.sql
--
-- PostgreSQL database dump
--
SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = true;
--
-- Name: people; Type: TABLE; Schema: public; Owner: _postgresql;
Tablespace:
--
CREATE TABLE people (
pp_id integer NOT NULL,
pp_stars_id integer,
pp_mentor_id numeric,
pp_trainer_id numeric,
pp_director_id numeric,
pp_apprentice_id numeric,
pp_first_name character varying(255) NOT NULL,
pp_last_name character varying(255) NOT NULL,
pp_address character varying(255),
pp_city character varying(50),
pp_state character varying(3),
pp_zip character varying(10),
pp_county character varying(255),
pp_home_phone character varying(10),
pp_work_phone character varying(10),
pp_work_phone_extension character varying(6),
pp_cell_phone character varying(10),
pp_fax character varying(10),
pp_dob date,
pp_gender character varying(20),
pp_race_native_american boolean,
pp_race_hispanic boolean,
pp_race_african_american boolean,
pp_race_asian boolean,
pp_race_caucasian boolean,
pp_race_pacific_islander boolean,
pp_email character varying(60),
pp_setup_date date DEFAULT ('now'::text)::timestamp(6) with time zone,
pp_last_updated_date date DEFAULT ('now'::text)::timestamp(6) with
time zone,
pp_education_level character varying(255),
pp_associates character varying(255),
pp_bachelors character varying(255),
pp_cda character varying(255),
pp_masters character varying(255),
pp_doctorate character varying(255),
pp_certifications text,
pp_prof_memberships text,
pp_job_title character varying(255),
pp_employer character varying(255),
pp_hourly_wage numeric(5,2),
pp_username character varying(25),
pp_password character varying(25),
pp_password_question character varying(255),
pp_password_answer character varying(255),
pp_notes text,
pp_last_updated_by character varying(50) DEFAULT "current_user"()
NOT NULL,
pp_provisional_p boolean DEFAULT false NOT NULL,
pp_ethnicity character varying(25),
pp_race_other_p boolean,
pp_race_other character varying(255),
CONSTRAINT pp_cell_phone_ck CHECK (((pp_cell_phone IS NULL) OR
((pp_cell_phone)::text ~
similar_escape('[2-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'::text,
NULL::text)))),
CONSTRAINT pp_fax_ck CHECK (((pp_fax IS NULL) OR ((pp_fax)::text ~
similar_escape('[2-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'::text,
NULL::text)))),
CONSTRAINT pp_home_phone_ck CHECK (((pp_home_phone IS NULL) OR
((pp_home_phone)::text ~
similar_escape('[2-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'::text,
NULL::text)))),
CONSTRAINT pp_work_phone_ck CHECK (((pp_work_phone IS NULL) OR
((pp_work_phone)::text ~
similar_escape('[2-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'::text,
NULL::text)))),
CONSTRAINT pp_work_phone_extension_ck CHECK
(((pp_work_phone_extension IS NULL) OR ((pp_work_phone_extension)::text
~ similar_escape('[0-9]{1,6}'::text, NULL::text)))),
CONSTRAINT pp_wyoming_county_required_ck CHECK ((((pp_state)::text
<> 'WY'::text) OR (pp_county IS NOT NULL))),
CONSTRAINT pp_zip_ck CHECK ((((pp_zip)::text ~
similar_escape('[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'::text,
NULL::text)) OR ((pp_zip)::text ~
similar_escape('[0-9][0-9][0-9][0-9][0-9]'::text, NULL::text))))
);
When psql begins loading this file, it throws the error:
psql:/tmp/people.sql:79: ERROR: unrecognized node type: 655
psql:/tmp/people.sql:82: ERROR: relation "public.people" does not exist
psql:/tmp/people.sql:92: ERROR: relation "people_pp_id_seq" already exists
Line 79 of the file is the closing ");" at the end of the CREATE TABLE
statement.
The interesting thing is that this exact process works just fine to a
third machine also running 8.2.4--my entire pg_dumpall script runs
without a single error. The only difference that I can see is that this
third machine did not start with a freshly initialized database.
Thanks for any insights.
Jeff Ross