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 1tSetm-000ZlJ-7P for pgsql-general@arkaria.postgresql.org; Tue, 31 Dec 2024 16:15:14 +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 1tSetl-003bXJ-HM for pgsql-general@arkaria.postgresql.org; Tue, 31 Dec 2024 16:15:13 +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 1tSetk-003bXB-Oz for pgsql-general@lists.postgresql.org; Tue, 31 Dec 2024 16:15:12 +0000 Received: from sonic312-24.consmr.mail.ne1.yahoo.com ([66.163.191.205]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tSetf-001Qho-1Z for pgsql-general@postgresql.org; Tue, 31 Dec 2024 16:15:11 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1735661706; bh=zvhCIRXk6jNni8g7Es3Ib2DxFc2uROS+Nts+WTf+Lfs=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=q5UOT7u0MQyjK+LBxmZFV2rowj9dcLLcThUoSXV33zoEkmo5IzSZd/uVfKmQ3+idwGweg3pDOV7j92Dk4wn07pmBkkyaciHwWM3hx4ZtCsOXOmf8l/+wX1008ztrEoojqZqVmAetzsDtnkcUJ2Fiua7Rb5qnKSh2lbf4/838Ei3bg2BRSjegzl/PMAerH+py+CPzVmSjGBc7ZS74jakaIEtdVQxuZauWp8sIA2fDIeVF/8f4D77sfOU0thu28PUfnY3/2Mok2aRlVXPENHVXU5jv2lYUXZfnE4SJhMOQ/KcNoJlH/1WYxMDWfkrSG8dWck6IttLN3MdhrHdekGWc3g== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1735661706; bh=qseo9qT4E9LReioDlYhXys1rQrZoltRv+2ZqpEqdOfN=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=KZ4FVTtWxc/4hArfNNfw+XNbzUd/HrnQmY30AnzScr91qiqP1Tdo7xWbYGw5CuRqF2csCLBJzy/vYQfId9BvCgXP7toOU19TSbx3RRREg26QQ1FFLjgJP4KxgK3SRl7fqGh7opStUTKU5Xtm6TO9ZIPhHYlA4lI6Qp2oyA6WnKj9ja+E4HuzKMsOQ0bMn+SDA27Sxoo8+7epzoL6SC95HwfrU1YvLozNkCYsmWBz3RIcdykTjAUbR5t3sNUWt5Ws8GDoWoelbz4KEWA/1rcXYFGFiBvDBwdguNHeq1kWpDNo6sbXRwqDeOKFk7yE5ciuAPluZ1KnvixGHuieVEKArg== X-YMail-OSG: oo1u3boVM1lVTxgV8gNvRjOmSTwndBB5iyGVN7kXxnCn9mlnT6ymTRXPMWnGZ5w CP5V3Zzkju5a9rC1u1_1IHhc9o0ZIWUrOeElC2Gxq_HwxqzWRwXY1V42KfjNfQyfQ0PksUctav.2 vEspKY0goa57oaqjTfnZl0qVr8CfttPpZqMGgxh1kIYwCGYF4J6G2Zz4Ue5ba7Ll86R48geS3_wt xTcIt.IgIctpj74s4Pru20Dcq6SBuuZKOXoztzzZSh3TPEOybWx1VAQ8j62aTLGKqd6eD2uETcAl WylD0Riu1WxgHu_71ptRmgIGR7k41xVf_0as3sUH5uAOqxIJ5MpdPGYXsOuqE1M91ojjtQXVzRuq 5g12dlLqlpQpZzWP4Jcua_nw37HNKix1rrSQxI2hTNOl9knEetuB5LgktOrQekz04Ru26K6_guQ7 pSSEty3KS3F1EcGWC7Ily1qqjJKe.SMG4Zql59BY13EnLzLjnzUk8UWrOPm.JFgJPWnO.EWB83G8 LjJ1UUHQhzk2Ljd7.gCI0urraHpK4oXR1fNErDc5a1cpNkDF4wY_18iDvZr9mMYGQt_OmYwoTVvA eUeoDAi4rB.xfkbyn7c1vYppmdHuKwRcRjhKjkk35QefvK1NeNwS6.9EEs0SFutOY04R2k.jBQIL lYd13.ACbkC5O9_kjCSS0tIcftolB._dyoguo5ng7RyMGvUyNy4IPGqQBhP.Ca1cyzyTFzpNlSGL lyZ3LsjVnhd5MLPksqM_kv3jx4hVUzYTRIwFLsERiJae8P_p8dG8SA0M1ilMf87wum.DeWPwsp2z c6AThWhf0CjOKOyiIWpjdJAaFjlmYWC2R_N_hIudtnd29pmBlM83zgT0RYQqgfCBQ.pAd4xOblU6 6eKHSPs2bWHbUdUlBh6pMgdxmzdijA0qll8XBuIWmZWm09C5aJfcUcRAtgElGmsARD9FTzblzQpf 3eKOdZgWCIonSXxeVkkORekRHJggr6bKQkdLgYw5bngidNL0jtlFl5wdfIEKCkE8lrGyERGTXi87 m62ykVLmkDK6_.L2O0946VW2.xQEzRQ2TDuw.NEPUHYATJ4y1zwXFFocK6cuD_HXP6L_tAjuPtoS EFiYixzxMXs0TEiUVjOjqSBVcg8aGwn8zbLXc1dUrKNn9VWQ3F0m9fxQi55ueVXotwQq4BtEosDy CVojL.8Igl1tG2UGaC404K81DASzRI8_hhyq8l0ykkkNQR6uR9H16GLFOFvsaskz7uiWyBLvzlD1 gpOlXVeeETW5McXbfqEBjno1lAJPO_xAUi9b4VCVQozSBNjYdO2HWyzaYpRCBW4qmXQLi6DD8MbK uvv6VdXdf3zNWCq00M_9btMqnC.mo0Bqs_DsT9gp8ARMZOXXSaZcwCkqT9g7YJ1_t4vbvfP71.S3 eSOGllZBVgskU4_Ozd0f0t.XHa8_21Cp7VXt1YIlD6RqH.ev3io1PIhfQgIgmg4rBGJ1wUgnzC41 8LYrDVcdPGNxXd3Nr5e1eupY9940D4Lwj8fj5wVsgnAO0PHXjgNtTf2x60IfWB9SGYjpXVLwRWP0 XCF65x9Dpn56KtLUJuMQj.0QBan4O0l6K2t87u7nt_Q54WxR29JJqNeE5OTejoOunAQufNgOTYzY Wlz.1m6nFxQPh7f8VGkE_2I7rKs0vRo236Wak_VtUFTK4m.Lqu1pzzYArS5P4tg.Avsjd_nJIrmT idnUgPnsaC.Sf.wn_qfXVRE.soHwPmLKA0njUXK6xRKQB_lc0_5rwxP7sqG_LW6E77DavSNNe8A9 vpCSYIrhfS4j3pwlyqt1BDj2JpP2dfpssdHBrj6IS2s4PskFAbRc5Bg8vIB2tEVG19tmIw28KwPo Gwd3ySVNKaoZQINhJPn4fVu2DTWrq2MDWyhf9yzbcNjKbMw8i7tbC2q2Ir7Q1_yjhT4p_bMrNca1 hEI7JJYcG0wqcFmyOBE.HTPDrQaY0tjb.I.LQHqZwrXD0S2D_nF36Mrp6lhtFpLCPniLFVfJuS9v z4jgu8IgIIjzlMmfsky_1nJULU9Rn8Q1wLFD1DjsmjN4UqyT88tE.6GWHo8J5qC4o0TozLoLv0Ga Joj1qwI6YjCAwydjD4AvRJqEKfSIta7jxbj9vyKPZt6UNBwYbtXBGUuK7LKuxCo7F93H27ZkybNN SvE95CSKHFSF4rcRPCJ5_0KnSm7zribISSB0O4Jlu1TsPqQO0hZ4WETLX1F1Kx.vJFrisJq38KVs Z_Kg7YyA5BY35y5x.n3MXLhNHgzF5_mJG4ntxN3C_JkPfYeACUW_KNnQXFjIOMOeAUPXYweSWdCQ 9X.QkfQoaYm_hjDdBEkjxK1vxKq0HNXg- X-Sonic-MF: X-Sonic-ID: c84e42c4-5fe8-4b48-bf4b-4c68f34a7a02 Received: from sonic.gate.mail.ne1.yahoo.com by sonic312.consmr.mail.ne1.yahoo.com with HTTP; Tue, 31 Dec 2024 16:15:05 +0000 Date: Tue, 31 Dec 2024 16:15:03 +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: <1482982714.8486017.1735661703839@mail.yahoo.com> In-Reply-To: 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> Subject: Re: Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_8486016_511332823.1735661703837" X-Mailer: WebService/1.1.23040 YMailNorrin Content-Length: 8314 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_8486016_511332823.1735661703837 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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_8486016_511332823.1735661703837 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Team

I fol= lowed Greg suggested steps .
On= e of big had only one table and around four million records
i am doing dev env restoration into new vm
the target VM env is an POC serv= er and took 3 hrs to restore four million records.
Now it is doing process of lo_open / lo_close /  l= owrite  etc
i.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


=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-= forum@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_8486016_511332823.1735661703837--