Transfer data from redshift to postgresql

Question

I tried searching for it but couldn't find out

What is the best way to copy data from Redshift to Postgresql Database ?

using Talend job/any other tool/code ,etc

anyhow i want to transfer data from Redshift to PostgreSQL database also,you can use any third party database tool if it has similar kind of functionality.

Also,as far as I know,we can do so using AWS Data Migration Service,but not sure our source db and destination db matches that criteria or not

Can anyone please suggest something better ?

Answer 1

The way I do it is with a Postgres Foreign Data Wrapper and dblink,

This way, the redshift table is available directly within Postgres.

Follow the instructions here to set it up https://aws.amazon.com/blogs/big-data/join-amazon-redshift-and-amazon-rds-postgresql-with-dblink/

The important part of that link is this code:

CREATE EXTENSION postgres_fdw;
CREATE EXTENSION dblink;
CREATE SERVER foreign_server
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (host '<amazon_redshift _ip>', port '<port>', dbname '<database_name>', sslmode 'require');
CREATE USER MAPPING FOR <rds_postgresql_username>
        SERVER foreign_server
        OPTIONS (user '<amazon_redshift_username>', password '<password>');

For my use case I then set up a postgres materialised view with indexes based upon that.

create materialized view if not exists your_new_view as
SELECT some,
       columns,
       etc
   FROM dblink('foreign_server'::text, '
<the redshift sql>
'::text) t1(some bigint, columns bigint, etc character varying(50));

create unique index if not exists index1
    on your_new_view (some);

create index if not exists index2
    on your_new_view (columns);

Then on a regular basis I run (on postgres)

REFRESH MATERIALIZED VIEW your_new_view;

or

REFRESH MATERIALIZED VIEW CONCURRENTLY your_new_view;