![]() Now, use the following command to enable the PostgreSQL FDW extension: main_fdw_db=# create extension postgres_fdw postgres=# CREATE DATABASE main_fdw_db postgres=# \c main_fdw_db Once you get into the CLI for the Master RDS instance, if you want to create the FDW setup in a new database, you will have to create the new database and switch to the new database before configuring the extension as extension is enabled only for the specific databases. ![]() Step 2: Configure Postgresql FDW extension in required database. Using following command we can get into the Database console: psql -host= -port=5432 -username= -db= -password Use following commands to install the PostgreSQL client: sudo apt-get install -y postgresql-client You should have a PostgreSQL client to get into the console. If not, then you might want to provision a separate EC2 instance through within the same VPC and subnet and provide access for EC2 instance to the Master RDS. If its public, you can access it from your own local machine by allowing your IP in the security group of the Master RDS instance. Your Master RDS instance might or might not be accessible from public. Once this is done, we need to configure in RDS by going into the console of Master RDS instance. If all of our RDS instances are in same VPC and same subnets, the only thing that we need to allow is access of Master RDS instance in the Security group of Service RDS instance. Make sure that master RDS instance is able to access the required service RDS instance. Now get into how we weer able to achieve this solution.įor the sake of the simplicity, we will consider that all of our RDS instances are in same VPC and the master RDS instance is allowed to access the RDS instances of services. Separating out the databases to separate RDS instance would break the ability of JOINS across the different databases.įig 1: PostgreSQL FDW solution architecture Querying from data lake was not possible as data pipeline was not ready. The problem that we had with both of these tasks: creation of data pipeline and migration of databases to separate RDS instances was that analytics team was already using the core RDS instance for generating different business insights for decision making as well as reporting. At the same time we wanted to migrate the every databases used within the organizational teams to a separate RDS instances in order to reduce the load on single RDS instance. We were trying to build a data pipeline and data lake in AWS which would take some time. Setting up Foreign Data Wrapper (FDW) within AWS PostgreSQL RDS instances.Ībout 3 weeks prior of writing this post I was completely unaware of the fact that PostgreSQL had this Foreign Data Wrapper (FDW) extension that can be used to connect multiple PostgreSQL databases into a single database.
0 Comments
Leave a Reply. |