0

I'm trying to run a postgresql Database on Ubuntu 16.04 LTS.

First I installed postgres using sudo apt-get install postgresql which installed version 9.5.1. I then created another user and a new database. I've granted all privileges for the new database to new new user and set the owner to the new user also.

I connected to the new database and filled it by restoring an plain backup (dump) I've created from another database (which has postgresql version 9.2) by using \i /path/to/dump.sql. I got no errors, and when I then typen \dt I got the list with the tables.

The problem is: When I now disconnect (\q) and re-connect (same as I connected before, sudo psql -U "username" dbname) and type \dt again, it says "No relations found". When I try to fill it again, I get a bunch of errors like "Relation relationname already exists".

Another problem/symptom appears when I try to use pgAdmin (installed via sudo apt-get install pgadmin3). When trying to connect using localhost, I can't connect using the newly created user (which which I can connect from the terminal). But I can connect using postgres with the password I set via Terminal. I do not see any DB I manually create from command line from pgAdmin tho.

So yeah, my guess is that at least one problem may be version incompatibility between 9.2 and 9.5. Is there any way to test/fix this? But I also think there should be other problems.

Improve this question
8
  • my guess, but did you try to do dump on 9.2 from 9.5 server? When I migrate to newer version , I usually always dump it remotely on then newer server. Commented May 6, 2016 at 10:14
  • The other way round. I have a running 9.2 db which I backuped using plain dump. I'm trying to restore this dump to 9.5. Commented May 6, 2016 at 10:16
  • bad english, I mean call dump script(in which you set the source database and server of 9.2 version) on the 9.5 machine using 9.5 version of PG_DUMP, and then restore it. Commented May 6, 2016 at 10:22
  • @e4c5 do I have to commit when working from the Ubuntu terminal and being connected to a database? I just tried to type "commit;" after a few changes and it says "Warning: there is no transaction in progress" Commented May 6, 2016 at 10:22
  • No I meant the commit after the import. I must confess that I have never used \i to load a database dump, but since the dump is in a transaction, I thought it wasn't being committed. Certainly the error you are having is typical of an uncommited transaction. Commented May 6, 2016 at 10:56

1 Answer 1

Reset to default
1

What you describe may happen if the SQL dump contains a SET search_path TO...command that sets it to a different value than what your user has by default.

So not only it will create its tables and other objects in that schema, but it will leave that search_path for the rest of the session, so when you do \dt in that same session, it does see and list the newly created tables.

But when you quit and reenter psql, this search_path is not longer in effect, you're back to the default search_path of your user, which supposedly doesn't reach the schema, so \dt no longer "sees" any table.

You can use show search_path to check this setting in a psql session, and grep "SET search_path" in the SQL file to check what it's being set to.

Per comment, it appears to be the case: the dump creates the tables into a schema that is outside of the user's default search path.

A possible solution for that user to work seamlessly would be to update its search path so that it always reaches this schema first. This can be done with: 

ALTER USER username SET search_path TO schema1,"$user",public;

where schema1 is what the SQL dump refers to and where it created the tables.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Hey, thanks for the answer. I've actually found a search_path command in the dump. I also checked the "show search_path" directly after filling the DB from the dump. At this point it was set to the value in the dump ("somestring", pg_catalog). Then I disconnected and connected again and now it shows as "$user", public. May you tell me how to fix this issue?
Hey, so I've set the search_path to the database to what is set in the dump and now pgadmin shows the table :) thanks!
@JacobS: I was just suggesting to do it at the user level in my edit, but that works too at the database level.

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.