Hi,
I'll try to answer all your question so that you will have more information about the situation :
I have one main table that is called main_table_hist. The "main_table _hist" is partitioned by range (date column) and includes data that is considered as "history data" . I'm trying to copy the data from the oracle table to my local postgresql table (about 5T). For every day in the year I have in the oracle table partition and therefore I will create for every day in year (365 in total) a partition in postgresql. Every partition of day consist of 4 different partitions by list (text values). So In total my tables hierarchy should look like that :
main_table_hist
14/08/2018_main
14/08/2018_value1
14/08/2018_value2
14/08/2018_value3
14/08/2018_value1
Moreover, I have another table that is called "present_data" that consist of 7 partitions (the data of the last 7 days - 300G) that I'm loading from csv files (daily). Every night I need to deattach the last day partition and attach it to the history table.
This hierarchy works well in oracle and I'm trying to build it on postgresql. Right now I'm trying to copy the history data from the remote database but as I suggested it takes 10 hours for 200G.
Some details :
-Seting the wals to minimum is possible but I cant do that as a daily work around because that means restarting the database.
I must have wals generated in order to restore the "present_data" in case of disaster.
-The network
-My network bandwidth is 1GB.
-The column in the table are from types : character varying,big int,timestamp,numeric. In other words no blobs.
-I have many check constraints on the table.
- Laurenz - "You could try a bigger value for the "prefetch" option."- Do you have an example how to do it ?
-Inserting directly into the right parittion might increase the preformance ?
Thanks , Mariel.