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?

Solution 1

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

Solution 2

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.

Solution 3

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

Solution 4

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

View more solutions

Comments

[Kungi]

you can get the help for all commands with \h in the psql shell

[bad_keypoints]

This was highly useful. I needed something that could be executed from a script. Thanks!

[Eric Leschinski]

This isn't an answer. This code only sends unknown sql statements from file to the postgresql database.

[eXPRESS]

This worked while using psql like: psql -d newdb -f db.sql did nothing. Anyone have a clue why? BTW thanks very much.

[volume one]

this restores the whole DB not a single table