Thread: Ora2pg.pl - some feedback
A few pointers if you're going to use ora2pg.pl to port your oracle schema and data to postgres. 1) For non-perl savvy folks - The script does not do this all at once - uncomment one TYPE option at a time. For example, say you want to export your sequences, tables, and data (as in my case). First uncomment type => 'SEQUENCE' and run ora2pg.sql. Save the output script as something else other than output.sql. Then comment that back and uncomment type => 'TABLE' and run your script again - again saving output.sql as something else.....etc. 2) In order to export your data, your postgres db and tables should be owned by the user name that runs ora2pg.pl. Otherwise, you get all kinds of creative errors. Good news is that I was able to get this script to work for me. Question - is there a way to change the owner of a postgres database after it's been created? I know I can alter table info, but is there something to do this for the db? -- Susan Lane DPN, Incorporated 4631 Spring Mountain Road Las Vegas, NV 89102 Email suel@dpn.com Ph. (702) 873-3282 Fax (702) 873-3913 http://www.dpn.com
On Fri, 10 Aug 2001, Susan Lane wrote: >Question - is there a way to change the owner of a postgres database after >it's been created? I know I can alter table info, but is there something >to do this for the db? Look at the pg_database system catalog. Specifically, the datdba column. It should be the PostgreSQL UID of the user who owns it. For example: lx=# SELECT datname, datdba FROM pg_database lx-# INNER JOIN pg_user ON (datdba=usesysid); datname | datdba --------------+-------- postgres | 507 twig2 | 501 template0 | 507 Regards, Jw. -- jlx@commandprompt.com by way of pgsql-general@commandprompt.com
On Fri, 10 Aug 2001 pgsql-general@commandprompt.com wrote: >For example: > >lx=# SELECT datname, datdba FROM pg_database >lx-# INNER JOIN pg_user ON (datdba=usesysid); > datname | datdba > -------------+-------- > postgres | 507 > twig2 | 501 > template0 | 507 Whoops, that was missing an important identifier to be meaningful. ;) Trying again: lx=# SELECT datname, datdba, usename FROM pg_database lx-# INNER JOIN pg_user ON (datdba=usesysid); datname | datdba | usename --------------+--------+---------- postgres | 507 | postgres twig2 | 501 | jd template0 | 507 | postgres ... So, look up the usename/usesysid in the pg_user table to find out what new datdba to UPDATE your database (datname) to in pg_database. Hope that's helpful. Regards, Jw. -- jlx@commandprompt.com by way of pgsql-general@commandprompt.com
Yes, thanks! That helps! ----- Original Message ----- From: <pgsql-general@commandprompt.com> To: Susan Lane <suel@dpn.com> Cc: <pgsql-general@postgresql.org> Sent: Friday, August 10, 2001 1:09 PM Subject: Re: [GENERAL] Ora2pg.pl - some feedback > On Fri, 10 Aug 2001 pgsql-general@commandprompt.com wrote: > >For example: > > > >lx=# SELECT datname, datdba FROM pg_database > >lx-# INNER JOIN pg_user ON (datdba=usesysid); > > datname | datdba > > -------------+-------- > > postgres | 507 > > twig2 | 501 > > template0 | 507 > > Whoops, that was missing an important identifier to be meaningful. ;) > Trying again: > > lx=# SELECT datname, datdba, usename FROM pg_database > lx-# INNER JOIN pg_user ON (datdba=usesysid); > datname | datdba | usename > --------------+--------+---------- > postgres | 507 | postgres > twig2 | 501 | jd > template0 | 507 | postgres > > ... > > So, look up the usename/usesysid in the pg_user table to find out what new > datdba to UPDATE your database (datname) to in pg_database. Hope that's > helpful. > > > Regards, > Jw. > -- > jlx@commandprompt.com by way of pgsql-general@commandprompt.com > >