PostgreSQL Notes

I volunteered to help out with a couple of open source Ruby projects. One of them is using PostgreSQL. I had some trouble getting it working.

One of the things I did not like about it is that it looks like I can only become the postgres user by becoming root first. I suppose I could reset the password, but I think I am going to deal with it for the time being. I don’t know if changing the password will affect everything. I found this command from the Ubuntu site:

sudo -u postgres psql postgres

Unlike other databases, some of the database commands are done with backslashes followed by letters. To quit the database, you do not type “exit”. That would be silly. It’s “\q”. The meta-commands (as they are called) can be found here. The usual SQL commands can be found here.

I tried to create a database using the createdb command after I started psql because that’s how this page tells you to create a database. There are a lot of client commands that I guess you have to run outside of psql. I think that they should tell you in big letters that you should only read those pages if you think that Postgres is like any other database that you have used.

I also had trouble logging in both from the console and in the Rails app. First I had to change the /etc/postgresql/9.1/main/pg_hba.conf file since Debian and Debian-based distributions do some things differently. To log in at the command line, I read this page to change pg_hba.conf. I added this:

I also had to change the port from 5433 to 5432. I am not clear why Ubuntu changed it.

The guy who started the project said that PostgreSQL is the standard database on Heroku. That is a pretty good reason to learn it. I have heard that Postgres can be more difficult to learn and configure. We shall see.

Image from Wikimedia, assumed allowed under Fair Use. Image from the Vatican Virgil, a 5th century manuscript of poems by Virgil.

One Reply to “PostgreSQL Notes”

  1. http://www.postgresql.org/docs/9.1/static/tutorial-createdb.html – this page tells you to use createdb command, but in no way it suggests that it’s psql command. What’s more – it even shows you that in some cases you might need to prefix the command with path (/usr/local/pgsql/bin/createdb) which strongly (in my opinion) suggests that it’s shell tool, and not sql command.

    As for \q – it’s a convention. What’s more – you don’t have to run webbrowser to read about \metacommands, you can just type \?. And if you don’t know about \?, then please read what exactly is psql “saying” on start.

Comments are closed.