public inbox for [email protected]
help / color / mirror / Atom feedHow to get single table data from backup
6+ messages / 4 participants
[nested] [flat]
* How to get single table data from backup
@ 2025-12-19 12:41 Andrus <[email protected]>
2025-12-19 16:12 ` Re: How to get single table data from backup Adrian Klaver <[email protected]>
2025-12-19 16:12 ` Re: How to get single table data from backup Ray O'Donnell <[email protected]>
2025-12-19 17:05 ` Re: How to get single table data from backup Christoph Moench-Tegeder <[email protected]>
0 siblings, 3 replies; 6+ messages in thread
From: Andrus @ 2025-12-19 12:41 UTC (permalink / raw)
To: pgsql-general
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get single table data from backup
2025-12-19 12:41 How to get single table data from backup Andrus <[email protected]>
@ 2025-12-19 16:12 ` Adrian Klaver <[email protected]>
2025-12-19 16:23 ` Re: How to get single table data from backup Adrian Klaver <[email protected]>
2 siblings, 1 reply; 6+ messages in thread
From: Adrian Klaver @ 2025-12-19 16:12 UTC (permalink / raw)
To: Andrus <[email protected]>; pgsql-general
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
[email protected]
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get single table data from backup
2025-12-19 12:41 How to get single table data from backup Andrus <[email protected]>
2025-12-19 16:12 ` Re: How to get single table data from backup Adrian Klaver <[email protected]>
@ 2025-12-19 16:23 ` Adrian Klaver <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Adrian Klaver @ 2025-12-19 16:23 UTC (permalink / raw)
To: Andrus <[email protected]>; pgsql-general
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]
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get single table data from backup
2025-12-19 12:41 How to get single table data from backup Andrus <[email protected]>
@ 2025-12-19 16:12 ` Ray O'Donnell <[email protected]>
2 siblings, 0 replies; 6+ messages in thread
From: Ray O'Donnell @ 2025-12-19 16:12 UTC (permalink / raw)
To: Andrus <[email protected]>; pgsql-general
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
[email protected]
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get single table data from backup
2025-12-19 12:41 How to get single table data from backup Andrus <[email protected]>
@ 2025-12-19 17:05 ` Christoph Moench-Tegeder <[email protected]>
2025-12-19 21:08 ` Re: How to get single table data from backup Andrus <[email protected]>
2 siblings, 1 reply; 6+ messages in thread
From: Christoph Moench-Tegeder @ 2025-12-19 17:05 UTC (permalink / raw)
To: Andrus <[email protected]>; +Cc: pgsql-general
## Andrus ([email protected]):
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get single table data from backup
2025-12-19 12:41 How to get single table data from backup Andrus <[email protected]>
2025-12-19 17:05 ` Re: How to get single table data from backup Christoph Moench-Tegeder <[email protected]>
@ 2025-12-19 21:08 ` Andrus <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Andrus @ 2025-12-19 21:08 UTC (permalink / raw)
To: Christoph Moench-Tegeder <[email protected]>; +Cc: pgsql-general
Hi!
Geat answer since doesnt require manual sql editing. It worked.
Thank you.
Andrus.
19.12.2025 19:05 Christoph Moench-Tegeder kirjutas:
> ## Andrus ([email protected]):
>
>> 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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-12-19 21:08 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-12-19 12:41 How to get single table data from backup Andrus <[email protected]>
2025-12-19 16:12 ` Adrian Klaver <[email protected]>
2025-12-19 16:23 ` Adrian Klaver <[email protected]>
2025-12-19 16:12 ` Ray O'Donnell <[email protected]>
2025-12-19 17:05 ` Christoph Moench-Tegeder <[email protected]>
2025-12-19 21:08 ` Andrus <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox