PostgreSQL Import

PostgreSQL Import

By default, conversion to PostgreSQL format is written to a file named 'output.sql'. The command:

psql mydb < output.sql

will import the contents of file output.sql into the PostgreSQL mydb database.

• DATA_LIMIT

  When performing INSERT/COPY export, Ora2Pg processes data in chunks of DATA_LIMIT tuples for speed improvement. Tuples are stored in memory before being written to disk, so if you want speed and have enough system resources you can increase this limit to a higher value, for example: 100000 or 1000000. Before release 7.0, a value of 0 meant no limit so that all tuples were stored in memory before being flushed to disk. In the 7.x branch this has been removed and chunks will be set to the default: 10000

• BLOB_LIMIT

  When Ora2Pg detects a table with BLOB data, it will automatically reduce the value of this directive by dividing it by 10 until its value is below 1000. You can control this value by setting BLOB_LIMIT. Exporting BLOBs uses lot of resources; setting it to a too high value can produce OOM errors.

• CLOB_AS_BLOB

  Applies same behavior on CLOBs than BLOBs with BLOB_LIMIT settings. This is especially useful if you have large CLOB data. Default: enabled

• OUTPUT

  The Ora2Pg output filename can be changed with this directive. Default value is output.sql. If you set the file name with extension .gz or .bz2 the output will be automatically compressed. This requires that the Compress::Zlib Perl module is installed if the filename extension is .gz and that the bzip2 system command is installed for the .bz2 extension.

• OUTPUT_DIR

  Since release 7.0, you can define a base directory where the files will be written. The directory must exist.

• BZIP2

  This directive allows you to specify the full path to the bzip2 program if it can not be found in the PATH environment variable.

• FILE_PER_CONSTRAINT

  Allows object constraints to be saved in a separate file during schema export. The file will be named CONSTRAINTS_OUTPUT, where OUTPUT is the value of the corresponding configuration directive. You can use .gz or .bz2 extension to enable compression. Default is to save all data in the OUTPUT file. This directive is usable only with TABLE export type.

  The constraints can be imported quickly into PostgreSQL using the LOAD export type to parallelize their creation over multiple (-j or JOBS) connections.

• FILE_PER_INDEX

  Allows indexes to be saved in a separate file during schema export. The file will be named INDEXES_OUTPUT, where OUTPUT is the value of the corresponding configuration directive. You can use .gz or .bz2 file extension to enable compression. Default is to save all data in the OUTPUT file. This directive is usable only with TABLE AND TABLESPACE export type. With the TABLESPACE export, it is used to write "ALTER INDEX ... TABLESPACE ..." into a separate file named TBSP_INDEXES_OUTPUT that can be loaded at the end of the migration after the indexes creation to move the indexes.

  The indexes can be imported quickly into PostgreSQL using the LOAD export type to parallelize their creation over multiple (-j or JOBS) connections.

• FILE_PER_FKEYS

  Allows foreign key declarations to be saved in a separate file during schema export. By default foreign keys are exported into the main output file or in the CONSTRAINT_output.sql file. When enabled, foreign keys will be exported into a file named FKEYS_output.sql

• FILE_PER_TABLE

  Allows data export to be saved in one file per table/view. The files will be named as tablename_OUTPUT, where OUTPUT is the value of the corresponding configuration directive. You can still use .gz or .bz2 extension in the OUTPUT directive to enable compression. Default 0 will save all data in one file, set it to 1 to enable this feature. This is usable only during INSERT or COPY export type.

• FILE_PER_FUNCTION

  Allows functions, procedures and triggers to be saved in one file per object. The files will be named as objectname_OUTPUT, where OUTPUT is the value of the corresponding configuration directive. You can still use .gz or .bz2 extension in the OUTPUT directive to enable compression. Default 0 will save all in one single file, set it to 1 to enable this feature. This is usable only during the corresponding export type; the package body export has a special behavior.

  When export type is PACKAGE and you've enabled this directive, Ora2Pg will create a directory per package, named with the lower case name of the package, and will create one file per function/procedure in that directory. If the configuration directive is not enabled, it will create one file per package as packagename_OUTPUT, where OUTPUT is the value of the corresponding directive.

• TRUNCATE_TABLE

  If this directive is set to 1, a TRUNCATE TABLE instruction will be added before loading data. This is usable only during INSERT or COPY export types.

  When activated, the instruction will be added only if there's no global DELETE clause or no specific one for to the current table (see below).

• DELETE

  Supports including a DELETE FROM ... WHERE clause filter before importing data to perform a delete of some lines instead of truncating tables. Value is constructed as follows: TABLE_NAME[DELETE_WHERE_CLAUSE], or if you have only one where clause for all tables just put the delete clause as a single value. Both are possible too. Here are some examples:

