Subject: How to get single table data from backup

From: Andrus <[email-masked]>
Date: Fri, 19 Dec 2025 14:41:37 +0200

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.

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 19 Dec 2025 08:12:29 -0800

> 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

-- 
Adrian Klaver
[email-masked]

---

From: Ray O'Donnell <[email-masked]>
Date: Fri, 19 Dec 2025 16:12:40 +0000

> 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.

-- 
Ray O'Donnell // Galway // Ireland
[email-masked]

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 19 Dec 2025 08:23:42 -0800

> 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.

---

From: Christoph Moench-Tegeder <[email-masked]>
Date: Fri, 19 Dec 2025 18:05:44 +0100

> 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

---

From: Andrus <[email-masked]>
Date: Fri, 19 Dec 2025 23:08:11 +0200

Hi!

Geat answer since doesnt require manual sql editing. It worked.

Thank you.

Andrus.