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.

3 comments
rmerchberger
rmerchberger

I'm not saying "MySQL is bad" but early on (read: over 10 years ago) it was pretty much "good for websites" and not much else. It didn't have transactions, and could crash under high load... but it was *fast*. Gosh-darned fast. PostgreSQL was _much_ slower at the time, but bulletproof. (this back when it was MySQL 3.x vs. PostgreSQL 6.x). Back then, almost everything I saw that was "Enterprise" quality was based on PostgreSQL, but quick websites (emphasis on both quick and websites) were MySQL. My first few applications that required SQL, needed bulletproof over speed. (One of the canned packages I used didn't include MySQL as an option!) Therefore, I started with PostgreSQL and didn't really use MySQL until a couple years later (when dealing with a CMS install that required plugins that were MySQL-only). MySQL 4 brought transactions as an option (which were still rather kinda painful...) and improved the high load issues... and PostgreSQL 7 brought speed. A lot of comparisons of the time put them nearly neck-n-neck. Unforch, as MySQL was already the established favorite of web hackers everywhere, almost everything web-based (e.g. CMS systems and plugins, PHP, etc.) was generally MySQL centric, and PostgreSQL was quite often considered an afterthought. Nowadays, the reason I stick with PostgreSQL is not technical prowess, but the fact that Oracle owns MySQL now. Now there's forking (MariaDB) and other political issues that I just don't want to deal with. I'm normally not a "conspiracy-theorist" type of individual, but Oracle just scares me. ;-) Anywho, that's my take... Laterz, "Merch"

Editor's Picks