Feedback-ID: i76984098:Fastmail
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 <adrian.klaver@aklaver.com>
To: Andrus <kobruleht2@hot.ee>, pgsql-general <pgsql-general@postgresql.org>
References: <ff66d3ac-2e78-41ee-a511-7f934cb388ac@hot.ee>
 <f5ab1d4f-e702-488c-a642-6a7967587451@aklaver.com>
Content-Language: en-US
In-Reply-To: <f5ab1d4f-e702-488c-a642-6a7967587451@aklaver.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/22e39323-a1d4-4c07-b840-d796f35e07cc%40aklaver.com>
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