Postgres Remote Login
Remote Postgres Login
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