Question 89375 in PostgreSQL Question posted in
The official documentation can be found here.

How to restore a .sql pg_dump on PostgreSQL

I have a dump from an SQL database, it is a .sql file. I tried restoring it in pgAdmin but I get an error:

pg_restore: error: input file appears to be a text format dump. Please use psql.

Then I tried using the psql CLI but it just lists my users and it doesn’t show any error or restores anything. I tried psql with the command

psql grana < c:dump-grana.sql

and got the result on the image:
psql_output

Also when I try to use the command psql -U [user] -d [dbname] -1 -f <filename>.sql it returns the same as the image above.

Tags:

2

Answers


  1. 0

    If you have used the following pg_dump to create the dump file

    pg_dump -Fc -b $database > $database.dump

    Then use the following restore command:

    pg_restore --verbose --clean --if-exists --exit-on-error --dbname $database $database.dump --single-transaction

    You will have to expose the following variables

    # Postgres restore
export PGPORT=${POSTGRES_PORT}
export PGHOST=${POSTGRES_HOST}
export PGUSER=${POSTGRES_USERNAME}
export PGPASSWORD=${POSTGRES_PASSWORD}
    Login or Signup to reply.
  2. 0

    You’re trying to use psql from inside psql

    The picture you show is from inside psql. Your command is supposed to call it from the outside. Type q and hit enter to exit psql, then use your command from outside it. Currently, psql is reading your input and:

    1. psql grana < c: gets ignored. It’s accepted as unfinished statement without verifying it yet
    2. du gets executed as du meta-command describing available users
    3. mp-grana.sql gets ignored as accidental parameters to du
    4. Your next lines or commands are accepted the same way, but only meta-commands get executed: until psql gets a ; from you, it assumes you’re still typing out some long and strange set of statements and commands. It won’t try to verify anything else than the meta-commands until you finish.

    If you’ve tried that from outside psql and it’s still happening, it means you likely overwrote the contents of the file with its path by accident and now psql is receiving literal c:dump-grana.sql from inside the file. In that case, if you created the dump yourself, you’ll need to re-do it. Otherwise, get a fresh copy and try again: your command should work just fine as long as the contents of the .sql file make sense.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search