Thread: Trouble migrating from PostgreSQL --> Oracle
Good morning, I've recently just inherited a PostgreSQL database that is a back end for some logistics software we use here. We have our own Oracle servers in our group on faster machines with automated backup so we would like to move all the data over to Oracle. The software came with scripts to create the owners, tables and permissions in Oracle but when using "pgAdmin" (Windows client being used to administer the PostgreSQL database at the moment) export feature which dumps the entire database in form of insert statements, seems to not carry over everything we need (lots of data is missing from the application). Can anyone recommend a clear cut way to migrate the data over? We're generating and importing a text file with almost a gig worth of import statements but we're figuring there has to be a easier way that will not only bring over all the data but do it more effeciently as well. Thanks
On Tue, 2006-11-14 at 06:08 -0800, matthew.d.williams@gmail.com wrote:
> The software came with scripts to create the owners, tables and
> permissions in Oracle but when using "pgAdmin" (Windows client being
> used to administer the PostgreSQL database at the moment) export
> feature which dumps the entire database in form of insert statements,
> seems to not carry over everything we need (lots of data is missing
> from the application).
>
"Missing"? That's not very descriptive. It may be some kind of
permissions problem (i.e. maybe PgAdmin is connecting as a user that
doesn't have permissions to read all the data). Use pg_dump if you still
have problems with PgAdmin, and make sure you're connecting as a user
with enough privileges.
> Can anyone recommend a clear cut way to migrate the data over? We're
> generating and importing a text file with almost a gig worth of import
1GB is not much data (especially in the form of INSERTs). Are you having
performance problems with the PostgreSQL DB? This might be a good
opportunity for you to learn some PostgreSQL ;-)
> statements but we're figuring there has to be a easier way that will
> not only bring over all the data but do it more effeciently as well.
You can export data as INSERTs or delimited form using pg_dump.
Hope this helps,
Jeff Davis
> ------- Original Message ------- > From: Jeff Davis <pgsql@j-davis.com> > To: "matthew.d.williams@gmail.com" <matthew.d.williams@gmail.com> > Sent: 14/11/06, 19:05:33 > Subject: Re: [GENERAL] Trouble migrating from PostgreSQL --> Oracle > > Use pg_dump if you still > have problems with PgAdmin, and make sure you're connecting as a user > with enough privileges. pgAdmin uses pg_dump to do it's backups anyway. Regards, Dave
On Tue, Nov 14, 2006 at 06:08:44AM -0800, matthew.d.williams@gmail.com wrote: > Good morning, > > I've recently just inherited a PostgreSQL database that is a back end > for some logistics software we use here. We have our own Oracle > servers in our group on faster machines with automated backup so we > would like to move all the data over to Oracle. > > The software came with scripts to create the owners, tables and > permissions in Oracle but when using "pgAdmin" (Windows client being > used to administer the PostgreSQL database at the moment) export > feature which dumps the entire database in form of insert statements, > seems to not carry over everything we need (lots of data is missing > from the application). > > Can anyone recommend a clear cut way to migrate the data over? We're > generating and importing a text file with almost a gig worth of import > statements but we're figuring there has to be a easier way that will > not only bring over all the data but do it more effeciently as well. If you just need to get an initial load into PostgreSQL, you could probably use EnterpriseDB to do it. Load the stuff into EnterpriseDB with the Oracle scripts and then try pg_dumping from there into PostgreSQL (might work better using the community pg_dump; I suspect our version has been changed to support Oracle-isms). There's also an Oracle to PostgreSQL migration program floating around somewhere; I think you can theoretically just run the creation scripts through it. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)