Subject: Re: postgresql-17.0-1 Application - silent installation Issue

From: Rob Sargent <[email-masked]>
Date: Thu, 14 Nov 2024 08:20:50 -0700

> 
> Please transfer the request who works with windows. We need the solution asap.
> 
> Thanks & Regards,
> Urvi Jolapara
> [email-masked]
> 

If a windows admin/user shows up they may be better able to help.  There is no one to "transfer" to. 
The list of missing keys suggests to me that the installation didn't do go well. Whose installer did you use?

> www.safran-group.com
> 
> -----Original Message-----
> From: Adrian Klaver <[email-masked]>
> Sent: Tuesday, November 12, 2024 10:06 PM
> To: JOLAPARA Urvi (SAFRAN) <[email-masked]>; [email-masked]
> Cc: KRISHNAN LINGATHAR Karupaswamy (SAFRAN) <[email-masked]>
> Subject: Re: postgresql-17.0-1 Application - silent installation Issue
> 
> CAUTION:  This message originated from an outside organization. In case of suspicion, click on "Report to SAFRAN Security" from the Outlook ribbon.
> 
>> On 11/11/24 22:09, JOLAPARA Urvi (SAFRAN) wrote:
>> C2 - Confidential
> 
> This is a publicly readable list, the above has no meaning in that context.
> 
>> 
>> Hello Klaver,
>> 
>> We are using PSADT for creating a script and installing through SCCM.
> 
> 1) I don't work with Windows so I have no idea what the above means.
> 
> 2) You did not answer the rest of the questions I asked in my previous post.
> 
> a) "we tried giving permission to this folder, then we have used"
> 
> Which directory(folder) would that be?
> 
> b) ' *“--installer-launage fr”* parameter *“--locale fr”* parameters but ...'
> 
> Should that not be --installer-language?
> 
> Also shouldn't this:
> 
> --locale fr
> 
> be?:
> 
> --locale fr-FR
> 
> 
>> And when we are installing Through Software center it is throwing error as below:
>> 
>> "There has been an error
>> error while writing file C:\windows\Temp\postgresql_installer_8b85d458af\temp_check_comspec.bat"
> 
> 
> I would suggest looking in the Windows system log to see if it provides more information for the above error.
> 
>> 
>> Thanks & Regards,
>> Urvi Jolapara
>> [email-masked]
>> 
>> https://urldefense.com/v3/__http://www.safran-group.com__;!!P3IToRM6tg
>> !i7_r9jHMtJfM4PYV1cyvZR9mQYp6lntjOt9T-GA9pRPIUtgmhYYAOML3XIg9yKfkczYXx
>> mYTan6MAGcfZ9TX8oUAU_sEIeqE$
> 
> --
> Adrian Klaver
> [email-masked]
> 
> #
> " Ce courriel et les documents qui lui sont joints peuvent contenir des informations confidentielles, être soumis aux règlementations relatives au contrôle des exportations ou ayant un caractère privé. S'ils ne vous sont pas destinés, nous vous signalons qu'il est strictement interdit de les divulguer, de les reproduire ou d'en utiliser de quelque manière que ce soit le contenu. Toute exportation ou réexportation non autorisée est interdite Si ce message vous a été transmis par erreur, merci d'en informer l'expéditeur et de supprimer immédiatement de votre système informatique ce courriel ainsi que tous les documents qui y sont attachés."
> ******
> " This e-mail and any attached documents may contain confidential or proprietary information and may be subject to export control laws and regulations. If you are not the intended recipient, you are notified that any dissemination, copying of this e-mail and any attachments thereto or use of their contents by any means whatsoever is strictly prohibited. Unauthorized export or re-export is prohibited. If you have received this e-mail in error, please advise the sender immediately and delete this e-mail and all attached documents from your computer system."
> #

---

From: Bharani SV-forum <[email-masked]>
Date: Fri, 15 Nov 2024 19:46:18 +0000 (UTC)

Team
Need exact SQL query to find List of Detach Partitioned Tables (Yet to be Dropped)
The following is the query which i used, i am using and i found an bug which is listing an newly created table (last week)
SELECT relnamespace::regnamespace::text AS schema_name, relname AS table_name
FROM   pg_class c
WHERE  NOT relispartition  -- !
AND    relkind = 'r' and lower(relnamespace::regnamespace::text) not in ('pg_catalog','partman','information_schema')  and
lower(relnamespace::regnamespace::text) in ('XYZ') 
order by  relnamespace::regnamespace::text, relname ;

---

From: "David G. Johnston" <[email-masked]>
Date: Fri, 15 Nov 2024 12:56:54 -0700

> Need exact SQL query to find List of Detach Partitioned Tables (Yet to be
The premise that a detached table is distinguishable from any other table
that is not a partition is an interesting one that I wouldn't necessarily
expect to be determinable.  You could probably write a query listing tables
that are candidates for attaching to an existing partitioned table and then
filter out those that are actually attached.

David J.

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 15 Nov 2024 13:13:30 -0800

On 11/15/24 11:46, Bharani SV-forum wrote:
> The following is the query which i used, i am using and i found an bug 
> which is listing an newly created table (last week)

As David G. Johnston said how would you know it was formally a partition?:

https://www.postgresql.org/docs/current/sql-altertable.html

"
DETACH PARTITION partition_name [ CONCURRENTLY | FINALIZE ]

     This form detaches the specified partition of the target table. The 
detached partition continues to exist as a standalone table, but no 
longer has any ties to the table from which it was detached.

[...]
"

The only I could see this working is if you had a standard naming scheme 
for partitions and then you could do a regex search in pg_class for that 
pattern where relkind = 'r'.

> AND    relkind = 'r' and lower(relnamespace::regnamespace::text) not in 
> ('pg_catalog','partman','information_schema')  and

---

From: Bharani SV-forum <[email-masked]>
Date: Mon, 2 Dec 2024 22:18:04 +0000 (UTC)

TeamPl Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X 

Env = EC2 based Community PostgreSQL Ver 13.16.2 

we will be performing upgrade of our EC2 server too along with new OS.
Need help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X 
 ASIS-existing server = EC2 with Community PostgreSQL Ver 13.16.2- ensure to capture all the pre.req meant for ver 15.10 are being met.
- shutdown db.- take offline full backup (PG_DATA folder alone)  using OS command
Proposed-new EC2 server (with new Operating System version along Postgres Ver 15.10 Binaries)- install postgres 15.10 binaries- ensure to DISABLE auto startup and shutdown of postgres 15.10-  Restore offline full backup (PG_DATA folder alone) using OS command
-  start performing pg_upgrade step to upgrade postgres from ver 13.16.2 to 15.10
please guide me, if i have missed any steps in the abovesaid process

To start new DB features, planning to rollout out the following feature's alone
a) TLE extension for password complianceb) parallelize vacuum jobs to utilize -j option

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 2 Dec 2024 14:25:14 -0800

On 12/2/24 14:18, Bharani SV-forum wrote:
> Team
> Pl Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 
> 15.X
> 
> *ASIS-existing server = EC2 with Community PostgreSQL Ver 13.16.2*
> - ensure to capture all the pre.req meant for ver 15.10 are being met.
> - shutdown db.
> - take offline full backup (PG_DATA folder alone)  using OS command
> 
> *Proposed-new EC2 server (with new Operating System version along 
> Postgres Ver 15.10 Binaries)*
> - install postgres 15.10 binaries
> - ensure to DISABLE auto startup and shutdown of postgres 15.10
> -  Restore offline full backup (PG_DATA folder alone) using OS command
> -  start performing pg_upgrade step to upgrade postgres from ver 13.16.2 

This is not going to work, you need to have Postgres 13 installed also.

It all laid out here:

https://www.postgresql.org/docs/15/pgupgrade.html

In Usage section.

> to 15.10
> a) TLE extension for password compliance
> b) parallelize vacuum jobs to utilize -j option
>

---

From: Ron Johnson <[email-masked]>
Date: Mon, 2 Dec 2024 17:31:40 -0500

On Mon, Dec 2, 2024 at 5:18 PM Bharani SV-forum <[email-masked]>
wrote:

> *Proposed-new EC2 server (with new Operating System version along Postgres
> Ver 15.10 Binaries)*

