How to copy the gpperfmon data from DCA1 to DCA2

Post date: Jan 15, 2016 6:53:59 PM

Task:  copy the gpperfmon V1 data from DCA1 V1 to DCA2  V2.  Both DCA's are accessible from a commodity server.

Here are the very simple steps. Since those tables are not big so we used power of copy commands.

1. Updated the .bashrc and created two aliases psqldca1gpperfmon and psqldca2gpperfmon

2. Connect to DCA2 V2 and create V1 tables which you wanted to move. For example

create table system_history_v1 as select * from system_history where 1=2;

create table database_history_v1 as select * from database_history where 1=2;

create table emcconnect_history_v1 as select * from emcconnect_history where 1=2;

create table queries_history_v1 as select * from queries_history where 1=2;

create table log_alert_history_v1 as select * from log_alert_history where 1=2;

create table diskspace_history_v1 as select * from diskspace_history where 1=2;

 3. Connect to DCA1 V1 and execute the following command.

 psqldca1gpperfmon -c "copy public.system_history to stdout " | psqldca2gpperfmon -c "copy system_history_v1 from stdin";

 psqldca1gpperfmon -c "copy public.queries_history  to stdout " | psqldca2gpperfmon -c "copy queries_history_v1 from stdin";

 psqldca1gpperfmon -c "copy public.database_history to stdout " | psqldca2gpperfmon -c "copy database_history_v1 from stdin";

 psqldca1gpperfmon -c "copy public.emcconnect_history to stdout " | psqldca2gpperfmon -c "copy emcconnect_history_v1 from stdin";

 psqldca1gpperfmon -c "copy public.log_alert_history to stdout " | psqldca2gpperfmon -c "copy log_alert_history_v1 from stdin";

 psqldca1gpperfmon -c "copy public.diskspace_history to stdout " | psqldca2gpperfmon -c "copy diskspace_history_v1 from stdin";

Note: Same procedure can be used to copy small tables from one database to another database on the same server or different server.  DO NOT USE this approach for large tables. Use external table approach, parallel backup/restore, gptransfer utility for large tables.