CODEWITHSUNDEEP
postgresqlsedpostgresql-9.1
AnApprentice
AnApprentice

Reputation: 111040

How to restore a single table from a .sql postgresql backup?

A table's rows were mistakenly deleted from the database. We have a db backup which results in a sql file that can restored like so:

psql -h localhost -d proddump -f /Users/U/Desktop/prod_db_backup/PostgreSQL/site_prod.sql

This ends up doing a full restore locally. But what we need is to restore a single table's rows to production. Any tips on how to make this work with PostgreSQL 9.1?

Thanks

Upvotes: 34

Views: 92684

Answers (7)

Pct Mtnxt
Pct Mtnxt

Reputation: 65

I've write some commands to restore only the data for one table, from a plain text pg_dumpall. Inspired from Zouppen, thanks.

Restore the data of a table from a backup plain text SQL, done for example with pg_dumpall.

  1. go to the directory /data/bkp/ where the backup is (pg_full_bak.dmp)
cd /data/bkp/
ls -l
  1. extract the data from table my_table from schema public into the file my_table.sql
sed -n '/^COPY public.my__table /,/^\\\.$/p' pg_full_bak.dmp > my_table.sql
ls -l
  1. check that the data is correct
less my_table.sql
  1. connect to the database db_name containing the empty table my_table which we want to restore, with an account that has write permission on the table
psql -d db_name
  1. check that my_table is empty
select count(*) nb from my_table ;
+------+
|  nb  |
+------+
|   0  |
+------+
  1. import the data
\i my_table.sql
  1. check that it worked correctly
select count(*) nb from my_table;
+-------+
|   nb  |
+-------+
| 9876  |
+-------+

That's working for me under pg 9.3

The easy way : steps 2, 4 & 6

Upvotes: 2

Thomas C. G. de Vilhena
Thomas C. G. de Vilhena

Reputation: 14595

I've recently written a step by step guide on how to restore individual postgres tables.

In short it doesn't work with SQL backups, you need to switch to pg_dump to generate backup files:

pg_dump.exe --host localhost --port 5432 --username "postgres" --schema "public" --format custom --blobs --verbose --file "my_database.dump" "my_database"

Then, whenever you need to restore a specific table for data recovery or bug investigating purposes, you'll have to:

  1. Create an empty local database my_database_restored
  2. Create the table that needs to be restored my_table in the empty database
  3. Run pg_restore to selectively import desired table’s data:
pg_restore.exe -U postgres --data-only -d "my_database_restored" -t "my_table" "my_database.dump"

Upvotes: 3

Zouppen
Zouppen

Reputation: 1264

I'm not aware of any tool for this, but this one-liner extracts precious_table from my_backup.sql file:

sed -n '/^COPY precious_table /,/^\\\.$/p' my_backup.sql

Upvotes: 29

Sergii Mostovyi
Sergii Mostovyi

Reputation: 1451

There is an easy way.

'pg_restore' has a '--table/-t' option. 

pg_restore -a -t your_table /path/to/dump.sql

Use '-h' for remote host. See other options here

Upvotes: 17

vyegorov
vyegorov

Reputation: 22905

I happen to have pg_dumpall dump around. And I would like to restore table named users from the database named edc, as you most likely will have equally named tables in different databases and even schemas.

For my case, the following sed oneliner works:

sed -ne '/^\\connect edc/,/^\\connect/{/\susers\(\s\|$\)/,/;\|\\\./p}' pg92.dump

What it does:

  1. /^\\connect edc/,/^\\connect/ limits the search to be the scope of my database;
  2. {…} will perform all inside commands for the range;
  3. /\susers\(\s\|$\)/ matches all lines with users on it's own, including at the end of the line;
  4. /;\|\\\./ matches lines with ; or containing \.
  5. p forces the matching lines to be outputted (note, that sed is invoked with -n).

Depending on the complexity of your data, you might need to tweak this.

All you have to do is to pipe sed output to the psql command with right switches.

Upvotes: 1

mys
mys

Reputation: 2473

You can use grep + sed in roder to get table data:

First, you need to identify boundaries:

$ fgrep -Ehn '^(COPY |CREATE TABLE )' db.sql
49:CREATE TABLE test (
60:CREATE TABLE test2 (
71:CREATE TABLE test3 (
82:COPY test (i) FROM stdin;
100090:COPY test2 (i) FROM stdin;
200098:COPY test3 (i) FROM stdin;

In order to extract data for table test2:

sed -n '100090,200097p' < db.sql | sed -e 's/^COPY test2/COPY new_table_name/' > new_table_name.sql

Note, you need to subtract one from the second number (i.e exclude next copy stmt)

Now, you can load new_table_name.sql and restore data which you need. Now, you can load data into new table

Upvotes: 10

Craig Ringer
Craig Ringer

Reputation: 324771

Don't do SQL backups if you need single table restore, etc. Use pg_dump's -Fc option - the "custom" format. This can be restored using pg_restore. Selective restore is possible, as are all sorts of other handy features. pg_restore can convert a custom-format dump into an SQL dump later if you need it.

If you're stuck with an existing dump, your only options are:

  • Use a text editor to extract the target table data to a separate file and just restore that; or

  • Restore the dump to a throwaway database then use pg_dump to take a selective dump including just that table. Since it's throwaway, you can use a separate Pg instance on some unloaded fast-but-unsafe machine where you turn on all the "make it fast but eat my data if you like" options like fsync=off. You should NEVER set that in production.

Upvotes: 19

Related Questions