To migrate from one server to another while upgrading, one must use
pg_dump/pg_restore OR Logical Replication.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 2 Dec 2024 14:46:10 -0800

On 12/2/24 14:31, Ron Johnson wrote:
> On Mon, Dec 2, 2024 at 5:18 PM Bharani SV-forum 
> <[email-masked] <mailto:[email-masked]>> wrote:
> 
>     Team
>     Pl Help in vetting my steps for Postgres DB upgrade from Ver 13.X to
>     ver 15.X
> 

Really?

Then this:

https://www.postgresql.org/docs/current/pgupgrade.html

must be random nose.

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 2 Dec 2024 14:48:01 -0800

> https://www.postgresql.org/docs/current/pgupgrade.html

Oh yeah, that was smooth.

Second attempt:

... must be random noise.

---

From: Bharani SV-forum <[email-masked]>
Date: Mon, 2 Dec 2024 23:41:20 +0000 (UTC)

Ron/AdrianThanks for your input.Your suggestion is
option#1 ASIS-existing server = EC2 with Community PostgreSQL Ver 13.16.2*-  ensure to capture all the pre.req meant for ver 15.10 are being  met.
     - enable logical replication tagged to proposed new EC2 server (with newer Higher OS Version ).

Proposed-new EC2 server (with new Operating System version along
    Postgres Ver 13.16.2 and 15.10 Binaries)*     - install postgres 13.16.2 binaries
    - have postgres setup on par with existing setup and having proper logical replication 
    - install postgres 15.10 binaries     - ensure to DISABLE auto startup and shutdown of postgres 13.16.2
      - ensure to DISABLE auto startup and shutdown of postgres 15.10    - start postgres db ver 13.16.2 and ensure all are good. no errors in postgres log file
    -  start performing pg_upgrade step to upgrade postgres from ver 13.16.2 to 15.10
pl Vet the newer steps (revised version).

RegardsBharani
    On Monday, December 2, 2024 at 05:48:19 PM EST, Adrian Klaver <[email-masked]> wrote:  

Second attempt:

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 2 Dec 2024 15:46:47 -0800

On 12/2/24 15:41, Bharani SV-forum wrote:
> Ron/Adrian
> Thanks for your input.
> Your suggestion is
> 
> *option#1*
>   ASIS-existing server = EC2 with Community PostgreSQL Ver 13.16.2*
>       - enable logical replication tagged to proposed new EC2 server 

No, logical replication != pg_upgrade process.

> (with newer Higher OS Version ).
> 
> 

Again, no.

>       - ensure to DISABLE auto startup and shutdown of postgres 13.16.2
>      - start postgres db ver 13.16.2 and ensure all are good. no errors 
> in postgres log file

I don't know how much clearer it can be, follow the step by step 
instructions shown here:

"These are the steps to perform an upgrade with pg_upgrade:

[...]

"
> Regards
> Bharani
>  >> <[email-masked] <mailto:[email-masked]> 
> <mailto:[email-masked]>> wrote:
>  >>
>  >>     Team
> <https://www.postgresql.org/docs/current/pgupgrade.html>
>  >
>  > must be random nose.
> 
> [email-masked] <mailto:[email-masked]>
> 
> 
>

---

From: Bharani SV-forum <[email-masked]>
Date: Mon, 2 Dec 2024 23:52:59 +0000 (UTC)

Adrian
Proposed new Server is intended to have higher OS Version (centos ver 9.0) and higher Postgres Version 15.10
Does logical replication will have issues , if the existing asis server is having Postgres ver 13.16.2 with Cent Os 7.0 with the new server having higher OS version Centos Ver 9.0 and then propose to have the Postgres to be upgraded from ver 13.16.2 to 15.10
Hope u have understood my question
    On Monday, December 2, 2024 at 06:47:10 PM EST, Adrian Klaver <[email-masked]> wrote:  

Again, no.

[...]

"
>  > On 12/2/24 14:31, Ron Johnson wrote:
>  >> On Mon, Dec 2, 2024 at 5:18 PM Bharani SV-forum
>  >> <[email-masked] <mailto:[email-masked]> 
>  >>
>  >>     Team
>  >>     Pl Help in vetting my steps for Postgres DB upgrade from Ver 13.X to
>  >>     ver 15.X
>  >>
>  >>     Env = EC2 based Community PostgreSQL Ver 13.16.2
>  >>
>  >>     we will be performing upgrade of our EC2 server too along with new
>  >> OS.
>  >>
>  >>     Need help in vetting my steps for Postgres DB upgrade from Ver 13.X
>  >>     to ver 15.X
>  >>     *ASIS-existing server = EC2 with Community PostgreSQL Ver 13.16.2*
>  >>     - ensure to capture all the pre.req meant for ver 15.10 are being
>  >> met.
>  >>     - shutdown db.
>  >>     - take offline full backup (PG_DATA folder alone)  using OS command
>  >>
>  >>     *Proposed-new EC2 server (with new Operating System version along
>  >>     Postgres Ver 15.10 Binaries)*
>  >>     - install postgres 15.10 binaries
>  >>     - ensure to DISABLE auto startup and shutdown of postgres 15.10
>  >>     -  Restore offline full backup (PG_DATA folder alone) using OS
>  >> command
>  >>     -  start performing pg_upgrade step to upgrade postgres from ver
>  >>     13.16.2 to 15.10
>  >>
>  >>     please guide me, if i have missed any steps in the abovesaid process
>  >>
>  >>     To start new DB features, planning to rollout out the following
>  >>     feature's alone
>  >>     a) TLE extension for password compliance
>  >>     b) parallelize vacuum jobs to utilize -j option
>  >>
>  >>
>  >> To migrate from one server to another while upgrading, one must use
>  >> pg_dump/pg_restore OR Logical Replication.
>  >
>  > Really?
>  >
>  > Then this:
>  >
>  > https://www.postgresql.org/docs/current/pgupgrade.html 
>  >
>  > must be random nose.
>  >
>  >>
>  >> --
>  >> Death to <Redacted>, and butter sauce.
>  >> Don't boil me, I'm still alive.
>  >> <Redacted> lobster!
>  >
> 
> -- 
> Adrian Klaver

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 2 Dec 2024 16:20:49 -0800

> 9.0) and higher Postgres Version 15.10

Alright I did not catch this " ... with new OS" from your original post. 
I saw "Take offline full backup (PG_DATA folder alone)  using OS 
command" and "Restore offline full backup (PG_DATA folder alone) using 
OS command" and assumed like to like on the OS, my mistake.

> 
> Does logical replication will have issues , if the existing asis server 
> is having Postgres ver 13.16.2 with Cent Os 7.0
> with the new server having higher OS version Centos Ver 9.0 and then 
> propose to have the Postgres to be upgraded
> from ver 13.16.2 to 15.10

Logical replication would not have issue with this as that is one of 
it's use cases. The question now becomes whether that is the quickest/ 
most efficient way to do this.

That depends on:

1) What is the size of database(s) you are dealing with?

2) What sort of downtime can you afford?

3) EC2 --> EC2, are they the same region?

---

From: Bharani SV-forum <[email-masked]>
Date: Tue, 3 Dec 2024 00:25:15 +0000 (UTC)

AdrianNoted about, Logical replication would not have issue with this as that is one of
it's use cases.

qsn1: What is the size of database(s) you are dealing with?
ans1: roughly 25 GB  (maximum size)

qsn2 : What sort of downtime can you afford?
ans2: can be maximum 30 mins or so
qsn3: EC2 --> EC2, are they the same region?ans3: Right Question. I assume the same region
Can you pl provide your insight now 
    On Monday, December 2, 2024 at 07:20:52 PM EST, Adrian Klaver <[email-masked]> wrote:  

That depends on:

---

From: "Peter J. Holzer" <[email-masked]>
Date: Tue, 3 Dec 2024 02:08:44 +0100

> ans2: can be maximum 30 mins or so

That does seem sufficient time to dump/restore a 25 GB database, so
that's what I'd try first. It's simple and you can easily test it
without disruption.

        hp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [email-masked]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

---

From: Ron Johnson <[email-masked]>
Date: Mon, 2 Dec 2024 20:23:59 -0500

