[Courses] [Postgresql] Installing PostgreSQL and Assignments

Sun Nov 25 23:21:49 UTC 2007

[A note: the syntax in this post is Dokuwiki. If it gets annoying, let 
me know. It's just easier since I write everything in Docuwiki these 
days. And, if you use Dokuwiki, you can just copy and paste. :-)]

//**Installing PostgreSQL**//

PostgreSQL can be installed on many platforms. I'll first outline, in a 
fair bit of detail, how to install PG on Linux, both using packages, as 
well as compiling from source. I'll also touch on installing PG in other 
platforms, enough so you should theoretically be able to do it (I have 
to admit I haven't tried it.)

The primary goal of this week is to get a working Postgresql 
installation that you can use. It's good if you can install this locally 
on your primary desktop or laptop first, then once you've gotten used to 
it, and we've talked about security issues, etc., then, if that's part 
of your project, you can install it on a production (or staging) server. 
Of course, if you've done this sort of thing lots of times, and are 
really familiar with systems and database admin, by all means, go ahead 
and do what you want.

//Linux//

  If you want to install PG easily, you can just use the package manager 
for your distribution. For most distributions, there are several 
packages   to install. The core three are generally called 
postgresql-version, postgresql-client, and postgresql-common. Here's the 
list for Ubuntu: 
http://packages.ubuntu.com/cgi-bin/search_packages.pl?searchon=names&subword=1&version=feisty&release=all&keywords=postgresql&sourceid=mozilla-search

For Ubuntu (or Debian):

   mpm at chicago:~$ sudo apt-get install postgresql-8.2
   Reading package lists... Done
   Building dependency tree
   Reading state information... Done
   The following extra packages will be installed:
     postgresql-client-8.2 postgresql-client-common postgresql-common
   Suggested packages:
    postgresql-doc-8.2
   The following NEW packages will be installed:
    postgresql-8.2 postgresql-client-8.2 postgresql-client-common 
postgresql-common
   0 upgraded, 4 newly installed, 0 to remove and 23 not upgraded.
   Need to get 4550kB of archives.
   After unpacking 22.9MB of additional disk space will be used.

So this picks up the basics that you need - postgresql-8.2 which is the 
server, plus the client and common files. The documentation is also useful.

On Fedora, you should be able to do the following:

    yum install postgresql-server

Other distributions (like Gentoo, or SUSE) will have their own 
postgresql packages. Find out what they are, and it should be pretty 
straightforward to install them. Ask the list if you run into trouble.

**Installing from source**

If you have one of those distros/platforms where you install from 
source, or you'd just like to do that, it's fairly straightforward, and 
I'll outline some of the options. The advantage, of course, of compiling 
from source is that you have granular control over your postgresql 
installation, and can choose many different versions (including the most 
recent beta). The downside is that it's not part of your package 
management system, so it's not as easy to upgrade.

Go to the www.postgresql.org, and find the source downloads 
([[http://ftp7.us.postgresql.org/pub/postgresql/source/|here's one 
mirror]]) Choose a version (I'm trying the beta out) and download it.

Once you download and expand the package, you'll see a file called 
"INSTALL". Have a look at that file. There are TONS of possible options. 
I'll tell you about a few. First, the simple version:

  ./configure
  gmake
  su
  gmake install
  adduser postgres
  mkdir /usr/local/pgsql/data
  chown postgres /usr/local/pgsql/data
  su - postgres
  /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
  /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
  /usr/local/pgsql/bin/createdb test
  /usr/local/pgsql/bin/psql test

So, first, configure, then "make" using gmake - this is the compile 
step. Then install - this puts all of the Then you need to add a new 
user, called postgres, who will be the admin user for the server. Create 
a directory for the data that is owned by the postgres user. Then 
initiate the database system (which creates the system tables). Start up 
PG, create a database called 'test' and then use the client to interact 
with that database.

The other thing you need to do is configure PG to startup automatically 
from boot (which basically means adding a startup script to /etc/init.d)

//(A note: installing using package managers will generally have done 
all of this for you).//

The options on the ./configure step (syntax would be ./configure 
--with-option1 --with-option2) include changing the destination 
directory, the directories on the libraries, installing perl, python or 
tcl support (full installs of these have to already be present), adding 
PAM, LDAP and/or SSL support, etc. Most Linux distributions have 
packages to give these, but for some reason, you might want to install 
them yourself (especially if you have an unusual setup.)

Make sure to carefully read the INSTALL file - that should take care of 
any snags.

//Installing PG on a Macintosh//

The easiest way to go about it is to use 
[[http://www.finkproject.org/index.php?phpLang=en|Fink]] which is the 
project that brings open source packages to the MacOS (they are compiled 
and configured for the mac). It's modeled after the Debian package 
manager. Once you install Fink, you can then just:

   fink install postgresql82

You can also look for other PG related packages.

You can also use [[http://www.macports.org/|MacPorts]]. I've not tried 
it, but it is another avenue. MacPorts work more like Portage - they 
aren't compiled, but you compile them.

//Installing PG on Windows//

In the downloads region of www.postgresql.org, there will be a directory 
labeled 'win32'. I used "postgresql-8.2.5-1.zip" - but there does exist 
a file with the label "binaries no installer" - which I guess you could 
try.

//**Assignment for the Week**//

   * Install Postgresql on your platform of choice. If you have more 
than one available, install on as many as you'd like
   * If you took the easy road (installing via a package manager) 
download and install the PG Beta (8.3) in /usr/local/src if you want to.
   * Open the client as the postgres user:

     $ sudo su - postgres (if on Ubuntu - otherwise become the postgres 
user to create a database)
     $ createdb test
     $ psql test

   * Have a look around at the commands (much more on this next week!)
   * Tell us all how it went? Any snags? Did you install it on a 
different platform (like BSD for instance?)

-- 
Michelle Murrain
Coordinator, Nonprofit Open Source Initiative
http://nosi.net

Skype: pearlbear
AIM: pearlbear0