search

darold / ora2pg

Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
http://www.ora2pg.com/
GNU General Public License v3.0
978 stars 341 forks source link

Support non-superuser pgtt loading #1743

Closed simonnagl closed 4 months ago

simonnagl commented 4 months ago

At the moment pgtt is loaded with LOAD 'pgtt', which only works as super user.

It would be nice, if you could support a configuration option to switch to the non super-user alternative LOAD '$libdir/plugins/pgtt.so'

This may happen with a custom string, to customize the LOAD path or a simple flag.

darold commented 4 months ago

This issue is not posted on the right project, please create it on https://github.com/darold/pgtt/issues/

simonnagl commented 4 months ago

Thank you @darold for the fast reaction.

I don't think this is the wrong project. I will give you some more context.

ora2pg supports converting global temporary tables to posgresql statements, which fit for the pgtt extension. To use the extension in a session you have to load it. This is done with LOAD 'pgtt'. ora2pg puts this statement before the first create /* global */ temporary table statement in the exported script. This works if you run the script ora2pg generated as postgresql superuser. If you do want to use the extension as non-superuser, you can install it into a different location. (Please see https://github.com/darold/pgtt?tab=readme-ov-file#installation). A non superuser postgresql user has to load the extension with LOAD '$libdir/plugins/pgtt.so'.

I would like to be able to configure the parameter ora2pg uses to load the pgtt.so extension, when exporting tables.

(https://github.com/darold/ora2pg/commit/890ed463b58d069593a57a714bba137ee7502a27#)

darold commented 4 months ago

Ah ok, sorry I have not understood that. Thanks for the explanation..

Commit 66967c6 adds this feature.

simonnagl commented 4 months ago

Wow, thank you very much!