Обсуждение:

Hey All,

Can anyone tell me where I can find documention on how to use mysql2pgsql?
I downloaded the software conversion utility from www.pgsql.com.

Thanks.

Peter

> Can anyone tell me where I can find documention on how to use mysql2pgsql?
> I downloaded the software conversion utility from www.pgsql.com.

Create a dump file from mysql, then give that file to mysql2pgsql (use
the --help option to see other possible parameters). Write the output to
a file:

  mysql2pgsql --verbose dumpfile > pgsql.dump

The output file will work as-is or with some modifications to give you
something you can pipe to psql:

  psql -e yourdb < pgsql.dump

Good luck!

                 - Thomas

Hello Peter,

Here is a complete procedure for converting from MySQL to PGSQL. You
need to use two scripts

     a)my2pg.pl
     b)mysql2pgsql.pl

------------------PROCEDURE--------------------------

1) Take the dump of the schema only
   mysqldump -d <YOURDBNAME> > /tmp/Tables.sql -p;

2) Convert this sql file into pgsql file using my2pg.pl
   perl my2pg.pl Tables.sql > TablesPg.sql

3) Take the dump of data
   mysqldump  -c -t <YOURDBNAME> > /tmp/Data.sql -p;

4) Convert this sql file into pgsql file using mysql2pgsql.pl
   perl mysql2pgsql.pl Data.sql

5) step 4 will create a Data.sql92 file

6) find and replace all occurances of 0000-00-00 with 0001-01-01 using
   regular expression, if you have date field in your table structures

7) Start dumping to Postgres

8) create a database <YOURDBNAME> using
   createdb <YOURDBNAME>

9) Enter ACTBilling using
   psql <YOURDBNAME> --username=postgres

10) At the <YOURDBNAME> prompt type
    \i TablesPg.sql

11) All the tables will be successfully created.

12) Next use this command to port the data
    \i Data.sql92

13) All the data would be successfully ported.

---------------------END OF PROCEDURE---------------------

Hope it is of use to you.

--
Best regards,
 Gurudutt                            mailto:guru@indvalley.com

Life is not fair - get used to it.
Bill Gates


Thursday, December 20, 2001, 9:30:27 PM, you wrote:

PEC> Hey All,

PEC> Can anyone tell me where I can find documention on how to use mysql2pgsql?
PEC> I downloaded the software conversion utility from www.pgsql.com.

PEC> Thanks.

PEC> Peter


PEC> ---------------------------(end of broadcast)---------------------------
PEC> TIP 5: Have you checked our extensive FAQ?

PEC> http://www.postgresql.org/users-lounge/docs/faq.html

Hello everyone. I apologize ahead of time for the length of this email, I
wanted to provide enough detailed information to show exactly what was
going on with my problem.

I'm using fti.so, a full text indexing procedure from the /contrib
directory of the postgresql source tree. For the most part it works
wonderfully.. except that I appear to be having problems when I have
multiple triggers on the same table calling fti.

------------------------------------------------------------------ CREATE
TRIGGER fti_employee_lastname AFTER UPDATE OR INSERT OR DELETE ON person
FOR EACH ROW EXECUTE PROCEDURE fti(fti, lastname);

CREATE TRIGGER fti_employee_firstname AFTER UPDATE OR INSERT OR DELETE ON
person FOR EACH ROW EXECUTE PROCEDURE fti(fti, firstname);

CREATE TRIGGER fti_employee_screenname AFTER UPDATE OR INSERT OR DELETE ON
person FOR EACH ROW EXECUTE PROCEDURE fti(fti, screenname);
------------------------------------------------------------------- For my
tests the values in the db initially are: firstname: Brian lastname: Knox
screenname: brianknox1

Okay... so I check to see if "Brian" has been indexed in my fti table,
which it has:

select * from fti where string ~* 'Brian';
  row_id | string | id --------+------------+--------
     18 | brianknox1 | 472111
     22 | brian | 472111

I update the firstname and set it to Frodo:

orb2=# update person set firstname = 'Frodo' where firstname = 'Brian';
UPDATE 1

Now, I look, and 'Brian' is no longer in the fti table, but neither is
'Frodo'

