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

2024-12-02 Thread Bharani SV-forum
 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


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

2024-12-02 Thread Bharani SV-forum
 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 
 wrote:  
 
 On 12/2/24 14:46, Adrian Klaver wrote:
> On 12/2/24 14:31, Ron Johnson wrote:
>> On Mon, Dec 2, 2024 at 5:18 PM Bharani SV-forum 
>> mailto:esteembsv-fo...@yahoo.com>> wrote:
>>
>>     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.

Oh yeah, that was smooth.

Second attempt:

... must be random noise.

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

-- 
Adrian Klaver
adrian.kla...@aklaver.com



  

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

2024-12-02 Thread Bharani SV-forum
 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 
 wrote:  
 
 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*
> -  ensure to capture all the pre.req meant for ver 15.10 are being  met.
>       - enable logical replication tagged to proposed new EC2 server 

No, logical replication != pg_upgrade process.

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

Again, no.

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

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

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

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

[...]


"
> 
> 
> Regards
> Bharani
> 
> On Monday, December 2, 2024 at 05:48:19 PM EST, Adrian Klaver 
>  wrote:
> 
> 
> On 12/2/24 14:46, Adrian Klaver wrote:
>  > On 12/2/24 14:31, Ron Johnson wrote:
>  >> On Mon, Dec 2, 2024 at 5:18 PM Bharani SV-forum
>  >> mailto:esteembsv-fo...@yahoo.com> 
> <mailto:esteembsv-fo...@yahoo.com>> wrote:
>  >>
>  >>     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 
> <https://www.postgresql.org/docs/current/pgupgrade.html>
>  >
>  > must be random nose.
> 
> Oh yeah, that was smooth.
> 
> Second attempt:
> 
> ... must be random noise.
> 
> 
>  >
>  >>
>  >> --
>  >> Death to , and butter sauce.
>  >> Don't boil me, I'm still alive.
>  >>  lobster!
>  >
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
> 
> 
> 

-- 
Adrian Klaver
adrian.kla...@aklaver.com



  

Need exact SQL query to find List of Detach Partitioned Tables (Yet to be Dropped)

2024-11-15 Thread Bharani SV-forum
 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 ;

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

2024-12-02 Thread Bharani SV-forum
 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 
 wrote:  
 
 On 12/2/24 15:52, Bharani SV-forum wrote:
> Adrian
> 
> Proposed new Server is intended to have higher OS Version (centos ver 
> 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?


> 
> Hope u have understood my question
> 
> On Monday, December 2, 2024 at 06:47:10 PM EST, Adrian Klaver 
>  wrote:
> 

-- 
Adrian Klaver
adrian.kla...@aklaver.com

  

Qsn on Setting replication using " set session_replication_role = "

2024-12-04 Thread Bharani SV-forum
  
 TeamNeed help on clarification on using" set 
session_replication_role "

Assume i am loggging intopsql and issue\o output.lstset 
session_replication_role=defaultupdate coming out of \psql command and 
verifying log fileand assume i come out of the psql session without doing set 
session_replication_role=replica.
what will be the impact.How to find out if the session replicat role is in 
Replica stage or local ?  

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

2024-12-04 Thread Bharani SV-forum
 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 
 wrote:  
 
 On 12/2/24 17:23, Ron Johnson wrote:
> Adrian,
> 
> OP is moving to a new VM when migrating to PG 15.  When was the 
> "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.


-- 
Adrian Klaver
adrian.kla...@aklaver.com



  

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

2024-12-31 Thread Bharani SV-forum
 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 
 wrote:  
 
 On Wed, Dec 4, 2024 at 7:42 AM Bharani SV-forum  
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
  

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

2024-12-11 Thread Bharani SV-forum
 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 
 wrote:  
 
 On 12/4/24 04:42, Bharani SV-forum wrote:
> Team /Ron/Adrian
> 
> Wann to reconfirm
> we 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"

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
> b) 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
> vs
> target - 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 
>  wrote:
> 
> 
> On 12/2/24 17:23, Ron Johnson wrote:
>  > Adrian,
>  >
>  > OP is moving to a new VM when migrating to PG 15.  When was the
>  > "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.
> 
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
> 
> 
> 

-- 
Adrian Klaver
adrian.kla...@aklaver.com



  

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

2024-12-16 Thread Bharani SV-forum
 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 
 wrote:  
 
 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.

> 
> Regards
> 
> 
> 

-- 
Adrian Klaver
adrian.kla...@aklaver.com



  

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

