Backing up the database

/usr/bin/pg_dump -h localhost -p 5432 -U timj -F c -v -f "/home/timj/db/backup_08_05_2008.backup" cddb

-h = host
-p = port
-U = database user
-F c = Format suitable for pg_restore
-v = verbose
-f = file to back up to

Could just say

pg_dump -U timj -F c -f "/home/timj/db/backup_08_05_2008.backup" cddb

CD Table with foreign key contraints!

CREATE TABLE cdtable
(
pubnum int4 NOT NULL,
artnum int4 NOT NULL,
cdtitle varchar(50) NOT NULL,
datepublished date,
CONSTRAINT artnum FOREIGN KEY (artnum) REFERENCES artist (artnum) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT pubnum FOREIGN KEY (pubnum) REFERENCES publisher (pubnum) ON UPDATE NO ACTION ON DELETE NO ACTION
) WITHOUT OIDS
TABLESPACE ts_cddb_1;

The Artist Table

Creating the Artist Table

CREATE TABLE artist (
artnum SERIAL,
artname VARCHAR(50),
CONSTRAINT uc_artist UNIQUE (artnum)
);

cddb=> insert into artist (artname) values ('Phatfish');
INSERT 0 1
cddb=> insert into artist (artname) values ('Bethany Dillon');
INSERT 0 1
cddb=> select * from artist;
artnum | artname
--------+----------------
1 | Phatfish
2 | Bethany Dillon
(2 rows)

cddb=>

The Publisher Table

Create a table with a serial key.

CREATE TABLE publisher (
pubnum SERIAL,
pubname VARCHAR(20));

ALTER TABLE publisher ADD CONSTRAINT uc_pub UNIQUE(pubnum);

cddb=> insert into publisher (pubname) values ('EMI');
INSERT 0 1
cddb=> insert into publisher (pubname) values ('SONY');
INSERT 0 1
cddb=> select * from publisher;
pubnum | pubname
--------+---------
1 | EMI
2 | SONY
(2 rows)

cddb=>

First Steps

I have set a unix password for postgres, and started /etc/init.d/postgresql

su - postgres
made a directory called: '/var/lib/pgsql/cddb_data' owned by postgres

run psql

CREATE USER timj WITH PASSWORD 'xxxxxxxx' ;
CREATE TABLESPACE ts_cddb_1 OWNER timj LOCATION '/var/lib/pgsql/cddb_data';
CREATE DATABASE cddb OWNER timj TABLESPACE ts_cddb_1;

This gives me all I need to pgAdminIII, and I have completed objectives 1, 2 and 3!