Backup and Recovery Part 2

To Perform a simple recovery of a single database table from your previous backup.

Perform the backup!
timj@dell-desktop:~$ pg_dump -i -h localhost -U timj -F c -b -v -f /var/tmp/mybackup testdb
pg_dump: reading schemas
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined operator families
pg_dump: reading user-defined conversions
pg_dump: reading user-defined tables
pg_dump: reading table inheritance information
pg_dump: reading rewrite rules
pg_dump: reading type casts
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: finding the columns and types of table "numbers"
pg_dump: finding the columns and types of table "myuser"
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = off
pg_dump: saving database definition
pg_dump: dumping contents of table myuser
pg_dump: dumping contents of table numbers
timj@dell-desktop:~$ ls -trl /var/tmp/mybackup
-rw-r--r-- 1 timj timj 2261 2008-10-24 17:11 /var/tmp/mybackup
timj@dell-desktop:~$

At this stage we lost the 'numbers' table.
drop table number;

So we need to recover it

timj@dell-desktop:~$ pg_restore -c -h localhost -U timj -v /var/tmp/mybackup -d testdb -t numbers
pg_restore: connecting to database for restore
pg_restore: dropping TABLE numbers
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1466; 1259 24589 TABLE numbers timj
pg_restore: [archiver (db)] could not execute query: ERROR: table "numbers" does not exist
Command was:
DROP TABLE public.numbers;
pg_restore: creating TABLE numbers
pg_restore: restoring data for table "numbers"
pg_restore: setting owner and privileges for TABLE numbers
WARNING: errors ignored on restore: 1
timj@dell-desktop:~$

Accessing a database via Perl

Here's a small program I wrote to access a Postgresql database.

#!/usr/bin/perl
use DBI;
$dbh1 = DBI->connect( "dbi:Pg:dbname=testdb@localhost","", "", { RaiseError => 1, AutoCommit => 0 })
|| die "Database connection not made: $DBI::errstr";

my $sql = "select vendor, country, freephone, payphone from numbers ";
my $sth = $dbh1->prepare($sql);

$sth->execute();

my( $vendor, $country, $freephone, $payphone );
$sth->bind_columns( \$vendor, \$country, \$freephone, \$payphone );

print "Vendor\tCountry\tFreephone\tPayphone\n";
while( $sth->fetch() ) {
print "$vendor\t$country\t$freephone\t$payphone\n";
}

my $sql = "select name, vendor, leader, participant from myuser ";
my $sth = $dbh1->prepare($sql);

$sth->execute();

my( $name, $vendor, $leader, $participant );
$sth->bind_columns( \$name, \$vendor, \$leader, \$participant );

print "\nName\tVendor\tLeader\tParticipant\n";
while( $sth->fetch() ) {
print "$name\t$vendor\t$leader\t$participant\n";
}

$dbh1->disconnect() if($dbh1);

The output looks like this:

timj@dell-desktop:~/MySync/db$ ./plsql_phones1.pl
Vendor Country Freephone Payphone
Verizon UK 44-20-7075-3246 0808-238-6025
Verizon France 33-1-70-70-74-20 080-563-9647
Verizon USA 1-203-418-3122 866-692-3163
Verizon India 000-800-852-1266

Name Vendor Leader Participant
Tim Verizon 1234567 1234567
timj@dell-desktop:~/MySync/db$

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

Reworking the CD table

The CD table works, but needs a unique key to link to the tracks on the CD.

CREATE TABLE cdtable
(
cdnum serial NOT NULL,
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;


insert into cdtable (artnum, pubnum, cdtitle, datepublished)
values (1,3,'Heavenbound','02apr2002');

My First Join In Postgresql

cddb=> select a.artname, p.pubname, c.cdtitle
cddb-> from artist a, publisher p, cdtable c
cddb-> where a.artnum = c.artnum
cddb-> and p.pubnum = c.pubnum;
artname | pubname | cdtitle
----------+-----------------+-------------
Phatfish | Authentic Media | Heavenbound
(1 row)

cddb=>

Exactly the same as Oracle really!

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!

Learning Postgresql

Having previously worked as an Oracle developer and an Oracle DBA, but fallen away from the hand's on technical stuff, I have decided to teach myself Postgresql. I have decided that the best way to do this is to set up a series of tacks to achieve, and to blog my attempts to achieve them. I appreciate that this may not be of wide interest to anyone else, but it will at least keep the details in one place.

I expect that the targets will change as I find out more, so I expect to have to restate the targets as I go along. Hopefully I will be able to spend 30 minutes a day on this.

Tim