Re: Need doc for PostgreSQL RDS External Table

Devendra Postgres <devendrapostgresdba@xxxxxxxxx> · Mon, 23 May 2022 00:27:48 +0530

Thxs Mladen for quick info.

Requirement: My CSV and flat files were there on the S3, and wanted to read the data which Iocated in S3 from the Postgres RDS table. 

Note: Data shouldn’t load into the table(Postgres RDS). 

Pls share any customized script/ doc what ever possible.

Thxs

On Mon, 23 May 2022 at 12:18 AM, Mladen Gogala <gogala.mladen@xxxxxxxxx> wrote:

    On 5/22/22 12:06, Devendra Postgres
      wrote:

      Thanks Mladen,

        Getting this error, when tried to create extension in AWS
          RDS .

        postgres=> create extension file_fdw;

          ERROR:  Extension "file_fdw" is not supported by Amazon RDS

          DETAIL:  Installing the extension "file_fdw" failed, because
          it is not on the list of extensions supported by Amazon RDS.

          HINT:  Amazon RDS allows users with rds_superuser role to
          install supported extensions. See: SHOW rds.extensions;

          postgres=>

        On Sun, May 22, 2022 at 8:23
          PM Mladen Gogala <gogala.mladen@xxxxxxxxx>
          wrote:

            On 5/22/22 10:13, Devendra Postgres wrote:

              Good Morning & Good Evening All,

               As Migration, I need to convert one table
                from Oracle (Source) to Postgres(Target)
              The Oracle Table is created as below: 

              The oracle table is external table. What
                is external table : We will keep  files(CSV/ flat files)
                in drive, we will create a one physical table in oracle
                and when we select * from table, The oracle external
                table will go to the CSV/Flat file and it will pull the
                data from the table and will share results to the user. 

              FYI : it won’t store any physical data in
                the oracle database.

              Requirement : we need similar kind of
                external concept in POSTGRESQL RDS. The data ( CSV/Flat)
                is storing in S3 Bucket.

              Could some one share doc/ URL .

              Thxs,
              Deva

    Deva, RDS is DaaS (Database as a disService). You don't get access
    to the underlying OS. There are certain admin functions which are
    described in the manual, but they don't give you access to anything
    else except pg_hba.conf and postgresql.conf. You will have to load
    CSV files from your local psql using \copy command or a custom
    script, preferably using DBD::Pg. And remember, always use strict.

    -- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com