CODEWITHSUNDEEP
postgresqlamazon-web-servicesmigrationamazon-redshiftdata-transfer
LKk
LKk

Reputation: 73

Transfer data from redshift to postgresql

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 ?

Upvotes: 2

Views: 6455

Answers (2)

John Rotenstein
John Rotenstein

Reputation: 269091

In the past, I managed to transfer data from one PostgreSQL database to another by doing a pg_dump and piping the output as an SQL command to the second instance.

Amazon Redshift is based on PostgreSQL, so this method should work, too.

You can control whether pg_dump should include the DDL to create tables, or whether it should just load the data (--data-only).

See: PostgreSQL: Documentation: 8.0: pg_dump

Upvotes: 1

Jon Scott
Jon Scott

Reputation: 4354

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;

Upvotes: 7

Related Questions