Results 1 to 5 of 5
  1. #1
    Join Date
    May 2011
    Posts
    6

    Unanswered: Convert PostgreSQL Dump to MySQL

    Hi Chaps,

    been searching for a while, but cannot find a suitable answer..yet.

    I'm trying to rebuild a site (currently Ruby/PostgreSQL) into PHP/MySQL.

    I have a PostgreSQL dump .sql file, which looks like:

    Code:
    CREATE TABLE addresses (
    id integer NOT NULL,
    firstname character varying(255),
    lastname character varying(255),
    address1 character varying(255),
    address2 character varying(255),
    city character varying(255),
    state_id integer,
    zipcode character varying(255),
    country_id integer,
    phone character varying(255),
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    state_name character varying(255),
    alternative_phone character varying(255)
    );
    
    ALTER TABLE public.addresses OWNER TO XXXXXX;
    
    CREATE SEQUENCE addresses_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
    
    ALTER TABLE public.addresses_id_seq OWNER TO XXXXXX;
    
    ALTER SEQUENCE addresses_id_seq OWNED BY addresses.id;
    
    SELECT pg_catalog.setval('addresses_id_seq', 348, TRUE);
    But there are no INSERT INTO's, rather:

    Code:
    COPY addresses (id, firstname, lastname, address1, address2, city, state_id, zipcode, country_id, phone, created_at, updated_at, state_name, alternative_phone) FROM stdin;
    1 Mr CustomerA 1 New St Somewhere \N PS1 2BC 213 07XXX XXX XXX 2010-06-24 11:44:25.130213 2010-06-24 12:35:14.501107 SomeState \N
    1 Mr CustomerB 2 New St Somewhere \N PS2 3AB 213 07XXX XXX XXX 2010-06-25 11:00:25.130213 2010-06-25 12:00:14.501107 SomeState \N
    I have tried a couple of converters, but it doesn't include any data (I'm guessing as there is no INSERT INTO syntax). Can someone shed some light on this, whether it is possible, and what's the best method of doing so (automatic/manual)?

    Thanks in advance

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Of course PostgreSQL has INSERT INTO, but using COPY is way more efficient.

    The only thing that remotely resembles that in MySQL is probably LOAD DATA INFILE as far as I know (I don't really use MySQL so I could be mistaken)

    Also MySQL is one of the few DBMS that does not have sequences as well - so you will need to change all those columns to autoincrement or however that is called in MySQL.

    But questions regarding a migration from PostgreSQL to MySQL should better be asked in the MySQL forum!

  3. #3
    Join Date
    May 2011
    Posts
    6
    Hi, thanks for the reply. I've checked out the LOAD DATA INFILE, but the examples I've seen deal with CSV files, where my PostgreSQL dump doesn't seem to use seperators. So I'm not too sure how to alter this, and whether I'd need to repeat the process for each table individually or if I can import the schema/data in one go?
    Also, how do I move this post to the MySQL forum?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by koolsamule View Post
    Hi, thanks for the reply. I've checked out the LOAD DATA INFILE, but the examples I've seen deal with CSV files, where my PostgreSQL dump doesn't seem to use seperators.
    The data in the dump uses a tab character as far as I know.

    Also, how do I move this post to the MySQL forum?
    Just re-post it there.

  5. #5
    Join Date
    May 2011
    Posts
    6
    OK, cheers for that, I'l re-post now.

Posting Permissions

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