47 lines
1.6 KiB
Python
47 lines
1.6 KiB
Python
# coding=utf-8
|
|
|
|
"""
|
|
python pg_transfer_db.py "host=test_src.postgres.database.azure.com port=5432 dbname=postgres user=test@test_src password=xxxx sslmode=require" test_table "host=test_dest.postgres.database.azure.com port=5432 dbname=postgres user=test@test_dest password=xxxx sslmode=require" test_table 8 411187501
|
|
|
|
手动分数据，并行调用 copy 命令实现加速数据迁移
|
|
"""
|
|
|
|
import os
|
|
import sys
|
|
|
|
|
|
def transfer():
|
|
#source info
|
|
source_url = sys.argv[1]
|
|
source_table = sys.argv[2]
|
|
|
|
#dest info
|
|
dest_url = sys.argv[3]
|
|
dest_table = sys.argv[4]
|
|
|
|
#others
|
|
total_threads=int(sys.argv[5]);
|
|
size=int(sys.argv[6]);
|
|
|
|
interval=size/total_threads;
|
|
start=0;
|
|
end=start+interval;
|
|
|
|
for i in range(0,total_threads):
|
|
if(i!=total_threads-1):
|
|
select_query = '\"\COPY (SELECT * from ' + source_table + ' WHERE id>='+str(start)+' AND id<'+str(end)+") TO STDOUT\"";
|
|
read_query = "psql \"" + source_url + "\" -c " + select_query
|
|
write_query = "psql \"" + dest_url + "\" -c \"\COPY " + dest_table +" FROM STDIN\""
|
|
os.system(read_query+'|'+write_query + ' &')
|
|
else:
|
|
select_query = '\"\COPY (SELECT * from '+ source_table +' WHERE id>='+str(start)+") TO STDOUT\"";
|
|
read_query = "psql \"" + source_url + "\" -c " + select_query
|
|
write_query = "psql \"" + dest_url + "\" -c \"\COPY " + dest_table +" FROM STDIN\""
|
|
os.system(read_query+'|'+write_query)
|
|
start=end;
|
|
end=start+interval;
|
|
|
|
if __name__ == "__main__":
|
|
transfer()
|
|
|