Message-ID: <9ca2d8ca-a175-630e-e449-48c2dee5e0f6@jakobs.com>
Date: Tue, 9 Aug 2022 15:21:33 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.1.1
Subject: Re: How to fully restore a single table from a custom dump?
To: pgsql-admin@lists.postgresql.org
References: <e434fe3f-0159-2223-8670-411d5c21c5bd@gmx.net>
 <1622540476.1110533.1660049347030@email.ionos.com>
From: Holger Jakobs <holger@jakobs.com>
In-Reply-To: <1622540476.1110533.1660049347030@email.ionos.com>
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="------------ascI2CrFJs2E08ORwBwft00O"
Archived-At: 
 <https://www.postgresql.org/message-id/9ca2d8ca-a175-630e-e449-48c2dee5e0f6%40jakobs.com>
Precedence: bulk

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--------------ascI2CrFJs2E08ORwBwft00O
Content-Type: multipart/mixed; boundary="------------J33JKNE50HB6FDK4jchSswKH";
 protected-headers="v1"
From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Message-ID: <9ca2d8ca-a175-630e-e449-48c2dee5e0f6@jakobs.com>
Subject: Re: How to fully restore a single table from a custom dump?
References: <e434fe3f-0159-2223-8670-411d5c21c5bd@gmx.net>
 <1622540476.1110533.1660049347030@email.ionos.com>
In-Reply-To: <1622540476.1110533.1660049347030@email.ionos.com>

--------------J33JKNE50HB6FDK4jchSswKH
Content-Type: multipart/alternative;
 boundary="------------Q49UB1ATi20Ub7debhnmkUjf"

--------------Q49UB1ATi20Ub7debhnmkUjf
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: base64

QW0gMDkuMDguMjIgdW0gMTQ6NDkgc2NocmllYiBNaWNoYWVsREJBIFZpdGFsZToNCj4gSGks
DQo+IElmIHlvdSB1c2UgdGhlIGRpcmVjdG9yeSBkdW1wIG1ldGhvZCwgLUZkLCB0aGVuIHlv
dSBjb3VsZCBnZW5lcmF0ZSBhbiANCj4gZWRpdGFibGUgbGlzdGluZyB3aGVyZSB5b3UgY2Fu
IHNlbGVjdGl2ZWx5IHJlbW92ZSBzdHVmZiB0aGF0IHlvdSBkb24ndCANCj4gd2FudCB0byBy
ZXN0b3JlLCBqdXN0IGtlZXBpbmcgdGhlIHN0dWZmIHJlbGF0ZWQgdG8geW91ciBzcGVjaWZp
YyANCj4gdGFibGUuwqAgwqBZb3UgcnVuIHBnX3Jlc3RvcmUgb25jZSB0byBnZW5lcmF0ZSB0
aGUgbGlzdGluZy7CoCBUaGVuIHJ1biANCj4gcGdfcmVzdG9yZSBhZ2FpbiB1c2luZyB0aGF0
IG1vZGlmaWVkIGxpc3RpbmcgdG8gbG9hZCBpbnRvIHRoZSB0YXJnZXQgDQo+IGRhdGFiYXNl
LiBTZWUgdGhlIHBnX3Jlc3RvcmUgZG9jcyBmb3IgZXhhY3Qgc3ludGF4Lg0KPiBSZWdhcmRz
LA0KPiBNaWNoYWVsIFZpdGFsZQ0KPj4gT24gMDgvMDkvMjAyMiA4OjA2IEFNIEVEVCBUaG9t
YXMgS2VsbGVyZXIgPHNoYW1tYXRAZ214Lm5ldD4gd3JvdGU6DQo+PiBIZWxsbywNCj4+IEkg
anVzdCByZWFsaXplZCB0aGF0IHVzaW5nDQo+PiBwZ19yZXN0b3JlIC10IHNvbWVfdGFibGUg
Li4uIHNvbWVfZHVtcF9maWxlDQo+PiBkb2Vzbid0IHJlc3RvcmUgdGhpbmdzIGxpa2UgaWRl
bnRpdHkgYXR0cmlidXRlcw0KPj4gb3IgaW5kZXhlcyBvbiB0aGUgc3BlY2lmaWVkIHRhYmxl
Lg0KPj4gVGhlIGR1bXAgY29udGFpbnMgbXVjaCBtb3JlIHRoYW4ganVzdCB0aGF0IHRhYmxl
LCBzbyBzaW1wbHkNCj4+IHVzaW5nIHBnX3Jlc3RvcmUgd2l0aG91dCAtdCBpcyBub3QgYW4g
b3B0aW9uLg0KPj4gV2hpbGUgSSBjb3VsZCBleHRyYWN0IHRoZSBpbmRleGVzIG1hbnVhbGx5
IHVzaW5nIHNvbWUgY2xldmVyIHJlZ2V4DQo+PiBvbiB0aGUgaW5kZXggbmFtZXMsIEkgZG9u
J3Qgc2VlIGEgd2F5IHRvIG1ha2Ugc3VyZSB0aGF0IGlkZW50aXR5DQo+PiBkZWZpbml0aW9u
cyAob3Igc2VxdWVuY2UgdmFsdWVzKSBhcmUgcmVzdG9yZWQgcHJvcGVybHkgZm9yIHRoZSAN
Cj4+IHNlbGVjdGVkIHRhYmxlLg0KPj4gQW55IGlkZWFzLCBob3cgSSBjYW4gX2Z1bGx5XyBy
ZXN0b3JlIGEgc2luZ2xlIHRhYmxlIGZyb20gYSBjdXN0b20gZHVtcD8NCj4+IFRob21hcw0K
DQpDcmVhdGluZyBhIGxpc3Qgb2YgY29udGFpbmVkIGl0ZW1zIGFuZCByZXN0b3Jpbmcgc29t
ZSBvZiB0aGVtIHdvcmtzIHRoZSANCnNhbWUgd2l0aCBjdXN0b20gZHVtcHMuIERpcmVjdG9y
eSBkdW1wcyBoYXZlIG5vIGFkdmFudGFnZSBoZXJlLg0KDQpKdXN0IGNvbW1lbnQgb3V0IGFs
bCBpdGVtcyB5b3UgZG9uJ3Qgd2FudCB0byByZXN0b3JlIGJ5IHB1dHRpbmcgYSA7IGluIA0K
ZnJvbnQgb2YgdGhlIGxpbmVzIG9yIGRlbGV0ZSB0aGUgdW53YW50ZWQgbGluZXMgYWx0b2dl
dGhlciBhbmQgcmVzdG9yZS4NCg0KQmVzdCByZWdhcmRzLA0KDQpIb2xnZXINCg0KLS0gDQpI
b2xnZXIgSmFrb2JzLCBCZXJnaXNjaCBHbGFkYmFjaCwgVGVsLiArNDktMTc4LTk3NTkwMTIN
Cg0K
--------------Q49UB1ATi20Ub7debhnmkUjf
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<html>
  <head>
    <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DUTF=
