CODEWITHSUNDEEP
postgresqlpg-restore
Anthony
Anthony

Reputation: 36008

How to restore output from pg_dump into a new table name

I am dumping a large Postgres table like this:

pg_dump -h myserver  -U mt_user --table=mytable  -Fc -Z 9 --file mytable.dump mydb

The above creates a mytable.dump file. I now want to restore this dump into a new table called mytable_restored.

How can I use the pg_restore command to do this?

Upvotes: 14

Views: 11141

Answers (5)

Fedor
Fedor

Reputation: 86

well, if you don't need to keep your current DB.mytable online - you could:

  1. rename it with ALTER TABLE to a mytable_live_tmp,
  2. import the dump mytable and rename it to mytable_restored,
  3. then rename mytable_live_tmp back to mytable.

Upvotes: 0

Otheus
Otheus

Reputation: 1082

As of PG-13, there is no built-in way, but if you are using Unix-ish system and perl is installed, I have simple solution below which should work as long as table-names do not contains spaces.

Go into your dump directory and run

 pg_restore -l -Fd .  |
 perl -ln -e '$Z=".gz";' -e 'print qq(mv "$1.dat$Z" "$3.sql$Z") if /^(\d+);.* TABLE DATA (\S+) (\S+) (\S+)$/'

Change "$Z=".gz" accordingly depending on the system's compressed filename-extensions (or set to empty string when dumping with --compress=0).

If the results look ok, run again, and pipe the whole thing through the shell, ie, | /bin/sh.

Upvotes: 0

Andy Gauge
Andy Gauge

Reputation: 1428

you can append sed to your pg_dump so that while pg_dump outputs the table name, sed will replace it with the right one.

pg_dump mytable mydb | sed 's/mytable/mytable_restored/g' > mytable_restored.dump

Upvotes: 1

edib
edib

Reputation: 882

My answer may not the exact answer for the question but for my case (directory dump with many tables), I have improved Laurenz's answer, it may be useful.
In my case I have just needed two tables to restore to another location. First I created the tables in new location. I find the table data in the toc.dat file. 

pg_restore -Fd <dir_name> -l | grep <table_name>

This will probably return more than single line. Then we look for "TABLE DATA" line. After getting [file_number].dat.gz file and use it in copy command like below in psql.

COPY <table_name> from PROGRAM 'zcat /<path>/<file_number>.dat.gz'

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 248215

There is no pg_restore option to rename tables.

I would do it like this:

-- create a table that is defined like the original
CREATE TABLE mytable_restored (LIKE mytable INCLUDING ALL);

-- copy the table contents
COPY mytable TO '/tmp/mytable.dmp';
COPY mytable_restored FROM '/tmp/mytable.dmp';

Upvotes: 15

Related Questions