Oracle support

[jqnatividad]

Using a similar approach as MySQL and MSSQL, i.e. using a connection string?

A lot of enterprise systems still run on Oracle and this would help migrations to PostgreSQL.

[dimitri]

It's definitely on the TODO list, still not something I am willing to work on on my free time. Care enough to sponsor the work?

[cdhouch]

We're migrating Oracle to PostgreSQL right now. I'm using ora2pg to dump out the oracle databases per table, then using a shell script to convert the .sql files with inline COPY data into .csv files that pgloader can load into postgres. Some of our tables are 7+ billion rows and would not load as a regular .sql file, pgloader makes short work of it though. If pgloader could read the data directly out of the .sql files it would cut out a step that is really just rearranging the data to shoehorn it into a format pgloader will accept. In any case, if someone wants me to write up the process we're using currently, I could do that.

[dimitri]

I think it would be even better if pgloader would connect to Oracle directly like it does for MySQL or MS SQL. What is needed is:

• an oracle driver for common lisp, like https://github.com/archimag/cl-oracle
• the introspection queries to get the list of table structures, indexes and foreign keys, plus other objects if we add support for more,
• the casting rules when switching from Oracle to PostgreSQL (number to numeric and such).

Also, I would need a test database available for me to develop against, and some sponsoring for the time spent working on the feature.

It sounds way better to have pgloader connects to Oracle directly rather than tweak other tools output so that we can use an existing supported format here... anyway thanks for the details of your use case, I'm quite happy to see pgloader used on 7+ billion rows files ;-)

Also, pgloader supports COPY files already, so if you just cut the inline COPY parts of the sql files into per-table files, you should be good to go.

[dimitri]

In case anyone is interested in direct Oracle support and want to sponsor: http://pgloader.io/pgloader-moral-license.html

[mwaf]

I would like Oracle support as well but I'm not able to sponsor it unfortunately. I also looked into ora2pg but found it too big a hassle.

Fortunately my use case is rather simple so I ended up just using Oracle's SQLcl to spool the tables to file.

SET TERMOUT OFF
SET FEEDBACK OFF
SET SQLFORMAT csv
SPOOL my_table.csv
SELECT /*+ parallel */ * FROM my_table;
SPOOL OFF

I preferred the loader format over csv which uses | as the delimiter, YMMV.

The file can be imported as is with \copy my_table FROM 'my_table.csv' CSV HEADER; or processed with pgloader from there.

[esoni]

Hi @dimitri , i want to ask you the current status of Oracle Support. As i ve read at the moment pgloader is not able to help to migrate to Postgresql from Oracle. Can you confirm it ? Is it in roadmap ? thanks

[phoe]

AFAIK the status is the same as before: there'll be no progress until there's a person willing to sponsor the development, since @dimitri is shooooort on pgloader time and since me, who's helping out, knows nothing about Oracle.

Perhaps if there are enough people interested in the feature, a fundraiser could be organized and/or a Bountysource bounty linking to this issue could be set; this way it should be possible for people to chip in with smaller amounts to gather a larger overall sum of money. Once that happens, and once there is a little bit of media coverage to spread the word, it should perhaps be possible to fund developing this feature. @dimitri - what do you think about this idea?

[dimitri]

Hi @esoni and @phoe ; several ways to contribute to pgloader are already in place.

• If you want to contribute code, it's Open Source and on GitHub, just open a PR. If you need help to discover either Common Lisp or the pgloader code base, open a PR and ask questions, and join on Freenode IRC #lisp channel where we will be able to help you, time permitting.
• If you want to contribute money, see https://pgloader.io/moral-licence/ and GitHub sponsoring program. Money that I will personally receive this way will then be organised to cover fees and expenses of contributors to the code. It used to be me doing that, so I would keep the money. Nowadays I would be even happier to mentor someone else and contract their work with the contributed money I would receive.

On top of those existing solutions, we can also organise a bounty program of sorts. I will stress out that pgloader is OSS meant to be used in enterprise setups. I want enterprise to pay for features, not individuals. Please speak to your bosses. I can easily arrange for a consulting contract to be made and have proper invoicing of course.

[KuasarCloud]

Hi all,

Still not possible to use pgloader to migrate online from Oracle to PostgreSQL?

For Example LOAD DATABASE FROM oracle://username:password@hostname:port/service INTO postgresql://username:password@hostname:port/database

I cannot user ora2pg for security reason, any other approach you may recommend?

BRm