Adrian,

OP is moving to a new VM when migrating to PG 15.  When was the
"cross-server" feature added to pg_upgrade?

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 2 Dec 2024 21:28:36 -0800

> "cross-server" feature added to pg_upgrade?

Moving to a new VM was not the issue, my mistake was thinking the OS 
version was staying the same.

Then:

On old VM:

"take offline full backup (PG_DATA folder alone)  using OS command"

On new VM:
"Restore offline full backup (PG_DATA folder alone) using OS command"

Followed by installing new Postgres version could be dealt with using 
pg_upgrade. Once I was corrected on what was actually going on then 
doing a dump/restore or logical replication became better choices.

---

From: Bharani SV-forum <[email-masked]>
Date: Wed, 4 Dec 2024 12:42:17 +0000 (UTC)

Team /Ron/Adrian
Wann to reconfirmwe have an setup with 
new server will be with 
will be following the following suggestion
On old VM [ existing server with OS "Amazon Linux release 2 (Karoo) " present in aws "us-east-1 region" and along with postgresql ver 13.16.2  - community edn ]

 - "take offline full backup (PG_DATA folder alone)  using OS command"

On new VM [OS "Amazon Linux 2023 " in aws region=us-east-1 and intended db as "postgresql 15.10 - community edn" ] 

 - "Restore offline full backup (PG_DATA folder alone) using OS command"
- create postgres unix userid- install postgresql ver 15.10 binaries- setup respective env variable to point correctly for PG_DATA
- will follow "pg_upgrade"

my question is a) is the above said steps is correct with the given existing and proposed setupb) is their any known issues using "cross over using pg_upgrade " option between the server's having below said operating system - source = existing server with OS = Amazon Linux release 2 (Karoo) " present in aws "us-east-1 region" and along with postgresql ver 13.16.2  - community edn 
vstarget - different server OS "Amazon Linux 2023 " in aws region=us-east-1 and intended db as "postgresql 15.10 - community edn"

    On Tuesday, December 3, 2024 at 12:28:58 AM EST, Adrian Klaver <[email-masked]> wrote:  

Then:

On old VM:

On new VM:

---

From: Ron Johnson <[email-masked]>
Date: Wed, 4 Dec 2024 09:49:33 -0500

On Wed, Dec 4, 2024 at 7:42 AM Bharani SV-forum <[email-masked]>
wrote:

> *On old VM* [ existing server with OS "Amazon Linux release 2 (Karoo) "
> present in aws "us-east-1 region" and along with postgresql ver 13.16.2  -
> community edn ]
>
> *On new VM [OS "Amazon Linux 2023 " in aws region=us-east-1 and intended
> db as "postgresql 15.10 - community edn" ] *
>
> - create postgres unix userid
> - install postgresql ver 15.10 binaries
> - setup respective env variable to point correctly for PG_DATA
> my question is
> a) is the above said steps is correct with the given existing and proposed
> setup
>

No.  You're vastly overcomplicating things.

> b) is their any known issues using "cross over using pg_upgrade " option
> between the server's having below said operating system
>

There is no "cross over using pg_upgrade" because it does not exist.

When migrating from OldServer to NewServer, your options are:
A) pg_dump/pg_restore
B) Logical Replication

> *- source = existing server with OS = *Amazon Linux release 2 (Karoo) "
> present in aws "us-east-1 region" and along with postgresql ver 13.16.2  -
> community edn
> vs
> target - different server *OS "Amazon Linux 2023 " in aws
> region=us-east-1 and intended db as "postgresql 15.10 - community edn"*
>

---

From: Greg Sabino Mullane <[email-masked]>
Date: Wed, 4 Dec 2024 10:46:32 -0500

> a) is the above said steps is correct with the given existing and proposed

No. Here are some steps:

* Install Postgres on the new VM
However you get it, use the newest version you can. As of this writing, it
is Postgres 17.2. Version 15 is okay, but going to 17 now means a better
Postgres today, and no worrying about replacing v15 in three years.

* Create a new Postgres cluster
On the new VM, use the initdb command to create a new data directory.
Use the --data-checksums option

* Start it up
Adjust your postgresql.conf as needed
Adjust your pg_hba.conf as needed
Install any extensions used on the old VM
Start the cluster using the pg_ctl command (or systemctl)

* Test connection to the old vm from the new vm
On the new vm, see if you can connect to the old one:
psql -h oldvm -p 5432 --list
You may need to adjust firewalls and pg_hba.conf on the old vm.

* Copy the data
Run this on the new VM, adjusting ports as needed:
time pg_dumpall -h oldvm -p 5432 | psql -p 5432

Bonus points for doing this via screen/tmux to prevent interruptions

* Generate new statistics and vacuum
On the new vm, run:
psql -c 'vacuum freeze'
psql -c 'analyze'

* Test your application

* Setup all the other stuff (systemd integration, logrotate, cronjobs,
etc.) as needed

As Peter mentioned earlier, this can be done without disrupting anything,
and is easy to test and debug. The exact steps may vary a little, as I'm
not familiar with how Amazon Linux packages Postgres, but the basics are
the same.

Take it slow. Go through each of these steps one by one. If you get stuck
or run into an issue, stop and solve it, reaching out to this list as
necessary.

Cheers,
Greg

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 4 Dec 2024 09:04:26 -0800

On 12/4/24 04:42, Bharani SV-forum wrote:
> present in aws "us-east-1 region" and along with postgresql ver 13.16.2  
> - community edn ]
> 

That will not work as you would need an install of Postgres 13 on the 
new machine as well. And then there is the issue that the OS version 
changed as well. That would cause issues. Follow the process Greg Sabino 
Mullane posted.

> 
> 
> my question is
> a) is the above said steps is correct with the given existing and 
> proposed setup
> present in aws "us-east-1 region" and along with postgresql ver 13.16.2  
> - community edn
> vs
> *
> *
> *
> *

---

From: Bharani SV-forum <[email-masked]>
Date: Wed, 11 Dec 2024 19:12:44 +0000 (UTC)

TeamAs suggested from old server, post shutdown of DB, I did OS level dump of PG_DATA folder and had restored in the new server.
Any idea on how to install the older binary postgres 13.18 ( OS=Amazon Linux 2023.6.20241121) under a dedicated folder suffixed as the following e.g.) /usr/pgsql1318
System Admin had already installed newer version pgsql 15.08 binaries in the  new server (OS= Amazon Linux 2023.6.20241121) in the folder "/usr/bin/"

We were quoted , OS = Amazon Linux 2023.6.20241121 doesnot support postgres ver 15.10 (Community edition) under its AWS-EC2.
Regards

    On Wednesday, December 4, 2024 at 12:04:47 PM EST, Adrian Klaver <[email-masked]> wrote:  

>  > Adrian,
>  > OP is moving to a new VM when migrating to PG 15.  When was the
>  > "cross-server" feature added to pg_upgrade?
>  >
>

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 11 Dec 2024 12:57:13 -0800

On 12/11/24 11:12, Bharani SV-forum wrote:
> Team
> As suggested from old server, post shutdown of DB, I did OS level dump 
> of PG_DATA folder and had restored in the new server.

If you follow the process shown here:

https://www.postgresql.org/message-id/CAKAnmmKZdhnhdNRd3OgDyEco9OPkT%3DqA_TeWMFMRvUM9pXauKg%40mail.gmail.com

You would not have to do the below.

> 
> Any idea on how to install the older binary postgres 13.18 ( OS=Amazon 
> Linux 2023.6.20241121) under a dedicated folder suffixed as the 
> following e.g.) /usr/pgsql1318
> 
> System Admin had already installed newer version pgsql 15.08 binaries in 
> the  new server (OS= Amazon Linux 2023.6.20241121) in the folder "/usr/bin/"
> 
> We were quoted , OS = Amazon Linux 2023.6.20241121 doesnot support 
> postgres ver 15.10 (Community edition) under its AWS-EC2.

That does not reflect well on Amazon Linux, that it is missing two 
critical bug releases.

---

From: Bharani SV-forum <[email-masked]>
Date: Mon, 16 Dec 2024 21:19:20 +0000 (UTC)

