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 1r5qWL-00FOjS-NZ for pgsql-general@arkaria.postgresql.org; Wed, 22 Nov 2023 16:56:13 +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 1r5qWI-00AugN-0o for pgsql-general@arkaria.postgresql.org; Wed, 22 Nov 2023 16:56:10 +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 1r5qWH-00AugE-EK for pgsql-general@lists.postgresql.org; Wed, 22 Nov 2023 16:56:09 +0000 Received: from sonic308-9.consmr.mail.ne1.yahoo.com ([66.163.187.32]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1r5qW9-006pxE-A5 for pgsql-general@lists.postgresql.org; Wed, 22 Nov 2023 16:56:07 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1700672158; bh=Z55zX92S0eCfTRr5zyp6LVW44uzjSlvnyd5Fx/R2qgc=; h=Date:From:To:In-Reply-To:References:Subject:From:Subject:Reply-To; b=fccmdCTbLsmUIXw4TQZZzkEY2xwi33LEMuJsPPDSni8gzZUxHEzd8bmtKWs/wMA8B+RdVuyBKuJz6ZCxq+YOCYtw/4qK7zif0HZNCn26jTsvfsPqTWt/DPEQDRzJriCj+8fh83cAOxeqTBkuUMaOTcH1LuUChLXhYhB5o/Y99G7ozR/xv+yoFOzfvqUsaVSowlNgQEXkdREYvf/K86KcKfylOcrXPlywzdmKExg6F5a5zG60RXk8MBykySBXv7rPYqI++lvtNFN3PVjGoSunf2ckTaOiPToPeXupH76NrktZFfDJ8ygsUjEHJg9QoLWoW2MOSl5iKIqmF6XWloDWNw== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1700672158; bh=r+aZRwU8vLtWMsxYu20nYLh4SDC+y++AtF1FNYNr970=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=SonfRFzNQD2zmrU1BM5twx5Ki45g+nBebb1azi73hnuUnHppD/UrYaly/Cg94KkE+PegiDqwyhx85rsaRf5t9nQq7NEmB+4cwFlaqmtezw23JcFy5/UC7sO6WVbh0bmKYHFc4+kehaFZjkBVQM0+t4MwcwQhN69ww9fdjEb4+vg1y/4EToDLqME259Z2n4MVxNkoVWijVCEtIuXwXaNw4AOF2XQJ9myVChrAhNu5KpQ0jN35XfVetptGj/klcGYJnZ7KmX6lDd6Bm7V0xz733XmNevPy9oJ8jQh8NKnoX7sSoZeQwMpdXOMF0PcgWOIqhAIN3I7DRFgaxb+Lv4lq+g== X-YMail-OSG: Ff2Sv4gVM1lWob3x5hc8ufGHgLOJkbdekoFD.I6ppX1oRnhOPnTHKiXjFNmhgHR OKPWRXVo_HgEUwnz4SeLHqe7wwDyfTVaNeP99BJn6Y6NIxlmg_eHyk3_RDRIy860xYlzk5VBklcu SqOlT3umJdh4BgWTAagQ5_nZP1rp11RpRy.Zz3NUKvFdlE1SumbxHOJAYwVMvA3jhGGHnU.4uOCA dCbyGztXlbL45cC9ZkYfWPNT0LTrTwFiYyfp0L6bs2EgmKLwPvvSyLcecD3ggu.qCv8tDzXO8TiT 6Q3N_pRoUKJ2prZTsFFI7kvfc2LjHbuH3za7o_uy.Ku6V6RX4K4FarxLNVlDxmLl_niz7aKi1.wj _AIk0U5FfyxOZ3Hcuo.Hck.OswhMdaRg.ry.nlA0Lwg0p2wg3zCGwDtdIh0JW_.5jiHpJE.MXvCV yD9_6O21t.Oq2kLcxVQ_MKCLJ.yrgEvfkxHX1OVl_N9W0NpkE56FBuBx9k4IK6.VwIGfzeiIIoD_ U63AXhNaJ6Yn7N2_YPJyIregEUh3OGAa876eAqcGbN_CbztRCVMXwnkWnnD7uVL4DqNuByn8Kezt xBhnJ.HnT46eFaQc9_0hDp9kpbj03nF8NaxQb2rGQTaQ1y3rXm1m15Q0_J.uAZDk3VMrsWj2f.0G wGp3Bwt0h1S5wItteWkdbUxn7HWM7JJTR7ivL8lqpWnKTZNrvepPlqciUYr_0yIN.k9.GbIIaj2o gENLwCy5hqVPo5187PO6Do92netNrT1.1pWLGJ13BFtNBPz6u6jYviRca7ZQBGeNf8BCgqJmUFLw nD44.UI3RZvYSmqVyitblWLA_5YeJ9_7hFp.rpMD69yOLVRBKC7MnrPKuQlm.y4StJ0ik2enIbJx f2ajoEIidSoAaB.jQNShPOb2AhhhCMxr3LJVUFglEqI4DIbtisTXClZ1uVrvg1NrnhcrViZzGVCn aWcKSjO6t468YiKApuFPx5UQ4TuAT4qCszG1x6XrOrY6M2yC4mcl_2YQwXBQCp78E.panNEzK5QE lFDyuCU55_FR.BEzjj9sH2omU_8UAZSxkt5XrR5Z4BGQntOKbpl4XQYwrBa_X_KodFiXBVxFDZas 89M30RmrEA_Qo5drExomzVcrDGs2FKViPFdLN0ZWOwBzSDMBZdy7TwUOBgXYzCqz3pYurfWm1KAM yrGlHH6WRlOzYYFeujUGngbKHElDmNXOQoh_Pk708TUASdgEh4p7ShiBPNCkl5_qQkxWlnF2Rosc m8vhPnJ5Kg75JngnML.L3FN0WRK3tAbsEVPYKFSpBaB7JE2WW68yE8xV_RYU5GeHaCzMAqUEIkH_ LhdALdSJnMT.8zxUvTjoJNkn0rHvH1lPI_UYeGVNtmbtdMdAr1LbyNzkqq0qDLSuTrAUFg49hpRI AbZTUcbLZOM4dOfHwQUx2qfe40kcWpIeJkMhnb8Wa6dRiGEPnfDPWkbzzbJfUgynXNhhcMbppGuj N4M49rm7_coXGE64ZGgQFpoi_fJDi5xbl7XI__.iytHLB2HrLb1a62FWnNoA8r3mAeY2lolAMZlB BeXar4MCTisvFYw8sTdp6AnYFV7ZR0mWxUdCR8VYKHZ2FSwYBp5aDdEOxzMyuitPrpsJB6QuUkde I6C1kr_7iGaBK11K19XDFKc9U5FMQKg21wefksYt7NusWAsgH3ASi_34jzF3YSeWqE9E161TyzIk nyGMKhKTMhykYmo7Nh2hrdXA6RglUnGZUYPwUhCb_3na1l.oocHm9HWaUdzCSTLPFLrFDWSuIDXE eCPBrOICosJ7x33u9JVSvQ7cXzGjSucWrWq6TzbHI.Zbd3IYscbQ2tqbaauBNbeyF6K85cM0ocuv Bm8WK53cxj24j0bA8RXFy_KwlJ4OI.xSubg7d5yictS9q4RyC1C1PikTi5XflQ16f1GqM34rEBVI DSaCtjGEgzDtZjXYBcVN1AhN.VOiSAq9HYYPwdErxstE4nz3aDLEsnHP86kJ7I0.4.9holc4ggTx mezYVBlA6OH6.q8Yi_.AEhc3A6wWrEa8pxpa798yl0nbXoaGzbjROwiM8AI1R0PhK2N7neEpEz95 fBUTXJZdnzML7t7DTdSjIwumysQyI7FxWnzh28RIW7NIaHxPpYiwRqOd6RndGO1Ic3.YQ0.Xyyif AqDHm0zY2Jn04JJqlgdY7o.nxAlM3.O8ZNc.OEQkIWpSwtnoGrNGGafBN9UTpp9vKH1ADvf_99dr s0C5e3Ddz1qMWhbGzoZ8foEa7z3KbSlXDz8kMXrvtf_RRtA-- X-Sonic-MF: X-Sonic-ID: 3ca3d3ff-a56a-437f-89e7-f22d49dbf0ae Received: from sonic.gate.mail.ne1.yahoo.com by sonic308.consmr.mail.ne1.yahoo.com with HTTP; Wed, 22 Nov 2023 16:55:58 +0000 Date: Wed, 22 Nov 2023 16:55:54 +0000 (UTC) From: "Efrain J. Berdecia" To: "pgsql-general@lists.postgresql.org" , Adrian Klaver Message-ID: <383739960.7061560.1700672154915@mail.yahoo.com> In-Reply-To: References: <362032628.6955904.1700659540303.ref@mail.yahoo.com> <362032628.6955904.1700659540303@mail.yahoo.com> Subject: Re: pg_restore enhancements MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_7061559_397944700.1700672154914" X-Mailer: WebService/1.1.21896 YMailNorrin Content-Length: 10924 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_7061559_397944700.1700672154914 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Thanks, the issue we've run into, which I guess could be really a setup iss= ue, with running a COPY command while executing pg_restore,=C2=A0is that if= we are restoring a large table (bigger than 500GB) our WAL directory can g= row to be very large. I would think that if the pg_restore or COPY command was able to support a = batch-size option,=C2=A0this should allow postgres to either archive or rem= ove wal files and prevent having to re-size the WAL directory for a one tim= e refresh operation. I'm trying to gage how feasible would be to start looking at contributing t= o add such a feature to either the COPY command or pg_restore. Thanks,Efrain J. Berdecia=20 On Wednesday, November 22, 2023 at 11:37:13 AM EST, Adrian Klaver wrote: =20 =20 On 11/22/23 05:25, Efrain J. Berdecia wrote: > After working for a site where we are constantly doing logical pg_dump=20 > to refresh environments I've come to miss features available in other=20 > RDBMS' refresh/restore utilities. >=20 > Someone could point me in the right direction otherwise, but pg_restore= =20 > seems to be lacking the ability to resume a restore upon failure, is all= =20 > or nothing with this guy. There also doesn't seem to be a way to control= =20 > batch size when doing the COPY phase, therefore preventing the WAL=20 > directory from filling up and crashing the system. The above needs more information on Postgres version(community or fork),=20 OS and version, the size of the data set, the storage type and size, the=20 Postgres conf, etc. Restores are being done all the time and this is the=20 first report, as far as I can remember,=C2=A0 about an issue with COPY and= =20 WAL in a restore. pg_restore https://www.postgresql.org/docs/current/app-pgrestore.html Does have: --section=3Dsectionname =C2=A0 =C2=A0 Only restore the named section. The section name can be pre-d= ata,=20 data, or post-data. This option can be specified more than once to=20 select multiple sections. The default is to restore all sections. =C2=A0 =C2=A0 The data section contains actual table data as well as large-= object=20 definitions. Post-data items consist of definitions of indexes,=20 triggers, rules and constraints other than validated check constraints.=20 Pre-data items consist of all other data definition items. AND -l --list =C2=A0 =C2=A0 List the table of contents of the archive. The output of this= =20 operation can be used as input to the -L option. Note that if filtering=20 switches such as -n or -t are used with -l, they will restrict the items=20 listed. -L list-file --use-list=3Dlist-file =C2=A0 =C2=A0 Restore only those archive elements that are listed in list-f= ile,=20 and restore them in the order they appear in the file. Note that if=20 filtering switches such as -n or -t are used with -L, they will further=20 restrict the items restored. =C2=A0 =C2=A0 list-file is normally created by editing the output of a prev= ious=20 -l operation. Lines can be moved or removed, and can also be commented=20 out by placing a semicolon (;) at the start of the line. See below for=20 examples. > > IMHO, it would be nice to have a feature that would allow pg_restore to= =20 > resume based on which part of the restore and/or object have already=20 > been restored. >=20 > When it comes to the COPY phase of the restore, it would be nice to be=20 > able to control batch size and resume COPY of a particular object upon=20 > failure. COPY as it stands now is all or none, so that command would have to be=20 changed. >=20 > Thanks in advance for any suggestions or the green light to post this to= =20 > the PG-developer group :-) >=20 > Thanks, > Efrain J. Berdecia --=20 Adrian Klaver adrian.klaver@aklaver.com =20 ------=_Part_7061559_397944700.1700672154914 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Thanks, the issue we've run = into, which I guess could be really a setup issue, with running a COPY comm= and while executing pg_restore, 
is that if we are restoring a large table (bigger than 500GB) our WA= L directory can grow to be very large.

I would think that= if the pg_restore or COPY command was able to support a batch-size option,=  
this should allow postgr= es to either archive or remove wal files and prevent having to re-size the = WAL directory for a one time refresh operation.

I'm tryin= g to gage how feasible would be to start looking at contributing to add suc= h a feature to either the COPY command or pg_restore.

<= div dir=3D"ltr" data-setdir=3D"false">Thanks,
Efrain J. Berdecia


=20
=20
On Wednesday, November 22, 2023 at 11:37:13 AM EST, Adr= ian Klaver <adrian.klaver@aklaver.com> wrote:


On 11/22/23 05:25, Efrain J. Berdecia= wrote:
> After working for a site where we are consta= ntly doing logical pg_dump
> to refresh environments = I've come to miss features available in other
> RDBMS= ' refresh/restore utilities.
>
>= Someone could point me in the right direction otherwise, but pg_restore > seems to be lacking the ability to resume a restore u= pon failure, is all
> or nothing with this guy. There= also doesn't seem to be a way to control
> batch siz= e when doing the COPY phase, therefore preventing the WAL
> directory from filling up and crashing the system.

The above needs more information on Postgres version(co= mmunity or fork),
OS and version, the size of the data s= et, the storage type and size, the
Postgres conf, etc. R= estores are being done all the time and this is the
firs= t report, as far as I can remember,  about an issue with COPY and
WAL in a restore.


pg_restore
https://www.postgresql.org/docs/current/app-pgrestore.html

Does have:

--section=3Dsectionname

&n= bsp;   Only restore the named section. The section name can be pre-da= ta,
data, or post-data. This option can be specified mor= e than once to
select multiple sections. The default is = to restore all sections.

   = The data section contains actual table data as well as large-object
definitions. Post-data items consist of definitions of indexe= s,
triggers, rules and constraints other than validated = check constraints.
Pre-data items consist of all other d= ata definition items.


AND

-l
--list

    List the table of contents= of the archive. The output of this
operation can be use= d as input to the -L option. Note that if filtering
swit= ches such as -n or -t are used with -l, they will restrict the items
listed.


-L list-file
--use-list=3Dlist-file
<= br clear=3D"none">    Restore only those archive elements that a= re listed in list-file,
and restore them in the order th= ey appear in the file. Note that if
filtering switches s= uch as -n or -t are used with -L, they will further
rest= rict the items restored.

   = list-file is normally created by editing the output of a previous
-l operation. Lines can be moved or removed, and can also be co= mmented
out by placing a semicolon (;) at the start of t= he line. See below for
examples.


>
> IMHO, it would= be nice to have a feature that would allow pg_restore to
> resume based on which part of the restore and/or object have already=
> been restored.
>
> When it comes to the COPY phase of the restore, it would be nic= e to be
> able to control batch size and resume COPY = of a particular object upon
> failure.

COPY as it stands now is all or none, so that comman= d would have to be
changed.


>
> Thanks in advance for any suggestions or th= e green light to post this to
> the PG-developer grou= p :-)
>
> Thanks,
> Efrain J. Berdecia


-- <= br clear=3D"none">Adrian Klaver
adria= n.klaver@aklaver.com


------=_Part_7061559_397944700.1700672154914--