How restore a PostgreSQL table from *.sql using pg_dump or psql?

Question

I need to restore a big table (250mb) in PostgreSQL database in console tool. How I can do this using ps_dump or psql?

you can get the help for all commands with \h in the psql shell — Kungi, Feb 24 '12 at 10:04

score 51 · Accepted Answer · answered Feb 24 '12 at 09:30

51

Just connect to database with psql and run \i /path/to/filename.sql.

answered Feb 24 '12 at 09:30

Tometzky

22,573
5
59
73

This worked while using psql like: psql -d newdb -f db.sql did nothing. Anyone have a clue why? BTW thanks very much. – eXPRESS Nov 26 '18 at 12:03
this restores the whole DB not a single table – volume one May 29 '22 at 18:26

score 23 · Answer 2 · edited Aug 10 '16 at 00:35

psql --username yourusername --dbname yourdatabasename -f yourfile.sql

as clarified here. Depending on configuration, may ask for your password.

If it is a newly installed database engine with no your database yet, use postgres for the database name and try to omit the username part (new installation should normally grant the full access to the current user who installed it).

If you still can’t login, edit temporarily pg_hba.conf wherever it could be in your installation and temporarily set the localhost to trusted. Then you can specify postgres both as username and as the database name.

Don’t forget to revert pg_hba.conf changes when done.

score 16 · Answer 3 · answered Feb 28 '13 at 16:16

16

psql dbname < /path/to/dump.sql

You may even modify your dump on the fly if required:

sed 's/OWNER TO olduser/OWNER TO newuser/g' <  /path/to/dump.sql | psql dbname

answered Feb 28 '13 at 16:16

Ruslan Kabalin

6,580
2
28
20

This was highly useful. I needed something that could be executed from a script. Thanks! – bad_keypoints Apr 09 '15 at 04:56

score 2 · Answer 4 · edited Jan 21 '17 at 07:49

2

psql -U postgres -d doctor_dev < /home/ravi/mydevelopment

edited Jan 21 '17 at 07:49

FelixSFD

6,052
10
43
117

answered Jan 21 '17 at 07:03

Bhimasen Rautaray

181
1
8

1

This isn't an answer. This code only sends unknown sql statements from file to the postgresql database. – Eric Leschinski Jan 21 '17 at 13:17

How restore a PostgreSQL table from *.sql using pg_dump or psql?

4 Answers4