Django cannot create superuser on PostgreSQL. "server closed the connection unexpectedly"

Question

I'm having an issue with a Django site that cannot create a superuser with a PostgreSQL database.

From the development machine

python manage.py createsuperuser --username user1 --email [email protected]

returns:

django.db.utils.OperationalError: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

Now, the PostgreSQL server is up. I can run python manage.py migrate and create tables over there. I can also tell the server is up by putting in incorrect credentials, and receiving an error for having bad creds. I've got Dbeaver up and running on the same machine the Django site is being developed on, and it can connect and see the database as the user the Django site's settings.py is using.

From the PostgreSQL Server machine

Running cat /var/lib/pgsql/data/pg_log/postgresql-Mon.log yields the following after an attempted superuser creation:

 LOG:  could not receive data from client: Connection reset by peer
 LOG:  unexpected EOF on client connection with an open transaction

Because I can connect to the server and have success with other queries, I'm not sure where this issue lies.

Where should I look next to troubleshoot this issue?

EDIT: UPDATE

I changed postgresql.conf to reflect log_statement = 'all'. Now, upon running the createsuperuser command, I see the following in the PostgreSQL log:

LOG:  statement: 
                SELECT c.relname, c.relkind
                FROM pg_catalog.pg_class c
                LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                WHERE c.relkind IN ('r', 'v')
                    AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
                    AND pg_catalog.pg_table_is_visible(c.oid)
LOG:  statement: SELECT "django_migrations"."app", "django_migrations"."name" FROM "django_migrations"
LOG:  statement: SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined" FROM "auth_user" WHERE "auth_user"."username" = 'user1'
LOG:  statement: BEGIN
LOG:  could not receive data from client: Connection reset by peer
LOG:  unexpected EOF on client connection with an open transaction

Changed log_statement = 'all' in postgresql.conf. Now I see a couple SELECT's come through from Django, and then I get this one, statement: BEGIN *next 2 lines are the LOG: error's from above. Connection reset by peer unexpected EOF on client connection with open transaction — Joshua Schlichting

Joshua Schlichting Joshua Schlichting · Accepted Answer · 2019-02-11T21:44:07

This turned out to be a networking issue (I'm not too surprised.)

So, the issue goes a bit deeper than just PostgreSQL configuration and/or Django configuration. On that note, I've thought about deleting this question, since my solution is so far from the question asked. However, if someone else ever ends up in this same situation, this could come in handy for them out here on the web.

The solution(s):

Turning off port-forwarding on routing device for the network the PostgreSQL server is on

OR having the database connection string point to the external IP address of the network, instead of the LAN IP address.

Explanation:

The issue ended up having everything to do with the router that facilitates the LAN that both the PostgreSQL server and Django development machine are on. The router had port forwarding turned on for the PostgreSQL server's port. By doing this, the router interrupted internal LAN connections to the PostgreSQL server partially, but not completely. I verified this claim by changing the Django settings.py file and having HOST point to the external address of the network. Once I did that, I was able to create the superuser and see some clean commands coming through the PostgreSQL log.

I doubled down on verifying this issue by disabling port forwarding, changing the HOST back to the internal LAN address, and updating the password of the user I had just created. Everything worked great. The PostgreSQL log looked clean, I could see the update command and its COMMIT. Ultimately, I disabled port forwarding and stuck with the internal address for my HOST in settings.py.

So, this turned out to be a networking issue, and not a code issue, but I think this information could be helpful to someone in the future. So, for now, I think I'll leave this question up.

Django cannot create superuser on PostgreSQL. "server closed the connection unexpectedly"

1 Answers