Amazon Redshift Federated Query with RDS PostgreSQL

   Go back to the Task List

  « 2: Create Security Group    4. Configure Secret Manager »

3. Launch RDS Instance

You launch Aurora \ PostgreSQL RDS instance which is federated with Redshift.

  1. Goto the RDS Management console and click on the Create database button.

    RDS Aurora

  2. On the next screen, select Standard create for the creation method. Select Amazon Aurora for the engine type. Select Amazon Aurora with PostgreSQL compatibility for the edition. Select Serverless for the capacity type. Keep the version to the default value.

    RDS Aurora

  3. On the same screen, in the settings section, type in dojordsinstance for the identifier, type in postgres for the user name and type in Password1! for the passwords.

    RDS Aurora

  4. On the same screen, in the capacity and connectivity sections, set Aurora Capacity Unit between 2 to 4. Select Default VPC for the VPC. Select Default Subnet Group for the subnet group. For security group, select Choose existing option and select dojo-postresql-sg security group.

    RDS Aurora

  5. On the same screen, select Data API option.

    RDS Aurora

  6. Finally, in the Additional configuration section, type in dojodatabase for the initial database name and click on the Create database button.

    RDS Aurora

  7. The database instance creation starts. Wait till the status changes to Available. Make note of the Endpoint for the database instance. You will need it later when configuring for the federation.

    RDS Aurora

  8. Next, click on the Query Editor menu option in the left.

    RDS Aurora

  9. On the next screen, select dojordsinstance for the cluster. Select Add new database credentials for the database username. Type in postgres as the username and Password1! as the password. Type in dojodatabase as the database name and click on the Connect to database button.

    RDS Aurora

  10. The database is connected. Run the following SQL Statements to create customers table and insert sample data for the table.

    RDS Aurora

    CREATE TABLE customers (
      customerNumber int,
      customerName varchar(50)
    );
    
    insert  into customers(customerNumber,customerName) values 
    (103,'Atelier graphique'), 
    (112,'Signal Gift Stores'), 
    (114,'Australian Collectors'),
    (119,'La Rochelle Gifts'),
    (121,'Baane Mini Imports');
    
  11. The database, table and records are ready. The next step is to configuration RDS credentials in AWS Secrets Manager which are later used for the federation.