MIME-Version: 1.0
References: <53760c70-4a87-a453-9e02-57abc9cb2e54@gmx.net> <756f3204-5cb9-5c7e-642c-bc5ec78db59d@gmx.net>
In-Reply-To: <756f3204-5cb9-5c7e-642c-bc5ec78db59d@gmx.net>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Fri, 21 Mar 2025 15:00:57 -0400
Message-ID: <CANzqJaD_ef+jyX8=r+TLYyQ0Wzc2dQiPpcPC5nMx+ixGvaNzyw@mail.gmail.com>
Subject: Re: Experience and feedback on pg_restore --data-only
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000008cd0040630dee086"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaD_ef%2BjyX8%3Dr%2BTLYyQ0Wzc2dQiPpcPC5nMx%2BixGvaNzyw%40mail.gmail.com>
Precedence: bulk

--0000000000008cd0040630dee086
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, Mar 21, 2025 at 2:36=E2=80=AFPM Dimitrios Apostolou <jimis@gmx.net>=
 wrote:

> On Thu, 20 Mar 2025, Dimitrios Apostolou wrote:
>
> > Rationale:
> >
> > When restoring a backup in an emergency situation, it's fine to run
> > pg_restore as superuser and get an exact replica of the dumped db.
>

How often do you have emergencies requiring database restore????  In my
seven years managing PG systems, I've had TWO.


> > AFAICT pg_restore (without --data-only) is optimised for such case.
>
 [snip]

> > Any feedback for improving my process?


Yes: don't use a logical backup tool like pg_dump to backup production
databases.

PgBackRest (the tool I have experience with; there are others, though) has
mandatory features like PITR, incremental and differential backups, delta
restores and encryption.  Use that instead.

To get rid of the cruft in your database, go through the schema and
manually drop unused tables.

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

--0000000000008cd0040630dee086
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Mar 21, 2025 at 2:36=E2=80=AFPM D=
imitrios Apostolou &lt;<a href=3D"mailto:jimis@gmx.net">jimis@gmx.net</a>&g=
t; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote=
 class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px so=
lid rgb(204,204,204);padding-left:1ex">On Thu, 20 Mar 2025, Dimitrios Apost=
olou wrote:<br>
<br>
&gt; Rationale:<br>
&gt;<br>
&gt; When restoring a backup in an emergency situation, it&#39;s fine to ru=
n<br>
&gt; pg_restore as superuser and get an exact replica of the dumped db.<br>=
</blockquote><div><br></div><div>How often do you have emergencies requirin=
g database restore????=C2=A0 In my seven years managing PG systems, I&#39;v=
e had TWO.</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D=
"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-le=
ft:1ex">
&gt; AFAICT pg_restore (without --data-only) is optimised for such case.<br=
></blockquote><div>=C2=A0[snip]</div><blockquote class=3D"gmail_quote" styl=
e=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddin=
g-left:1ex">&gt; Any feedback for improving my process?</blockquote><div><b=
r></div><div>Yes: don&#39;t use a logical backup tool like pg_dump to backu=
p production databases.</div><div><br></div><div><div>PgBackRest (the tool =
I have experience with; there are others, though) has mandatory features li=
ke PITR, incremental and differential backups, delta restores and encryptio=
n.=C2=A0 Use that instead.</div><div><br></div></div><div>To get rid of the=
 cruft in your database, go through the schema and manually drop unused tab=
les.</div><div><br></div></div><span class=3D"gmail_signature_prefix">-- </=
span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death =
to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still=
 alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div></div></div></div=
></div>

--0000000000008cd0040630dee086--