Basic postgresql server setup

So many tool require databases. If you are a web administrator or a company with large stores of information, then you know the importance of databases.

One of the most oft-used databases available is MySQL. But that is not the only player on the court. Another cross platform object-relational database management tool is PostgreSQL.

Many people refer to PostgreSQL as the Oracle of the open source world. That is because PostgreSQL is dense with features but not as fast as MySQL. And where MySQL is a simple to use database management tool, PostgreSQL is often seen as overly complicated.

Of course there are variations on that opinion. But that is neither here nor there. The purpose of this tutorial is to help you get a PostgreSQL server up and running quickly and easily.

To make this simple we will make this a part of our Ubuntu Server series, so all you have to do is have your Ubuntu Server up and running (see my article “Installing Ubuntu Server 9.04” to get started.)

Once you have that server up and running you are ready to get your PostgreSQL server up.


Installation
The first thing you need to do is to install the necessary software. Since this is Ubuntu, it’s quite easy. Open up a terminal window and issue the command:


# sudo apt-get install postgresql


Once the software is installed you are ready to set it all up.


Change the default user password
One of the first steps you want to take is to change the default password for the user postgres. Sine we are using Ubuntu you will have to use the sudo command to change to the postgres user like so:


# sudo su – postgres

You will have to enter your sudo password after which you will now be issuing commands as the user postgres.

The next step is to gain access to the postgresql command prompt with the command:


# psql

Your new command prompt will look like:


postgres=#

NOTE: The only user that can open the PostgreSQL prompt without defining a database to work with is the user postgres. Other users would have to gain access to the command prompt with a command like:
psql DB_NAME

Where DB_NAME is the name of an existing database.
Changing the password is as simple as issuing the command:

\password postgres

You will then be asked to enter a password and then verify that password.
Your default password has not been changed. You can exit from the PostgreSQL prompt by issuing the command:


\q


Create a database
Now, while still logged in as the postgres user, let’s create a database. For this you do not have to be logged into the PostgreSQL command prompt. Instead just issue the command:


createdb testdb

Where testdb is the name of the database you want to create. To check to make sure that database was created go back to the PostgreSQL command prompt (remember, the command psql) and enter:


\l

You should see a listing for your new database like:


testdb | postgres | UTF8 | en_US.UTF-8  | en_US.UTF-8

Once again, log out of the PostgreSQL command prompt with the command:


\q


Create a user
By default, the only user that can connect to a database is the postgres user. This will be of no help when you need to connect with another user.

To create a new user (that can connect to databases) you would issue the command (as the user postgres):


createuser –superuser USERNAME

Where USERNAME is the name of the user you want to create.


Final thoughts
Now you should have a basic PostgreSQL installation with a test database and a user, besides postgres, that can work with the tools.

Next time we work with PostgreSQL we’ll discuss more challenging issues with this outstanding database tool.