PostgreSQL PostgreSQL
PostgreSQL (UCB) Unix/Linux/Windows LC2002 Copyright(C)2002 Tatsuo Ishii 1
PostgreSQL API C, C++, Java, Perl, Tcl/Tk, PHP, Ruby LC2002 Copyright(C)2002 Tatsuo Ishii 2
PostgreSQL (SQL) PostgreSQL TCP/IP LC2002 Copyright(C)2002 Tatsuo Ishii 3
PostgreSQL (1) SQL92/99 SQL LC2002 Copyright(C)2002 Tatsuo Ishii 4
PostgreSQL (2) MVCC(Multi Version Concurrency Control) Oracle LC2002 Copyright(C)2002 Tatsuo Ishii 5
(Row Level Lock) LC2002 Copyright(C)2002 Tatsuo Ishii 6
MVCC(Multi Version Concurrency Control) LC2002 Copyright(C)2002 Tatsuo Ishii 7
PostgreSQL (3) Unicode(UTF-8) Unicode SJIS/EUC-JP Unicode Unicode LC2002 Copyright(C)2002 Tatsuo Ishii 8
PostgreSQL (4) -- -- 2000 1GB BLOB/CLOB 100-1000 LC2002 Copyright(C)2002 Tatsuo Ishii 9
PostgreSQL (5) SQL GUI LC2002 Copyright(C)2002 Tatsuo Ishii 10
Pgaccess LC2002 Copyright(C)2002 Tatsuo Ishii 11
PostgreSQL RPM LC2002 Copyright(C)2002 Tatsuo Ishii 12
http://www.postgresql.org postgresql-7.2.2.tar.gz LC2002 Copyright(C)2002 Tatsuo Ishii 13
120MB( 60MB 512MB (X 64MB GNU make gcc LC2002 Copyright(C)2002 Tatsuo Ishii 14
Apache+PHP Apache+PHP Apache+PHP Apache+PHP Apache+PHP Apache+PHP Apache+PHP Apache+PHP PostgreSQL PostgreSQL PostgreSQL PostgreSQL PostgreSQL PostgreSQL Unix TCP/IP TCP/IP TCP/IP LC2002 Copyright(C)2002 Tatsuo Ishii 15
.bashrc postgresql.conf/pg_hba.conf postmaster LC2002 Copyright(C)2002 Tatsuo Ishii 16
(1) # useradd postgres postgres # mkdir /usr/local/src/postgresql-7.2.2 # mkdir /usr/local/pgsql # chown postgres /usr/local/src/postgresql-7.2.2 /usr/local/pgsql # su postgres $ cd /usr/local/src $ tar xfz /tmp/postgresql-7.2.2.tar.gz $ cd postgresql-7.2.2 $./configure --enable-multibyte enable-unicode-conversion -- enable-syslog Unicode syslog $ make $ make check LC2002 Copyright(C)2002 Tatsuo Ishii 17
(2) $ make install /usr/local/pgsql/ (data ) OS /usr/local/pgsql bin lib share doc (HTML) man data LC2002 Copyright(C)2002 Tatsuo Ishii 18
PostgreSQL postgres.bashrc PG=/usr/local/pgsql export PGLIB=$PG/lib export PGDATA=$PG/data export LD_LIBRARY_PATH=$PG/lib PATH=$PG/bin:$PATH $ initdb (postmaster) $ postmaster -S -i LC2002 Copyright(C)2002 Tatsuo Ishii 19
/usr/local/pgsql/data PG_VERSION postmaster.opts postmaster.pid pg_hba.conf pg_ident.conf global pg_xlog pg_clog base postmaster postmaster ID ident LC2002 Copyright(C)2002 Tatsuo Ishii 20
postgresql.conf = syslog SQL syslog = 2 debug_print_query = true /etc/syslog.conf LC2002 Copyright(C)2002 Tatsuo Ishii 21
$ createuser foo Shall the new user be allowed to create databases? (y/n) n Shall the new user be allowed to create more new users? (y/n) n CREATE USER $ createdb -E foo CREATE DATABASE EUC_JP, UNICODE(UTF-8) LC2002 Copyright(C)2002 Tatsuo Ishii 22
IP GRANT/REVOKE LC2002 Copyright(C)2002 Tatsuo Ishii 23
pg_hba.conf local all trust host all 127.0.0.1 255.255.255.255 trust IP or password md5 reject : IP = IP & IP = 192.168.1.0, = 255.255.255.0 192.168.1.x OK LC2002 Copyright(C)2002 Tatsuo Ishii 24
(1) VACUUM PostgreSQL VACUUM 1 VACUUM FULL LC2002 Copyright(C)2002 Tatsuo Ishii 25
(2) VACUUM postgresql.conf max_fsm_pages (/usr/local/pgsql/data) (du -s ) FSM > DB ( )/8192 VACUUM $ vacuumdb -a LC2002 Copyright(C)2002 Tatsuo Ishii 26
(3) REINDEX $ psql -c REINDEX TABLE foo bar postgres (man reindex ) LC2002 Copyright(C)2002 Tatsuo Ishii 27
(4) ANALYZE ANALYZE SELECT psql -c ANALYZE bar LC2002 Copyright(C)2002 Tatsuo Ishii 28
(5) Web DB postgresql.conf max_connections = 128 shared_buffers = 1024 deadlock_timeout = 128 max_files_per_process = 40 OS /etc/sysctl.conf fs.file-max = 16384 kernel.shmmax = 134217728 LC2002 Copyright(C)2002 Tatsuo Ishii 29
(6) ( ) LC2002 Copyright(C)2002 Tatsuo Ishii 30
(8) LC2002 Copyright(C)2002 Tatsuo Ishii 31
(9) ( ) LC2002 Copyright(C)2002 Tatsuo Ishii 32
(10) PostgreSQL usogres http://usogres.good-day.net FC Replicator http://www.fastconnector.com LC2002 Copyright(C)2002 Tatsuo Ishii 33
(11) usogres FC replicator Apache+PHP Apache+PHP Apache+PHP Apache+PHP Apache+PHP Apache+PHP Apache+PHP Apache+PHP usogres usogres PostgreSQL PostgreSQL PostgreSQL PostgreSQL PostgreSQL PostgreSQL PostgreSQL PostgreSQL LC2002 Copyright(C)2002 Tatsuo Ishii 34
(12) (High Availability) Apache+PHP Apache+PHP TCP/IP HA Apache+PHP Apache+PHP (1) (4) (3) (2) PostgreSQL PostgreSQL shared disk PostgreSQL PostgreSQL (5) LifeKeeper+PostgreSQL Ark (1) (2) (3)postmaster (4)OS (5) LC2002 Copyright(C)2002 Tatsuo Ishii 35
PostgreSQL 7.3 DOMAIN SCHEMA LC2002 Copyright(C)2002 Tatsuo Ishii 36
DOMAIN SQL DOMAIN LC2002 Copyright(C)2002 Tatsuo Ishii 37
DOMAIN: LC2002 Copyright(C)2002 Tatsuo Ishii 38
DOMAIN: LC2002 Copyright(C)2002 Tatsuo Ishii 39
DOMAIN: LC2002 Copyright(C)2002 Tatsuo Ishii 40
SCHEMA SQL SELECT * FROM foo.table1; LC2002 Copyright(C)2002 Tatsuo Ishii 41
SCHEMA A B A A LC2002 Copyright(C)2002 Tatsuo Ishii 42
CREATE SCHEMA schemaname [ AUTHORIZATION username ] [ schema_element [... ] ] CREATE SCHEMA AUTHORIZATION username [ schema_element [... ] ] LC2002 Copyright(C)2002 Tatsuo Ishii 43
myschema CREATE SCHEMA myschema; foo fooschema CREATE SCHEMA fooschema AUTHORIZATION foo; LC2002 Copyright(C)2002 Tatsuo Ishii 44
LC2002 Copyright(C)2002 Tatsuo Ishii 45
LC2002 Copyright(C)2002 Tatsuo Ishii 46
LC2002 Copyright(C)2002 Tatsuo Ishii 47
LC2002 Copyright(C)2002 Tatsuo Ishii 48
public pg_catalog search_path LC2002 Copyright(C)2002 Tatsuo Ishii 49
GRANT { { CREATE USAGE } [,...] ALL [ PRIVILEGES ] } ON SCHEMA schemaname [,...] TO { username GROUP groupname PUBLIC } [,...] CREATE USAGE LC2002 Copyright(C)2002 Tatsuo Ishii 50
LC2002 Copyright(C)2002 Tatsuo Ishii 51
URL PostgreSQL ISBN 4-8443-1589-7 http://www.postgresql.jp PostgreSQL http://www.postgresql.jp pgsql-jp ML http://www.sra.co.jp LC2002 Copyright(C)2002 Tatsuo Ishii 52
LC2002 Copyright(C)2002 Tatsuo Ishii 53