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.