MIME-Version: 1.0
References: <CABbRoj6EmGOzvk3O2r5RwT_Yn=B53D=ZXP3pNf-3c8DpB10W1Q@mail.gmail.com>
In-Reply-To: <CABbRoj6EmGOzvk3O2r5RwT_Yn=B53D=ZXP3pNf-3c8DpB10W1Q@mail.gmail.com>
From: Saul Perdomo <saul.perdomo@gmail.com>
Date: Wed, 18 Dec 2024 19:02:30 -0500
Message-ID: <CAN3jBgE6nWgkXRZWJHyGA=p6M0-W1vk-h6_WULnQXEwEEn8pVQ@mail.gmail.com>
Subject: Re: How to deal with dangling files after aborted `pg_restore`?
To: Ivan Kurnosov <zerkms@zerkms.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000b3795b0629943f0e"
Archived-At: <https://www.postgresql.org/message-id/CAN3jBgE6nWgkXRZWJHyGA%3Dp6M0-W1vk-h6_WULnQXEwEEn8pVQ%40mail.gmail.com>
Precedence: bulk

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

Genuine question: Why are you looking to recover from this half-cooked
state instead of restarting the restore process from the beginning?

On Tue, Dec 17, 2024, 1:10=E2=80=AFa.m. Ivan Kurnosov <zerkms@zerkms.com> w=
rote:

> The scenario:
>
> 1. There is a postgresql 17 server running
> 2. Restore dump with `--single-transaction` flag
> 3. For whatever reason the server goes away (eg: we kill the process)
> 4. Now `base` directory is filled with abandoned table files which
> postgresql know nothing about
>
> Playground:
>
> Terminal 1:
> Start server: `docker run --rm -it -e POSTGRES_HOST_AUTH_METHOD=3Dtrust -=
v
> $PWD/postgres:/var/lib/postgresql/data postgres:17.2`
>
> Terminal 2:
> 1. Start container with recent pg_restore: `docker run --rm -it -v
> $PWD:/app -w /app postgres:17.2 bash`
> 2. Start dump recovery: `pg_restore -h 172.17.0.2 -U postgres -d postgres
> --single-transaction -v -Fc --no-owner dump.sql`
>
> Terminal 3:
> 1. Find what container is the server: `docker ps`
> 2. Kill it: `docker kill d7ecf6e66c1d`
>
> Terminal 1:
> Start the server again, with the same command
>
> Terminal 3:
> Check there are abandoned large files:
> ```
> # ls -la /home/ivan/postgres/base/5
> <truncated>
> -rw------- 1 systemd-coredump systemd-coredump 342884352 Dec 17 18:58 163=
99
> -rw------- 1 systemd-coredump systemd-coredump  11149312 Dec 17 18:58 164=
04
> -rw------- 1 systemd-coredump systemd-coredump    188416 Dec 17 18:58
> 16403_fsm
> -rw------- 1 systemd-coredump systemd-coredump 686145536 Dec 17 18:58 164=
03
> ```
>
> Terminal 2:
> 1. Confirm those OIDs are not accounted:
> ```
> psql -h 172.17.0.2 -U postgres -c 'select * from pg_class'|grep 16404
> psql -h 172.17.0.2 -U postgres -c 'select * from pg_class'|grep 16403
> psql -h 172.17.0.2 -U postgres -c 'select * from pg_class'|grep 16399
> ```
>
> Hence a question: am I doing something wrong? Is it expected behaviour? I=
f
> so - how would one recover from this scenario now WITHOUT dropping entire
> database?
>
> --
> With best regards, Ivan Kurnosov
>

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

<p dir=3D"ltr">Genuine question: Why are you looking to recover from this h=
alf-cooked state instead of restarting the restore process from the beginni=
ng? </p>
<br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=
=3D"gmail_attr">On Tue, Dec 17, 2024, 1:10=E2=80=AFa.m. Ivan Kurnosov &lt;<=
a href=3D"mailto:zerkms@zerkms.com">zerkms@zerkms.com</a>&gt; wrote:<br></d=
iv><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left=
:1px #ccc solid;padding-left:1ex"><div dir=3D"ltr"><div>The scenario:</div>=
<div><br></div><div>1. There is a postgresql 17 server running</div><div>2.=
 Restore dump with `--single-transaction` flag</div><div>3. For whatever re=
ason the server goes away (eg: we kill the process)</div><div>4. Now `base`=
 directory is filled with abandoned table files which postgresql know nothi=
ng about</div><div><br></div><div>Playground:</div><div><br></div><div>Term=
inal 1:</div><div>Start server: `docker run --rm -it -e POSTGRES_HOST_AUTH_=
METHOD=3Dtrust -v $PWD/postgres:/var/lib/postgresql/data postgres:17.2`</di=
v><div><br></div><div>Terminal 2:</div><div>1. Start container with recent =
pg_restore: `docker run --rm -it -v $PWD:/app -w /app postgres:17.2 bash`</=
div><div>2. Start dump recovery: `pg_restore -h 172.17.0.2 -U postgres -d p=
ostgres --single-transaction -v -Fc --no-owner dump.sql`</div><div><br></di=
v><div>Terminal 3:</div><div>1. Find what container is the server: `docker =
ps`</div><div>2. Kill it: `docker kill d7ecf6e66c1d`</div><div><br></div><d=
iv>Terminal 1:</div><div>Start the server again, with the same command</div=
><div><br></div><div>Terminal 3:</div><div>Check there are abandoned large =
files:</div><div>```</div><div># ls -la=C2=A0/home/ivan/postgres/base/5</di=
v><div>&lt;truncated&gt;</div><div>-rw------- 1 systemd-coredump systemd-co=
redump 342884352 Dec 17 18:58 16399<br>-rw------- 1 systemd-coredump system=
d-coredump =C2=A011149312 Dec 17 18:58 16404<br>-rw------- 1 systemd-coredu=
mp systemd-coredump =C2=A0 =C2=A0188416 Dec 17 18:58 16403_fsm<br>-rw------=
- 1 systemd-coredump systemd-coredump 686145536 Dec 17 18:58 16403</div><di=
v>```</div><div><br></div><div>Terminal 2:</div><div>1. Confirm those OIDs =
are not accounted:=C2=A0</div><div>```</div><div>psql -h 172.17.0.2 -U post=
gres -c &#39;select * from pg_class&#39;|grep 16404</div><div>psql -h 172.1=
7.0.2 -U postgres -c &#39;select * from pg_class&#39;|grep 16403</div><div>=
psql -h 172.17.0.2 -U postgres -c &#39;select * from pg_class&#39;|grep 163=
99</div><div>```</div><div><br></div><div>Hence a question: am I doing some=
thing wrong? Is it expected behaviour? If so - how would one recover from t=
his scenario now WITHOUT dropping entire database?</div><div><br></div><spa=
n class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"=
gmail_signature" data-smartmail=3D"gmail_signature">With best regards, Ivan=
 Kurnosov</div></div>
</blockquote></div>

--000000000000b3795b0629943f0e--