Data Management

Tech Tip: Create and manage a PostgreSQL database

Here's how to create and manage a PostgreSQL database.

The two most popular open source databases are MySQL and PostgreSQL. Each database has its strengths and weaknesses. However, MySQL is much simpler to operate than PostgreSQL, so many people tend to use it rather than PostgreSQL.

Let's look at how to manage a PostgreSQL database. In this example, let's say you've stored your database data in /var/lib/pgsql/data. To start a PostgreSQL database with this data, use the following:

$ pg_ctl -D /var/lib/pgsql/data start

This starts the postmaster service. You must su to the postgres user (or whoever owns the /var/lib/pgsql/data directory and subsequent directories and files).

To stop the database, execute the following. (Again, execute the command as the postgres user.)

$ pg_ctl -D /var/lib/pgsql/data stop

To create a database as the postgres user, execute the following:

$ createdb database

This creates a database named "database." By default, it grants access solely to the postgres user or the user issuing the createdb command if he or she has access to the PostgreSQL database.

To create a user, execute the following as the postgres user:

$ createuser —createdb —no-adduser joe

This creates the user "joe" in the database and gives him the ability to create his own databases, but it doesn't grant the ability to add more users.

If you're familiar with the MySQL method of creating databases and users, you know that this process is quite different. With MySQL, you actually connect to the database and issue CREATE and GRANT commands in an SQL shell, but with PostgreSQL, you execute command-line tools to perform these types of operations.

You can still log into the database using the psql command-line tool and use a similar "shell" where you can fine-tune access permissions and such, but you create users and databases (and likewise remove them) from the command line.

Editor's Picks

Free Newsletters, In your Inbox