2024-12-16 Thread Bharani SV-forum
 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 
 wrote:  
 
 On 12/16/24 13:19, Bharani SV-forum wrote:
> Team
> I am getting the following error.
> 
> pg_dump: error: error reading large object 2113418:
> 
> pg_dump: error: could not open large object 3391830:

What user are you running pg_dump as?

What version of pg_dump?

> 
> 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) ].

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.
> 
> We were told by AWS team, in the new VM tagged OS [OS= amazon linux 
> 2023] , pgsql Ver 13.16 is not supported

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
> 
> 
> On Wednesday, December 11, 2024 at 03:57:31 PM EST, Adrian Klaver 
>  wrote:
> 
> 
> 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
>  
> <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.
> 
> 
>  >
>  > Regards
>  >
>  >
>  >
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
> 
> 
> 

-- 
Adrian Klaver
adrian.kla...@aklaver.com



  

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

2024-12-16 Thread Bharani SV-forum
 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 
 wrote:  
 
 On 12/16/24 14:30, Bharani SV-forum wrote:
> *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 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?


-- 
Adrian Klaver
adrian.kla...@aklaver.com



  

Re: cannot drop a tablespace which never exists in pg_tablespace

2024-12-20 Thread Bharani SV-forum
 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 
 wrote:  
 
 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//ABC/15/data
> 
> the tablespace was created under  /XXX//ABC/15/tblspace/<>/
> assume tablespace name is abc_data
> 
> *cd /XXX//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 |        |
> 
> not having any entry about tablespace abc_data
> 
> Can you guide
> 

-- 
Adrian Klaver
adrian.kla...@aklaver.com



  

Re: cannot drop a tablespace which never exists in pg_tablespace

2024-12-20 Thread Bharani SV-forum
 Adrian

initially i had created tblspace outside pg_data env variablee.g PG_DATA env 
variable is defined to /XXX//ABC/15/data
the tablespace was created under   
/XXX//ABC/15/tblspace/<>/assume tablespace name is abc_data
cd /XXX//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 
 wrote:  
 
 On 12/19/24 15:36, Bharani SV-forum wrote:
> 
> 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:

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
> 

-- 
Adrian Klaver
adrian.kla...@aklaver.com



  

cannot drop a tablespace which never exists in pg_tablespace

2024-12-19 Thread Bharani SV-forum
 
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


 

Re: any tips to have restricted inbound and getting connected with postgresql dB

2024-12-20 Thread Bharani SV-forum
 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 
 wrote:  
 
 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
> 
> Changing password is the last option
> 
> Any tips
> 

-- 
Adrian Klaver
adrian.kla...@aklaver.com



  

Re: Help in vetting error using "pg_upgrade" - steps for Postgres DB upgrade from Ver 13.X to ver 15.X

2025-01-24 Thread Bharani SV-forum
 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 
 wrote:  
 
 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)
> 
> i will narrate the latest error
> 
> 
> 
> executing: SELECT pg_catalog.set_config('search_path', '', false);
> Checking for presence of required libraries                 fatal
> 

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

 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


-- 
Adrian Klaver
adrian.kla...@aklaver.com

  

Help in vetting error using "pg_upgrade" - steps for Postgres DB upgrade from Ver 13.X to ver 15.X

2025-01-24 Thread Bharani SV-forum
 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 
 wrote:  
 
  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 
 wrote:  
 
 On Wed, Dec 4, 2024 at 7:42 AM Bharani SV-forum  
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


Help in vetting error using "pg_upgrade" - steps for Postgres DB upgrade from Ver 13.X to ver 14.X/15.X and issue with python binaries

2025-01-28 Thread Bharani SV-forum
 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 
 wrote:  
 
 

On 1/24/25 10:01 AM, Bharani SV-forum wrote:
> Adrian
> Thanks
> 
> This is the exact error which the system admin is facing
> 
> 
>     postgresql15-contrib installation on Amazon Linux 2 fails on Python
>  shared lib dependency

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:

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

Shows how to install Python 3.6


-- 
Adrian Klaver
adrian.kla...@aklaver.com
  

Help in vetting measuring egress/ingress w.r.to " pg_dumpall " - old vm to new vm

2025-01-28 Thread Bharani SV-forum
 TQ Adriananother Question on the measuring egress (out bound traffic) /ingress 
(inbound traffic) w.r.to " pg_dumpall " during usage of 
pg_dumpall-h    -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.


Ver 15.X and restriction for schema=public

2024-12-23 Thread Bharani SV-forum
 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