orb2=# select * from fti where string ~* 'Frodo';
 row_id | string | id --------+--------+---- (0 rows)

orb2=# select * from fti where string ~* 'Brian';
 row_id | string | id --------+--------+---- (0 rows)

Now, watch this: if I update the field that the *first* trigger is on
(lastname), then everything works fine:

orb2=# update person set lastname = 'Baggins' where lastname = 'Knox';
UPDATE 1

orb2=# select * from fti where string ~* 'Baggins';
 row_id | string | id --------+---------+--------
   2138 | baggins | 472111 (1 row)

Now, I can verify that it is indeed the order that matters, and that only
the first trigger works, by recreating the triggers in a new order:

Now, the firstname trigger is first in order:

CREATE TRIGGER fti_employee_firstname AFTER UPDATE OR INSERT OR DELETE ON
person FOR EACH ROW EXECUTE PROCEDURE fti(fti, firstname);

CREATE TRIGGER fti_employee_lastname AFTER UPDATE OR INSERT OR DELETE ON
person FOR EACH ROW EXECUTE PROCEDURE fti(fti, lastname);

CREATE TRIGGER fti_employee_screenname AFTER UPDATE OR INSERT OR DELETE ON
person FOR EACH ROW EXECUTE PROCEDURE fti(fti, screenname);

orb2=# select * from fti where string ~* 'Brian';
 row_id | string | id --------+------------+--------
     18 | brianknox1 | 478636
     22 | brian | 478636

orb2=# update person set firstname = 'Frodo' where firstname = 'Brian';
UPDATE 1

orb2=# select * from fti where string ~* 'Frodo';
 row_id | string | id --------+--------+--------
   2110 | frodo | 485133


orb2=# select * from fti where string ~* 'Brian';
 row_id | string | id --------+--------+---- (0 rows)

==========================================================================
NOTE: I notice that even when I update the first field that is run through
the fti() routine and it updates successfully, that the last two fields
are deleted from the fti search table.

Here is the information from the FTI table before and after and update:

orb2=# select oid from person where firstname = 'Brian';
  oid --------
 498130

orb2=# select * from fti where id = '498130';
 row_id | string | id --------+------------+--------
      1 | et | 498130
      2 | net | 498130
      3 | ol | 498130
      4 | aol | 498130
      5 | se | 498130
      6 | tse | 498130
      7 | otse | 498130
      8 | aotse | 498130
      9 | laotse | 498130
     10 | x1 | 498130
     11 | ox1 | 498130
     12 | nox1 | 498130
     13 | knox1 | 498130
     14 | nknox1 | 498130
     15 | anknox1 | 498130
     16 | ianknox1 | 498130
     17 | rianknox1 | 498130
     18 | brianknox1 | 498130
     19 | ox | 498130
     20 | nox | 498130
     21 | knox | 498130
     22 | an | 498130
     23 | ian | 498130
     24 | rian | 498130
     25 | brian | 498130 (25 rows)

orb2=# update person set firstname = 'Frodo' where firstname = 'Brian';
UPDATE 1

orb2=# select * from fti where id = '498130';
 row_id | string | id --------+--------+--------
   2107 | do | 498130
   2108 | odo | 498130
   2109 | rodo | 498130
   2110 | frodo | 498130 (4 rows)

Notice that the other two fields, which were indexed in the fti table
(lastname, and screenname) are gone.

Anyone have any idea what might be going on? Is there anyone on the list
that is very familiar with the fti.so module, or who can spot something
obviously wrong in the way I'm using my triggers? I'm completely stumped.

Brian Knox laotse@lumberjack.snurgle.org

-----------------------------------------------------------------------------
Brian Knox Just Another Perl Hacker perl -le
'$_="6110>374086;2064208213:90<307;55";tr[0->][ LEOR!AUBGNSTY];print'

laotse@lumberjack.snurgle.org writes:
> ------------------------------------------------------------------ CREATE
> TRIGGER fti_employee_lastname AFTER UPDATE OR INSERT OR DELETE ON person
> FOR EACH ROW EXECUTE PROCEDURE fti(fti, lastname);

> CREATE TRIGGER fti_employee_firstname AFTER UPDATE OR INSERT OR DELETE ON
> person FOR EACH ROW EXECUTE PROCEDURE fti(fti, firstname);

