Subject: Steps to Install archived Postgres 9.3 and 9.6

From: aditya desai <[email-masked]>
Date: Fri, 20 May 2022 21:15:04 +0530

Hi,
We have a requirement where we need to migrate Oracle to Postgres. However
the current version of Application JIRA is 6 and it is not compatible with
Postgres 11. We need to do an incremental upgrade like below.
1. Migrate Oracle 11g to Postgres 9.3.
2. Upgrade JIRA 6 to 7.
3. Upgrade Postgres 9.3 to 9.6.
4. Upgrade JIRA 7 to 7.x
5. Upgarde Postgres 9.6 to 11.
6. Upgrade JIRA 7.x to 8.x

Does anyone have steps to install PG 9.3 and 9.6. I know it is not
supported anymore but we are stuck with this approach. Please help :(

Regards,
Aditya.

---

From: aditya desai <[email-masked]>
Date: Sat, 21 May 2022 08:55:40 +0530

Thanks all. This really helps. Will let you know if any issues.

On Sat, May 21, 2022 at 4:06 AM S Hoffman <[email-masked]> wrote:

> Hello Aditya,
>
> A link to the archive manuals is below. Each manual contains the
> instructions for the archived version. Version 9.3 is available.
>
> https://www.postgresql.org/docs/manuals/archive/
>
> I hope this helps!
>
> Thank you,
> Sondra
>
> Sent from my iPhone
>
> On May 20, 2022, at 8:45 AM, aditya desai <[email-masked]> wrote:
>
> 
> Hi,

---

From: Holger Jakobs <[email-masked]>
Date: Sat, 21 May 2022 20:53:41 +0200

Are you really sure that your application software (Jira) is 
incompatible with current PostgreSQL versions?

Please make a test whether it works. It might very well do.

In most cases, not only the approved PostgreSQL versions work fine with 
application software, but also all younger versions.

Backward compatibility is very high.

Regards,

Holger

Am 21.05.22 um 05:25 schrieb aditya desai:
>>     We have a requirement where we need to migrate Oracle to
>>     Postgres. However the current version of Application JIRA is 6
>>     and it is not compatible with Postgres 11. We need to do an
>>     incremental upgrade like below.
>>     Does anyone have steps to install PG 9.3 and 9.6. I know it is
>>     not supported anymore but we are stuck with this approach. Please
>>     help :(
>>
>>     Regards,
>>     Aditya.
>
-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

---

From: aditya desai <[email-masked]>
Date: Sun, 22 May 2022 13:39:26 +0530

Hi Jakobs,
Yes JIRA version itself is EOL.

Regards,
Aditya.

On Sun, May 22, 2022 at 12:23 AM Holger Jakobs <[email-masked]> wrote:

> Are you really sure that your application software (Jira) is incompatible
> with current PostgreSQL versions?
>
>> We have a requirement where we need to migrate Oracle to Postgres.
>> However the current version of Application JIRA is 6 and it is not
>> compatible with Postgres 11. We need to do an incremental upgrade like
>> below.

---

From: Devendra Postgres <[email-masked]>
Date: Sun, 22 May 2022 19:43:47 +0530

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

---

From: Mladen Gogala <[email-masked]>
Date: Sun, 22 May 2022 10:53:38 -0400

On 5/22/22 10:13, Devendra Postgres wrote:
> 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.
>
> Requirement : we need similar kind of external concept in POSTGRESQL 
> RDS. The data ( CSV/Flat) is storing in S3 Bucket.
>
Well, the same feature in Postgres is called file_fdw. The documentation 
is here: https://www.postgresql.org/docs/14/file-fdw.html

Here is an example: 
https://dbaclass.com/article/how-to-access-csv-files-on-file-system-using-file_fdw_in_postgres/

Good luck!

PS:

I hate foreign tables in Oracle and I don't use them in Postgres. You 
cannot index them and about the only thing you can do with them is 
sequential scan. The problem with that is that the larger file gets, the 
more time it takes to process it. External tables in Oracle were meant 
as a shortcut for loading them, bypassing SQL*Loader. You are the first 
person in my 30 years long career of an Oracle architect/DBA/consultant 
who uses external tables.

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

---

From: Devendra Postgres <[email-masked]>
Date: Sun, 22 May 2022 21:36:21 +0530

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 <[email-masked]>
wrote:

> more time it takes to process it. External tables in Oracle were meant as a
> shortcut for loading them, bypassing SQL*Loader. You are the first person
> in my 30 years long career of an Oracle architect/DBA/consultant who uses
> external tables.
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>
>

---

From: Mladen Gogala <[email-masked]>
Date: Sun, 22 May 2022 14:47:59 -0400

On 5/22/22 12:06, Devendra Postgres wrote:
> 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 
> <[email-masked]> wrote:
>
>>      As Migration, I need to convert one table from Oracle (Source)
>>     to Postgres(Target)
>>     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.
>>
>>     Requirement : we need similar kind of external concept in
>>     POSTGRESQL RDS. The data ( CSV/Flat) is storing in S3 Bucket.
>>
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.

---

From: Devendra Postgres <[email-masked]>
Date: 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 <[email-masked]>
wrote:

>> 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

---

From: Scott Ribe <[email-masked]>
Date: Sun, 22 May 2022 13:25:26 -0600

> On May 22, 2022, at 12:57 PM, Devendra Postgres <[email-masked]> wrote:
> 
> 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. 
> 

If you are going to use RDS, you will be limited to what Amazon provides--meaning you will have to load the data into a table:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PostgreSQL.S3Import.html

---

From: Doug Reynolds <[email-masked]>
Date: Sun, 22 May 2022 22:17:53 +0000

AWS RDS for PostgreSQL has an extension to read/write from S3.  

Contact your AWS TAM if you need help with the AWS specific module.

> On May 22, 2022, at 3:25 PM, Scott Ribe <[email-masked]> wrote:
> 
> 
>>

---

From: Devendra Postgres <[email-masked]>
Date: Mon, 23 May 2022 06:25:18 +0530

Thank you Doug, Will contact AWS Support.

Cheers,

On Mon, May 23, 2022 at 3:47 AM Doug Reynolds <[email-masked]> wrote:

> 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.
>
>
> If you are going to use RDS, you will be limited to what Amazon
> provides--meaning you will have to load the data into a table:
>
>

---

From: Laurenz Albe <[email-masked]>
Date: Mon, 23 May 2022 17:17:46 +0200

> Yes JIRA version itself is EOL.

Then do yourself and your users a favor and upgrade to a recent
release of JIRA, which supports at least PostgreSQL v13.

Yours,
Laurenz Albe

---

From: aditya desai <[email-masked]>
Date: Mon, 23 May 2022 23:17:45 +0530

That is the plan! But the current version of JIRA is 6.x and to upgrade it
we have to do incremental Postgres upgrade.