Sunday, April 3, 2011

PostgreSQL

Now lets get the database installed. PostgreSQL is a fantastic database and another company has built an "extension" for it called PostGIS that gives it an amazing amount of GIS functionality. This has become the standard database platform for the open source GIS community. Let's install, configure, and discuss PostgreSQL in this post. I'll add another blog entry regarding PostGIS shortly.

Install the database...(or postgresql-8.4 for version specific)
$ sudo apt-get install postgresql 
Configuration files are located at /etc/postgresql/8.4/main...
environment (variables used by postmaster)
pg_ctl.conf (database cluster specific options)
pg_hba.conf (controls access to the database)
pg_ident.conf (username mapping?)
postgresql.conf (general configuration file)
start.conf (assume it has something to do with starting up :) )
First let's enable TCP/IP connections since they are disabled by default by adding the following lines to /etc/postgresql/8.4/main/postgresql.conf...
#For local connection only
listen_addresses = 'localhost'
#For remote connections
#listen_addresses = '*'
Now we need to set a password for the PostgreSQL database user that is automatically created called postgres. There is also an operating system user account named postgres that is added specifically for running the PostgreSQL daemon. By default this OS user is actually set to "login disabled". So, the user is really used only for running PostgreSQL. Anyway let's go ahead and set the password for this database user account now...
$ sudo -u postgres psql template1
# ALTER USER postgres with encrypted password '<password>';
# \q
After configuring the password, edit the file /etc/postgresql/8.4/main/pg_hba.conf to use MD5 authentication with the postgres user. This will allow localhost access only and requires an encrypted password...
#For local connections only
local         all       postgres       127.0.0.1/32        md5
#For remote connections from a certain ip address
host          all       postgres       xxx.x.x.x/32        md5
Restart PostgreSQL using this command...
$ sudo /etc/init.d/postgresql-8.4 restart
Now let's connect to a database. Since the postgres OS user is not setup to allow login, you have to connect to the database as that user with sudo as you see below.
$ sudo -u postgres psql postgres
Frankly I think it is pretty confusing and takes a little while to get the hang of navigating PostgreSQL using the psql command, but it works great after a bit of a learning curve.

Now you should see the postgresql command prompt where you can perform SQL and interact with the database. Type \? or \help to get some information on how to work with this program. Most importantly, \q gets you back to the shell prompt...


I'll add some common PostgreSQL tasks cheatsheet below a bit later since the database should be running fine now.

...

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.