Re: How to get single table data from backup

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