TeamI am getting the following error.
pg_dump: error: error reading large object 2113418:
pg_dump: error: could not open large object 3391830: 
I tried to give this command DB name = abcefg
ALTER DATABASE abcefgd SET lo_compat_privileges=on;

and reran and once again , i am getting the same error
while doing using psql/pg_dump from old version server running 13.18 [ OS = Amazon Linux release 2 (Karoo) ].
Will be pg_dump and pg_restore to restore in the new VM with new OS [OS= amazon linux 2023] and new DB bin pgsql ver 15.09.
We were told by AWS team, in the new VM tagged OS [OS= amazon linux 2023] , pgsql Ver 13.16 is not supported
I cross checkedSELECT oid, count(*)  FROM pg_largeobject_metadata group by oid order by oid ;Rows =  4260170 rows
Can you suggest

    On Wednesday, December 11, 2024 at 03:57:31 PM EST, Adrian Klaver <[email-masked]> wrote:

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 16 Dec 2024 14:19:08 -0800

On 12/16/24 13:19, Bharani SV-forum wrote:
> Team
> I am getting the following error.
> 

What user are you running pg_dump as?

What version of pg_dump?

> OS = Amazon Linux release 2 (Karoo) ].

It is either psql or pg_dump. psql is the CLI client for the Postgres 
server. If you are using psql as an alias for Postgres(sql), don't,  it 
only adds confusion.

> 
> Will be pg_dump and pg_restore to restore in the new VM with new OS [OS= 
> amazon linux 2023] and new DB bin pgsql ver 15.09.
> 

Not sure why? It still a community supported version and will be through 
November 2025.

> 
> *I cross checked*
> SELECT oid, count(*)  FROM pg_largeobject_metadata group by oid order by 
> oid ;
> Rows =  4260170 rows
> 
> Can you suggest
> 
> 
> https://www.postgresql.org/message-id/CAKAnmmKZdhnhdNRd3OgDyEco9OPkT%3DqA_TeWMFMRvUM9pXauKg%40mail.gmail.com <https://www.postgresql.org/message-id/CAKAnmmKZdhnhdNRd3OgDyEco9OPkT%3DqA_TeWMFMRvUM9pXauKg%40mail.gmail.com>
>

---

From: Bharani SV-forum <[email-masked]>
Date: Mon, 16 Dec 2024 22:30:59 +0000 (UTC)

a) user = postgres
b)pg_dump version = /usr/bin/pg_dump -V
pg_dump (PostgreSQL) 13.16
c)
DB version
select version () ;                                                 version---------------------------------------------------------------------------------------------------------- PostgreSQL 13.16 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
use this script for backup
pg_dump -Fp -p 5432 -U "$USERNAME" "$DATABASE" 

using username = postgres
for one of the DB (ver 13.16), it worked fine by doing oldvm = pg_dump from ver 13.16 andlater restoring in new VM with new OS and new db binary 15.09, post creating dummy db (appln related) and restoring the pg_dump from oldvm .   
 On Monday, December 16, 2024 at 05:19:31 PM EST, Adrian Klaver <[email-masked]> wrote:  

What version of pg_dump?

>  > Team
>  > As suggested from old server, post shutdown of DB, I did OS level dump
>  > of PG_DATA folder and had restored in the new server.
> 
>  > Any idea on how to install the older binary postgres 13.18 ( OS=Amazon
>  > Linux 2023.6.20241121) under a dedicated folder suffixed as the
>  > following e.g.) /usr/pgsql1318
>  >
>  > System Admin had already installed newer version pgsql 15.08 binaries in
>  > the  new server (OS= Amazon Linux 2023.6.20241121) in the folder 
> "/usr/bin/"
>  >
>  > We were quoted , OS = Amazon Linux 2023.6.20241121 doesnot support
>  > postgres ver 15.10 (Community edition) under its AWS-EC2.
> 
>  > Regards

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 16 Dec 2024 14:49:10 -0800

On 12/16/24 14:30, Bharani SV-forum wrote:
> *a) *
> *user = *
> postgres
> b)
> *pg_dump version = *
> /usr/bin/pg_dump -V
> *DB version*
> select version () ;
>                                                   version
> ----------------------------------------------------------------------------------------------------------
>   PostgreSQL 13.16 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 
> 20150623 (Red Hat 4.8.5-44), 64-bit
> 
> *use this script for backup*
> 
> for one of the DB (ver 13.16), it worked fine by doing oldvm = pg_dump 
> from ver 13.16 and
> later restoring in new VM with new OS and new db binary 15.09, post 
> creating dummy db (appln related) and restoring the pg_dump from oldvm .
> 

That's nice, but the issue is the case that did not work.

What process where you running that caused the error?

---

From: Bharani SV-forum <[email-masked]>
Date: Mon, 16 Dec 2024 23:05:18 +0000 (UTC)

TeamBeing dev server, I noticed, we haven't performed analyze/vacuum process.
Noticed and re-triggerred vacuum full and analyze for all the application related db's
Re ran backup.
No issue's appeared during backup.
Not yet performed restoration in pgsql ver 15.09 in new vm with different OS.
Thank you for guiding me

    On Monday, December 16, 2024 at 05:49:28 PM EST, Adrian Klaver <[email-masked]> wrote:

---

From: Bharani SV-forum <[email-masked]>
Date: Thu, 19 Dec 2024 23:36:44 +0000 (UTC)

cannot drop a tablespace which never exists in pg_tablespace

I remember i had create previously an tablespace named " mq_data" , but cannot find in pg_tablespace listing.
if i try to create the same tablespace name, it is giving error as " already in use as a tablespace"how to force drop the pointer entry and the tablespace from postgres

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 19 Dec 2024 15:46:06 -0800

On 12/19/24 15:36, Bharani SV-forum wrote:
> if i try to create the same tablespace name, it is giving error as " 
> already in use as a tablespace"
> how to force drop the pointer entry and the tablespace from postgres

From:

https://www.postgresql.org/docs/current/manage-ag-tablespaces.html

"The directory $PGDATA/pg_tblspc contains symbolic links that point to 
each of the non-built-in tablespaces defined in the cluster."

Is there a symlink at the above location?

Does the directory in the image have files?

> Inline image

---

From: Bharani SV-forum <[email-masked]>
Date: Fri, 20 Dec 2024 16:09:56 +0000 (UTC)

Adrian

initially i had created tblspace outside pg_data env variablee.g PG_DATA env variable is defined to /XXX/YYYY/ABC/15/data
the tablespace was created under   /XXX/YYYY/ABC/15/tblspace/<<tbl_spcname>>/assume tablespace name is abc_data
cd /XXX/YYYY/ABC/15/tblspace/abc_data/ls -atldrwxr-x---. 2 postgres postgres  6 Dec 19 22:08 PG_15_202209061
 ls -altRdrwxr-x---. 2 postgres postgres  6 Dec 19 22:08 PG_15_202209061
i reconfirmedSELECT * FROM pg_tablespace ; oid  |  spcname   | spcowner | spcacl | spcoptions------+------------+----------+--------+------------ 1663 | pg_default |       10 |        | 1664 | pg_global  |       10 |        |
not having any entry about tablespace abc_data
Can you guide

    On Thursday, December 19, 2024 at 06:46:33 PM EST, Adrian Klaver <[email-masked]> wrote:  

From:

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 20 Dec 2024 08:50:01 -0800

On 12/20/24 08:09, Bharani SV-forum wrote:
> Adrian
> Inline image
> 
> initially i had created tblspace outside pg_data env variable

Did you run CREATE TABLESPACE as shown below?:

https://www.postgresql.org/docs/current/sql-createtablespace.html

Did you ever use the tablespace?

