Date [date]

frightenedfroggeryΔιαχείριση Δεδομένων

16 Δεκ 2012 (πριν από 4 χρόνια και 8 μήνες)

235 εμφανίσεις

 
 
An Introduction to PostgreSQL

Roger Leigh

rleigh@debian.org
 
 
Overview

What is a relational database

Feature comparison with other databases

Installation and configuration

Using the database with SQL

Maintenance

Procedural languages
 
 
What is a relational database?

Data is organised in tables

Rows of records, comprised of fields

Relations between tables enforce data
integrity

Indexes are used for performance and to
enforce uniqueness

Uses relational algebra for set operations

union, intersection, cartesian product
 
 
A simple table (relation)
Name [text]
Date [date]
Order number [integer]
1
Leigh
12/10/05
6556
2
Johnson
14/10/05
7432
3
Matthews
19/10/05
8000
Columns (relation variable=domain+name)
Rows
(n-tuples)
Field
(attribute value)
 
 
History

1977-1985

Ingres
(Michael Stonebraker,
CS professor and Informix CTO)

1986-1994
POSTGRES
(object-relational
research and development)

1995
Postgres95
(SQL query
language)

1996-2005
PostgreSQL
(Source opened,
and much development)
 
 
Alternatives

Free Software

InterBase (MPL)

PostgreSQL
(BSD)

MySQL (GPL)

SAP DB (GPL)

SQLite (PD)

Proprietary

Adabas D

DB2

Ingres

Informix

MS SQL Server
(Windows
only)

Oracle

Sybase
 
 
Why use PostgreSQL?

ANSI SQL 89, 92 and 99 syntax

Transactions ACID compliance

Referential Integrity

Multi-version concurrency control (MVCC)

Triggers

Data types, operators and functions

Procedural languages

Security (SQL
ROLE
s, secure from install
)
 
 
Why not use PostgreSQL

Too big and complex to administer

CSV

GDBM/NDBM

BerkeleyDB

SQLite
 
 
Documentation
(see on-line docs)
 
 
Installing from packages

RedHat/Fedora:

yum -y install postgresql
postgresql-server

/sbin/chkconfig postgresql on

/sbin/service postgresql start

Debian

apt-get install postgresql-8.1
postgresql-doc-8.1

Automatic setup and upgrades
 
 
Installing from source

tar xfvj postgresql-8.1.0.tar.bz2

cd postgresql-8.1.0

./configure --prefix=
/path/to/install

make

su -c 'make install'

initdb -D UTF8
/path/to/database

postmaster -D
/path/to/database
 
 
Installing from source (2)

Create a
postgres
user

Create a database owned by
postgres

Add an init script to start and stop the
postmaster
 
 
Additional setup

Create new users (roles)

createuser

CREATE ROLE

Create new databases

createdb

CREATE DATABASE

Assign access permissions

GRANT
and
REVOKE

See
setup.sql
 
 
Configuration files

postgresql.conf

General postmaster server configuration

pg_hba.conf

Host-Based Access controls

pg_ident.conf

Identity mappings
 
 
Migration from other DBs

The major hurdle is working around SQL
incomptibilities and data type
differences

Oracle is mostly compatible with
Postgres

MySQL is rather more different (non-
standard)

For the simple case, a dump and reload
will suffice.
 
 
Connection
Connection is via a UNIX socket or TCP/IP
(v4 or v6)
socket
tcp4
tcp4
tcp6
socket
socket
postmaster
(server)
 
 
SQL


Structured Query Language”

It's supposed to resemble English, so
even PHBs can understand it...

Most of ISO SQL 92 and 99 supported

Most databases are not 100%
compatible, each having their own
extensions and little differences.
 
 
Data types

String types

char, varchar, text

Numeric types

integer, numeric

Date types

date, timestamp [with time zone]

Geometric Types

Custom Types and Domains
 
 
SQL Examples
CREATE * / DROP *
INSERT
UPDATE
DELETE
SELECT
See
bookings.sql
The example database demonstrates
basic SQL usage with a very simple
restaurant table booking system.
 
 
Database structure
Customers
id
name
telephone
discount
Staff
id
name
Tables
id
near_window
seats
Bookings
id
customer_id
staff_id
table_id
start_time
end_time
guests
Orders
booking_id
menu_id
quantity
Menu
id
item
description
item_type
price
Menu Item Types
id
name
Key
Primary Key
Foreign Key
 
 
Joining Tables
1 2 3
3 4 5
1 2 3
3 4 5
3
a
=
{
1,2,3
}
b
=
{
3,4,5
}
Intersection
1 2 3
3 4 5
3
Union
a

b
=
{
3
}
a

b
=
{
1,2,3,4,5
}
See
set.sql
 
 
3 4 5
3 4 5
1 2 3
1 2 3
1 2 3
3 4 5
1 2 3
a

b
=
{
1,2
}
b

a
=
{
4,5
}
Difference
SQL Difference
3 4 5
 
 
Procedural Languages

PL/pgSQL

PL/Tcl

PL/Perl

PL/Python

PL/Java (extension)

C

Support for other languages may also be
added by adding a call handler.
 
 
Backups

Copy the cluster (non-portable, version-
dependent and required shutting down
the postmaster)

pg_dump
and
pg_dumpall (on-line)

Clustering/mirroring using replication
tools
 
 
Upgrades

Dump the database, upgrade and reload
the dumped data

Run both the old and new versions and
mirror the data using replication (zero
downtime!)
 
 
Graphical frontends

pgAccess

pgAdmin

OpenOffice

Any ODBC client, e.g. MS Access
 
 
Transactions

BEGIN and COMMIT

Isolation levels

Nested transactions (savepoints)