public inbox for [email protected]help / color / mirror / Atom feed
How to deal with dangling files after aborted `pg_restore`? 3+ messages / 3 participants [nested] [flat]
* How to deal with dangling files after aborted `pg_restore`? @ 2024-12-17 06:10 Ivan Kurnosov <[email protected]> 2024-12-19 00:02 ` Re: How to deal with dangling files after aborted `pg_restore`? Saul Perdomo <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Ivan Kurnosov @ 2024-12-17 06:10 UTC (permalink / raw) To: [email protected] 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to deal with dangling files after aborted `pg_restore`? 2024-12-17 06:10 How to deal with dangling files after aborted `pg_restore`? Ivan Kurnosov <[email protected]> @ 2024-12-19 00:02 ` Saul Perdomo <[email protected]> 2024-12-19 04:53 ` Re: How to deal with dangling files after aborted `pg_restore`? Ron Johnson <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Saul Perdomo @ 2024-12-19 00:02 UTC (permalink / raw) To: Ivan Kurnosov <[email protected]>; +Cc: [email protected] 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 > ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to deal with dangling files after aborted `pg_restore`? 2024-12-17 06:10 How to deal with dangling files after aborted `pg_restore`? Ivan Kurnosov <[email protected]> 2024-12-19 00:02 ` Re: How to deal with dangling files after aborted `pg_restore`? Saul Perdomo <[email protected]> @ 2024-12-19 04:53 ` Ron Johnson <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Ron Johnson @ 2024-12-19 04:53 UTC (permalink / raw) To: pgsql-generallists.postgresql.org <[email protected]> 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! ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-12-19 04:53 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-12-17 06:10 How to deal with dangling files after aborted `pg_restore`? Ivan Kurnosov <[email protected]> 2024-12-19 00:02 ` Saul Perdomo <[email protected]> 2024-12-19 04:53 ` Ron Johnson <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox