Postgres for Programmers

Database Administration

Creating a DB User

Before a user can access a Postgres database, they MUST be added as users of the Database. To create the first user, you'll need to become the postgres user. This user isn't allowed to log in, so you'll have to use sudo to become that user:

sudo su - postgres

Add your user to the database:

$ createuser jam
Shall the new user be allowed to create databases? (y/n) 
Shall the new user be allowed to create more new users? (y/n)
CREATE USER

Creating a Database

You need to create a database called mug to play with

createdb mug

Creating a Table

Here's an example of the SQL syntax to create a simple table:

create table Members (
  id            integer,
  inactive      boolean,
  name          char(30),
  emailaddr     char(30),
  PRIMARY KEY (id)
);

Slightly better example using a sequence:

create sequence Members_id_seq;

create table Members (
  id            integer DEFAULT nextval('Members_id_seq'),
  inactive      boolean DEFAULT false,
  name          char(30),
  emailaddr     char(30),
  PRIMARY KEY (id)
);

To run the SQL statements, save them to a file, and use the psql command:

psql mug <create_members.sql

Interacting with the database

You can use the psql utility to interact with the database.

psql mug

Welcome to psql 7.4.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

Some handy commands are:

• \d - Shows a list of relations (tables,sequences)
• \? - A list of psql internal commands
• \h - Help on SQL statements
• \q - Exit from the psql utility

Database Programming

Perl

Connect to the database

#!/usr/bin/perl -w

use strict;
use DBI;

my $dbh = DBI->connect("dbi:Pg:dbname=mug;host=localhost")
                                 or die "Unable to connect to database: $DBI::errstr\n";

Insert data into a table

Here's an example of a simple insert into a table:

my $query = "insert into Members ( name, emailaddr ) "
                  . "values ( 'John Doe', 'jd@gmail.com' );";

$dbh->do( $query );

Here's another example:

my $query = "insert into Members ( name, emailaddr ) values ( ?, ? ); ";

my $sth = $dbh->prepare( $query );

$sth->bind_param( 1, 'John Doe' );
$sth->bind_param( 2, 'jd@gmail.com' );
$sth->execute();

$sth->bind_param( 1, 'Jim Smith' );
$sth->bind_param( 2, 'jsmith@yahoo.com' );
$sth->execute();

$sth->bind_param( 1, 'Sally Brown' );
$sth->bind_param( 2, 'sallyb@netscape.com' );
$sth->execute();

To see what value was assigned to the id column, you can do this:

printf("member_id = %d\n", $dbh->selectrow_array( "select currval('members_id_seq');" ) );

Retrieve data from the table

#!/usr/bin/perl -w

use strict;
use DBI;

my $dbh = DBI->connect("dbi:Pg:dbname=mug;host=localhost")
                                 or die "Unable to connect to database: $DBI::errstr\n";

my $query = "select id, name, emailaddr from members order by name;";

my $sth = $dbh->prepare( $query );
$sth->execute();

while( my $row = $sth->fetchrow_hashref() ){
  #
  # Now, $row is a reference to a hash containing the fields for one row
  #
  printf("id: %d\n", $row->{id} );
  printf("  name: %s\n", $row->{name} );
  printf("  emailaddr: %s\n", $row->{emailaddr} );
  printf("\n");
}

$dbh->disconnect();

Shutting down the connection

When done with the database, you should disconnect from it:

$dbh->disconnect();

Python

More Database Administration

Backups

pg_dump and pg_restore

Security

Protecting your database with privileges

Upgrading the Database

Ok, a new version of Postgres is available, how do you upgrade it on your system, and make sure that your application is working properly with it?

Upgrading Applications

You've put lots of changes into your development version of your application, and there are database schema changes along with it. How do you roll those changes out to any production environments that you want to upgrade with your new code?

Advanced Topics

Views

Triggers

Stored Procedures

to top