public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: How to deal with dangling files after aborted `pg_restore`?
Date: Wed, 18 Dec 2024 23:53:12 -0500
Message-ID: <CANzqJaDV5TENO5Dis9D9QbndzXUyMKjohi8zZHawE5qqR_1YAw@mail.gmail.com> (raw)
In-Reply-To: <CAN3jBgE6nWgkXRZWJHyGA=p6M0-W1vk-h6_WULnQXEwEEn8pVQ@mail.gmail.com>
References: <CABbRoj6EmGOzvk3O2r5RwT_Yn=B53D=ZXP3pNf-3c8DpB10W1Q@mail.gmail.com>
<CAN3jBgE6nWgkXRZWJHyGA=p6M0-W1vk-h6_WULnQXEwEEn8pVQ@mail.gmail.com>
It could be that he only restored *some* tables in an existing database.
On Wed, Dec 18, 2024 at 7:02 PM Saul Perdomo <[email protected]> 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 <[email protected]> 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!
view thread (3+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: How to deal with dangling files after aborted `pg_restore`?
In-Reply-To: <CANzqJaDV5TENO5Dis9D9QbndzXUyMKjohi8zZHawE5qqR_1YAw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox