1

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. – Evan Carroll Aug 17 '17 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 '17 at 16:48
2

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 '17 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. – KookieMonster Aug 18 '17 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 '17 at 13:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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