How to restore single table from its dump file (xyz.dump) in PostgreSQL?

Question

I tried this command

pg_restore -U postgres -d test1  table4.dump

and got error

pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

How to restore the dump file?

Just feed it to psql as mentioned in my answer - this should solve your problem. You can also use [pgAdmin](https://www.pgadmin.org/) to restore dumps.. — iLuvLogix, Sep 21 '18 at 09:13
@horse: no need to do this manually ;) krist: did you try what i suggested in my answer? — iLuvLogix, Sep 21 '18 at 09:32
@iLuvLogix, It Works thanks...one more thing Is it possible to restore the same dump in AWS redshift through cmd line — krist, Sep 21 '18 at 11:05
@krist: I saw you accepted my answer - If it was usefull to you i would appreciate an upvote aswell ;) — iLuvLogix, Sep 21 '18 at 11:22

iLuvLogix · Accepted Answer · 2018-09-21T11:15:59.350

You have dumped the table in plain sql format which was designed to feed to psql.

This is not recognized by pg_restore! -> doc on pg_restore

For whole db:

cat db.txt | psql dbname

or

psql dbname < db.txt

for table:

connect to the db using psql:

psql /i path/to/filename

will help..

EDIT/UPDATE:

You asked for AWS redshift - here's the solution:

Sign in to the AWS Management Console and open the Amazon Redshift console at https://console.aws.amazon.com/redshift/

Choose Clusters
Choose the Table restore tab
Choose Restore table.
In the Table restore panel, select a date range that contains the cluster snapshot that you want to restore from.

1 Answers1