Re: How to deal with dangling files after aborted `pg_restore`?

[Ron Johnson <ronljohnsonjr@xxxxxxxxx>]

It could be that he only restored some tables in an existing database.

On Wed, Dec 18, 2024 at 7:02 PM Saul Perdomo <saul.perdomo@xxxxxxxxx> wrote:

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 a.m. Ivan Kurnosov <zerkms@xxxxxxxxxx> wrote:

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

--

Death to <Redacted>, and butter sauce.

Don't boil me, I'm still alive.

<Redacted> lobster!