1

I have backup created like this:

pg_dump dbname > file

I am trying to restore the database (after drop database and create database) like this:

psql dbname < file

What I get is a database full of tables that are created with dbname.tablename instead of just tablename.

How do I restore a postgres database making sure the tables it creates has just tablename and not dbname.tablename?

Indu Devanath
  • 2,068
  • 1
  • 16
  • 17
  • `dbname` is not a "database name" it's a **schema** name (two very different things). And a table can't have "no schema", every table resides inside a schema. There is no way to restore with "just a tablename". It might not always be necessary to *use* the schema name though when accessing a table. Tables located in the `public` schema usually don't need the prefix. More details in the manual: http://www.postgresql.org/docs/current/static/ddl-schemas.html –  Apr 03 '14 at 06:44
  • 1
    See also `search_path`. I wonder if your original DB had `ALTER DATBASE dbname SET search_path = dbname;` or something... – Craig Ringer Apr 03 '14 at 08:53

1 Answers1

0

Thanks to @Craig Ringer for pointing me in the right direction.

Yes, there was SET search_path on the database for the original DB. This created the table names with schema names prefixed to table names.

Removing or commenting those out of the backup script created tables without a schema prefix. Which was desirable. But the restore didn't result in complete restore, and many tables got left out.

So did the restore, with usual means. Tables are created with schema names prefixed. The sql query scripts broke because they were not specifying the schema names every time they queried the table. To fix this, I followed this - https://stackoverflow.com/a/2875705/1945517

ALTER ROLE <your_login_role> SET search_path TO dbname;

This fixed the broken queries.

Community
  • 1
  • 1
Indu Devanath
  • 2,068
  • 1
  • 16
  • 17