-8">
  </head>
  <body>
    <div class=3D"moz-cite-prefix">Am 09.08.22 um 14:49 schrieb MichaelDB=
A
      Vitale:<br>
    </div>
    <blockquote type=3D"cite"
      cite=3D"mid:1622540476.1110533.1660049347030@email.ionos.com">
      <meta http-equiv=3D"content-type" content=3D"text/html; charset=3DU=
TF-8">
      <meta charset=3D"UTF-8">
      <div> Hi, </div>
      <div class=3D"default-style"> If you use the directory dump method,=

        -Fd, then you could generate an editable listing where you can
        selectively remove stuff that you don't want to restore, just
        keeping the stuff related to your specific table.=C2=A0 =C2=A0You=
 run
        pg_restore once to generate the listing.=C2=A0 Then run pg_restor=
e
        again using that modified listing to load into the target
        database. See the pg_restore docs for exact syntax. </div>
      <div class=3D"default-style"> =C2=A0 </div>
      <div class=3D"default-style"> Regards, </div>
      <div class=3D"default-style"> Michael Vitale </div>
      <div class=3D"default-style"> =C2=A0 </div>
      <blockquote type=3D"cite">
        <div> On 08/09/2022 8:06 AM EDT Thomas Kellerer &lt;<a
            href=3D"mailto:shammat@gmx.net" moz-do-not-send=3D"true"
            class=3D"moz-txt-link-freetext">shammat@gmx.net</a>&gt; wrote=
:
        </div>
        <div> =C2=A0 </div>
        <div> =C2=A0 </div>
        <div> Hello, </div>
        <div> =C2=A0 </div>
        <div> I just realized that using </div>
        <div> =C2=A0 </div>
        <div> pg_restore -t some_table ... some_dump_file </div>
        <div> =C2=A0 </div>
        <div> doesn't restore things like identity attributes </div>
        <div> or indexes on the specified table. </div>
        <div> =C2=A0 </div>
        <div> The dump contains much more than just that table, so
          simply </div>
        <div> using pg_restore without -t is not an option. </div>
        <div> =C2=A0 </div>
        <div> While I could extract the indexes manually using some
          clever regex </div>
        <div> on the index names, I don't see a way to make sure that
          identity </div>
        <div> definitions (or sequence values) are restored properly for
          the selected table. </div>
        <div> =C2=A0 </div>
        <div> Any ideas, how I can _fully_ restore a single table from a
          custom dump? </div>
        <div> =C2=A0 </div>
        <div> Thomas </div>
      </blockquote>
    </blockquote>
    <p>Creating a list of contained items and restoring some of them
      works the same with custom dumps. Directory dumps have no
      advantage here.</p>
    <p>Just comment out all items you don't want to restore by putting a
      ; in front of the lines or delete the unwanted lines altogether
      and restore.</p>
    <p>Best regards,</p>
    <p>Holger<br>
    </p>
    <pre class=3D"moz-signature" cols=3D"72">--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012</pre>
  </body>
</html>

--------------Q49UB1ATi20Ub7debhnmkUjf--

--------------J33JKNE50HB6FDK4jchSswKH--

--------------ascI2CrFJs2E08ORwBwft00O
Content-Type: application/pgp-signature; name="OpenPGP_signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="OpenPGP_signature"

-----BEGIN PGP SIGNATURE-----

wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCYvJfXQUDAAAAAAAKCRCKV5yTsxlhuhUu
AKDlPxvxwzZjNIxs9AMX7IHa70klnwCg5OsQ6imE4wY1t4YSKW2qwmEWX4M=
=1VXf
-----END PGP SIGNATURE-----

--------------ascI2CrFJs2E08ORwBwft00O--