pg_dump object dump-order; Part II - Mailing list pgsql-admin

Greetings

In advance I'd like to apoligize to start a new thread,
but I couldn't figure out how to reply to Mr. Lane's
thread-sensitive.

Mr. Lane kindly replied (in a highly appreciated patronizing way :)

>I will point out, however, that the given script does not in fact
>fail to load in any Postgres version known to me.  Maybe you should
>post your real problem instead of an oversimplified example.

Right, the error occurs in exactly such a simple
example, but I'm not above to provide details galore ...

####################
1: INITIAL SITUATION
####################

A database is to be transferred from its old host to a new DB-server.

Old host: UBUNTU 5.10 _Breezy Badger_ - Release i386 (20051012)
PgSQL v : PostgreSQL 8.0.6-1~bre

New host: Debian GNU/Linux 4.0 r0 _Etch_ - Official i386
PgSQL v : postgresql-d 8.1.9-0etch


####################
2: DUMPING THE DATA
####################

On the new machine the following command was used to dump
the databases (in a script that loops through all avail.
DBs). That means: the "new" pg_dump" was used to connect
to the "old" DB to dump the "old" DB-data directly onto
the "new" machine (maybe that causes havoc???):

# pg_dump -h old.database-host.tld -U dbusername -d -E LATIN1 -O -f testdb_dump.sql testdb

This yields the following SQL-file ...

>>>>> SQL OUTPUT OF pg_dump (file: testdb_dump.sql) >>>>>

--
-- PostgreSQL database dump
--

SET client_encoding = 'LATIN1';
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';


SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = true;

--
-- Name: event; Type: TABLE; Schema: public; Owner: testuser; Tablespace:
--

CREATE TABLE event (
    event_id integer DEFAULT nextval('event_id_seq') NOT NULL,
    haupt character varying(11),
    vorschau character varying(100),
    bild_text character varying(46),
    liste_titel character varying(37),
    liste_text character varying(45),
    link_0_id text,
    link_1_id character varying(100),
    link_1_text character varying(16),
    link_2_id character varying(100),
    link_2_text character varying(16),
    bild_0 character varying(100),
    bild_1 character varying(100),
    bild_2 character varying(100),
    bild_3 character varying(100),
    bild_4 character varying(100),
    bild_5 character varying(100)
);


--
-- Name: event_id_seq; Type: SEQUENCE; Schema: public; Owner: testuser
--

CREATE SEQUENCE event_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


--
-- Name: event_id_seq; Type: SEQUENCE SET; Schema: public; Owner: testuser
--

SELECT pg_catalog.setval('event_id_seq', 74, true);

--
-- Data for Name: event; Type: TABLE DATA; Schema: public; Owner: testuser
--

INSERT INTO event VALUES (29, 'sect', '../img/somejpg.jpg', '', 'Some text', '09/2005', '', 'gallery_name', 'Title',
'some-moretext', 'images', '', '', '', '', '', ''); 

--
-- Name: event_pkey; Type: CONSTRAINT; Schema: public; Owner: testuser; Tablespace:
--

ALTER TABLE ONLY event
    ADD CONSTRAINT event_pkey PRIMARY KEY (event_id);


--
-- Name: event; Type: ACL; Schema: public; Owner: testuser
--

REVOKE ALL ON TABLE event FROM PUBLIC;
REVOKE ALL ON TABLE event FROM testuser;
GRANT ALL ON TABLE event TO testuser;
GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE event TO www;


--
-- Name: event_id_seq; Type: ACL; Schema: public; Owner: testuser
--

REVOKE ALL ON TABLE event_id_seq FROM PUBLIC;
REVOKE ALL ON TABLE event_id_seq FROM testuser;
GRANT ALL ON TABLE event_id_seq TO testuser;
GRANT INSERT,SELECT,UPDATE ON TABLE event_id_seq TO www;

--
-- PostgreSQL database dump complete
--

<<<<< EOF testdb_dump.sql <<<<<

NOTE: The SQL-file has been edited as the inserts for table
event have been removed just as the GRANTs/REVOKEs on
scheme PUBLIC.

NOTE 2: Also the MySQL-tool "replace" has been run on
the file to remove the "::text"-typecasts on the
sequence's nextval-statement (to prevent double-casting
in PgSQL 8.1.series the like of "::text)::regclass")


##############
3: DATA IMPORT
##############

On the shell, the import was manually prepared
by issuing the following commands ...

3.1 Deleting old DB (should it exists)
# dropdb -h localhost -U dbusername testdb
DROP DATABASE

3.2 Creating a new DB
# createdb -h localhost -U dbusername -E LATIN1 testdb
CREATE DATABASE

3.3 Importing the data
# psql -h localhost -U dbusername -f testdb_dump.sql -d testdb

>>>>> OUTPUT OF psql (Beware! German version :) >>>>>
SET
SET
SET
COMMENT
SET
SET
SET
psql:testdb_dump.sql:44: FEHLER:  Relation »event_id_seq« existiert nicht
CREATE SEQUENCE
 setval
--------
     74
(1 Zeile)

psql:testdb_dump.sql:68: FEHLER:  Relation »event« existiert nicht
psql:testdb_dump.sql:75: FEHLER:  Relation »event« existiert nicht
REVOKE
REVOKE
GRANT
GRANT
psql:testdb_dump.sql:92: FEHLER:  Relation »event« existiert nicht
psql:testdb_dump.sql:93: FEHLER:  Relation »event« existiert nicht
psql:testdb_dump.sql:94: FEHLER:  Relation »event« existiert nicht
psql:testdb_dump.sql:95: FEHLER:  Relation »event« existiert nicht
REVOKE
REVOKE
GRANT
GRANT
<<<<< END OF OUTPUT OF psql <<<<<

As can be seen clearly, psql quite firmly thinks the dump
is not OK and refers to line 44 as the erroneous line
(which is ");", the closing bracket of the "CREATE TABLE"-statement")


######################
4: CHECKING THE IMPORT
######################

A psql to the new db and a check upon the contents
of the newly created DB reveals ...

# psql testdb

testdb=# \d
             Liste der Relationen
 Schema |     Name     |   Typ   | Eigentümer
--------+--------------+---------+-------------
 public | event_id_seq | Sequenz | klep
(1 Zeile)

Which clearly states:
The table "events" HAS NOT been created.

--------------------
5: VERIFYING RESULTS
--------------------
To verify our findings so far, the above SQL-file
was edited thusly, so that the creation of the
sequence will be triggered before the table-creation ...

>>>>> EDITED SQL-FILE "testdb_dump.sql" >>>>>
[...]
--
-- Name: event_id_seq; Type: SEQUENCE; Schema: public; Owner: testuser
--

CREATE SEQUENCE event_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


--
-- Name: event; Type: TABLE; Schema: public; Owner: testuser; Tablespace:
--

CREATE TABLE event (
    event_id integer DEFAULT nextval('event_id_seq') NOT NULL,
    .
[...]

<<<<< EOF "testdb_dump.sql"/edited version >>>>>

The following "psql"-command yields ...
# psql -h localhost -U dbusername -f testdb_dump.sql -d testdb
SET
SET
SET
COMMENT
SET
SET
SET
CREATE SEQUENCE
CREATE TABLE
 setval
--------
     74
(1 Zeile)

INSERT 625784 1
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
REVOKE
REVOKE
GRANT
GRANT
REVOKE
REVOKE
GRANT
GRANT

###############
VOILA! NO ERROR
###############

I hope that is enough "real problem"

regards, Klaus L.