Home

Projects

Tutorials

Analysis

Remote Postgres Login

by Matthew Barlowe


Ok if you've been following along with the other tutorials you have seen me use the psql command to login into your database that you've setup on your computer. But that's not all psql is use for, and in fact it has many diverse properties that you can use to pass SQL queries to the database without ever logging into the server itself. But I won't get into that with this tutorial, instead I'm going to focus on using psql to login into a remote Postgres server

A couple notes here first about OS differences. If you are on a Mac which I assume most people will be if you're following these tutorials, the psql will work anywhere at the command line. Windows though is a bit different. If you have your cmd window up and ready to log into your Postgres server, you will need to change to the bin directory located in the directory of your Postgres to run the psql command.

Login Syntax


psql --host=<hostname> --port=5432 --username=<username> --dbname=<dbname>
      

Ok this is a sample command you would type in the command line if you were going to login in to a remote Postgres server. Let's break it down piece by piece. --host=<hostname>: This is the address of the postgres server. It could be an IP address, or a url, or it's hosted on amazon it will have something that ends like this rds.amazonaws.com. This what tells psql where to go and connect so you can login to the Postgres server.

--port=5432: This is the port that psql will attempt to connect. This is the default port for Postgres and will almost always be the same no matter what Postgres server you are connecting to and you shouldn't have to change this unless otherwise specified.

--username=<username>: This one is pretty self explanatory. You will place your user name where <username> goes and it will be the username given to you by your database administrator

--dbname=<dbname>: This will be the name of the database you want to connect to and you will need to know this before you connect because you can't connect to a Postgres server without specifying a database. This should also be given to you by your database administrator as well.

Ok that's basically it for using psql to log into a Postgres server. But what if you don't want to type all that in everytime you want to log into a Postgres server? Well glad you asked because I'll briefly touch on how you can do that.

Aliases

Ok I talked about aliases in some other SQL tutorials and this is similar but not quite the same. The alias I'm going to talk about right now is an alias for a command for the Bash shell in Mac OS X. This alias is a shortcut that you create so you don't have to type in long complicated commands. To create a shortcut we will be editing the .bash_profile file with a text editor.

The '.' before a file in a Unix/Linux file structure means that it is a hidden file and won't normally show up when you do a standard ls command. The .bash_profile file controls how the Bash shell operates for you. I discussed editing it briefly in the Git Tutorial so if you forgot how to edit it you can refresh your memory there. But to create our alias you'll need to open the file and put this line inside it:


alias dblogin='psql --host=<host_name> --port=5432 --username=<username> --dbname=<dbname>'
      

So after you add that all on one line to your .bash_profile file and source ~/.bash_profile to effect the changes to your shell all you would need to do is type dblogin and the Bash shell would automatically log you into your Postgres server with the selected database. And that's all it takes to create the alias. Aliases can be used for any series of Bash commands not just the psql so if you run into another command that requires a lot of inputs, or even a chain of different commands now you know how to set up an alias to make it easier to type into the command line