From kobruleht2@hot.ee Fri May 15 23:21:49 2026 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.96) (envelope-from ) id 1vWcrB-0065bF-31 for pgsql-general@arkaria.postgresql.org; Fri, 19 Dec 2025 15:57:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vWcrA-008FJX-1m for pgsql-general@arkaria.postgresql.org; Fri, 19 Dec 2025 15:57:29 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vWZna-007hqn-2A for pgsql-general@lists.postgresql.org; Fri, 19 Dec 2025 12:41:35 +0000 Received: from smtpout05.dka.mailcore.net ([185.138.56.205]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vWZnZ-001VIM-0m for pgsql-general@postgresql.org; Fri, 19 Dec 2025 12:41:34 +0000 Received: from SMTP.DKA.mailcore.net (unknown [10.1.0.52]) by SMTPOUT01.DKA.mailcore.net (Postfix) with ESMTP id 26AB5E0059 for ; Fri, 19 Dec 2025 13:41:31 +0100 (CET) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=online.ee; s=mailcore; t=1766148091; bh=KIZKuMRaEnwpy0HaK32W7b7qChYxifczUeIKSCKhZjY=; h=Date:To:From:Subject:From; b=erxfKEJP28E/bL4qfJBn0VFEWqJiB+RFWXInn9zqEtb2iGitp7eVL89LpWmPqwy3o ZqnrYwZTL3rcevc6VLYfJ8/N+mLR3pbSKGwkye0eBO0HOdRoMuD5HnLtluXH41p30L 8QKgn+s6UlSGIqPCYhWzlRCxd4BxFShGc7wMWiFfIGJ07Jfb5QmiRQ0+5O52K6afbm dsxSnBEpn+TZYnmil5zYt2c7AHh1Dhnq8c2rXREvLvcqXnTY4uMaEFdGch/ib55ZKl CRa84GlOLnHVFcTlDFmcvsD9eDvp+qpme94+jRX9Wxvo73xgSySd4jVbRo5pkYDt5a x3MX4zP1AFHOw== Received: from [192.168.1.38] (73-142-35-213.sta.estpak.ee [213.35.142.73]) by SMTP.DKA.mailcore.net (Postfix) with ESMTPSA id 048444019E for ; Fri, 19 Dec 2025 13:41:30 +0100 (CET) Content-Type: multipart/alternative; boundary="------------4lBpmS00tIH0lZ4jBupE8c43" Message-ID: Date: Fri, 19 Dec 2025 14:41:37 +0200 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Content-Language: et To: pgsql-general From: Andrus Subject: How to get single table data from backup 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. --------------4lBpmS00tIH0lZ4jBupE8c43 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Hi! Large database backup is created using pg_dump -b -f backup.backup -F c How to restore single table in schema firma2 from it in Windows? Created database recovery and tried pg_restore --schema-only --n firma2 -d recovery backup.backup pg_restore -n firma2 -t tabletorecover -d recovery backup.backup This fails since tabletorecover contains lot foreign key references. Foreign keys refer to other tables which have foreign keys to another tables etc. making huge graph. All tables contain also data. I need to get table data only. Using PostgresSql 17 in windows. Andrus. --------------4lBpmS00tIH0lZ4jBupE8c43 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

Hi!

Large database backup is created using

pg_dump -b -f backup.backup -F c

How to restore single table in schema firma2 from it in Windows?

Created database recovery and tried

pg_restore --schema-only --n firma2 -d recovery backup.backup
pg_restore -n firma2 -t tabletorecover -d recovery backup.backup

This fails since tabletorecover contains lot foreign key references. Foreign keys refer to other tables which have foreign keys to another tables etc. making huge graph. All tables contain also data.

I need to get table data only. Using PostgresSql 17 in windows.

Andrus.

--------------4lBpmS00tIH0lZ4jBupE8c43-- From adrian.klaver@aklaver.com Fri May 15 23:22:46 2026 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.96) (envelope-from ) id 1vWd5r-006B5j-0t for pgsql-general@arkaria.postgresql.org; Fri, 19 Dec 2025 16:12:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vWd5p-008LPW-0b for pgsql-general@arkaria.postgresql.org; Fri, 19 Dec 2025 16:12:37 +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.96) (envelope-from ) id 1vWd5o-008LPN-0z for pgsql-general@lists.postgresql.org; Fri, 19 Dec 2025 16:12:37 +0000 Received: from fout-b3-smtp.messagingengine.com ([202.12.124.146]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vWd5l-001cwt-1v for pgsql-general@postgresql.org; Fri, 19 Dec 2025 16:12:36 +0000 Received: from phl-compute-04.internal (phl-compute-04.internal [10.202.2.44]) by mailfout.stl.internal (Postfix) with ESMTP id 80FC51D0011D; Fri, 19 Dec 2025 11:12:30 -0500 (EST) Received: from phl-frontend-03 ([10.202.2.162]) by phl-compute-04.internal (MEProxy); Fri, 19 Dec 2025 11:12:30 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm3; t=1766160750; x=1766247150; bh=bHjn0b+NpanTicV9xfHNzfi3jPCUZrujJWCTZyfKCy0=; b= uQuHC4Db86cyMMcVMXojXdnbAbwJgivjvMY3tiCVEOcKttDOmWbJCZRwZBCO2anB b3AmEQVGHFoIX6YZvZQ4SlxLVCvOMUdg8bPzexVdoGPmBoa9+1Jh5Ka24uMR2ZRm Moj6FSr1pYaFug4y69O03mvaZfwAHA/ZZOoXqOAvm4mwBWdc2dQsFWWY6bCI16L8 OVHjGi+Z/JjG3Z1zSZo3WQOZdAoB5vuZQSiPoZivDg1qF8NtwAL+NpOVzf2q0393 kj9abcoN/G8rfrqCGgWs/x0r3fKVuSH4tabu1ae2xMkb8FFZvUPRLNjC4Xj+Ra4S pgHYw7jT5htSqEXYKQkUfQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:subject:subject:to:to:x-me-proxy:x-me-sender :x-me-sender:x-sasl-enc; s=fm1; t=1766160750; x=1766247150; bh=b Hjn0b+NpanTicV9xfHNzfi3jPCUZrujJWCTZyfKCy0=; b=oe0Hcc8HgNzXbRe6j 99SfcnInrp0GvyVh1b7e7ZUH3Ml8ocWZxTGc/f/FScPuOMnMDayPAxO2f0uWCX1W xEzNGmDwhgOh05wGCuPoDzzMVKk9dHQ3JMih+mra/GjopD+SVupIXZ6zvsLI8XKb QqjNnuR9+oxAVMnXZhWhARhvLIwwsWk0rZ3rBjjkNcUoUDZiBsmVeajeXwMXtxCU k29qBFq0+8b9w/AqbIw+87lkRUXEmE8NqasOlqjqlImrWAshn9AeCKa4ursLdFo4 UmCEbMIYmBftnhIOIM4X2ivqMj+EztdXLZnpLwymrOeo+pUJeEM8XVc4l4XTXrYr M3Ccg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeefgedrtddtgdegkeejhecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpuffrtefokffrpgfnqfghnecuuegr ihhlohhuthemuceftddtnecunecujfgurhepkfffgggfuffvfhfhjggtgfesthekredttd dvjeenucfhrhhomheptegurhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghv vghrsegrkhhlrghvvghrrdgtohhmqeenucggtffrrghtthgvrhhnpeffleegieefgfevud ehtdfhkeeutdffjeevgeffgeejvedthefgudeiteefheejheenucevlhhushhtvghrufhi iigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvghrse grkhhlrghvvghrrdgtohhmpdhnsggprhgtphhtthhopedvpdhmohguvgepshhmthhpohhu thdprhgtphhtthhopehkohgsrhhulhgvhhhtvdeshhhothdrvggvpdhrtghpthhtohepph hgshhqlhdqghgvnhgvrhgrlhesphhoshhtghhrvghsqhhlrdhorhhg X-ME-Proxy:

Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Fri, 19 Dec 2025 11:12:29 -0500 (EST) Message-ID: Date: Fri, 19 Dec 2025 08:12:29 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: How to get single table data from backup To: Andrus , pgsql-general References: Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 12/19/25 04:41, Andrus wrote: > Hi! > > Large database backup is created using > > pg_dump -b -f backup.backup -F c > > How to restore single table in schema firma2 from it in Windows? > > Created database recovery and tried > > pg_restore --schema-only --n firma2 -d recovery backup.backup > pg_restore -n firma2 -t tabletorecover -d recovery backup.backup > > This fails since tabletorecover contains lot foreign key references. > Foreign keys refer to other tables which have foreign keys to another > tables etc. making huge graph. All tables contain also data. > > I need to get table data only. Using PostgresSql 17 in windows. 1) Does the table name tabletorecover occur in more then one SCHEMA? 2) Do you want only the table data for tabletorecover? Assuming 1 is one SCHEMA only and 2 is tabletorecover data only then maybe something like: 1) pg_restore --schema-only --table=tabletorecover -d recovery backup.backup 2) In psql ALTER TABLE tabletorecover DROP CONSTRAINT the FK references in tabletorecover. 3) pg_restore --data-only --table=tabletorecover -d recovery backup.backup > > Andrus. > > -- Adrian Klaver adrian.klaver@aklaver.com From ray@rodonnell.ie Fri May 15 23:23:36 2026 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.96) (envelope-from ) id 1vWd63-006BAY-1d for pgsql-general@arkaria.postgresql.org; Fri, 19 Dec 2025 16:12:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vWd62-008O2d-1R for pgsql-general@arkaria.postgresql.org; Fri, 19 Dec 2025 16:12:51 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from <0102019b37627d9c-03ffe58c-44b4-4245-8cde-fb8f84b97509-000000@mail.rodonnell.ie>) id 1vWd61-008NqX-33 for pgsql-general@lists.postgresql.org; Fri, 19 Dec 2025 16:12:50 +0000 Received: from a7-34.smtp-out.eu-west-1.amazonses.com ([54.240.7.34]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from <0102019b37627d9c-03ffe58c-44b4-4245-8cde-fb8f84b97509-000000@mail.rodonnell.ie>) id 1vWd5v-001X7u-0t for pgsql-general@postgresql.org; Fri, 19 Dec 2025 16:12:49 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/simple; s=pttss7zjfbrazujxl6mrlo4sg34ocala; d=rodonnell.ie; t=1766160760; h=Message-ID:Date:MIME-Version:Subject:To:References:From:In-Reply-To:Content-Type:Content-Transfer-Encoding; bh=KzzJ+67lvr+8Izx0NlLDl7EosK9OWLjIi7Pr7T+RjqI=; b=yywyKcSoWEVDnlW+146ERnvZ66GL8cONS/OPKk7PRPfzeMNb1QSpbgxF3IRrcotv L8fcltN9vCNuAipWvbekXweXVWngcJOp6qfreNWAVHmAlDtjLmOEDHgll5lWevS5qA3 RKWK0bHf0mjhTL8zRuiretx0sXPIPXJw9RgljWFVpoDmKhhR+rzz9YOQHiPCAMeZdrx sfSkCQt+bnR4iuJ1ZtqTWkphJAeq4VMc2EZKs/onP6UnRKZcAGgWXxsb2X8yIXHLLaj Dsr+XH5dnfnZDj9PgN0ozF0pY+J1rjrN+VpLwquUwUM+mjrF+lSCCPEgER40OI0LFqP z/mSdNgMpg== DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/simple; s=ihchhvubuqgjsxyuhssfvqohv7z3u4hn; d=amazonses.com; t=1766160760; h=Message-ID:Date:MIME-Version:Subject:To:References:From:In-Reply-To:Content-Type:Content-Transfer-Encoding:Feedback-ID; bh=KzzJ+67lvr+8Izx0NlLDl7EosK9OWLjIi7Pr7T+RjqI=; b=VZtoBlXo3jxB6i5fxCyxdZ8YUL/y0BGr7YVdchd7BCXSB4BwMOrReefiG0b1cJU+ F+EcXx9lpZDENETBN1D+t5EO2XCd6RCOepWmuOUdl2LJMlVWOsWZFkEx12fiR2+S2QY YGSR6xsKfnz/2k86TpQ+UlFYOQAoKKbI7RZGL0Tg= Message-ID: <0102019b37627d9c-03ffe58c-44b4-4245-8cde-fb8f84b97509-000000@eu-west-1.amazonses.com> Date: Fri, 19 Dec 2025 16:12:40 +0000 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: How to get single table data from backup To: Andrus , pgsql-general References: Content-Language: en-GB From: Ray O'Donnell In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Feedback-ID: ::1.eu-west-1.Bw1eu/7Uepg3xcsiFBL71JJRCh2A2yTFrgBhhiulXmU=:AmazonSES X-SES-Outgoing: 2025.12.19-54.240.7.34 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 19/12/2025 12:41, Andrus wrote: > Hi! > > Large database backup is created using > > pg_dump -b -f backup.backup -F c > > How to restore single table in schema firma2 from it in Windows? > > Created database recovery and tried > > pg_restore --schema-only --n firma2 -d recovery backup.backup > pg_restore -n firma2 -t tabletorecover -d recovery backup.backup > > This fails since tabletorecover contains lot foreign key references. > Foreign keys refer to other tables which have foreign keys to another > tables etc. making huge graph. All tables contain also data. > I haven't tried it, but - off the top of my head - how about: (i) Create the table separately by hand, without the FK references. (ii) Do a data-only restore, using the -a and -t options to pg_restore Ray. > I need to get table data only. Using PostgresSql 17 in windows. > > Andrus. > > -- Ray O'Donnell // Galway // Ireland ray@rodonnell.ie From adrian.klaver@aklaver.com Fri May 15 23:23:15 2026 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.96) (envelope-from ) id 1vWdGf-006EQD-1E for pgsql-general@arkaria.postgresql.org; Fri, 19 Dec 2025 16:23:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vWdGe-008R9O-0s for pgsql-general@arkaria.postgresql.org; Fri, 19 Dec 2025 16:23:49 +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.96) (envelope-from ) id 1vWdGd-008R9F-1M for pgsql-general@lists.postgresql.org; Fri, 19 Dec 2025 16:23:48 +0000 Received: from fhigh-b3-smtp.messagingengine.com ([202.12.124.154]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vWdGb-001d1z-1a for pgsql-general@postgresql.org; Fri, 19 Dec 2025 16:23:47 +0000 Received: from phl-compute-11.internal (phl-compute-11.internal [10.202.2.51]) by mailfhigh.stl.internal (Postfix) with ESMTP id 818237A001C; Fri, 19 Dec 2025 11:23:43 -0500 (EST) Received: from phl-frontend-03 ([10.202.2.162]) by phl-compute-11.internal (MEProxy); Fri, 19 Dec 2025 11:23:43 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm3; t=1766161423; x=1766247823; bh=UgRFtdPVRV4X4JaUAY7PV1bb6S4jHjlkmfnqZcpOTY8=; b= u7tID/D2qk14O/iUWEPTnnCLWKLk26+lSLLmHtqOsKTiquz9iS4RCas8slQI9Tq9 Cjgxv+hPT6sE6sVdqeYM1ePgyOJpH5GBP8tQwxeZh9i1Enma/NEgigUjXDH2kjGB UQZaudjUi0efCfJcpxmb6aOAsl+6vvImBtdsF9WNddBHPvOT80iQXPAw7UUpP7ST WYDhIK9plKAra5juaYOcZ5r5is9axSNwCzKHiR2vlTmcbTsP5upZrHNBzLPDwXj7 KXpUbAscIyy9fYUwuErIryS3WbXkOoQsLj1UkEFYrhj00H7rl6JrP+6OHKMCfYif KX9Vw0cHlx+MouzniT1BqA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:subject:subject:to:to:x-me-proxy:x-me-sender :x-me-sender:x-sasl-enc; s=fm1; t=1766161423; x=1766247823; bh=U gRFtdPVRV4X4JaUAY7PV1bb6S4jHjlkmfnqZcpOTY8=; b=fc9skH5zH39GFxCA2 1PgZgcnHlskFrs07LTM6iYY0mGdQIo0+yrqqslt6MRV9l2UVcyRayRou94BheISM L6Ic+3yCqIkastEln8k+5jqaBXDUMGNuJtBEGkgfIdIsD3RCt3gkSEPLQivyHQgl omxsrngQGnNITCQOi+Zl8aA2nrBdaUmfesvcM5+xtqkHb6q09wDPvqlpno0Btjok C/jus1d7+aWdZ9piqxFG/nYvYNmIleD8KbQybd1+Fsllpl3y3DeFZfvR+PdoHyG2 pdbM600wzQySYenahsl9kEQ7u078tQhrP1nOxsQGGBjsak+fzg7uC6WdfTXBovp1 E5TXw== X-ME-Sender:

