GIS analyst with minimal experience of this back-end stuff but a big task at hand. I have a redshift database that changes multiple times a day. I want to use this "live" data to make web maps and I have a Postgres DB where all my web-mapping data is stored. I want to continuously dump data from the ever-changing Redshift DB to my maps. What is the simplest way to do this? I have experience with SQL so if that's the route it would be helpful.
Your best bet is probably to use the dblink feature of PostgreSQL to create a connection to Redshift and query the necessary data from Redshift to add to your PostgreSQL instance.
Here is an example using RDS PostgreSQL, but I think you would probably be able to get this to work with an EC2 hosted PostgreSQL instance as well:
Note the use of pgbouncer-rr in the example is optional, you will probably not need this.
There's two ways you can do this:
You can the UNLOAD command to unload data from Redshift into S3 and then load it back into your Postgres DB. However, you'd have to do a full data dump, and that's not very elegant.
The other option is to use Amazon RDS with dblink. That's the approach we use ourselves (we run an analytical application on Redshift). Full write up on how we did it and how it works available here: