SSH Connection Using Pgadmin4 on Ubuntu 16.04

Pgadmin4 has a GUI for connecting to a remote DB. Seems pretty straight forward. And then you get this error:

module ‘paramiko’ has no attribute ‘Ed25519Key’

After some googling you find this Stack Overflow post. Looks like you need to install paramiko 2.4. If you follow the instructions in SO, you end up with a failed install due to missing dependencies. Turns out there is no pre-built distribution of paramiko 2.4 for Ubuntu 16.04.

If you move on the the SO answer that suggests editing /usr/lib/python3/dist-packages/ I tried that. It was easy. Only two lines needed to be removed. However after that, I kept getting errors like ‘Could not establish session to SSH gateway’ and ‘Could not connect to gateway’.

Eventually I solved the problem by creating an ssh tunnel from the Ubuntu command line and then connecting to the tunnel in pgadmin.




Django Tests: Cannot Drop Database

Sometimes when tests crash, if you try to re-run them, the code will fail because the old database cannot be dropped and the error message will be something like:

ERROR: database "my_db" is being accessed by other users
DETAIL: There is 1 other session using the database.

Or if you are using PyCharm the message will be:

Got an error recreating the test database: database "my_db" is being accessed by other users
DETAIL: There is 1 other session using the database.

To resolve this situation, see this blog post.

Also, you can run SQL from PGAdmin.

Or you can make a Django management command:

from import BaseCommand
from django.db import connection
from django.conf import settings

class Command(BaseCommand):
    help = 'Run this after tests crash'

    def handle(self, *args, **options):
        cursor = connection.cursor()

        database_name = 'test_{}'.format(settings.DATABASES['default']['NAME'])

        # Clears connections to db so that it can be dropped
            "SELECT pg_terminate_backend( FROM pg_stat_activity "
            "WHERE pg_stat_activity.datname = %s AND pid <> pg_backend_pid();", [database_name])



Postgresql: pg_dump and pg_restore

I had a problem with a database and I wanted to copy a table from a backup and install it in the current version. There are some subtleties. Here are my notes.


Use the “-t <table name>” argument to specify the table to dump. More multiple tables, use it multiple times.

Use the “–format custom” to make the dump compatible with pg_restore. If you do not set the format, the output will be a text file of SQL commands (useful in some cases) and you will need to use psql to restore the table.

Use –oids to maintain id numbers. This is especially important if the id is a foreign key else where.

Here is the command I used:

pg_dump roihubdiazlaw2db -f django_migrations.dump -t django_migrations -U my_username --format custom --oids


The trick here is not NOT use the -t flag! If you use this flag, pg_restore will NOT restore ids as primary keys. Instead they will become ordinary integers. The info needed to restore the table is already in the dump file. Restore with:

pg_restore django_migrations.dump -d roihubdiazlaw2db -U my_username --format custom


PostgreSQL: password authentication failed for user

Everything was working fine. Then for no apparent reason PostgreSQL started failing with the error message:

FATAL:  password authentication failed for user


.pgpass had my user name along with postgres. Running this worked:

psql -U postgres -h localhost -p 5432

This failed:

psql -U my_username -h localhost -p 5432

This allowed me to manually enter the password and by pass .pgpass, but it still failed:

psql -U my_username -h localhost -p 5432 -W

This allowed me to list the users:

psql -c '\du' -U postgres

And that is how I discovered that my password had expired. I did not even know it could expire. I fixed it in pgadmin.

It’s Back…

So I had things working. I was do a lot of work using pg_restore. All of a sudden, password auth failed again. The above procedures were no help. In pgadmin I re-entered the password. Now its working again.

Configuring Postgresql and Dropdb

Seems like an on-going nightmare. I am configuring a new Ubuntu 14.04 system and having troubles with getting passwords from .pgpass. Here are the fundamentals:

  • .pgpass should be in the users home dir with permissions 0600 (rw). Lesser permissions will cause postgresql to ignore this file
  • /etc/postgresql/9.3/main/pg_hba.conf is set to md5

Let the fun begin. I have a this line in my .pgpass file:


When I run:

psql -U my_user -d my_db

I am able to launch psql. When I run the psql command “\conninfo”, I get:

You are connected to database "my_db" as user "my_user" via socket in "/var/run/postgresql" at port "5432"

But when I exit psql and run:

dropdb my_db -U my_user -w

I get:

dropdb: could not connect to database template1: fe_sendauth: no password supplied

WTF? Then I saw this statement on Stackoverflow:

recall that creatdb and dropdb aren’t really acting on “myprojectdb” despite the fact that you may be asking them to create and drop that db

Maybe my attempts at tightening security are the problem. When I changed .pgadmin to:


Then dropdb worked.

PyCharm, Django Dev Server and “password authentication failed for user”

This stuff drives me crazy. I was cruising along, run the Django dev server from the command line. Everything was working well. Then I decided to do some debugging, so I configured the PyCharm Django dev server and got this error:

django.db.utils.OperationalError: FATAL:  password authentication failed

Huh? It was just working. Here’s what was happening. I should mention I was using PostgreSQL. In my local settings file I have:

    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': DB_NAME,
        'USER': 'roi_erp',
        'PASSWORD': os.environ.get('LOCAL_DB_PASSWORD', get_secrets('DB_PASSWORD')),
        'HOST': os.environ.get('POSTGRESQL_HOST', 'localhost'),
        'PORT': '',

First I try to get the DB password from a local environmental variable. If that fails, I look in my project secrets file. My local .pgpass has the LOCAL_DB_PASSWORD, but not the one in secrets. When I ran from the command line, LOCAL_DB_PASSWORD was defined. When I ran from PyCharm, it wasn’t, hence the error message. To solve the problem, I set LOCAL_DB_PASSWORD in PyCharm.

Maybe I should just make the python settings code fail if LOCAL_DB_PASSWORD.

Connecting to Remote POSTGRESQL Using PgAdmin

Here is how I connect to the PostgreSQL database on a Digital Ocean droplet using pgAdmin III.

Screenshot from 2015-02-17 21:57:05

Screenshot from 2015-02-17 21:55:25


Error connecting to the server: fe_sendauth: no password supplied

I got this error after not using pgadmin remotely for a long time. Turns out it was a brain-fart. SSH allows you to connect to the server without a password. But you still need to enter a database password. I did not enter the database password. Maybe that’s why the error message said “no password supplied”. Duh!