Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tbLxL-00A2ba-Ms for pgsql-general@arkaria.postgresql.org; Fri, 24 Jan 2025 15:50:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tbLxK-00FUII-Dg for pgsql-general@arkaria.postgresql.org; Fri, 24 Jan 2025 15:50:50 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tbLxJ-00FUI9-H6 for pgsql-general@lists.postgresql.org; Fri, 24 Jan 2025 15:50:50 +0000 Received: from sonic314-20.consmr.mail.ne1.yahoo.com ([66.163.189.146]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tbLxG-001HzI-0W for pgsql-general@postgresql.org; Fri, 24 Jan 2025 15:50:48 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1737733845; bh=d+/DyqJNAhbVgRK3Wt1OOmLgMY7kyQ/QxSVNPgfYLuM=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=rAHDuW0gFrto1+ni1hzG75f55J21LrzArXxj5+0fCvc6aAVMKUlzvHdJ7Cp2aq2dCsO6EX1tvulfPAOwcFnq7iRoZkiVlJa05oCbo7Q2ufdHvGdyKYG/Tqu/WQf8wRKOlI9DG9szvufbMDtq3/PjM83nqarx/j+tHNA8SpkXBrjdr+rDpmiGjhFRvhhAm8QLOjTAmWGf4/9fiKrGQBodhXD5c8N/izPZWA7r99x5I6VHbpRz2VXNCtur8ajWBgjBTowQ9YmT6/BoGNRCLu0iY5DFPRwOWXW4iCaUU14YEQWtU2/Mvl9EGSeSgWilUUHn8vEuerpZQ/F2Vir5gPx+8w== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1737733845; bh=EQKqa+tHLe7lsDEwLS/M5AJz9w7LkLafZA6aY6/BL1f=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=TSIKuSvqy9tczoW3uOpChd/yu18HNPCD1UOGV+l1vz5nk+huJad4+50i5TCFzfXdah1muaR2ThBUERn0Zja6USiRhuyt9WG/6QdVJeBNsEE/wOE8qce3Hjt6ImSIH4KXUtqRMpswkBGKwi2vcvABOO89GewJ3W68CGafvBV/1cmAyQe/guGesjIvOwe1HnjN+s2TEIgFszfHdqlkG0Af0ImsKyRi3OBZsVSyPKKSUV977+76kzKczk+jPbGSApmXIC9mIW4Arjf51hOAFniQvdzb/EMMtSjMJPwAfIYNNMkQD3OVx2F5KbOti90ODera7myiWNVLHSiu5oSKe7dl0g== X-YMail-OSG: l_eMrOgVM1l2ldxw1nrKvU0qVh0nGpCVQ_gsy1F1XC4PqEbm3N.U4ndVrid8yD_ VZen.lvoPgT6TS0.gYXS30yBQ7XPJmdUwPI8iY6bhF72JVObolbGNWxlzUql0.YNi_cXsk8GuE3k kb4GFfhXniISaYPAwcsqPCmWHzvuoKb83AX28W1Kq1Ha7dQawbafWkoPB7p4wBBROWEenZt88vmJ b0_MLmTmBfXYzuZRMcxb9eGJ85gz2U2wNYlqsvrEcr9e0PVt6Jklcdblcfyc3CTdzsAkY3txFlnc ENu0kkFFgcgPFA6uydKSGGCorFVs3T9Zeh2pWfNByIp.PYYi33trXPOXYHh_v4Z4TbppGsBCD_JY HTwotBSaDUfPG5wnLtM5kJbk9iSv3q4.O81bFN7DLgCSz_kSdhX.7GxAIMMr1WKTQEV8ll0am0DW 51C9MteyM7n.ZekqxrD8gnrMv_1Zp5IwzwfCnnyNHGNq7CFMm7tFxpSk9d.eW_4sL3.mFvITNvU. T33GqhFEd7tiO_kkxoDUKAWghnBqX6tt.fIRu313_sj5vGF2bX4E50fKjd6CTwBr5wu.ppJrFEt3 F3S1V7IOnz7DRHi3.ErJFON3AShbWy.cssnxYUE0Wifdj4S.FIs5jErCoyiYb7QmIe5UHjJlq7EW a.eOcSV1lzwyPBcB67wXC7YB2pphJtuREALos.jfAGD5EtpnNfpsaNp7vlrQozp_grIsB2YVd.s6 Sy4IPcOgtmCxEoshPa2Cy1PsphajY0R.ka4R1nGEENKHWOXqD0bLYD_AbRkHZPbMGUSrLEHz7kB5 LI7me6vEUFectfiACByFRaxAa9ROgY.tk2w7zOMWeJffZzLyNxTHUK4rqMuZ0dpayK0s2lglMOqt OvOe.uhqQ4RGjbCeUuqJPnobnRvWxfoKos50KgYRm3Q_vA9_ve.K7GkpXyVLo46eBU5riQfUnLOx VGLC8oiVyxkqOtFsgwEopyfKUwygDdJLJQnhaUPynZVpmmqCQ4B6x1tVZJ24CSC63jYyGkFezM4H bo63rLUoch0Z.6YjrFUTj.DejiF.CHKdvjvs5vQj4fRZqAk3yc6jG7_gWPnMRJh8hSTff9ieIUI2 dL7vU4lNj_4LaczoZ52OSziu50SwY9CwlMMVSpDiXa3CTaZO6uqpuOfOysO54Ocet.G2mojqwOQ1 5Gh9DqybN31nGGqI4PBBjStNijDUJsl9mUDRrfKEOEGxfDYwj..09vzq0eE9O_eF03EHBYszbO3J PTO1nVPKZSilrMZWg1PkjaP6OqPaCEujgdHnx9gyUcu3BIeOEL_YfzKEpIN3OPJytz0zapIB9pUO Wing18OKchnrxq1DyzfezTOYZuQrmug.DHCnWiyoyK8wc4OYhm83TE4yVFUmB51OUw_H_EwOEpxO BPy9SRzEgUWc3M0rxb9jWNiMfOsqgBWj8Em.44KqO0Nv9TYctETfwaLpd10W2NMXTonz8hEmxT8R Ug2II9lk6csQ9yqt.ilTCogTFJk80hi0JvvIUo_YBdQdDZs1f9ONEEjQTXk8CEaf9c99F7JZyAae dPwVByEMypLNERZBf4m_w2sd3_6HAQe3hFrTpptFCNuQEypARM6EQUbwIOn4kifomcvGto.EIe2S L8BDDyKaMr.IIelZE5On_giHy6ALMo29j9B4.pZm5pFKmPz1zbkyMg.2_QFHKqkE7c_5TRdI67IG qyVMVZ04xwcUkwfT777t2mVD16HdO4Rjr4aQwwMEN8ljpgTqQnEqdcFcaYbLoFG0vfa4XAtY6e2H Rt2qAB4aNuK7LNor_1gcGSFy_clfXnJpLImf9IIR..Gr8m9hEtWHoyFFI11tKQbw3V2mO4PSTfwz 42K10UZPfYeZ34R0zEcLwaIAc4KD_GktTA87MiZ86x_SN2iBUQtxTMgJWiV5vz59CnfixUAy6I1P j20APZkY0on7EThVFxIuIztHxA5YJ7A4C7Cx3H3U36qVILN0kujvcbLMnJa.0GLt5aBcJhWNQp6M jqXw04SNHn7SUjjjwNlEpf4rsvbN4A5dEjRKoj8qNHlcLukJd4j67mB2whVnTMBH0GGjzMFEWPcf 4__YyEEjwUOpzv8tO7BlyHFoy9s5WJuUKGJgcq36XUx2MQr_oystBRYpO.gbmmVHho68CqIxhh0P p5A7iTGmLK2djsL94RvR01U73EuzTI7lp6ltVGHD8bazJog3eSV0Mzbm3KtbkR.cqG4WjVqiUwV1 SCkP7dOeNs8LusbTrO43OKldYq.gxL5d3MZ00d4uin7uGYhDamjCJp4e3i60s1X4yWQNIuB37BYD gNm55EOghyUzOmlSLIw4LH2.E.Se53Us- X-Sonic-MF: X-Sonic-ID: f151bb01-526d-4d0e-b94d-bd7321a9c623 Received: from sonic.gate.mail.ne1.yahoo.com by sonic314.consmr.mail.ne1.yahoo.com with HTTP; Fri, 24 Jan 2025 15:50:45 +0000 Date: Fri, 24 Jan 2025 15:50:41 +0000 (UTC) From: Bharani SV-forum To: Greg Sabino Mullane , "ronljohnsonjr@gmail.co" , "adrian.klaver@aklaver.com" Cc: pgsql-general , Bharani SV esteembsv-forum Message-ID: <1763130721.4001842.1737733841628@mail.yahoo.com> In-Reply-To: <1482982714.8486017.1735661703839@mail.yahoo.com> References: <0558ddd4d71641bdb41fa49b2425f73c@safrangroup.com> <98965993.3138805.1731699978332@mail.yahoo.com> <564950518.5117550.1733177884387@mail.yahoo.com> <07ab2d83-ffe5-4bec-9626-22a68f732579@aklaver.com> <273a88dc-4134-47d5-bc19-30ff5f97926c@aklaver.com> <498dfb34-4dd7-4f48-8188-355e1488d7e6@aklaver.com> <1061066336.5835157.1733316137292@mail.yahoo.com> <1482982714.8486017.1735661703839@mail.yahoo.com> Subject: Help in vetting error using "pg_upgrade" - steps for Postgres DB upgrade from Ver 13.X to ver 15.X MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4001841_759661702.1737733841625" X-Mailer: WebService/1.1.23187 YMailNorrin Content-Length: 17637 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_4001841_759661702.1737733841625 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable TeamNeed your additional input.VM is based on EC2 OS Version =3D=C2=A0Amaz= on Linux 2=C2=A0Existing DB version =3D 13.X - Pg Community under EC2 - VM = (Same VM)Target DB version =3D 15.x=C2=A0=C2=A0- Pg Community under EC2 - V= M (Same VM) i will narrate the latest error executing: SELECT pg_catalog.set_config('search_path', '', false);Checking = for presence of required libraries=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0fatal Your installation references loadable libraries that are missing from thene= w installation.=C2=A0 You can add these libraries to the new installation,o= r remove the functions using them from the old installation.=C2=A0 A list o= fproblem libraries is in the file:=C2=A0 =C2=A0 /var/lib/pgsql/15/data/pg_u= pgrade_output.d/20250122T161405.335/loadable_libraries.txt output of "=C2=A0=C2=A0/var/lib/pgsql/15/data/pg_upgrade_output.d/20250122T= 161405.335/loadable_libraries.txt" is"could not load library "$libdir/dblin= k": ERROR:=C2=A0 could not access file "$libdir/dblink": No such file or di= rectoryIn database: main" I cross checked on the=C2=A0 existing (old db _version package list along w= ith new db version package listand found one of the package is missingpostg= resql15-contrib.x86_64=C2=A0 Any guidance how to come the error. my unix system admin is quoting that=C2=A0 he is facing built issue with po= stgres15 version w.r.to "=C2=A0postgresql15-contrib.x86_64 "=C2=A0under AWS= based OS "=C2=A0=C2=A0Amazon Linux 2=C2=A0 ".=C2=A0His version is "It need= s 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 libra= ries because we already have a different python version installed and clash= ing" Any quidance to overcome the error, as the existing python version being us= ed 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 pos= tgresql - EC2 version=C2=A0Best Viable option. I have tried with previous s= uggestion steps and found "pg_upgrade" as the most viable and faster=C2=A0 Regards=C2=A0 On Tuesday, December 31, 2024 at 11:15:26 AM EST, Bharani SV-forum wrote: =20 =20 Team I followed Greg suggested steps .One of big had only one table and around f= our 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 /=C2=A0 lowrite=C2=A0 etci.e=C2=A0pg= -dump-creates-a-lot-of-pg-catalog-statements is there any alternate way , to speedup=C2=A0 this process. i can see in the select count(*) record count is matching (target and sourc= e) Regards On Wednesday, December 4, 2024 at 10:47:26 AM EST, Greg Sabino Mullane = wrote: =20 =20 On Wed, Dec 4, 2024 at 7:42=E2=80=AFAM 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 g= oing to 17 now means a better Postgres today, and no worrying about replaci= ng v15 in three years. * Create a new Postgres clusterOn the new VM, use the initdb command to cre= ate a new data directory.Use the --data-checksums option * Start it upAdjust your postgresql.conf as neededAdjust your pg_hba.conf a= s neededInstall any extensions used on the old VMStart the cluster using th= e pg_ctl command (or systemctl) * Test connection to the old vm from the new vmOn the new vm, see if you ca= n 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_du= mpall -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 free= ze'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, a= nd 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 s= ame. Take it slow. Go through each of these steps one by one. If you get stuck o= r run into an issue, stop and solve it, reaching out to this list as necess= ary. Cheers,Greg =20 ------=_Part_4001841_759661702.1737733841625 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Team
Need your additional input.
VM is based on EC2 OS Version =3D Amazon Linux= 2 
Existing = DB version =3D 13.X - Pg Community under EC2 - VM (Same VM)
Target DB version =3D 15.x <= span> - Pg Community under EC2 -= VM (Same VM)

i will na= rrate the latest error


executing: SELECT pg_catalog.set_config('search_path', '', fals= e);
Checking for presence of required libraries    &nbs= p;            fatal

= Your installation references loadable libraries that are missing from the
new installation.  You can add these libraries to the new ins= tallation,
or remove the functions using them from the old instal= lation.  A list of
problem 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/20250= 122T161405.335/loadable_libraries.txt" is
"
could not load library "$libdir/dblink"= : ERROR:  could not access file "$libdir/dblink": No such file or dire= ctory
In database: main
"

I cross checked on the  existing (= old db _version package list along with new db version package list
and found one of the package is missin= g
postgresql15-contrib.x8= 6_64 


Any guidance how to come the error.

my unix system admin is quoting that  he is facing built issue w= ith postgres15 version w.r.to " postgresql15-contrib.x86_64 " 
under AWS based OS "  Amaz= on Linux 2  ". 
His version is "It needs libpython3.6m.so.1.0()(64bit) to install package: post= gresql15-contrib-15.10-1PGDG.rhel7.x86_64. in our environment. I can't inst= all 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.re= q 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
 
<= span>
=20
=20
On Tuesday, December 31, 2024 at 11:15:26 AM EST, B= harani SV-forum <esteembsv-forum@yahoo.com> wrote:


=20 =20
Team

I followed Greg suggested steps .
= One of big had only one table and around four million records
i am doing dev env restoration into new vm
t= he target VM env is an POC server and took 3 hrs to restore four million re= cords.
Now it is doing process of lo_open / lo_close = /  lowrite  etc
i.e pg-dump-crea= tes-a-lot-of-pg-catalog-statements

is there any alternate wa= y , to speedup  this process.

i can see in the select c= ount(*) record count is matching (target and source)

Regar= ds

=
=20
=20
On Wednesday, December 4, 2024 at 10:47:26 AM EST, = Greg Sabino Mullane <htamfids@gmail.com> wrote:


=20 =20
On Wed, Dec 4, 2024= at 7:42=E2=80=AFAM Bharani SV-forum <esteembsv-foru= m@yahoo.com> wrote:
a) is the abo= ve said steps is correct with the given existing and proposed setup<= /div>

No. Here are som= e 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 clu= ster
On the new VM, use the initdb command to create a new data d= irectory.
Use the --data-checksums option

* Start it up
Adjust your postgresql.conf as nee= ded
Adjust your pg_hba.conf as needed
Install any exten= sions used on the old VM
Start the cluster using the pg_ctl comma= nd (or systemctl)

* Test connection= to the old vm from the new vm
On the new vm, see if you can conn= ect to the old one:
psql -h oldvm -p 5432 --list
Y= ou may need to adjust firewalls and pg_hba.conf on the old vm.
<= div>
* Copy the data
Run this on the= new VM, adjusting ports as needed:
time pg_dumpall -h oldvm -p 5= 432 | 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 th= e same.

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

Cheers,
Greg

------=_Part_4001841_759661702.1737733841625--