PostgreSQL import not working

Question

I have been trying to import a postgres dump (with psql dbname < dump.sql and its variants, specifying hostname and username and so on), but I have been unsuccessful with it so far.

I am new to Postgres, so I may have been missing the obvious here. Based on the output of importing commands, the extension plpgsql probably doesn't exist. Am I expected to set up the plpgsql extension before I import?

Here are the first few lines of the output:

SET
ERROR:  unrecognized configuration parameter "lock_timeout"
SET
SET
SET
SET
CREATE EXTENSION
ERROR:  must be owner of extension plpgsql
ERROR:  could not open extension control file "/usr/share/postgresql/9.1/extension/hstore.control": No such file or directory
ERROR:  extension "hstore" does not exist
SET
SET
SET
ERROR:  relation "active_admin_comments" already exists
ERROR:  role "finalstep" does not exist
ERROR:  relation "active_admin_comments_id_seq" already exists
ERROR:  role "finalstep" does not exist
ALTER SEQUENCE
ERROR:  relation "admin_users" already exists
ERROR:  role "finalstep" does not exist

Later down the road, it looks like it's trying to execute some of the content as sql queries (the content contains mathml tags):

ERROR:  syntax error at or near "</"
LINE 1: </mo> <msqrt>
        ^
ERROR:  syntax error at or near "&"
LINE 1: &nbsp;
        ^
ERROR:  syntax error at or near "</"
LINE 1: </mi> <mi>&alpha;

Any pointers? My Postgres version is 9.1 (it was 9.3 initially, but I downgraded it to 9.1 after seeing the error messages).

Answer 1

Hard to say without the file itself. Some pointers:

• I would stick with version 9.3 if at all possible. Many improvements over 9.1, and you will have more time before you need to upgrade again. Also, the first error message indicates you need Postgres 9.3 anyway, since the GUC (configuration setting) lock_timeout was introduced with Postgres 9.3.

• Use psql with the -f switch to get more detailed error messages:

  psql dbname -f dump.sql
  

  (And there is no - in front of dbname: -dbname, that's just a typo, I assume?)

• Language plpgsql shouldn't be a problem if your installation isn't broken. Per documentation:

  In PostgreSQL 9.0 and later, PL/pgSQL is installed by default.

• These error messages indicate problems reading files from your contrib package at the OS level.

  ERROR:  must be owner of extension plpgsql
  ERROR:  could not open extension control file "/usr/share/postgresql/9.1/extension/hstore.control": No such file or directory
  ERROR:  extension "hstore" does not exist
  

  • You need the optional package postgresql-contrib-9.1 (or postgresql-contrib-9.3 respectively) installed for additional modules like hstore. Details in this related answer:
    Error when creating unaccent extension on PostgreSQL

  • The files must be accessible to the system user that started Postgres, typically postgres (the OS user). I.e., when typing whoami in the shell, the answer should be postgres.

  • Also, you must be executing the bash command as superuser, typically as postgres (the DB user). Typically, if you don't specify a user name, and IDENT or PEER authentication is enabled (default), then the DB user matching the OS user is used automatically. Details in this related answer:
    Run batch file with psql command without password