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

Improve this question
2
  • I don't get the question do they show when you execute \dt or not you say both things. Commented 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? Commented Aug 17, 2017 at 16:48

1 Answer 1

Reset to default
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)

Improve this answer
3
  • 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; Commented 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. Commented 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; Commented 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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.