public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Andrus <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: How to get single table data from backup
Date: Fri, 19 Dec 2025 08:23:42 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>

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
[email protected]

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: How to get single table data from backup
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox