PostgreSQL is a first-rate, enterprise-worthy open source RDBMS (relational database management system) that compares very favorably to high-priced closed-source commercial databases. Databases are complex, tricksy beasts full of pitfalls. In this two-part crash course, we'll get a new PostgreSQL database up and running with elegant ease, and learn important fundamentals.
['NewSQL' could combine the best of SQL and NoSQL and NoSQL offers users scalability, flexibility, speed]
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 organize 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 favorite 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
postgresqlinstalls all of these packages:
# <b>apt-get install postgresql</b>
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.