Re: Migrating Data from MySQL to PostgreSQL

There is an interesting database we need for a project that is
downloadable as the equivalent of postgres' pg_dumpall. The file contains
the DDL for the tables and supporting features and there are 19 separate
files containing the data with the pipe symbol separating attributes. Before
I upset psql I want to check my thinking with you experts.

I modified the DDL statements to remove the apparently MySQL-specific calls
to a database engine, and I modified the data load statements to use the
postgres copy command.

My question is whether I need to prepend a slash to the copy command as I
would when running it from the command line.

I'll be happy to provide samples and further information if that's
desired.

Rich

On 05/05/2011 04:35 PM, Rich Shepard wrote:
> There is an interesting database we need for a project that is
> downloadable as the equivalent of postgres' pg_dumpall. The file contains
> the DDL for the tables and supporting features and there are 19 separate
> files containing the data with the pipe symbol separating attributes.
> Before
> I upset psql I want to check my thinking with you experts.
>
> I modified the DDL statements to remove the apparently
> MySQL-specific calls
> to a database engine, and I modified the data load statements to use the
> postgres copy command.
>
> My question is whether I need to prepend a slash to the copy command
> as I
> would when running it from the command line.
>
> I'll be happy to provide samples and further information if that's
> desired.
>
> Rich
>

Hello Rich,

In all of the times where I've migrated data from MySQL to PostgreSQL,
this is all I have needed, for example:

COPY table (col1,col2,col3,col4) FROM stdin;

<tab separated data goes here/>

Assuming all of your columns are of the correct types and size,
everything should load just fine from that file with a psql command:

psql -U postgres mydb -f data_file.copy

Hope that helps! =)

Regards,

Lacey

On 05/05/2011 04:35 PM, Rich Shepard wrote:
> There is an interesting database we need for a project that is
> downloadable as the equivalent of postgres' pg_dumpall. The file contains
> the DDL for the tables and supporting features and there are 19 separate
> files containing the data with the pipe symbol separating attributes.
> Before
> I upset psql I want to check my thinking with you experts.
>
> I modified the DDL statements to remove the apparently
> MySQL-specific calls
> to a database engine, and I modified the data load statements to use the
> postgres copy command.
>
> My question is whether I need to prepend a slash to the copy command
> as I
> would when running it from the command line.
>
> I'll be happy to provide samples and further information if that's
> desired.
>
> Rich
>

One other small thing to add. For NULLs you might want to add FROM stdin
WITH NULL AS '' to the end of the command.

=)

Lacey

On Thu, 5 May 2011, Lacey L. Powers wrote:

> In all of the times where I've migrated data from MySQL to PostgreSQL,
> this is all I have needed, for example:
>
> COPY table (col1,col2,col3,col4) FROM stdin;

Lacey,

The data are not coming from stdin but from files on the disk.

I have at the bottom of the DDL file lines such as these:

COPY comments FROM 'comments.csv' WITH DELIMITER '|';
COPY experts FROM 'experts.csv' WITH DELIMITER '|';

and the first couple of lines of comments.csv are:

7|J. D. Hardy|animal|1996-06-13 14:51:08|1996-06-17
8|J. D. Hardy|annelida|1996-06-13 14:51:08|1996-06-17

I could add WITH NULL AS "" to the commands.

Thanks,

Rich

On 05/05/2011 05:08 PM, Rich Shepard wrote:
> On Thu, 5 May 2011, Lacey L. Powers wrote:
>
>> In all of the times where I've migrated data from MySQL to PostgreSQL,
>> this is all I have needed, for example:
>>
>> COPY table (col1,col2,col3,col4) FROM stdin;
>
> Lacey,
>
> The data are not coming from stdin but from files on the disk.
>
> I have at the bottom of the DDL file lines such as these:
>
> COPY comments FROM 'comments.csv' WITH DELIMITER '|';
> COPY experts FROM 'experts.csv' WITH DELIMITER '|';
>
> and the first couple of lines of comments.csv are:
>
> 7|J. D. Hardy|animal|1996-06-13 14:51:08|1996-06-17
> 8|J. D. Hardy|annelida|1996-06-13 14:51:08|1996-06-17
>
> I could add WITH NULL AS "" to the commands.
>
> Thanks,
>
> Rich

Oh, apologies, then, Rich. I missed the part where they were in separate
files.

You can still do that, but it runs with the permissions of the postgres
user, then, so you would need to make sure that wherever you stored
those files was accessable by the postgres user.

Additionally, I've always had to give a fully qualified path to the
files in question, since it doesn't deal well with relative paths. =(

Keeping those things in mind, from there, it shouldn't give you any
trouble. =)

Hope that helps. =)

Lacey