> CREATE TRIGGER fti_employee_screenname AFTER UPDATE OR INSERT OR DELETE ON
> person FOR EACH ROW EXECUTE PROCEDURE fti(fti, screenname);

This will not work because there's no guarantee about the order of the
execution of the triggers.  I haven't worked with fti much, but it's
obvious that it expects you to have only *one* trigger relating a given
indextable to the master --- on update, the trigger deletes all existing
indextable rows for that master row.

It looks like the intended way to index multiple columns using a single
indextable is

CREATE TRIGGER fti_person AFTER UPDATE OR INSERT OR DELETE ON person
FOR EACH ROW EXECUTE PROCEDURE fti(fti, firstname, lastname, screenname);

Or you could use a separate indextable for each column, but that might
not be what you want.

            regards, tom lane

I'll try passing multiple column names and see how that does. If that does
not work, I could write a plpgsql function that gets called first with all
the arguments and then calls fti multiple times, once for each keyword, I
suppose.

-----------------------------------------------------------------------------
Brian Knox
Just Another Perl Hacker
perl -le '$_="6110>374086;2064208213:90<307;55";tr[0->][ LEOR!AUBGNSTY];print'

On Fri, 21 Dec 2001, Tom Lane wrote:

> laotse@lumberjack.snurgle.org writes:
> > ------------------------------------------------------------------ CREATE
> > TRIGGER fti_employee_lastname AFTER UPDATE OR INSERT OR DELETE ON person
> > FOR EACH ROW EXECUTE PROCEDURE fti(fti, lastname);
>
> > CREATE TRIGGER fti_employee_firstname AFTER UPDATE OR INSERT OR DELETE ON
> > person FOR EACH ROW EXECUTE PROCEDURE fti(fti, firstname);
>
> > CREATE TRIGGER fti_employee_screenname AFTER UPDATE OR INSERT OR DELETE ON
> > person FOR EACH ROW EXECUTE PROCEDURE fti(fti, screenname);
>
> This will not work because there's no guarantee about the order of the
> execution of the triggers.  I haven't worked with fti much, but it's
> obvious that it expects you to have only *one* trigger relating a given
> indextable to the master --- on update, the trigger deletes all existing
> indextable rows for that master row.
>
> It looks like the intended way to index multiple columns using a single
> indextable is
>
> CREATE TRIGGER fti_person AFTER UPDATE OR INSERT OR DELETE ON person
> FOR EACH ROW EXECUTE PROCEDURE fti(fti, firstname, lastname, screenname);
>
> Or you could use a separate indextable for each column, but that might
> not be what you want.
>
>             regards, tom lane
>

If you need them to go in a specific order why not call one trigger and
have it call other functions not triggers and then you would get the
desired result


Darren

Darren Ferguson
Software Engineer
Openband

On Fri, 21 Dec 2001, Tom Lane wrote:

> laotse@lumberjack.snurgle.org writes:
> > ------------------------------------------------------------------ CREATE
> > TRIGGER fti_employee_lastname AFTER UPDATE OR INSERT OR DELETE ON person
> > FOR EACH ROW EXECUTE PROCEDURE fti(fti, lastname);
>
> > CREATE TRIGGER fti_employee_firstname AFTER UPDATE OR INSERT OR DELETE ON
> > person FOR EACH ROW EXECUTE PROCEDURE fti(fti, firstname);
>
> > CREATE TRIGGER fti_employee_screenname AFTER UPDATE OR INSERT OR DELETE ON
> > person FOR EACH ROW EXECUTE PROCEDURE fti(fti, screenname);
>
> This will not work because there's no guarantee about the order of the
> execution of the triggers.  I haven't worked with fti much, but it's
> obvious that it expects you to have only *one* trigger relating a given
> indextable to the master --- on update, the trigger deletes all existing
> indextable rows for that master row.
>
> It looks like the intended way to index multiple columns using a single
> indextable is
>
> CREATE TRIGGER fti_person AFTER UPDATE OR INSERT OR DELETE ON person
> FOR EACH ROW EXECUTE PROCEDURE fti(fti, firstname, lastname, screenname);
>
> Or you could use a separate indextable for each column, but that might
> not be what you want.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>