Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oLVnx-00005v-3y for pgsql-admin@arkaria.postgresql.org; Tue, 09 Aug 2022 20:26:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oLVmx-0005XD-W7 for pgsql-admin@arkaria.postgresql.org; Tue, 09 Aug 2022 20:25:20 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oLVmx-0005X3-J3 for pgsql-admin@lists.postgresql.org; Tue, 09 Aug 2022 20:25:19 +0000 Received: from mail-oi1-x22a.google.com ([2607:f8b0:4864:20::22a]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oLVmu-0006UF-Tl for pgsql-admin@lists.postgresql.org; Tue, 09 Aug 2022 20:25:18 +0000 Received: by mail-oi1-x22a.google.com with SMTP id bb16so15205503oib.11 for ; Tue, 09 Aug 2022 13:25:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc; bh=ZRuvLxQq3SF77XdRam9uklbqESncmNsQsXZWblu6hb0=; b=Jh82e0r75Y7pZ74y8KQXY3MSvkJWj/0OocvoYd9szPO0apSfGSX4rg/fFhENkoLrO1 8g9f5hgyKXhQf0ZdgL8cmiu9ZahRLcgudmDkwmjR992eIK31hFIQ5r3m0twOYxk1hXwx 5fz7Q8MCi9LLpPRLwci5XlklKjf8zI0ENi9CpuBQqYW+jvzCdheJcosgW04bEO5nPpvG by1r5ySNXN81oOj2lT/jPbY3FCIR5VblNEq/4e8FCaBQ8S3sXuNMwW0NBAsrk5ABG8Dd FVJHf+mpsPHyvpdhv2jBxgAPXwz1a+5YRQELD1Uk5tMnUo2LPv8jAOyLKziT0QcrCJuQ ejcg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc; bh=ZRuvLxQq3SF77XdRam9uklbqESncmNsQsXZWblu6hb0=; b=XXfXiMVPR1pNoqrhaHCpTpRYQUjjtqYsTbfblHnBTIl98wrZBxR4SNjrTTBKbcqIp1 pXn4pGoNd7FUPOiKlwnfkB1i+oWv4i8snMzvOf0ANB10ObWogB6yKfmOQmS2EXG96EiY JONXQX8QVeeZJ41SAjB7tkX6uAAUZ3pq6oGlOsJ4/Um3eXkF1hRlGZnbDHX2PR4uFDX6 78OSRaivH/tRNCQvHTeVHDN7cfODNeRIi+vkU3MTMJC+tGWQ/seaMd+N57UAZnn1zhPk w0Hk9LdoTwWVt/hefmlcguXOKqRdnpFCBQyjd+yW5+81FjSZmxBU5lOyqVs2ZMoFWawp 7O2g== X-Gm-Message-State: ACgBeo3wnTXRbpcWXjjMeRFJCaMJJpeqAzw+tT/J7ZDmD6tsmVihmtZ/ dz3/OaxwiEshWemejnwQIWIfen5YUy0= X-Google-Smtp-Source: AA6agR4vgBmiWgaZcf1OKtm0JQU9lJeKtr/HbCxW2c+4dm3J+YtNyu/oN4J1iSZXG0wcyF7yZFn9Hw== X-Received: by 2002:a05:6808:300f:b0:2fa:6fd5:9723 with SMTP id ay15-20020a056808300f00b002fa6fd59723mr91551oib.202.1660076714614; Tue, 09 Aug 2022 13:25:14 -0700 (PDT) Received: from [192.168.88.10] (ip68-11-68-85.no.no.cox.net. [68.11.68.85]) by smtp.googlemail.com with ESMTPSA id v19-20020a056870709300b0010d75440f41sm3333135oae.45.2022.08.09.13.25.14 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 09 Aug 2022 13:25:14 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------wAn0QB07QuSyC1hLS1k6aOTN" Message-ID: <5cf8b21d-0517-4e82-2ad4-bf243b6efe85@gmail.com> Date: Tue, 9 Aug 2022 15:25:13 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.9.1 Subject: Re: How to fully restore a single table from a custom dump? Content-Language: en-US To: Pgsql-admin References: <1622540476.1110533.1660049347030@email.ionos.com> <9ca2d8ca-a175-630e-e449-48c2dee5e0f6@jakobs.com> <93797605-23dc-de94-d49e-0849475ad426@gmail.com> <3ff41470-0eb2-0f29-86c3-86c37dd15f20@gmail.com> From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------wAn0QB07QuSyC1hLS1k6aOTN Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 8/9/22 15:17, Guillaume Lelarge wrote: > Le mar. 9 août 2022 à 19:18, Ron a écrit : > > On 8/9/22 12:13, Guillaume Lelarge wrote: >> Le mar. 9 août 2022, 18:41, Ron a écrit : >> >> On 8/9/22 08:21, Holger Jakobs wrote: >>> Am 09.08.22 um 14:49 schrieb MichaelDBA Vitale: >>>> Hi, >>>> 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.   You run pg_restore once to >>>> generate the listing.  Then run pg_restore again using that >>>> modified listing to load into the target database. See the >>>> pg_restore docs for exact syntax. >>>> Regards, >>>> Michael Vitale >>>>> On 08/09/2022 8:06 AM EDT Thomas Kellerer >>>>> wrote: >>>>> Hello, >>>>> I just realized that using >>>>> pg_restore -t some_table ... some_dump_file >>>>> doesn't restore things like identity attributes >>>>> or indexes on the specified table. >>>>> The dump contains much more than just that table, so simply >>>>> using pg_restore without -t is not an option. >>>>> While I could extract the indexes manually using some clever >>>>> regex >>>>> on the index names, I don't see a way to make sure that identity >>>>> definitions (or sequence values) are restored properly for the >>>>> selected table. >>>>> Any ideas, how I can _fully_ restore a single table from a >>>>> custom dump? >>>>> Thomas >>> >>>  Creating a list of contained items and restoring some of them >>> works the same with custom dumps. Directory dumps have no >>> advantage here. >>> >>> 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. >>> >> >> Which is less than convenient when there's 4000 tables, and each >> one has 3 or four indices, a Primary Key and one or more Foreign >> Keys. >> >> >> Agreed, but it's already less convenient to give 4000 -t's :-) > > What's your point? > > > My point is that if you have 4k tables to dump, it's already a burden with > or without indices and constraints. It's gonna be hard anyway. At some point, you just restore the whole database, and then drop what you don't need. -- Angular momentum makes the world go 'round. --------------wAn0QB07QuSyC1hLS1k6aOTN Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 8/9/22 15:17, Guillaume Lelarge wrote:
Le mar. 9 août 2022 à 19:18, Ron <ronljohnsonjr@gmail.com> a écrit :
On 8/9/22 12:13, Guillaume Lelarge wrote:
Le mar. 9 août 2022, 18:41, Ron <ronljohnsonjr@gmail.com> a écrit :
On 8/9/22 08:21, Holger Jakobs wrote:
Am 09.08.22 um 14:49 schrieb MichaelDBA Vitale:
Hi,
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.   You run pg_restore once to generate the listing.  Then run pg_restore again using that modified listing to load into the target database. See the pg_restore docs for exact syntax.
 
Regards,
Michael Vitale
 
On 08/09/2022 8:06 AM EDT Thomas Kellerer <shammat@gmx.net> wrote:
 
 
Hello,
 
I just realized that using
 
pg_restore -t some_table ... some_dump_file
 
doesn't restore things like identity attributes
or indexes on the specified table.
 
The dump contains much more than just that table, so simply
using pg_restore without -t is not an option.
 
While I could extract the indexes manually using some clever regex
on the index names, I don't see a way to make sure that identity
definitions (or sequence values) are restored properly for the selected table.
 
Any ideas, how I can _fully_ restore a single table from a custom dump?
 
Thomas

 Creating a list of contained items and restoring some of them works the same with custom dumps. Directory dumps have no advantage here.

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.


Which is less than convenient when there's 4000 tables, and each one has 3 or four indices, a Primary Key and one or more Foreign Keys.

Agreed, but it's already less convenient to give 4000 -t's :-)

What's your point?


My point is that if you have 4k tables to dump, it's already a burden with or without indices and constraints. It's gonna be hard anyway.

At some point, you just restore the whole database, and then drop what you don't need.


--
Angular momentum makes the world go 'round.
--------------wAn0QB07QuSyC1hLS1k6aOTN--