MIME-Version: 1.0
From: Ivan Kurnosov <zerkms@zerkms.com>
Date: Tue, 17 Dec 2024 19:10:09 +1300
Message-ID: <CABbRoj6EmGOzvk3O2r5RwT_Yn=B53D=ZXP3pNf-3c8DpB10W1Q@mail.gmail.com>
Subject: How to deal with dangling files after aborted `pg_restore`?
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000002f089c06297127c7"
Archived-At: <https://www.postgresql.org/message-id/CABbRoj6EmGOzvk3O2r5RwT_Yn%3DB53D%3DZXP3pNf-3c8DpB10W1Q%40mail.gmail.com>
Precedence: bulk

--0000000000002f089c06297127c7
Content-Type: text/plain; charset="UTF-8"

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=trust -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 16399
-rw------- 1 systemd-coredump systemd-coredump  11149312 Dec 17 18:58 16404
-rw------- 1 systemd-coredump systemd-coredump    188416 Dec 17 18:58
16403_fsm
-rw------- 1 systemd-coredump systemd-coredump 686145536 Dec 17 18:58 16403
```

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? If
so - how would one recover from this scenario now WITHOUT dropping entire
database?

-- 
With best regards, Ivan Kurnosov

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

<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-trans=
action` flag</div><div>3. For whatever reason the server goes away (eg: we =
kill the process)</div><div>4. Now `base` directory is filled with abandone=
d table files which postgresql know nothing about</div><div><br></div><div>=
Playground:</div><div><br></div><div>Terminal 1:</div><div>Start server: `d=
ocker run --rm -it -e POSTGRES_HOST_AUTH_METHOD=3Dtrust -v $PWD/postgres:/v=
ar/lib/postgresql/data postgres:17.2`</div><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 postgres --single-transaction -v -F=
c --no-owner dump.sql`</div><div><br></div><div>Terminal 3:</div><div>1. Fi=
nd what container is the server: `docker ps`</div><div>2. Kill it: `docker =
kill d7ecf6e66c1d`</div><div><br></div><div>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># l=
s -la=C2=A0/home/ivan/postgres/base/5</div><div>&lt;truncated&gt;</div><div=
>-rw------- 1 systemd-coredump systemd-coredump 342884352 Dec 17 18:58 1639=
9<br>-rw------- 1 systemd-coredump systemd-coredump =C2=A011149312 Dec 17 1=
8:58 16404<br>-rw------- 1 systemd-coredump systemd-coredump =C2=A0 =C2=A01=
88416 Dec 17 18:58 16403_fsm<br>-rw------- 1 systemd-coredump systemd-cored=
ump 686145536 Dec 17 18:58 16403</div><div>```</div><div><br></div><div>Ter=
minal 2:</div><div>1. Confirm those OIDs are not accounted:=C2=A0</div><div=
>```</div><div>psql -h 172.17.0.2 -U postgres -c &#39;select * from pg_clas=
s&#39;|grep 16404</div><div>psql -h 172.17.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 16399</div><div>```</div><div><br></d=
iv><div>Hence a question: am I doing something wrong? Is it expected behavi=
our? If so - how would one recover from this scenario now WITHOUT dropping =
entire database?</div><div><br></div><span 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>

--0000000000002f089c06297127c7--