How to browse django psql backend from command line?

Question

I'm developing a website with Django 3 (in a docker container) using postgres sql as the backend; i.e. in the project settings file I have:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'postgres',
        'USER': 'postgres',
        'PASSWORD': 'postgres',
        'HOST': 'db',
        'PORT': 5432
    }
}

I've populated the backend database and can browse the data using the admin. However, Id like to connect to the database via the command line so I can more easily test queries. I have tried connecting to the database the normal way from the command line:

sudo -u postgres psql
postgres=# \c postgres

The problem is that there is no data found:

postgres=# \dt
Did not find any relations.

Since I'm new to docker I thought to try connecting other ways as well; specifically, based on another post I tried:

sudo docker run -d -p 5432 -t postgres/postgresql /bin/su postgres -c '/usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/10/main -c config_file=/etc/postgresql/10/main/postgresql.conf'

This throws an error:

pull access denied for psql/postgresql, repository does not exist or may require 'docker login'

Again, I'd like to connect to the database via the command line so I can more easily test queries. Perhaps Im on the right track but assistance would be appreciated.

Answer 1

It is a bad idea to use postgres for the database name, as there is a postgres database used for maintenance by default by PostgreSQL itself. I'd recommend calling the database something like my_project, then creating a service account user my_project_user, and assign a password:

sudo -u postgres psql
postgres=# CREATE USER my_project_user WITH PASSWORD 'blahblah';
postgres=# CREATE DATABASE my_project WITH OWNER my_project_user;
postgres=# \q

Update your Django DATABASES["default"] settings accordingly, and run migrations. After running migrations, you can access your Django database using the following management command:

python manage.py dbshell

You may be able to issue the command above with your current setup, but you may run into problems using postgres as your database name. Good luck!