Exporting Data to PostgreSQL - Exportizer Documentation

Here you can find the detailed instruction on how to export data from any database to PostgreSQL database in Exportizer software.

Below, it is shown how to export data from GUI or command line.

Export Conditions

In most cases, to export data to PostgreSQL, it is important to fulfill the following conditions:

We recommend to use Exportizer Enterprise and FD interface when working with PostgreSQL databases. FD engine does not require ODBC drivers and exports data much faster. But if you prefer to use ODBC for both source and target databases, Exportizer Pro can be used.
PostgreSQL client must be installed. Make sure the Exportizer and PostgreSQL client have the same architecture, i.e. either all are 32-bit or all are 64-bit.
All other involved components, i.e. Exportizer, ODBC drivers (if you use ODBC on source or target side), OLE DB providers etc., should have the same architecture: 32-bit or 64-bit.

Note: If your operating system is 64-bit, you can install both 32-bit and 64-bit versions of the Exportizer software and use them independently.

Export to PostgreSQL Preparations

The following preparations are recommended, but not required. If you want to dive into the process and understand how it works, do these steps. Anyway, you can skip them and proceed to the Export Steps below; in this case, all the preparations will be done on the fly, explicitly or implicitly.

Launch Exportizer.
Register your source database.
Register your target PostgreSQL database. The recommended interface is FD, but other options are also available. Basically, you specify a server and a port. Other important options are database, vendor library, and charset. Note: You can register the target database from the Export dialog during the exporting.

Export Steps (GUI)

Open the registered source database.
Choose a table or tables to export, or write and execute your SQL query or queries. Please note that exporting multiple datasets at a time is available in Exportizer Enterprise only.
Click Export button or choose a needed item from Export menu.
Switch to the Database tab and select your registered PostgreSQL database as a target database. If you did not register it yet, do it now by clicking the '...' button to the right.
Specify export parameters:
- Turn on the Memory saving mode option and its related options. In case of exporting problems, if you suspect that they can be caused by these options, try different combinations of them. If exported data or column names on any side can contain Unicode characters, set the corresponding stream Encoding, e.g. UTF-8.
- Specify a Table name, i.e. name of the target table. For multi-table exporting, you can leave it empty, otherwise, all source datasets will be exported to one destination table.
- Optionally, specify the target table description (comment). For multi-table exporting, leave it empty; you will able to specify the target descriptions later on the Table Mappings step.
- Choose the Export mode. You can read detailed description for selected export mode to the right (hover mouse over it to see full text). For multi-table exporting, this mode will be applied to the most of the tables, and you can override it for specific tables at the next step. For example, it can be Replace+Insert for the most of tables, and Update or Append+Update for others etc.
- Specify Commit interval. The bigger its value, the faster your exporting process. But too big value may cause memory and other issues. So, try to play with it to find the optimal value for your database before porting the solution to your production environment.
Click Next.

Specify the source-to-target table and/or field mappings.

In field mappings, you create the correspondence between the source and target columns. In addition to source table columns, you can also use calculated fields specified by formulas. It is possible to define full specifications for target columns here, i.e. rename target columns, choose their types, specify NOT NULL constraints, default values, primary key flags, descriptions (comments) etc. These specifications will be applied when the target table needs to be created or overwritten.

In table mappings (for multi-table exporting), you create the correspondence between the source datasets (tables and/or queries) and target tables. Here, you can specify target table names, their descriptions (comments), and nested field mappings for each table pair.

You can skip this step by clicking Export instead of clicking Next. In this case table and/or field mappings will be built automatically, which may be not desirable.
Click Next to proceed to export logs and error handling options or click Export to start the export process immediately.

In case of performance problems: try different combinations of Memory saving mode options and Commit interval option; you might want to read all the data exporting recommendations.

Exporting Data to PostgreSQL from Command Line

In documentation, you can find full Exportizer command line specification. But basic command lines can be built in Exportizer GUI automatically by clicking Build Command Line button. In Export dialog, you can also create an action file using Tools button. Below, you can find a set of command line examples of exporting data to PostgreSQL.

Exporting an Access Table to Existing PostgreSQL Database

"C:\Program Files (x86)\Vitalii Levchenko\Exportizer Enterprise 9\exptizer.exe" /export /ExportType=DATABASE /ExportMode=REPLACE+INSERT /IncludeMemo /SrcDBInterface=ado /SrcDBKind=FILE /SrcDBDriver=Access /SrcDB=C:\TEST\employees.accdb /SrcTableName=BONUSES /TrgDBInterface=FD /TrgDBKind=DSN /TrgDBDriver=POSTGRESQL "/TrgServer=222.333.2.14" /TrgPort=5432 /TrgDB=dwh /TrgDBUserName=dwh_master /TrgDBPassword=dwh_master_password /TrgTableName=bonuses

Exporting in Silent Mode

Let's improve the above example by running it in silent mode, when no windows are shown. Also, let's add a log file to control exporting process:

"C:\Program Files (x86)\Vitalii Levchenko\Exportizer Enterprise 9\exptizer.exe" /silent /export /ExportType=DATABASE /ExportMode=REPLACE+INSERT /IncludeMemo /SrcDBInterface=ado /SrcDBKind=FILE /SrcDBDriver=Access /SrcDB=C:\TEST\employees.accdb /SrcTableName=BONUSES /TrgDBInterface=FD /TrgDBKind=DSN /TrgDBDriver=POSTGRESQL "/TrgServer=222.333.2.14" /TrgPort=5432 /TrgDB=dwh /TrgDBUserName=dwh_master /TrgDBPassword=dwh_master_password /TrgTableName=bonuses /LogFile=C:\Test\export.log

Exporting All Tables from Access File to PostgreSQL database

Exporting all tables from Access file to existing PostgreSQL database in silent mode:

"C:\Program Files (x86)\Vitalii Levchenko\Exportizer Enterprise 9\exptizer.exe" /silent /export /ExportType=DATABASE /ExportMode=REPLACE+INSERT /IncludeMemo /SrcDBInterface=ado /SrcDBKind=FILE /SrcDBDriver=Access /SrcDB=C:\TEST\employees.accdb /SrcTableName=* /TrgDBInterface=FD /TrgDBKind=DSN /TrgDBDriver=POSTGRESQL "/TrgServer=222.333.2.14" /TrgPort=5432 /TrgDB=dwh /TrgDBUserName=dwh_master /TrgDBPassword=dwh_master_password /TrgTableName=* /LogFile=C:\Test\export.log

Notes

These are just basic command lines, and you can improve them by adding other command line parameters in accordance with the documentation. For example, consider explicit using of the field or table mappings by adding the following parameters:

/FieldMappingsFile Specify the file containing field mappings, i.e. the correspondence between the source and target fields/columns, when exporting one dataset.
/TableMappingsFile Specify the file containing table mappings, i.e. the correspondence between the source and target tables, when exporting multiple tables. The table mappings may optionally contain nested field mappings for certain tables.

Alternative Scenario Using SQL

If you have some reason not to export data to PostgreSQL directly, you can export the source data in Exportizer to a SQL script first, and then load data from the script in Exportizer or using an external SQL tool. And, of course, you can export data to SQL script either by GUI or from the command line.

But please note, that when choosing exporting to SQL script, your export process will have two phases: first, exporting the data to SQL script; second, loading data from the script to your target database. So, this way can be less effective, especially for large datasets or when automating the data exporting. Another disadvantage of this way is that BLOB data will not be transferred.

Anyway, you can to try both export scenarios and select the fastest and/or the most convenient one.