Data Migration in PostgreSQL and ADempiere, Open Source ERP

Hi Everyone,

There are times when you need to move high volumes of data between ADempiere PostgreSQL databases. An example includes copying business partner records to a soon-to-be production database. The question is: “what is the fastest way to move the contents of Table A in Database A to Table A in Database B?”.

If Table A in Database B already has lots of records, the delete process can be agonizingly slow. The reason is that the database has to ensure no foreign key constraints are violated.

You can try dropping Table A in Database B; however, you will be forced to use the CASCADE keyword to remove all dependencies. Using cascade to remove dependencies will automatically DROP dependent VIEWS and CONSTRAINTS. This is bad!!

An alternative method is to remove the constraints before you delete the old data and insert the new data. The below PostgreSQL queries illustrate how to automate this process by:

1. Listing all the constraints that point to your table.
2. Building the ALTER TABLE ADD sql command for each constraint.
3. Building the ALTER TABLE DROP sql command for each constraint.

–ADD foreign keys back

select 'ALTER TABLE adempiere.'|| tc.table_name ||' add constraint '|| tc.constraint_name ||' FOREIGN KEY ('|| kcu.column_name ||') References adempiere.'||ccu.table_name||' ('|| ccu.column_name || ');'
FROM 
 information_schema.table_constraints AS tc 
 JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
 JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
WHERE ccu.table_name='r_request'
 and tc.constraint_type = 'FOREIGN KEY';

–Example output from the above query

ALTER TABLE adempiere.ad_issue add constraint rrequest_adissue FOREIGN KEY (r_request_id) References adempiere.r_request (r_request_id);
ALTER TABLE adempiere.r_request add constraint rrequest_related FOREIGN KEY (r_requestrelated_id) References adempiere.r_request (r_request_id);
ALTER TABLE adempiere.r_issueknown add constraint rrequest_rissueknown FOREIGN KEY (r_request_id) References adempiere.r_request (r_request_id);
ALTER TABLE adempiere.r_requestaction add constraint rrequest_rrequestaction FOREIGN KEY (r_request_id) References adempiere.r_request (r_request_id);
ALTER TABLE adempiere.r_requestupdate add constraint rrequest_rrequestupdate FOREIGN KEY (r_request_id) References adempiere.r_request (r_request_id);
ALTER TABLE adempiere.r_requestupdates add constraint rrequest_rrupdates FOREIGN KEY (r_request_id) References adempiere.r_request (r_request_id);

–DROP foreign keys

select 'ALTER TABLE adempiere.'|| tc.table_name ||' Drop Constraint '||tc.constraint_name||';'
FROM 
 information_schema.table_constraints AS tc 
 JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
 JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
WHERE ccu.table_name='r_request'
 and tc.constraint_type = 'FOREIGN KEY';

— Example output from the above query

ALTER TABLE adempiere.ad_issue Drop Constraint rrequest_adissue;
ALTER TABLE adempiere.r_request Drop Constraint rrequest_related;
ALTER TABLE adempiere.r_issueknown Drop Constraint rrequest_rissueknown;
ALTER TABLE adempiere.r_requestaction Drop Constraint rrequest_rrequestaction;
ALTER TABLE adempiere.r_requestupdate Drop Constraint rrequest_rrequestupdate;
ALTER TABLE adempiere.r_requestupdates Drop Constraint rrequest_rrupdates;

Once you have copied the results to both queries to a text editor, you can execute the results of the DROP CONSTRAINT sql statement. Once the constraints are gone, the database is able to much more quickly remove the old records.

Use PG_Dump to export the contents of Table A in Database A to an uncompressed text file. Use the options ‘-t’ to export a specific table and ‘-a’ for data only (removes any DDL that modifies the table itself). Use PSQL to pipe the new data into database B.

Once all the data has been entered into Database B, add back the constraints. Please be aware that adding constraints can take a while since the database has to ensure that all constraints are respected and valid.

For reference, here is the general purpose query:

SELECT
 tc.constraint_name, tc.table_name, kcu.column_name, 
 ccu.table_name AS foreign_table_name,
 ccu.column_name AS foreign_column_name 
FROM 
 information_schema.table_constraints AS tc 
 JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
 JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
WHERE ccu.table_name='r_request';

About Chuck Boecking: I am an ERP educator. I believe that open source ERP have achieved mainstream capabilities, and as a result, more companies can create greater efficiency across their organization. I started using the iDempiere code base in 2003. Back then, it was called Compiere. In 2006, I started my first multi-million dollar installation. Since then, ADempiere has helped me create great success with distribution and manufacturing companies all over the world. My vision of success is to find companies that can best use open source ERP to help them achieve a single, global instance that drives a discontinuous increase in profitability. I believe that organizations win when they own their technology.
If you have questions, comments or concerns, let me know. I definitely want your feedback.
You can contact me by phone using 512.850.6068.
My email is chuck@chuboe.com.
You can complete the form on this page.
Thank you for taking the time. I look forward to speaking with you.
Regards,

Chuck Boecking

http://www.linkedin.com/pub/chuck-boecking/10/970/17b