How to access data of Redshift cluster from PostgreSQL RDS

Setting up DBLink

  • First we need to enable the DBLink extensions on the PostgreSQL RDS instance.
  • Next step is the main step which creates the link to the Amazon Redshift cluster and PostgreSQL RDS instance.
  • To achieve this we need to create the server link and the user mapping
  • Please note that all the values that are enclosed within <angle brackets> need to be replaced with actual values from your environment.
  • You might have to use the private IP address of your Redshift cluster. It totally depends on your setup.
  • Now that the DBLink is created, its time to query the Redshift cluster from the PostgreSQL to check if everything is setup correctly
  • Everything enclosed within $REDSHIFT$ is the actual query that runs on Redshift and results are sent back to PostgreSQL.
  • To create the cache of the Redshift data, we need to create a materialised view.
  • Query the view just like you query any other view.
  • To refresh the view, use the following code

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store