Follow Us

We use cookies to provide you with a better experience. If you continue to use this site, we'll assume you're happy with this. Alternatively, click here to find out how to manage these cookies

hide cookie message

Learn the basics of PostgreSQL

Get started with the world's most advanced open source database

Article comments

PostgreSQL is a first-rate, enterprise-worthy open source RDBMS (relational database management system) that compares very favourably to high-priced closed source commercial databases.

Databases are complex, tricky beasts full of pitfalls. In this crash course, we'll get a new PostgreSQL database up and running with elegant ease, and learn important fundamentals.

If you're a database novice, then give yourself plenty of time to learn your way around. PostgreSQL is a great database for beginners because it's well documented and aims to adhere to standards. Even better, everything is discoverable, nothing is hidden, not even the source code, so you can develop as complete an understanding of it as you want.

Planning

The most important part of administering any database is preparation, in planning and design and in learning best practices. A good requirements analysis will help you decide what data to store, how to organise it and what business rules to incorporate. You'll need to figure out where your business logic goes: in the database, in middleware or applications?

You may not have the luxury of a clean, fresh new installation, but must instead grapple with a migration from a different database. These are giant topics for another day. Fortunately there are plenty of good resources online, starting with the excellent PostgreSQL manuals and Wiki.

Installation

We'll use three things in this crash course: PostgreSQL, its built-in interactive command shell psql, and the excellent pgAdmin3 graphical administration and development tool. Linux users will find PostgreSQL and pgAdmin3 in the repositories of their favourite Linux distributions, and there are downloads on PostgreSQL.org for Linux, FreeBSD, Mac OS X, Solaris and Windows.

There are one-click installers for OS X and Windows, and they include pgAdmin3. Any of these operating systems are fine for testing and learning. For production use, I recommend a Linux or Unix server, because they're reliable, efficient and secure.

Linux and FreeBSD split PostgreSQL into multiple packages. You want both the server and client. For example, on Debian the metapackage postgresql installs all of these packages:

# apt-get install postgresql

postgresql postgresql-9.0 postgresql-client-9.0

postgresql-client-common postgresql-common

See the detailed installation guides on the PostgresSQL wiki for more information for all platforms.

The downloads page also includes some live CDs which make it dead easy to set up a test server. Simply boot the CD and go to work. For this article, I used a Debian Wheezy (Testing) system running PostgreSQL 9.0.4, the current stable release.

Creating and destroying a new PostgreSQL database

You'll have to jump through a number of startup and user account hurdles to get started. First, verify that your PostgreSQL server has started by opening psql, its interactive command shell:

$ psql

psql: could not connect to server: No such file or directory

Oops, PostgreSQL is not running. Go back to the appropriate installation guide to see how to start it on your system. On Debian, it starts automatically after installation, so the above command produces the following result:

carla@xena:~$ psql

psql: FATAL: role "carla" does not exist

Well, excuse me all to heck! But this really isn't a big deal, because PostgreSQL creates a default postgres superuser with no password. So you have to change to this user, and then create a new user account that you will use to administer the database. On Linux and Unix you need to gain rootly powers, like this:

carla@xena:~$ su

root@xena:/home/carla# su postgres

postgres@xena:/home/carla$

There, now we can get some real work done! Let's create a carla superuser for PostgreSQL:

postgres@xena:/home/carla$ createuser carla

could not change directory to "/home/carla"

Shall the new role be a superuser? (y/n) y

These PostgreSQL roles, postgres and carla, are PostgreSQL user accounts that are independent of system accounts. A role can be a single user or a group of users. Roles can own database objects, such as tables, and can assign privileges to access those tables to other roles. Use the dropuser command to delete a role:

$ dropuser carla

Now, let's create a brand new database:

$ createdb testdb

No news is good news. If this is successful, there will be no feedback. You'll see a message only if something went wrong. Now let's destroy our new database:

$ dropdb testdb

Again, silence equals success.


Share:

More from Techworld

More relevant IT news

Comments

ZK said: This is a really nice tutorial Thanks




Send to a friend

Email this article to a friend or colleague:

PLEASE NOTE: Your name is used only to let the recipient know who sent the story, and in case of transmission error. Both your name and the recipient's name and address will not be used for any other purpose.

Techworld White Papers

Choose – and Choose Wisely – the Right MSP for Your SMB

End users need a technology partner that provides transparency, enables productivity, delivers...

Download Whitepaper

10 Effective Habits of Indispensable IT Departments

It’s no secret that responsibilities are growing while budgets continue to shrink. Download this...

Download Whitepaper

Gartner Magic Quadrant for Enterprise Information Archiving

Enterprise information archiving is contributing to organisational needs for e-discovery and...

Download Whitepaper

Advancing the state of virtualised backups

Dell Software’s vRanger is a veteran of the virtualisation specific backup market. It was the...

Download Whitepaper

Techworld UK - Technology - Business

Innovation, productivity, agility and profit

Watch this on demand webinar which explores IT innovation, managed print services and business agility.

Techworld Mobile Site

Access Techworld's content on the move

Get the latest news, product reviews and downloads on your mobile device with Techworld's mobile site.

Find out more...

From Wow to How : Making mobile and cloud work for you

On demand Biztech Briefing - Learn how to effectively deliver mobile work styles and cloud services together.

Watch now...

Site Map

* *