COMP9315-week1-lecture3

COMP9315 19T2 Week 1 Lecture 3_哔哩哔哩_bilibili

COMP9315 Introduction (unsw.edu.au)  --88/129

PostgreSQL 47/100
PostgreSQL is a full-featured open-source (O)RDBMS.

• provides a relational engine with:
  • efficient implementation of relational operations
  • transaction processing (concurrent access)
  • backup/recovery (from application/system failure)
  • novel query optimisation (genetic algorithm-based)
  • replication, JSON, extensible indexing, etc. etc.
• already supports several non-standard data types
• allows users to define their own data types
• supports most of the SQL3 standard

PostgreSQL Online 48/100
Web site: www.postgresql.org
Key developers: Bruce Momjian, Tom Lane, Marc Fournier, ...
Full list of developers: www.postgresql.org/developer/bios
Local copy of source code:
http://www.cse.unsw.edu.au/~cs9315/19T2/postgresql/src.tar.bz2

Index of /~cs9315/24T1/postgresql (unsw.edu.au)

Documentation is available via WebCMS menu

---------------------------------------------------------

User View of PostgreSQL 49/100
Users interact via SQL in a client process, e.g.
$ psql webcms
psql (11.3)
Type "help" for help.
webcms2=# select * from calendar;
id | course | evdate | event

or

$dbconn = pg_connect("dbname=webcms");
$result = pg_query($dbconn,"select * from calendar");
while ($tuple = pg_fetch_array($result))
{ ... $tuple["event"] ... }

----------------------------------------------------

PostgreSQL Functionality 50/100
PostgreSQL systems deal with various kinds of entities:
users ... who can access the system
groups ... groups of users, for role-based privileges
databases ... collections of schemas/tables/views/...
namespaces ... to uniquely identify objects (schema.table.attr)
tables ... collection of tuples (standard relational notion)
views ... "virtual" tables (can be made updatable)
functions ... operations on values from/in tables
triggers ... operations invoked in response to events
operators ... functions with infix syntax
aggregates ... operations over whole table columns
types ... user-defined data types (with own operations)
rules ... for query rewriting (used e.g. to implement views)
access methods ... efficient access to tuples in tables

----------------------------------------------------

... PostgreSQL Functionality 54/100
Uses multi-version concurrency control (MVCC)
multiple "versions" of the database exist together
a transaction sees the version that was valid at its start-time
readers don't block writers; writers don't block readers
this significantly reduces the need for locking
Disadvantages of this approach:
extra storage for old versions of tuples (vacuum fixes this)
PostgreSQL also provides locking to enforce critical concurrency

----------------------------------------------------

Installing PostgreSQL 57/100
PostgreSQL is available via the COMP9315 web site.
Provided as tar-file in ~cs9315/web/19T2/postgresql/
File: src.tar.bz2 is ~20MB **
Unpacked, source code + binaries is ~130MB **
If using on CSE, do not put it under your home directory
Place it under /srvr/YOU/ which has 500MB quota

----------------------------------------------------

Installing/Using PostgreSQL 59/100
Environment setup for running PostgreSQL in COMP9315:
# Must be "source"d from sh, bash, ksh, ...
# can be any directory
PGHOME=/home/jas/srvr/pgsql
# data does not need to be under $PGHOME
export PGDATA=$PGHOME/data
export PGHOST=$PGDATA
export PGPORT=5432
export PATH=$PGHOME/bin:$PATH
alias p0="$D/bin/pg_ctl stop"
alias p1="$D/bin/pg_ctl -l $PGDATA/log start"
Will probably work (with tweaks) on home laptop if Linux or MacOS

file:///Users/jas/srvr/apps/cs9315/19T2/lectures/week01/notes.html

Brief summary of installation:
$ tar xfj ..../postgresql/src.tar.bz2
# create a directory postgresql-11.3
$ source ~/your/environment/file
# set up environment variables
$ configure --prefix=$PGHOME
$ make
$ make install
$ initdb
# set up postgresql configuration ... done once?
$ edit postgresql.conf
$ pg_ctl start -l $PGDATA/log
# do some work with PostgreSQL databases
$ pg_ctl stop
On CSE machines, ~cs9315/bin/pgs can simplify some things

----------------------------------------------------

做作业的时候，改源码就可以了，不要再修改数据

Using PostgreSQL for Assignments 61/100
If changes don't modify storage structures ...
$ edit source code
$ pg_ctl stop
$ make
$ make install
$ pg_ctl start -l $PGDATA/log
# run tests, analyse results, ...
$ pg_ctl stop
In this case, existing databases will continue to work ok

----------------------------------------------------

存储结构改了，就要倒一次数据

... Using PostgreSQL for Assignments 62/100
If changes modify storage structures ...
$ edit source code
$ save a copy of postgresql.conf
$ pg_dump testdb > testdb.dump
$ pg_ctl stop
$ make
$ make install
$ rm -fr $PGDATA
$ initdb
$ restore postgresql.conf
$ pg_ctl start -l $PGDATA/log
$ createdb testdb
$ psql testdb -f testdb.dump
# run tests and analyse results
Old databases will not work with the new server

----------------------------------------------------

----------------------------------------------------

----------------------------------------------------

----------------------------------------------------

----------------------------------------------------