DELETE  1=1    # Apply to all tables and delete all tuples
DELETE  TABLE_TEST[ID1='001']   # Apply only on table TABLE_TEST
DELETE  TABLE_TEST[ID1='001' OR ID1='002] DATE_CREATE > '2001-01-01' TABLE_INFO[NAME='test']

The last example applies two different delete where clauses on tables TABLE_TEST and TABLE_INFO and a generic delete where clause on DATE_CREATE to all other tables. If TRUNCATE_TABLE is enabled it will be applied to all tables not covered by the DELETE definition.

These DELETE clauses might be useful with regular "updates".

• STOP_ON_ERROR

  Set this parameter to 0 to not include the call to \set ON_ERROR_STOP ON in all SQL scripts generated by Ora2Pg. By default this order is always present so that the script will immediately abort when an error is encountered.

• COPY_FREEZE

  Enable this directive to use COPY FREEZE instead of a simple COPY to export data with rows already frozen. This is intended as a performance option for initial data loading. Rows will be frozen only if the table being loaded has been created or truncated in the current sub-transaction. This will only work with export to file and when -J or ORACLE_COPIES is not set or defaults to 1. It can be used with direct import into PostgreSQL under the same condition but -j or JOBS must also be unset or default to 1.

• CREATE_OR_REPLACE

  By default Ora2Pg uses CREATE OR REPLACE in functions and views DDL. If you need not to override existing functions or views, disable this configuration directive - DDL will not include OR REPLACE.

• DROP_IF_EXISTS

  To add a DROP <OBJECT> IF EXISTS before creating the object, enable this directive. Can be useful in iterative work. Default is disabled.

• EXPORT_GTT

  PostgreSQL does not support Global Temporary Tables natively but you can use the pgtt extension to emulate this behavior. Enable this directive to export global temporary tables.

• PGTT_NOSUPERUSER

  By default the pgtt extension is loaded using superuser privileges. Enable it if you run the SQL scripts generated using a non superuser user. It will use:

LOAD '$libdir/plugins/pgtt';

instead of default:

LOAD 'pgtt';

• NO_HEADER

  Enabling this directive will prevent Ora2Pg from printing its header into output files. Only the translated code will be written.

• PSQL_RELATIVE_PATH

  By default, Ora2Pg uses \i psql command to execute generated SQL files. If you want to use a relative path following the script execution file, enabling this option will use \ir. See psql help for more information.

• DATA_VALIDATION_ROWS

  Number of rows that must be retrieved on both sides for data validation. Default is to compare the first 10000 rows. A value of 0 means compare all rows.

• DATA_VALIDATION_ORDERING

  Order of rows between both sides is different once the data has been modified. In this case data must be ordered using a primary key or a unique index, meaning that a table without such object cannot be compared. If the validation is done just after the data migration without any data modification, the validation can be done on all tables without any ordering.

• DATA_VALIDATION_ERROR

  Stop validating data from a table after a certain amount of row mismatches. Default is to stop after 10 rows validation errors.

• TRANSFORM_VALUE

  Use this directive to specify which transformation should be applied to a column when exporting data. Value must be a semicolon-separated list of

TABLE[COLUMN_NAME, <replace code in SELECT target list>]

For example, to replace the string 'Oracle' with 'PostgreSQL' in a varchar2 column, use the following.

TRANSFORM_VALUE   ERROR_LOG_SAMPLE[DBMS_TYPE:regexp_replace("DBMS_TYPE",'Oracle','PostgreSQL')]

or to replace all Oracle char(0) in a string with a space character:

TRANSFORM_VALUE   CLOB_TABLE[CHARDATA:translate("CHARDATA", chr(0), ' ')]

The expression will be applied in the SQL statement used to extract data from the source database.

• NO_START_SCN

  Enable this directive if you don't want to export all data based on current SCN. By default Ora2Pg get first the current SCN and then retrieve all table data using this SCN to be consistant in case of data modification.

When using Ora2Pg export type INSERT or COPY to dump data to a file and FILE_PER_TABLE is enabled, you will be warned that Ora2Pg will not export data again if the file already exists. This is to prevent downloading table data twice when dealing with huge amount of data. To force the download of data from these tables you have to remove the existing output file first.

If you want to import data on the fly to the PostgreSQL database, you have three configuration directives to set the PostgreSQL database connection. This is only possible with COPY or INSERT export type as for database schema there's no real benefit to do that.

• PG_DSN

  Use this directive to set the PostgreSQL data source namespace using DBD::Pg Perl module as follows:

dbi:Pg:dbname=pgdb;host=localhost;port=5432

will connect to database 'pgdb' on localhost at tcp port 5432.

Note that this directive is only used for data export, other exports need to be imported manually through the use of psql or any other PostgreSQL client.

To use SSL encrypted connection you must add sslmode=require to the connection string as follows:

dbi:Pg:dbname=pgdb;host=localhost;port=5432;sslmode=require

• PG_USER and PG_PWD

  These two directives are used to set the login user and password.

  If you do not supply credentials with PG_PWD and you have installed the Term::ReadKey Perl module, Ora2Pg will ask for the password interactively. If PG_USER is not set it will be asked interactively too.

• SYNCHRONOUS_COMMIT

  Specifies whether transaction commit will wait for WAL records to be written to disk before the command returns a "success" indication to the client. This is equivalent to setting the synchronous_commit directive in the postgresql.conf file. This is only used when you load data directly to PostgreSQL; the default is off to disable synchronous commit to gain speed at writing data. Some modified versions of PostgreSQL, like Greenplum, do not have this setting, so in this case set this directive to 1, and ora2pg will not try to change the setting.

• PG_INITIAL_COMMAND

  This directive can be used to send an initial command to PostgreSQL, just after the connection. For example to set some session parameters. This directive can be used multiple times.

• INSERT_ON_CONFLICT

  When enabled this instructs Ora2Pg to add an ON CONFLICT DO NOTHING clause to all INSERT statements generated for this type of data export.