I have no experience in postgres, and am trying to get a staging site correctly configured.

The database has a schema data in the database drupal. I imported the database from development using (as postgres user):

psql drupal <drupal.dump.sql

When I connect as the drupal user, and execute \dt, it only shows the regular tables and not the tables in the schema "data".

I see those "missing" tables when I execute \dt *.*

I am probably missing some permissions to be added to the drupal user. Any help greatly appreciated!

Postgres version: 9.3.17

  • I don't get the question do they show when you execute \dt or not you say both things. Aug 17, 2017 at 16:06
  • When you run \dt and see the tables, are you connected as the drupal user still, or as a different user?
    – RDFozz
    Aug 17, 2017 at 16:48

1 Answer 1


You have to put the schema "data" in your search_path if you want the tables from that schema to show up in a \dt run with no arguments. (If your import worked, then you already have permissions to the schema, unless you changed users)

  • I compared the search_path settings between development and staging, and saw that the settings were very different. Set the search path to match, and all is well now, thanks! On the postgres prompt: show search_path; set search_path public, data;
    – Ursula
    Aug 17, 2017 at 19:53
  • It's usually done the other way around (set search_path data, public) as it is often - but not always - more important to get information from your specific schema first than the public one. Aug 18, 2017 at 7:06
  • In this particular case, the public one is more important than the specific one. I also realized, that I had to execute this, to make the change permanent: alter role drupal set search_path to public, data;
    – Ursula
    Aug 18, 2017 at 13:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.