Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2003
    Location
    Sydney
    Posts
    3

    Question Unanswered: Import data from old (slightly different) schema

    Hi all.

    I'm new to Postgres (usually use MySQL).
    I have a database which has live data in it (with RI between tables), and I have a new database schema which we've been working on.

    What's the best way to go about dumping the data from the old schema into the new?

    I have tried by making separate files for each table (makes problem solving easier):


    for I in {list of table names}
    do
    pg_dump -a -o -t $I -f $I my_database
    done


    This seems to create backup files like I want. (By the way I realise that I'll have to import the tables in the right order to keep the RI constraints happy). However when I go to import them into the new schema I get a whole heap of parse errors:


    [postgres@supergrover cme_table_dump]$ psql my_database < suburb
    CREATE
    DROP
    You are now connected as new user postgres.
    ERROR: parser: parse error at or near "public"
    ERROR: parser: parse error at or near "("
    invalid command \.
    ERROR: parser: parse error at or near "36284"
    [postgres@supergrover cme_table_dump]$


    First bit of suburb:


    --
    -- PostgreSQL database dump
    --

    --
    -- TOC entry 1 (OID 0)
    -- Name: Max OID; Type: <Init>; Schema: -; Owner:
    --

    CREATE TEMPORARY TABLE pgdump_oid (dummy integer);
    COPY pgdump_oid WITH OIDS FROM stdin;
    42692 0
    \.
    DROP TABLE pgdump_oid;


    \connect - postgres

    SET search_path = public, pg_catalog;

    --
    -- Data for TOC entry 3 (OID 35114)
    -- Name: suburb; Type: TABLE DATA; Schema: public; Owner: postgres
    --


    COPY suburb (suburbid, geogstateid, suburbname, suburbpostcode, suburbvalid, suburbtimestamp) WITH OIDS FRO
    M stdin;
    36284 1 1 Wollongong 2500 t 2003-02-26 07:49:20.963675
    36285 2 1 Warrimoo 2774 t 2003-02-26 07:49:20.97498
    36286 3 1 Springwood 2774 t 2003-02-26 07:49:20.978052
    36287 4 1 Winmalee 2774 t 2003-02-26 07:49:20.979515
    36288 5 8 Klerksdorp 2000 t 2003-02-26 07:49:20.981056


    I assume I'm doing something wrong...
    Any hints?

    Thanks in advance!

  2. #2
    Join Date
    Dec 2002
    Posts
    15
    You cannot restore a 7.3.x dump into a < 7.3 backend.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •