View source | View content page | Page history | Printable version   

Projects:Import Export Sample Data Using PostgreSQL Copy

Contents

Introduction

One requirement of the Multiserver project is to be able to, given a central server, be able to export the data relative to a given store. The ExportStoreData [1] class takes care of this, and it does it by extending the ExportSampledata class, which is part of dbsourcemanager.

Given the big size of the exported data, some changes are required in ExportSampledata to make it suitable to be extended to export stores. Mainly there are three points that needed to be modified in order to support the big volumes:

Tracking issue: https://issues.openbravo.com/view.php?id=33875

[1] https://code.openbravo.com/erp/pmods/org.openbravo.retail.storeserver.synchronization/file/dc57a9fb7c64/src/org/openbravo/retail/storeserver/synchronization/task/ExportStoreData.java

PostgreSQL's COPY

PostgreSQL offers a way to import and export the contents of a table directly to a file using the COPY command [1].

For instance, this command can be invoked to export the rows of the F&B client of c_uom to a file:

COPY (SELECT * FROM c_uom WHERE ad_client_id = '23C59575B9CF467C9620760EB255B389') TP '/tmp/c_uom.copy' WITH (FORMAT CSV, HEADER true)

To import it a copy file to the database this command can be invoked:

COPY c_uom FROM '/tmp/c_uom.copy' WITH (FORMAT CSV, HEADER true)

[1] https://www.postgresql.org/docs/9.5/static/sql-copy.html

The standard format split the column values with tabs and uses \N to represent null values, while the CSV format uses a comma to split the column values, as does not use any character to replace null values. This results in the files exported with the CSV format being smaller. The size difference is proportional to the number of null values in the exported table.

It is possible to exclude some columns from being exported. This makes necessary to include the HEADER parameter, so that the full list of exported column is present in the exported file, and can be used to import it in the target database.

How to export the sample data using PostgreSQL's COPY

To export the sample data using PostgreSQL's COPY, the exportFormat ant parameter must be included with the value 'copy', for instance:

ant export.sample.data -Dclient="F&B International Group" -Dmodule=org.openbravo -DexportFormat=copy

How to import the sample data using PostgreSQL's COPY

There is no need to do any extra steps to import export that that has been exported in the copy format. The ImportSampledata task will know how to import the data based on the file extension.

If the database where the data is being imported is Oracle, the .copy files will be ignored.

QA

Retrieved from "http://wiki.openbravo.com/wiki/Projects:Import_Export_Sample_Data_Using_PostgreSQL_Copy"

This page has been accessed 1,481 times. This page was last modified on 2 December 2016, at 07:43. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.