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(dot)klaver(at)aklaver(dot)com