Vagrant, PostgreSQL and pgAdmin

Here’s how to use pgAdmin to inspect a database on a Vagrant virtual machine. Vagrant already has SSH setup so the easiest and most secure way to connect to that database is using pgAdmin’s SSH Tunnel feature. To do this, click the connection icon (a plug) and you will see something like this:

Screenshot from 2015-01-20 17:42:48

For Name you can enter anything. Note: Host is localhost, not the vagrant IP address . Username is the database username. Password is the database password. DO NOT CLICK OK yet. Instead, click the SSH Tunnel tab. You will see something like this:

pgadmin2

Tunnel host is the IP address of the Vagrant VM. Username is the Linux username used when you SSH in to the virtual machine. The identity file is the SSH private key for this virtual machine. When you run “vagrant up”, it creates a .vagrant folder. The private key is in there in a folder like:

~/my_project/.vagrant/machines/default/virtualbox/private_key

Now click OK and you should be connected.

Addendum

I just started getting this error:

Error connecting to the server: server closed the connection unexpectedly
 This probably means the server terminated abnormally
 before or while processing the request.

Turns out the error was in the first GUI (above) for setting up the connection. For Host, I put the vagrant IP address. However, since I was using SSH Tunneling, the correct Host is localhost. Ugh…

Advertisements

Filters in pgAdmin

I use PostgreSQL in most of my Django projects. PostgreSQL has a tool called pgAdmin that is invaluable for debugging. pgAdmin allows the user to manually enter filters. The problem is the filter does not naturally handle column names that are capitalized, while Django often creates column names that are capitalized. What to do?

The solution is to put the column name in double quotes:

Job='xyz'    - is Not valid
"Job"='xyz'   - is valid