psycopg2/python copy data from postgresql to Amazon RedShift(postgresql)

You can use Python/psycopg2/boto to code it end-to-end. Alternative to psycopg2 is PosgtreSQL client (psql.exe).

If you use psycopg2 you can:

1. Spool to a file from PostgreSQL
2. Upload to S3
3. Append to Redshift table.

If you use psql.exe you can:

1. Pipe data to S3 multipart uploader from PostgreSQL

   in_qry=open(opt.pgres_query_file, "r").read().strip().strip(';')
   db_client_dbshell=r'%s\bin\psql.exe' % PGRES_CLIENT_HOME.strip('"')
   loadConf=[ db_client_dbshell ,'-U', opt.pgres_user,'-d',opt.pgres_db_name, '-h', opt.pgres_db_server]
   
   q="""
   COPY ((%s) %s)
   TO STDOUT
   WITH DELIMITER ','
   CSV %s
   """ % (in_qry, limit, quote)
   #print q
   p1 = Popen(['echo', q], stdout=PIPE,stderr=PIPE,env=env)
   
   p2 = Popen(loadConf, stdin=p1.stdout, stdout=PIPE,stderr=PIPE)
   
   p1.wait()
   return p2
   

2. Upload to S3.

3. Append to Redshift table using psycopg2.

   fn='s3://%s' % location
   conn_string = REDSHIFT_CONNECT_STRING.strip().strip('"')    
   con = psycopg2.connect(conn_string);
   cur = con.cursor(); 
   quote=''
   if opt.red_quote:
       quote='quote \'%s\'' % opt.red_quote
   ignoreheader =''
   if opt.red_ignoreheader:
       ignoreheader='IGNOREHEADER %s' % opt.red_ignoreheader
   timeformat=''
   if opt.red_timeformat:
       #timeformat=" dateformat 'auto' "
       timeformat=" TIMEFORMAT '%s'" %     opt.red_timeformat.strip().strip("'")
   sql="""
   COPY %s FROM '%s' 
   CREDENTIALS 'aws_access_key_id=%s;aws_secret_access_key=%s' 
   DELIMITER '%s' 
   FORMAT CSV %s 
   GZIP 
   %s 
   %s; 
   COMMIT;
   """ % (opt.red_to_table, fn, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY,opt.red_col_delim,quote, timeformat, ignoreheader)
   cur.execute(sql)    
   con.close()
   

I did my best compiling all 3 steps into one script.