I have two servers (Redhat) with identical databases.
I upgraded to one of them to 6.4.2 (RH6.0) and restored
the database on that machine from a pg_dump
created on the other (6.3: RH5.2) machine.
Now pg_dump does not function properly on the 6.4.2
machine. I've included listings of the original dump file: db.out.sav,
created on the 6.3 machine, and the new dump file: db.out,
created on the 6.4.2 machine below, along with the complete listing
of the db.out file. The pg_dump executables both behave similarly
on either machine (both don't work on the 6.4, do work on the 6.3.)
The database on the 6.4 machine works fine.
What am I doing wrong?
[postgres@pp1 pgsql]$ ./pg_dump cweblog > db.out
[postgres@pp1 pgsql]$ ls -al db*
-rw-r--r-- 1 postgres postgres 915 Jun 11 13:01 db.out
-rw-r--r-- 1 postgres postgres 3232228 Jun 4 17:05 db.out.sav
-rw-r--r-- 1 postgres postgres 945 Jun 11 12:33 db.out2
[postgres@pp1 pgsql]$ more db.out
\connect - cweblog
CREATE TABLE pga_queries (queryname varchar(64), querytype char(1), querycommand
text);
\connect - cweblog
CREATE TABLE pga_forms (formname varchar(64), formsource text);
\connect - cweblog
CREATE TABLE pga_scripts (scriptname varchar(64), scriptsource text);
\connect - cweblog
CREATE TABLE pga_reports (reportname varchar(64), reportsource text, reportbody
text, reportprocs text, reportoptions text);
\connect - cweblog
CREATE TABLE pga_layout (tablename varchar(64), nrcols int2, colnames text, colw
idth text);
COPY pga_queries FROM stdin;
\.
COPY pga_forms FROM stdin;
\.
COPY pga_scripts FROM stdin;
\.
COPY pga_reports FROM stdin;
\.
COPY pga_layout FROM stdin;
patient_info 10 anon_patient_id date_time_group student_id ms block hosp
ital clerkship setting age sex 150 150 150 150 150 150 150 150 150 150
problems_encountered 3 anon_patient_id problem_type problem_category
150 150 150
\.
[postgres@pp1 pgsql]$
____________________________________________
[postgres@pp1 pgsql]$ pg_dumpall
\connect template1
select datdba into table tmp_pg_shadow from pg_database where datname =
'template1';
delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba;
drop table tmp_pg_shadow;
copy pg_shadow from stdin;
bob 500 t t t t \N \N
cweblog 502 t t t t \N \N
\.
\connect template1
create database cweblog;
\connect cweblog
CREATE TABLE "pga_queries" (
"queryname" character varying(64),
"querytype" character,
"querycommand" text);
CREATE TABLE "pga_forms" (
"formname" character varying(64),
"formsource" text);
CREATE TABLE "pga_scripts" (
"scriptname" character varying(64),
"scriptsource" text);
CREATE TABLE "pga_reports" (
"reportname" character varying(64),
"reportsource" text,
"reportbody" text,
"reportprocs" text,
"reportoptions" text);
CREATE TABLE "pga_layout" (
"tablename" character varying(64),
"nrcols" int2,
"colnames" text,
"colwidth" text);
COPY "pga_queries" FROM stdin;
\.
COPY "pga_forms" FROM stdin;
\.
COPY "pga_scripts" FROM stdin;
\.
COPY "pga_reports" FROM stdin;
\.
COPY "pga_layout" FROM stdin;
patient_info 10 anon_patient_id date_time_group student_id ms block
hospital clerkship setting age sex 150 150 150 150 150 150 150 150 150 150
problems_encountered 3 anon_patient_id problem_type problem_category
150 150 150
\.
\connect template1 bob
create database bob;
\connect bob bob
CREATE SEQUENCE "patient_id_generator" start 1 increment 1 maxvalue 2147483647
minvalue 1 cache 1 ;
CREATE TABLE "patient_info" (
"anon_patient_id" int4 DEFAULT nextval ( 'patient_id_generator' ),
"date_time_group" timestamp NOT NULL,
"student_id" character(40),
"ms" character(10),
"block" character(10),
"hospital" character(40),
"clerkship" character(40),
"setting" character(10),
"age" int2,
"sex" character(10));
CREATE TABLE "problems_encountered" (
"anon_patient_id" int4 DEFAULT nextval ( 'patient_id_generator' ),
"date_time_group" timestamp NOT NULL,
"student_id" character(40),
"ms" character(10),
"block" character(10),
"hospital" character(40),
"clerkship" character(40),
"setting" character(10),
"age" int2,
"sex" character(10));
COPY "patient_info" FROM stdin;
\.
COPY "problems_encountered" FROM stdin;
\.
______________________________________
_________________________________________________________________________
Here is what the db.out.sav file looks like (in part):
\connect template1
select datdba into table tmp_pg_shadow from pg_database where datname =
'template1';
delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba;
drop table tmp_pg_shadow;
copy pg_shadow from stdin;
bob 500 t t t t \N \N
cweblog 502 t t t t \N \N
\.
\connect template1 cweblog
create database cweblog;
\connect cweblog cweblog
\connect - cweblog
CREATE SEQUENCE patient_id_generator start 5546 increment 1 maxvalue 2147483647
minvalue 1 cache 1 ;
SELECT nextval ('patient_id_generator');
\connect - cweblog
CREATE TABLE patient_info (anon_patient_id int4 DEFAULT nextval (
'patient_id_generator' ), date_time_group timestamp NOT NULL, student_id
char(40), ms char(40), block char(40), hospital char(40), clerkship char(40),
setting char(40), age float4, sex char(40));
\connect - cweblog
CREATE TABLE problems_encountered (anon_patient_id int4 DEFAULT nextval (
'patient_id_generator' ), problem_type char(40), problem_category char(40));
\connect - cweblog
CREATE TABLE pga_queries (queryname varchar(64), querytype char(1), querycommand
text);
REVOKE ALL on pga_queries from PUBLIC;
GRANT ALL on pga_queries to Public;
\connect - cweblog
CREATE TABLE pga_forms (formname varchar(64), formsource text);
REVOKE ALL on pga_forms from PUBLIC;
GRANT ALL on pga_forms to Public;
\connect - cweblog
CREATE TABLE pga_scripts (scriptname varchar(64), scriptsource text);
REVOKE ALL on pga_scripts from PUBLIC;
GRANT ALL on pga_scripts to Public;
\connect - cweblog
CREATE TABLE pga_reports (reportname varchar(64), reportsource text, reportbody
text, reportprocs text, reportoptions text);
REVOKE ALL on pga_reports from PUBLIC;
GRANT ALL on pga_reports to Public;
\connect - cweblog
CREATE TABLE pga_layout (tablename varchar(64), nrcols int2, colnames text,
colwidth text);
REVOKE ALL on pga_layout from PUBLIC;
GRANT ALL on pga_layout to Public;
COPY patient_info FROM stdin;
1 1996-09-24 16:52:47-04 pangaro
III
3
Brooke_AMC
General_Surgery
outpatient 42 female
2 1996-09-24 17:00:35-04 hurlbutt
III
3
Walter_Reed_AMC
Internal_Medicine_Inpatient
inpatient 24 female
3 1996-09-24 17:04:16-04 dinelli
III
3
Walter_Reed_AMC
Internal_Medicine_Inpatient
inpatient 82 female
4 1996-09-24 17:05:12-04 higgins
III
3 Walter_Reed_AMC
etc.
--
Bob Williams, http://bob.usuhs.mil/
office: 301-295-3568