> e.g PG_DATA env variable is defined to /XXX/YYYY/ABC/15/data
> 
> *cd /XXX/YYYY/ABC/15/tblspace/abc_data/*
> ls -atl
> drwxr-x---. 2 postgres postgres  6 Dec 19 22:08 PG_15_202209061
> 
> ls -altR
> drwxr-x---. 2 postgres postgres  6 Dec 19 22:08 PG_15_202209061
> 
> i reconfirmed
> SELECT * FROM pg_tablespace ;
>   oid  |  spcname   | spcowner | spcacl | spcoptions
> ------+------------+----------+--------+------------
>   1663 | pg_default |       10 |        |
>   1664 | pg_global  |       10 |        |
>

---

From: Bharani SV-forum <[email-masked]>
Date: Fri, 20 Dec 2024 16:52:19 +0000 (UTC)

I found the culprit env variable was defined as PG_DATA inlieu of PGDATA
cleared and dropped itand restarted dband created once again tablespace in the desired location and it worked
Thank YouRegards
    On Friday, December 20, 2024 at 11:50:20 AM EST, Adrian Klaver <[email-masked]> wrote:

---

From: Y_Bharani_mbsv <[email-masked]>
Date: Fri, 20 Dec 2024 19:25:45 +0000 (UTC)

TeamI have the need to have postgresql db running in multiuser mode and do my needed tasks for few mins.
How to restrict all the application layer , not to get connected with the postgres db ,during my specific time window I am aware of their application layer - username
Changing password is the last option
Any tips

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 20 Dec 2024 12:13:03 -0800

On 12/20/24 11:25, Y_Bharani_mbsv wrote:
>   Team
> I have the need to have postgresql db running in multiuser mode and do 
> my needed tasks for few mins.

What are the tasks and why do you think they can't be run concurrently 
with other users?

> 
> How to restrict all the application layer , not to get connected with 
> the postgres db ,
> during my specific time window
> I am aware of their application layer - username
>

---

From: Ron Johnson <[email-masked]>
Date: Fri, 20 Dec 2024 15:25:57 -0500

On Fri, Dec 20, 2024 at 2:25 PM Y_Bharani_mbsv <[email-masked]> wrote:

>  Team
> I have the need to have postgresql db running in multiuser mode and do my
> needed tasks for few mins.
>
> How to restrict all the application layer , not to get connected with the
> postgres db ,

1. Create a pg_hba_maintmode.conf with only the relevant hosts in it.
2. Copy pg_hba.conf to pg_hba_multi.conf.
3. Copy pg_hba_maintmode.conf to pg_hba.conf.
4. pg_ctl reload.
5. Use pg_stat_activity and pg_cancel_backend() to kill the application
connections.
6. Do your work.
7. Copy pg_hba_multi.conf to pg_hba.conf.
8. pg_ctl reload

Or... just shut down the user application.

---

From: Y_Bharani_mbsv <[email-masked]>
Date: Fri, 20 Dec 2024 22:49:35 +0000 (UTC)

TQ
    On Friday, December 20, 2024 at 03:26:30 PM EST, Ron Johnson <[email-masked]> wrote:  
 
 On Fri, Dec 20, 2024 at 2:25 PM Y_Bharani_mbsv <[email-masked]> wrote:
  TeamI have the need to have postgresql db running in multiuser mode and do my needed tasks for few mins.
How to restrict all the application layer , not to get connected with the postgres db ,during my specific time window

1. Create a pg_hba_maintmode.conf with only the relevant hosts in it.2. Copy pg_hba.conf to pg_hba_multi.conf.3. Copy pg_hba_maintmode.conf to pg_hba.conf.4. pg_ctl reload.5. Use pg_stat_activity and pg_cancel_backend() to kill the application connections.6. Do your work.7. Copy pg_hba_multi.conf to pg_hba.conf.8. pg_ctl reload
Or... just shut down the user application.
-- 
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Bharani SV-forum <[email-masked]>
Date: Fri, 20 Dec 2024 22:51:02 +0000 (UTC)

I am having heavy polling into the database and need to perform certain tasks without any application interference.

    On Friday, December 20, 2024 at 03:13:21 PM EST, Adrian Klaver <[email-masked]> wrote:

---

From: Bharani SV-forum <[email-masked]>
Date: Mon, 23 Dec 2024 19:26:21 +0000 (UTC)

TeamI am in the process of upgrading EC2-PGS ver 13.X to 15.X I am aware  since ver 14.X, we have restriction in the usage of schema=public and the DBA need to grant exclusive priv for the tagged db user's.
Assume i want to enforce it,Can i retag all the object tables/indexex/packages/procedures/functions etc tagged under schema =public to a newly created schema e.g = schemaname = allowallusr and grant respective priv's.
Whether it will resolve the issue, as application time need time to validated all the use case for testing the objects which is present under schema=public and ported to new schema= allowallusr .
Any suggestions or best practise

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 23 Dec 2024 12:58:27 -0700

On Monday, December 23, 2024, Bharani SV-forum <[email-masked]>
wrote:

>
> I am in the process of upgrading EC2-PGS ver 13.X to 15.X
> I am aware  since ver 14.X, we have restriction in the usage of
> schema=public and the DBA need to grant exclusive priv for the tagged db
> user's.
>

The only change at that point was removing some overly permissive
defaults.  You don’t need to move objects, just reinstate explicitly those
permissions that are needed that are no longer being granted by default.

You really cannot avoid the testing for a major version upgrade though.
Just expect error messages and be prepared to deal with them.

David J.

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 23 Dec 2024 12:06:37 -0800

> schema=public and the DBA need to grant exclusive priv for the tagged db 

Are you referring to this?:

https://www.postgresql.org/docs/15/release-15.html

"Remove PUBLIC creation permission on the public schema (Noah Misch) §

The new default is one of the secure schema usage patterns that Section 
5.9.6 has recommended since the security release for CVE-2018-1058. The 
change applies to new database clusters and to newly-created databases 
in existing clusters. Upgrading a cluster or restoring a database dump 
will preserve public's existing permissions.

For existing databases, especially those having multiple users, consider 
revoking CREATE permission on the public schema to adopt this new 
default. For new databases having no need to defend against insider 
threats, granting CREATE permission will yield the behavior of prior 
releases.
"

> 
> Assume i want to enforce it,
> Can i retag all the object tables/indexex/packages/procedures/functions 
> etc tagged under schema =public to a newly created schema e.g = 
> *schemaname = allowallusr *and grant respective priv's.
> 
> Whether it will resolve the issue, as application time need time to 
> validated all the use case for testing the objects which is present 
> under schema=public and ported to new schema= allowallusr .
>

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 23 Dec 2024 12:34:34 -0800

On 12/23/24 12:17 PM, Bharani SV-forum wrote:
> Adrian
> TQ. Exactly , you have provided the needed information
> 
> For existing databases, especially those having multiple users,
> - I will consider revoking CREATE permission on the public schema to 
> adopt this new default.

You may want to do some testing before you do that. It may very well be 
that your existing code depends on being able to CREATE objects in the 
public schema using the public role.

> 
> 1) Do i need to revoke "SELECT" access on any tables/indexes etc in 
> schema = public ?
> 2) Do i need to revoke "Execute" access on any procedures/functions  in 
> schema = public ?

Again this is going to need to be tested. I'm betting though that your 
present code depends on these privileges existing.

Just remember the change was for:

"Remove PUBLIC creation permission on the public schema"

where PUBLIC is a built in role. I would suggest reading:

https://www.postgresql.org/docs/current/ddl-priv.html

for more information on what that means.

> 
> 
> I agree with your suggestion - - For new databases having no need to 
> defend against insider threats, granting CREATE permission will yield 
> the behavior of prior releases.

It is not my suggestion, what I quoted comes from the documentation.

> 
> Can you pl seed more information with qsn#1 and #2
> 
> On Monday, December 23, 2024 at 03:06:56 PM EST, Adrian Klaver 
> <[email-masked]> wrote:
> 
> 
> 
> 
> <https://www.postgresql.org/docs/15/release-15.html>
>

---

From: Bharani SV-forum <[email-masked]>
Date: Tue, 31 Dec 2024 16:15:03 +0000 (UTC)

Team
I followed Greg suggested steps .One of big had only one table and around four million recordsi am doing dev env restoration into new vmthe target VM env is an POC server and took 3 hrs to restore four million records.Now it is doing process of lo_open / lo_close /  lowrite  etci.e pg-dump-creates-a-lot-of-pg-catalog-statements
is there any alternate way , to speedup  this process.
i can see in the select count(*) record count is matching (target and source)
Regards

    On Wednesday, December 4, 2024 at 10:47:26 AM EST, Greg Sabino Mullane <[email-masked]> wrote:  
 
 On Wed, Dec 4, 2024 at 7:42 AM Bharani SV-forum <[email-masked]> wrote:
 a) is the above said steps is correct with the given existing and proposed setup

No. Here are some steps:
* Install Postgres on the new VMHowever you get it, use the newest version you can. As of this writing, it is Postgres 17.2. Version 15 is okay, but going to 17 now means a better Postgres today, and no worrying about replacing v15 in three years.
* Create a new Postgres clusterOn the new VM, use the initdb command to create a new data directory.Use the --data-checksums option
* Start it upAdjust your postgresql.conf as neededAdjust your pg_hba.conf as neededInstall any extensions used on the old VMStart the cluster using the pg_ctl command (or systemctl)
* Test connection to the old vm from the new vmOn the new vm, see if you can connect to the old one:psql -h oldvm -p 5432 --listYou may need to adjust firewalls and pg_hba.conf on the old vm.
* Copy the dataRun this on the new VM, adjusting ports as needed:time pg_dumpall -h oldvm -p 5432 | psql -p 5432
Bonus points for doing this via screen/tmux to prevent interruptions
* Generate new statistics and vacuumOn the new vm, run:psql -c 'vacuum freeze'psql -c 'analyze'
* Test your application
* Setup all the other stuff (systemd integration, logrotate, cronjobs, etc.) as needed
As Peter mentioned earlier, this can be done without disrupting anything, and is easy to test and debug. The exact steps may vary a little, as I'm not familiar with how Amazon Linux packages Postgres, but the basics are the same.
Take it slow. Go through each of these steps one by one. If you get stuck or run into an issue, stop and solve it, reaching out to this list as necessary.
Cheers,Greg

---

From: Bharani SV-forum <[email-masked]>
Date: Fri, 24 Jan 2025 15:50:41 +0000 (UTC)

TeamNeed your additional input.VM is based on EC2 OS Version = Amazon Linux 2 Existing DB version = 13.X - Pg Community under EC2 - VM (Same VM)Target DB version = 15.x  - Pg Community under EC2 - VM (Same VM)
i will narrate the latest error

executing: SELECT pg_catalog.set_config('search_path', '', false);Checking for presence of required libraries                 fatal
Your installation references loadable libraries that are missing from thenew installation.  You can add these libraries to the new installation,or remove the functions using them from the old installation.  A list ofproblem libraries is in the file:    /var/lib/pgsql/15/data/pg_upgrade_output.d/20250122T161405.335/loadable_libraries.txt

output of "  /var/lib/pgsql/15/data/pg_upgrade_output.d/20250122T161405.335/loadable_libraries.txt" is"could not load library "$libdir/dblink": ERROR:  could not access file "$libdir/dblink": No such file or directoryIn database: main"
I cross checked on the  existing (old db _version package list along with new db version package listand found one of the package is missingpostgresql15-contrib.x86_64 

Any guidance how to come the error.
my unix system admin is quoting that  he is facing built issue with postgres15 version w.r.to " postgresql15-contrib.x86_64 " under AWS based OS "  Amazon Linux 2  ". His version is "It needs libpython3.6m.so.1.0()(64bit) to install package: postgresql15-contrib-15.10-1PGDG.rhel7.x86_64. in our environment. I can't install python3.6 libraries because we already have a different python version installed and clashing"
Any quidance to overcome the error, as the existing python version being used by us is clashing with the pre.req version python ver 3.6
My unix admin, too quoted me to have it installed under OS RHEL7 w.r.to postgresql - EC2 version Best Viable option. I have tried with previous suggestion steps and found "pg_upgrade" as the most viable and faster 
Regards 
    On Tuesday, December 31, 2024 at 11:15:26 AM EST, Bharani SV-forum <[email-masked]> wrote:  
 
  Team

No. Here are some steps:

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 24 Jan 2025 08:22:18 -0800

On 1/24/25 07:50, Bharani SV-forum wrote:
> Team
> Need your additional input.
> VM is based on EC2 OS Version = Amazon Linux 2
> Existing DB version = 13.X - Pg Community under EC2 - VM (Same VM)
> Target DB version = 15.x  - Pg Community under EC2 - VM (Same VM)
> 
> executing: SELECT pg_catalog.set_config('search_path', '', false);
> Checking for presence of required libraries                 fatal
> 

> my unix system admin is quoting that  he is facing built issue with 
> postgres15 version w.r.to " postgresql15-contrib.x86_64 "
> under AWS based OS " Amazon Linux 2  ".
> His version is "It needs libpython3.6m.so.1.0()(64bit) to install 
> package: postgresql15-contrib-15.10-1PGDG.rhel7.x86_64. in our 
> environment. I can't install python3.6 libraries because we already have 
> a different python version installed and clashing"

 From what I know Amazon Linux 2 is a RH clone, confirmed by 
postgresql15-contrib-15.10-1PGDG.rhel7.x86_64 above.

 From link below it seems it is possible to run multiple versions of 
Python together RH7 so it should work on AL2 also(?):

https://developers.redhat.com/blog/install-python3-rhel#installing_python_3_on_rhel_7

> 
> Any quidance to overcome the error, as the existing python version being 
> used by us is clashing with the pre.req version python ver 3.6
> 
> My unix admin, too quoted me to have it installed under OS RHEL7 w.r.to 
> postgresql - EC2 version
> Best Viable option. I have tried with previous suggestion steps and 
> found "pg_upgrade" as the most viable and faster
> 
> Regards

---

From: Bharani SV-forum <[email-masked]>
Date: Fri, 24 Jan 2025 18:01:27 +0000 (UTC)

AdrianThanks
This is the exact error which the system admin is facing

 postgresql15-contrib installation on Amazon Linux 2 fails on Python shared lib dependency

"https://www.postgresql.org/message-id/CABUevEz_OHR%2BaGU%2B7yuhpfJD%2BeWgC8aHgVRjP5U30kJqu%2B7jmg%40mail.gmail.com"
    On Friday, January 24, 2025 at 11:22:23 AM EST, Adrian Klaver <[email-masked]> wrote:

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 24 Jan 2025 11:14:52 -0800

On 1/24/25 10:01 AM, Bharani SV-forum wrote:
> Thanks

Which from your post the admin said was due to:

"His version is "It needs libpython3.6m.so.1.0()(64bit)" "

Note the libpython3.6.

The link I posted previously:

Shows how to install Python 3.6

---

From: Bharani SV-forum <[email-masked]>
Date: Tue, 28 Jan 2025 16:23:41 +0000 (UTC)

TeamNeed your help.We are trying to use existing VM with underlying OS = Amazon Linux 2 (AL2)  along with Pgsql ver 13.X community edn.Trying to upgrade from 13.X to 15.X and had hit the bottleneck for the mandate to have python ver 3.X binaries.We have limitation with the existing VM with AWS and currently AL2 uses the yum package manager that has a hard dependency on Python 2.7 and the pgsql ver 15. needed Ver 3.x python binaries and the package community edition (pgsql ver 15.x) "postgresql15-contrib.." is not getting installed.
We have limitation , where the application being used is having python ver 2.x binaries and we cannot install python 3.x binaries , as  we have the underlying OS with AL2 which is having "a hard dependency on Python 2.7"
In lieu of using pgsql ver 15.X. We are OK to upgrade to the next version which is 14.X  from existing ver 13.X.
Can anyone re-confirm if the Community edition (pgsql ver14.x)  - "postgresql14-contrib .." needed python ver 2.X binaries or Ver 3.X binaries.
I cross checked and found one of the url " Install Postgres v14 + -contrib on AWS Linux 2 | the gabriellephant" is quoting pgsql ver 14.x needed python ver 3.x binary
    On Friday, January 24, 2025 at 02:14:56 PM EST, Adrian Klaver <[email-masked]> wrote:  

Note the libpython3.6.

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 28 Jan 2025 11:36:00 -0800

On 1/28/25 08:23, Bharani SV-forum wrote:
> Team
> Need your help.
> We are trying to use existing VM with underlying OS = Amazon Linux 2 
> (AL2)  along with Pgsql ver 13.X community edn.
> Trying to upgrade from 13.X to 15.X and had hit the bottleneck for the 
> mandate to have python ver 3.X binaries.
> We have limitation with the existing VM with AWS and currently AL2 uses 
> the |yum| package manager that has a hard dependency on Python 2.7 and 
> the pgsql ver 15. needed Ver 3.x python binaries and the package 
> community edition (pgsql ver 15.x) "postgresql15-contrib.." is not 
> getting installed.
> 
> We have limitation , where the application being used is having python 
> ver 2.x binaries and we cannot install python 3.x binaries , as  we have 
> the underlying OS with AL2 which is having "a hard dependency on Python 2.7"
> 
> In lieu of using pgsql ver 15.X. We are OK to upgrade to the next 
> version which is 14.X  from existing ver 13.X.
> 
> Can anyone re-confirm if the Community edition (pgsql ver14.x)  - 
> "postgresql14-contrib .." needed python ver 2.X binaries or Ver 3.X 
> binaries.

 From here:

https://yum.postgresql.org/14/redhat/rhel-7-x86_64/repoview/postgresql14-contrib.html

postgresql14-contrib-14.15-1PGDG.rhel7.x86_64

hstore_plpython3.so
jsonb_plpython3.so
ltree_plpython3.so

So I'm going to say yes Python 3 is needed.

FYI I don't see those files in postgresql13-contrib.

If you want to move forward you need to either install Python 3 in your 
current distro or use a different distro.

> 
> I cross checked and found one of the url " Install Postgres v14 + 
> -contrib on AWS Linux 2 | the gabriellephant 
> <https://gorthx.wordpress.com/2022/02/08/install-postgres-v14-contrib-on-aws-linux-2/>" is quoting pgsql ver 14.x needed python ver 3.x binary
>

---

From: Bharani SV-forum <[email-masked]>
Date: Tue, 28 Jan 2025 20:13:50 +0000 (UTC)

TQ Adriananother Question on the measuring egress (out bound traffic) /ingress (inbound traffic) w.r.to " pg_dumpall " during usage of 
pg_dumpall-h  <old vm>  -p 5432 | psql -p 5462

taking data from old_vm and copying to new_vm, as i need to use across the network for taking data and i cannot use "pg_upgrade" tool as AWS - AL3 doesnot support postgresql 13 - community edition. My existing DB size is  around 60 GB (all the DB's) using postgresql Ver 13.

---

From: Ron Johnson <[email-masked]>
Date: Tue, 28 Jan 2025 16:27:15 -0500

On Tue, Jan 28, 2025 at 3:13 PM Bharani SV-forum <[email-masked]>
wrote:

> TQ Adrian
> another Question on the measuring egress (out bound traffic) /ingress
> (inbound traffic) w.r.to " pg_dumpall " during usage of
>
> pg_dumpall -h  <old vm>  -p 5432 | psql -p 5462
>
> taking data from old_vm and copying to new_vm, as i need to use across the
> network for taking data and i cannot use "pg_upgrade" tool as AWS - AL3
> doesnot support postgresql 13 - community edition.
>
> My existing DB size is  *around 60 GB* (all the DB's) using postgresql
> Ver 13.
>

iotop is *always* useful.

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 28 Jan 2025 13:45:44 -0800

> (inbound traffic) w.r.to " pg_dumpall " during usage of

Since you are using AWS EC2(?) instances wouldn't the AWS dashboard show 
you this?

> taking data from old_vm and copying to new_vm, as i need to use across 
> the network for taking data and i cannot use "pg_upgrade" tool as AWS - 
> AL3 doesnot support postgresql 13 - community edition.

The PGDG repo does:

https://www.postgresql.org/download/linux/redhat/

Have you tried using it?

Also from what I gather there is no Amazon Linux 3.

There is Amazon Linux 2023, is that what you are referring to?

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 6 Feb 2025 17:03:19 -0800

On 2/6/25 16:37, Bharani SV-forum wrote:
>   Team
> I am in the process of doing DB Upgrade from EC2- PGS - Community Edn 
> ver 13.X to 15.X including switching from "MD5 " to "scram-sha-256" for 
> password hashing and authentication.
> 
> We are having tightly integrated appln tech stack having 256+ roles used 
> by application with MD5 mechanism and having Password setting's 
> replicated in each of the four server's (used to load balance the data 
> flow to each server wise and  each stream Wise (e.g
> LAYER#A-server#1,LAYER#A-server#2,LAYER#A-server#3,LAYER#A-server#4
> LAYER#B-server#1,LAYER#B-server#2,LAYER#B-server#3,LAYER#B-server#4
> etc and had been embedded in an config file in each server wise
> 
> We want to have very minimum down time, during the PG upgrade from ver 
> 13.X to 15.X, need to , how to switch over "MD5" to "scram-sha-256" for 
> password hashing and authentication.

https://www.postgresql.org/docs/15/auth-password.html

"md5

     The method md5 uses a custom less secure challenge-response 
mechanism. It prevents password sniffing and avoids storing passwords on 
the server in plain text but provides no protection if an attacker 
manages to steal the password hash from the server. Also, the MD5 hash 
algorithm is nowadays no longer considered secure against determined 
attacks.

     The md5 method cannot be used with the db_user_namespace feature.

     To ease transition from the md5 method to the newer SCRAM method, 
if md5 is specified as a method in pg_hba.conf but the user's password 
on the server is encrypted for SCRAM (see below), then SCRAM-based 
authentication will automatically be chosen instead.
"

This means you can upgrade with the md5 passwords and then change over 
to scram-sha-256 as needed.

> 
> Need the best practice including steps to avoid mandated change of 
> Application related Role Password during db migration ,as the # of 
> roles/userid count is more.
> 
> Regards
> Bharani
> 
>

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 6 Feb 2025 19:16:45 -0800

On 2/6/25 18:03, Bharani SV-forum wrote:
> Adrian
> TQ for your valuable input's.
> 
> *Additional Qsn*
> 
> Assume  DB ver = 15.X
> 
> By default encryption = scram-sha-256, Assume pg_hba.conf is quoted the 
> usage as MD5 for the
>   dbuserid "test_usr_1"
> 
> *e.g .)*
> *
> *
> hostssl   all test_usr_1 10.20.30.40  md5
> 
> i.e .)
> Assume if the respective db userid (e.g test_usr_1) is quoted for usage 
> md5,  in pg_hba.conf, No Need to Change, the respective *Role/Userid 
> password mandatorily.* DB System will allow to use existing password 
> with the old MD5 passwords still work, as long as the authentication 
> method in pg_hba.conf is set to md5

Yes.

It gives you time to switch the passwords to scram-sha-256 encryption 
after you do the migration. In other words you can have both md5 and 
scram-sha-256 passwords in use without changing the pg_hba.conf lines. 
Once the transition to scram-sha-256 is done then you can change the 
lines to scram-sha-256 and that will prevent use of m5 passwords going 
forward.

> 
> e.g.) hostssl     all         LOGS_USER_1 10.9.0.0/21    md5
> 
> Is their,  any security problem due to usage of md5 in the pg_hba.conf 
> file  with underlying db =15.X ?

You are currently using it, have there been any issues?

If not then moving to Postgres 15 won't change that.

> 
> I am Aware ,
> (a) *MD5 hash algorithm is nowadays no longer considered secure against 
> determined attacks.*
> *(a)  MD5 method cannot be used with the db_user_namespace feature.
> *
> 
> 
>

---

From: Ron Johnson <[email-masked]>
Date: Sun, 16 Feb 2025 09:35:41 -0500

On Sun, Feb 16, 2025 at 8:13 AM Y_Bharani_mbsv <[email-masked]> wrote:

> Team
> Good Morning.
> As part of DB upgrade from EC2 - PGS - community Edn Ver 13.X to 14.X
> I followed steps of "pg_upgrade" and had executed the last step (post
> successful db migration)
>
> vacuumdb --analyze-in-stages
>
> and later noticed an caveat
> url = https://www.postgresql.org/docs/current/app-vacuumdb.html
>
>
> --analyze-in-stages
>
> Only calculate statistics for use by the optimizer (no vacuum), like
> --analyze-only. Run three stages of analyze; the first stage uses the
> lowest possible statistics target (see default_statistics_target
> <https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET>)
> to produce usable statistics faster, and subsequent stages build the full
> statistics.
>
> This option is only useful to analyze a database that currently has no
> statistics or has wholly incorrect ones, such as if it is newly populated
> from a restored dump or by pg_upgrade. *Be aware that running with this
> option in a database with existing statistics may cause the query optimizer
> choices to become transiently worse due to the low statistics targets of
> the early stages.*
>
> How to overcome the issue to avoid "transiently worse"
>

"Transiently" means "temporarily".

And since pg_upgrade does not carry over optimizer statistics, query
optimizer choices would be transiently worse *anyway* until the ANALYZE
completes.

>   Later, I too did
> a) vacuum(full,verbose,skip_locked) ... each table wise
>

Why?  It certainly didn't do what you think it did.

(This is why giving "rewrite the whole table" the name VACUUM FULL was a
horrible idea.)

> b) analyze (verbose,skip_locked) .. each table wise
>  Any guidance
>

You wasted much time and effort.  Best to have just waited until
the --analyze-in-stages had completed.

---

From: Adrian Klaver <[email-masked]>
Date: Sun, 16 Feb 2025 08:19:35 -0800

On 2/16/25 05:13, Y_Bharani_mbsv wrote:
> <https://www.postgresql.org/docs/current/app-vacuumdb.html>
> 
> 
> |--analyze-in-stages|
> 
>     |--analyze-only|. Run three stages of analyze; the first stage uses
>     the lowest possible statistics target (see default_statistics_target
>     <https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET>) to produce usable statistics faster, and subsequent stages build the full statistics.
> 
>     This option is only useful to analyze a database that currently has
>     no statistics or has wholly incorrect ones, such as if it is newly
>     populated from a restored dump or by |pg_upgrade|. *Be aware that
>     running with this option in a database with existing statistics may
>     cause the query optimizer choices to become transiently worse due to
>     the low statistics targets of the early stages.*

Did you skip over reading this part?:

"This option is only useful to analyze a database that currently has no
statistics or has wholly incorrect ones, such as if it is newly 
populated from a restored dump or by pg_upgrade."

---

From: Adrian Klaver <[email-masked]>
Date: Sun, 16 Feb 2025 09:12:02 -0800

On 2/16/25 08:27, Y_Bharani_mbsv wrote:
> Adrian
> TQ for the instant reply.
> post DB migration to Ver 14.X (successfully) and Post executing the 
> "vacuumdb --analyze-in-stages", i noticed "read me option" and the 
> caveat on it.

Did you do:

or

vacuumdb --all --analyze-in-stages

?

> 
> Later, I too did
> a) vacuum(full,verbose,skip_locked) ... each table wise b) analyze 
> (verbose,skip_locked) .. each table wise
> against all the DB's

VACUUM FULL has not purpose at this point as FULL recycles unneeded 
tuples from DELETEs and UPDATEs and at this stage there are none of 
those. Also going table by table is not necessary when you can 
vacuuum/analyze an entire database, which is what you want, with one 
command.

> 
> Any guidance on how to overcome the issue.

There is no issue to overcome.

Per:

17. Statistics

"Because optimizer statistics are not transferred by pg_upgrade, you 
will be instructed to run a command to regenerate that information at 
the end of the upgrade. You might need to set connection parameters to 
match your new cluster."

Running vacuumdb --all --analyze-in-stages will create the statistics 
you need it just does it in steps(stages) vs doing vacuumdb --all 
--analyze-only which does it in a single pass. Unless you are planning 
to run some large complicated queries immediately upon completion of the 
upgrade you will not notice the difference.

> Any suggestion ?

---

From: Adrian Klaver <[email-masked]>
Date: Sun, 16 Feb 2025 09:51:18 -0800

On 2/16/25 09:43, Bharani SV-forum wrote:
> Adrian
> Post pg_upgrade command (success ) from DB ver 13.X to 14.X,
> I had used following command
> 

You are done then, move on.

> 
> Need your best suggestion to avoid any unknown headache

Keep on moving forward. 'unknown headache' is not something I can help with.

---

From: Ron Johnson <[email-masked]>
Date: Mon, 17 Mar 2025 15:17:26 -0400

Aurora is very nonstandard.  Thus, "we" don't support it.

Having said that... "report running out of memory" smells like work_mem is
set too high.

On Mon, Mar 17, 2025 at 3:12 PM Bharani SV-forum <[email-masked]>
wrote:

> Team
> Any one faced similar issue with Ver 13.16.X
>
>
>
> ----- Forwarded Message -----
> *From:* Bharani SV-forum <[email-masked]>
> *To:* [email-masked] <[email-masked]>
> *Cc:* Bharani SV esteembsv-forum <[email-masked]>
> *Sent:* Saturday, March 15, 2025 at 12:18:04 PM EDT
> *Subject:* Known bug (memory related) with respect to Aurora postgresql
> 13.16.3
>
> Team
> We have  Aurora postgresql ver (AWS- RDS) ver 13.16.3 and we faced issued
> with an report process (batch process) running out of memory.
>
> regarding url " https://www.postgresql.org/docs/release/13.18/"
> and url :
> https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Updates.html#aurorapostgresql-versions-version1318x
>
> i am aware of
> a)
> Critical stability enhancements for 13.18
>
>    -
>
>    Fixed an issue that in rare cases can cause CPU usage spike
>
>
> Will be implementing RDS based Aurora Postgresql ver 13.18 patch fixes by
> next week.
>
> Any one had noticed or have experienced w.r.to memory process w.r.to RDS-
> aurora postgresql 13.16.3 ?
>
> Regards
>
>

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 2 Apr 2025 08:31:42 -0700

On 4/2/25 08:18, Bharani SV-forum wrote:
> Hello MVP's
> Good Morning
> Any industry best practise to overcome this specific malware "pg_mem".
> 
> url = 
> https://www.aquasec.com/blog/pg_mem-a-malware-hidden-in-the-postgres-processes/ <https://www.aquasec.com/blog/pg_mem-a-malware-hidden-in-the-postgres-processes/>

 From above:

"The first stage is a simple brute force attack. We observe several 
login attempts to the PostgreSQL database being refused until the brute 
force attack successfully guesses the honeypot’s username and password 
(which were intentionally set to be easy to guess)."

After the threat actor successfully guess the user and password, the 
attack sequence commenced. The following set of SQL commands, were 
executed: ...
"

The first command being creating a role with SUPERUSER privileges which 
depends the hacked role being a SUPERUSER itself.

So the solution is basic practices:

1) Don't expose the database anymore then necessary. It other words keep 
access to the instance as restricted as possible, e.g. behind firewall.

2) Don't use easy passwords or use one or more of the auth methods shown 
here:

https://www.postgresql.org/docs/current/client-authentication.html

3) Try to avoid using SUPERUSER roles as login roles.

Keeping up to date is good practice, but in and of itself it will not 
prevent the attack shown.

> 
> We are up to date with the respective postgres server major version 13 
> and minor patch as .20
> i.e 13.20
> Also working on the steps for db migration from ver 13.X to ver 14.X
> We are also update with respective AWS based EC2 server based OS patches

---

From: Ron Johnson <[email-masked]>
Date: Wed, 2 Apr 2025 12:22:08 -0400

On Wed, Apr 2, 2025 at 11:31 AM Adrian Klaver <[email-masked]>
wrote:

> https://www.aquasec.com/blog/pg_mem-a-malware-hidden-in-the-postgres-processes/
> <
> https://www.aquasec.com/blog/pg_mem-a-malware-hidden-in-the-postgres-processes/
> >
>
>  From above:
>

Besides deny-by-default firewalls, be strict with pg_hba.conf entries.

> 2) Don't use easy passwords

openssl rand -base64 24

WordList=($(egrep '^.{4,9}$' /usr/share/dict/words | shuf -n2
--random-source=/dev/urandom | tr -d [:punct:] | sort));
First=${WordList[0]^};
Second=${WordList[1]};
Number=`printf "%02d\n" $(shuf -i00-99 -n1)`;
echo ${First}.${Second}${Number}