X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeefgedrtddtgdegkeejjecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpuffrtefokffrpgfnqfghnecuuegr ihhlohhuthemuceftddtnecunecujfgurhepkfffgggfuffhvfhfjggtgfesthekredttd dvjeenucfhrhhomheptegurhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghv vghrsegrkhhlrghvvghrrdgtohhmqeenucggtffrrghtthgvrhhnpefhudevueefveffje eigeehffetteehjeevtedtgfdtfedvgeejgfevvdefhfetffenucevlhhushhtvghrufhi iigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvghrse grkhhlrghvvghrrdgtohhmpdhnsggprhgtphhtthhopedvpdhmohguvgepshhmthhpohhu thdprhgtphhtthhopehkohgsrhhulhgvhhhtvdeshhhothdrvggvpdhrtghpthhtohepph hgshhqlhdqghgvnhgvrhgrlhesphhoshhtghhrvghsqhhlrdhorhhg X-ME-Proxy:

Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Fri, 19 Dec 2025 11:23:42 -0500 (EST) Message-ID: <22e39323-a1d4-4c07-b840-d796f35e07cc@aklaver.com> Date: Fri, 19 Dec 2025 08:23:42 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: How to get single table data from backup From: Adrian Klaver To: Andrus , pgsql-general References:

Content-Language: en-US In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 12/19/25 08:12, Adrian Klaver wrote: > On 12/19/25 04:41, Andrus wrote: >> Hi! >> >> Large database backup is created using >> >> pg_dump -b -f backup.backup -F c >> >> How to restore single table in schema firma2 from it in Windows? >> >> Created database recovery and tried >> >> pg_restore --schema-only --n firma2 -d recovery backup.backup >> pg_restore -n firma2 -t tabletorecover -d recovery backup.backup >> >> This fails since tabletorecover contains lot foreign key references. >> Foreign keys refer to other tables which have foreign keys to another >> tables etc. making huge graph. All tables contain also data. >> >> I need to get table data only. Using PostgresSql 17 in windows. > > 1) Does the table name tabletorecover occur in more then one SCHEMA? > > 2) Do you want only the table data for tabletorecover? > > Assuming 1 is one SCHEMA only and 2 is tabletorecover data only then > maybe something like: > > 1) pg_restore --schema-only --table=tabletorecover -d recovery > backup.backup > > 2) In psql ALTER TABLE tabletorecover DROP CONSTRAINT the FK references > in tabletorecover. > > 3) pg_restore --data-only --table=tabletorecover -d recovery backup.backup > An alternate method: 1) pg_restore --table=tabletorecover --file recovery.sql backup.backup Here you are restoring the table structure and data to a text file. 2) Open the text file and remove the FK references from the table definition. 3) Then do psql -f recovery.sql -d recovery If the amount of data is large and you don't want to deal with that in a text file then in step 1 add --schema to get just the table definition. Then do pg_restore --data-only --table=tabletorecover -d recovery backup.backup as step 4. >> >> Andrus. >> >> > > -- Adrian Klaver adrian.klaver@aklaver.com From cmt@burggraben.net Fri May 15 23:22:51 2026 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.96) (envelope-from ) id 1vWdvi-006Sz4-35 for pgsql-general@arkaria.postgresql.org; Fri, 19 Dec 2025 17:06:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vWdvQ-008XV9-2X for pgsql-general@arkaria.postgresql.org; Fri, 19 Dec 2025 17:05:57 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vWdvQ-008XV0-1U for pgsql-general@lists.postgresql.org; Fri, 19 Dec 2025 17:05:57 +0000 Received: from smtp.burggraben.net ([2a01:4f8:140:510a::3]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vWdvL-001XTR-2N for pgsql-general@postgresql.org; Fri, 19 Dec 2025 17:05:56 +0000 Received: from elch.exwg.net (elch.exwg.net [IPv6:2001:470:7120:1:21b:21ff:fef0:248b]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client CN "elch.exwg.net", Issuer "R13" (not verified)) by smtp.burggraben.net (Postfix) with ESMTPS id 89EA6C00311; Fri, 19 Dec 2025 18:05:44 +0100 (CET) Received: by elch.exwg.net (Postfix, from userid 1000) id 45010FE59C; Fri, 19 Dec 2025 18:05:44 +0100 (CET) Date: Fri, 19 Dec 2025 18:05:44 +0100 From: Christoph Moench-Tegeder To: Andrus Cc: pgsql-general Subject: Re: How to get single table data from backup Message-ID: References: MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: User-Agent: Mutt/2.2.16 (2025-11-22) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ## Andrus (kobruleht2@hot.ee): > This fails since tabletorecover contains lot foreign key references. Check pg_restore options "--section=pre-data" and "--section=data" - pre-data is the schema without indexes, contraints, etc. (those would be in section post-data). Regards, Christoph -- Spare Space From kobruleht2@hot.ee Fri May 15 23:23:47 2026 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.96) (envelope-from ) id 1vZFoa-006khu-0v for pgsql-general@arkaria.postgresql.org; Fri, 26 Dec 2025 21:57:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vZFoY-00ACvR-2z for pgsql-general@arkaria.postgresql.org; Fri, 26 Dec 2025 21:57:39 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vWhhn-0091Zd-23 for pgsql-general@lists.postgresql.org; Fri, 19 Dec 2025 21:08:08 +0000 Received: from smtpout05.dka.mailcore.net ([185.138.56.205]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vWhhm-001ZBi-19 for pgsql-general@postgresql.org; Fri, 19 Dec 2025 21:08:07 +0000 Received: from SMTP.DKA.mailcore.net (unknown [10.1.0.52]) by SMTPOUT01.DKA.mailcore.net (Postfix) with ESMTP id CA7F9E0099; Fri, 19 Dec 2025 22:08:03 +0100 (CET) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=online.ee; s=mailcore; t=1766178483; bh=3HSi/sm76Qy0aiE3YTDFYdFspkYcruhzUUKT0NLU/r0=; h=Date:Subject:To:Cc:References:From:In-Reply-To:From; b=eHSbGsYMAd9e3LDFANzyxxQ9p/ryOIcnsj/upBBaq8fQn5kcrfk5YYe7BG3zgolLF /0TzTo7X0QnNWAOwEEbnkv6C5eyUl2oVGu+BolPPeyfJohjLHAMQhxfdS9MOf3Zkyq c3wPD5LPT0cq2lVZ9pRK998L7ANoUx3qlfSwTbrE2LDNZat6vj8v15YpmAXbX6aYIk Bqnh4APYtfeoIxiT5oPbEsTUgYu2cTkHSTy9aMmtyOW3dyOV4VSFUyqFmzLbvaqsSn tgy4whg2fu7nXxIIOBjxswbBsePGu8vdmhUmqVySX3mF1oX8qYq3s7SsqTxHRXLYCM HicEhjZbsCFeQ== Received: from [192.168.1.38] (73-142-35-213.sta.estpak.ee [213.35.142.73]) by SMTP.DKA.mailcore.net (Postfix) with ESMTPSA id A4F4740190; Fri, 19 Dec 2025 22:08:03 +0100 (CET) Content-Type: multipart/alternative; boundary="------------lVWhqXWgwpoLKu5SK0DmX0dL" Message-ID: <5b7b0914-ae51-48cb-a6a8-db678f42ebd5@hot.ee> Date: Fri, 19 Dec 2025 23:08:11 +0200 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: How to get single table data from backup To: Christoph Moench-Tegeder Cc: pgsql-general References: Content-Language: et From: Andrus 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. --------------lVWhqXWgwpoLKu5SK0DmX0dL Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Hi! Geat answer since doesnt require manual sql editing. It worked. Thank you. Andrus. 19.12.2025 19:05 Christoph Moench-Tegeder kirjutas: > ## Andrus (kobruleht2@hot.ee): > >> This fails since tabletorecover contains lot foreign key references. > Check pg_restore options "--section=pre-data" and "--section=data" - > pre-data is the schema without indexes, contraints, etc. (those > would be in section post-data). > > Regards, > Christoph > --------------lVWhqXWgwpoLKu5SK0DmX0dL Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

Hi!

Geat answer since doesnt require manual sql editing. It worked.

Thank you.

Andrus.

19.12.2025 19:05 Christoph Moench-Tegeder kirjutas:

## Andrus (kobruleht2@hot.ee):

This fails since tabletorecover contains lot foreign key references.

Check pg_restore options "--section=pre-data" and "--section=data" -
pre-data is the schema without indexes, contraints, etc. (those
would be in section post-data).

Regards,
Christoph

--------------lVWhqXWgwpoLKu5SK0DmX0dL--