Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tO8XT-004M4m-ED for pgsql-general@arkaria.postgresql.org; Thu, 19 Dec 2024 04:53:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tO8XS-0076kK-1x for pgsql-general@arkaria.postgresql.org; Thu, 19 Dec 2024 04:53:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tO8XR-0076kB-KQ for pgsql-general@lists.postgresql.org; Thu, 19 Dec 2024 04:53:29 +0000 Received: from mail-oo1-xc31.google.com ([2607:f8b0:4864:20::c31]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tO8XN-000Wi3-SM for pgsql-general@lists.postgresql.org; Thu, 19 Dec 2024 04:53:28 +0000 Received: by mail-oo1-xc31.google.com with SMTP id 006d021491bc7-5f362c3950dso140470eaf.1 for ; Wed, 18 Dec 2024 20:53:25 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1734584004; x=1735188804; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=vVIb7QBCdCMZ9VFtvYhPm786Svb6oLrML/Nst16eiU4=; b=ivjzG8OnKrY3qQMaMqICuz/QNa4CovrAlHqGnOryVLpHLjehWH16vfnkcK4UC/g9YX V3Pdq0Xj7xzEr26pcCsod7ZC/EX0qxMvVPB8ZZWDzLPeANL1eUkiuLuav0LBPb4DKNiK /XwukOe2lG7494V1p6AIRGMSEyoIHjrEX8kWuTHGZDrLJFyHLPEjrLP9oFTI1NftX4J9 iH+TJynO96mX8cCCsdTeq+P0U/IHc0vl8Ff5sSxhiKlmgieY7qjOdSYbUt1UiWavawaZ wntC9In6KEdYezKAu7i0k1M0O+nu6OzljkAREZekHh2BO9p9Cvj4KRMe9Yw97MzXi6nZ LvUg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1734584004; x=1735188804; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=vVIb7QBCdCMZ9VFtvYhPm786Svb6oLrML/Nst16eiU4=; b=MayUrQp7iaA1jpQ7mZ2LHSa9A8OJoirIV4Xb6Y5L6dey1InnQ6CyLOu2at+BhHbCyO UIzxjjO+A5T4GlgZq3UVMn4HitfsTnNqIBMDBYa6456mp4aKUw5Z7wAfEaYRpnmMdqi2 tIs31Mi/is7bJ5o5S4CKQXnTQ8eeg3JB1qRa9GEk47oTZwb7SsvXcpWiKTq59L+BYLqI EKMGJ+rCrXIci4H/k4mHadVHQBnsRqEx7ZIe9a2V0fF4pEyRQMhwziACKaUTV47Ru0EL fSYrzDhIXZMZ5152vw381T6kMCYgPuqxv9piIuJ3nmchAyHkdqI+8prP9l8lTsupnfSw ORNg== X-Gm-Message-State: AOJu0YyKhngiHQBRzumy1fWJ8j1fE2KSlrObk58Nmw9zRXlo4psBAzvb wtsy66ryxic83u8IJXD75RdBLYX+V6w6+GU2fn5+BkBsL44Wi4yKsNf6Esu6N8DUA23grFTNeq/ 6+tYDTztWSnRE5xHN4e6vOKf1Jv0h9w== X-Gm-Gg: ASbGnctPNSnTRQlBDPJA10chkrnhkFUJflcxHGS4lZc6Oe23if5QOcaopt3xBbATEU6 Q+osxKp5uPD9gIIckjWaw2Gp2Eu7e9jrehYlz7JKV5Ia/2nbbTC5yf1bz39FtoQ+MbZh1qTDW X-Google-Smtp-Source: AGHT+IEVW4qyEM6LtlbZm0qkQb2hRZ4wYi+qbdei5LTn8I21sz8UIm8TDOueeI+5brJexx6HnuieBIBBW8Z/RlVvNZw= X-Received: by 2002:a05:6870:c996:b0:297:28ec:9b26 with SMTP id 586e51a60fabf-2a7d09b0632mr1120581fac.33.1734584003900; Wed, 18 Dec 2024 20:53:23 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Wed, 18 Dec 2024 23:53:12 -0500 Message-ID: Subject: Re: How to deal with dangling files after aborted `pg_restore`? To: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="00000000000062c0fe0629984f4b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000062c0fe0629984f4b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable It could be that he only restored *some* tables in an existing database. On Wed, Dec 18, 2024 at 7:02=E2=80=AFPM Saul Perdomo 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=E2=80=AFa.m. Ivan Kurnosov = 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=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 postgre= s >> --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 >> >> -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 >> > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --00000000000062c0fe0629984f4b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
It could be that he only restored some=C2=A0ta= bles in an existing database.

On Wed, Dec 18, 2024 at = 7:02=E2=80=AFPM Saul Perdomo <= saul.perdomo@gmail.com> wrote:

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


On Tue= , Dec 17, 2024, 1:10=E2=80=AFa.m. Ivan Kurnosov <zerkms@zerkms.com> wrote:
<= blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l= eft:1px solid rgb(204,204,204);padding-left:1ex">
The = scenario:

1. There is a postgresql 17 server runni= ng
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 postgr= esql know nothing about

Playground:

=
Terminal 1:
Start server: `docker run --rm -it -e POST= GRES_HOST_AUTH_METHOD=3Dtrust -v $PWD/postgres:/var/lib/postgresql/data pos= tgres:17.2`

Terminal 2:
1. Start contain= er with recent pg_restore: `docker run --rm -it -v $PWD:/app -w /app postgr= es: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 s= erver: `docker ps`
2. Kill it: `docker kill d7ecf6e66c1d`

Terminal 1:
Start the server again, with the sa= me command

Terminal 3:
Check there are a= bandoned large files:
```
# ls -la=C2=A0/home/ivan/post= gres/base/5
<truncated>
-rw------- 1 systemd-core= dump systemd-coredump 342884352 Dec 17 18:58 16399
-rw------- 1 systemd-= coredump systemd-coredump =C2=A011149312 Dec 17 18:58 16404
-rw------- 1= systemd-coredump systemd-coredump =C2=A0 =C2=A0188416 Dec 17 18:58 16403_f= sm
-rw------- 1 systemd-coredump systemd-coredump 686145536 Dec 17 18:58= 16403
```

Terminal 2:
1. Conf= irm those OIDs are not accounted:=C2=A0
```
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 1= 6403
psql -h 172.17.0.2 -U postgres -c 'select * from pg_clas= s'|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> lobs= ter!
--00000000000062c0fe0629984f4b--