CODEWITHSUNDEEP
postgresqldumppsql
NiLL
NiLL

Reputation: 13853

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

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

Upvotes: 31

Views: 96925

Answers (5)

pawel.codes
pawel.codes

Reputation: 1631

psql -d <db_name> -f <path_to>/<latest_DB_BACKUP>.psql

Upvotes: 0

Bhimasen Rautaray
Bhimasen Rautaray

Reputation: 301

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

Upvotes: 4

Audrius Meškauskas
Audrius Meškauskas

Reputation: 21778

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.

Upvotes: 27

Ruslan Kabalin
Ruslan Kabalin

Reputation: 6908

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

Upvotes: 19

Tometzky
Tometzky

Reputation: 23920

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

Upvotes: 55

Related Questions