X
X
Xcopy2015-06-17 09:58:32
PostgreSQL
Xcopy, 2015-06-17 09:58:32

How can I find out if I am connecting to the correct PostgreSQL database?

Good day everyone!
For the Django (Python web framework) project, I decided to use a new PostgreSQL database for myself.
Created a database and made it the owner of the newly created user dbuser, who was given a password.
I registered in the Django project settings (everything seems to be correct here):

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'ew',
        'USER': 'dbuser',
        'PASSWORD': '...',
        'HOST': '127.0.0.1',
        'PORT': '5432',
    }
}

Then I launched migrate (using this command, Django connects to the database and uploads changes to it).
And everything went well. I can go to the admin interface automatically created by Django, which shows that Django itself was able to connect to the database, create the necessary tables and records in it.
But after I wanted to enter this database through the psql console and see the data in it, I got an authentication error.
After that, I found the hba_file file:
postgres=# SHOW hba_file;
               hba_file               
--------------------------------------
 /etc/postgresql/9.3/main/pg_hba.conf
(1 row)

And I changed the line local all postgres peer to local all postgres md5 in it , restarted postgesql and seemed to be able to log in:
[email protected]:/home/WMUser/ew$ psql -U dbuser -d ew
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
  LANGUAGE = (unset),
  LC_ALL = (unset),
  LC_PAPER = "ru_RU.UTF-8",
  LC_ADDRESS = "ru_RU.UTF-8",
  LC_MONETARY = "ru_RU.UTF-8",
  LC_NUMERIC = "ru_RU.UTF-8",
  LC_TELEPHONE = "ru_RU.UTF-8",
  LC_IDENTIFICATION = "ru_RU.UTF-8",
  LC_MEASUREMENT = "ru_RU.UTF-8",
  LC_TIME = "ru_RU.UTF-8",
  LC_NAME = "ru_RU.UTF-8",
  LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Password for user dbuser: 
psql (9.3.8)
Type "help" for help.

ew=>

But going into the database: \c ew
And deciding to see all the tables that Django created, I saw:
ew=> \dt
No relations found.

I don’t understand anything, it seems that the data is somewhere, but either I simply don’t see it with this user, or they are somewhere else.
Django has such a command: dbshell, through which you can enter the database that I registered in the Django config. And here's the magic: when I entered the postgresql console through it, I was able to see all the signs:
[email protected]:~/ew/eworld$ python manage.py dbshell
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
  LANGUAGE = (unset),
  LC_ALL = (unset),
  LC_PAPER = "ru_RU.UTF-8",
  LC_ADDRESS = "ru_RU.UTF-8",
  LC_MONETARY = "ru_RU.UTF-8",
  LC_NUMERIC = "ru_RU.UTF-8",
  LC_TELEPHONE = "ru_RU.UTF-8",
  LC_IDENTIFICATION = "ru_RU.UTF-8",
  LC_MEASUREMENT = "ru_RU.UTF-8",
  LC_TIME = "ru_RU.UTF-8",
  LC_NAME = "ru_RU.UTF-8",
  LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
psql (9.3.8, server 9.3.3)
Type "help" for help.

ew=> \dt
                  List of relations
 Schema |            Name            | Type  | Owner  
--------+----------------------------+-------+--------
 public | auth_group                 | table | dbuser
 public | auth_group_permissions     | table | dbuser
 public | auth_permission            | table | dbuser
 public | auth_user                  | table | dbuser
 public | auth_user_groups           | table | dbuser
 public | auth_user_user_permissions | table | dbuser
 public | django_admin_log           | table | dbuser
 public | django_content_type        | table | dbuser
 public | django_migrations          | table | dbuser
 public | django_session             | table | dbuser
(10 rows)

ew=>

There is another interesting feature (can help me help) if I do \l from the django console, then I get:
List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 ew        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | dbuser=CTc/postgres

And if from psql:
List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 ew        | dbuser   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

Those. in one case, the ew database has one owner, in the other case, another. How so? One gets the feeling that these are generally 2 completely different bases ...
I ask for help in understanding what is happening.
Thanks in advance for your reply!

Answer the question

In order to leave comments, you need to log in

2 answer(s)
M
marazmiki, 2015-06-17
@perminovma

Most likely, django connects via a tcp socket, and psql connects locally; try specifying a host to connect to:
psql -U dbuwer -H 127.0.0.1 ew

D
denizen, 2015-06-17
@denizen

After \c ewtry \dn- get a list of base schemes, then try \dt имясхемы.*
Or as an option - set search_path=имясхемы;. If there are several schemes, then write them separated by commas.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question