id="info"

Data Management

DIY: A PostgreSQL database server setup anyone can handle

If want to use PostgreSQL but you're unsure where to begin in terms of installation, setup, and management, this primer by Jack Wallen guides you through the necessary steps.

When it comes to databases, I'm a fan of MySQL. The open source database can handle just about any load you want to throw at it, and it has lots of powerful tools that can be used to manage it.

The other popular open source database is PostgreSQL, which is cross-platform and is used by numerous applications. Although PostgreSQL is often seen as being as powerful as MySQL, it doesn't have nearly the number of available tools to make setup and management as easy as its competition. So I've written this handy PostgreSQL primer on how to get your database server up and running and ready to use. (Although PostgreSQL is cross-platform, I demonstrate the installation and setup on a Ubuntu 11.04 machine because it's my platform of choice. The translation to other platforms should be simple.)

Step 1: Install PostgreSQL

Here are the installation steps on Ubuntu (this installation will also work on any Debian-based distribution):

  1. Open a terminal window.
  2. Issue the command sudo apt-get install postgresql.
  3. Type the sudo password necessary to give you admin rights and hit Enter.
  4. Allow apt to pick up any necessary dependencies.

Once the installation is complete, it's time to set this baby up.

Step 2: Change the default user password

Caution: If you don't follow this step, you will not be able to add databases and administer PostgreSQL, and the database will not be secure.

Here's how to change the password for the default user. The user in question is postgres, and the password is changed like so:

  1. Open a terminal window.
  2. Issue the command sudo passwd postgres.
  3. Type (and confirm) that password to be used for this user.

The postgres user will be the only user on your system that can open the PostgreSQL prompt without defining a database, which means postgres is the only user who can administer PostgreSQL. To test this, change to the postgres user with the command su - postgres and then enter the command psql. You should now be at the Postgres prompt, which looks like:

postgres=#

All other users have to gain access to the prompt like so:

psql DB_NAME

where DB_NAME is the name of an existing database.

Step 3: Change the Postgres admin password

The administrator password must be set; otherwise, external applications will not be able to communicate with the databases.

To change the admin password for Postgres, follow these steps:

  1. Open a terminal window.
  2. Change to the postgres user.
  3. Log in to the postgres prompt.
  4. Issue the command \password postgres.
  5. Enter (and verify) the new password.
  6. Exit the prompt with the command \q.

Step 4: Create your first database

This is where it gets exciting. Let's create a new database called testdb. To do this, follow these steps:

  1. Open a terminal window.
  2. Change to the postgres user.
  3. Log in to the postgres prompt.
  4. Issue the command CREATE DATABASE testdb;.

To make sure the database was successfully created, you should issue the command (from the postgres command prompt):

\l

You should see output similar to:

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

Congratulations on creating your first PostgreSQL database!

Step 5: Create users

Out of the box, the only user on your system who can connect to the databases is the postgres user. There may be instances when third-party software needs to connect to the database server as a user other than the admin user. In this case, that user will have to be created and given permission to use the database. Here's how you can create users:

  1. Open a terminal window.
  2. Change to the postgres user.
  3. Log in to the postgres command prompt.
  4. Issue the command CREATE USER username; (where username is the name of the user you want to create).

You can use the command \du to make sure the user was created. As you will see in the output, the user has no privileges. Let's give the user privileges on the newly created database. Here's how:

  1. Open a terminal window.
  2. Change to the postgres user.
  3. Log in to the postgres prompt (using the testdb database) with the command psql testdb.
  4. Issue the command GRANT ALL PRIVILEGES ON DATABASE testdb TO user; (where user is the actual username to be given privileges).

Now the newly created user has access to the database.

Conclusion

You now have a working (albeit empty) PostgreSQL database server ready to rock. It's powerful, it's flexible, and best of all it's free. How's that for DIY?

About

Jack Wallen is an award-winning writer for TechRepublic and Linux.com. He’s an avid promoter of open source and the voice of The Android Expert. For more news about Jack Wallen, visit his website getjackd.net.

Editor's Picks