PostgreSQL 9.0 Update ~ホット・スタンバイがやってきた!~
-
Upload
takahiro-itagaki -
Category
Technology
-
view
10.780 -
download
8
description
Transcript of PostgreSQL 9.0 Update ~ホット・スタンバイがやってきた!~
1
PostgreSQL 9.0
PostgreSQL / NTT OSS
2010.6.26
2010 Hokkaido http://www.ospn.jp/osc2010-do/
2
PostgreSQL 9.0
/
3
PostgreSQL 9.0
1977
20032004
2005
2006
20072008
2009
2000
19961986
PostgreSQL6.0
PostgreSQL6.0
7.37.3
7.47.4
IngressIngress
POSTGRESPOSTGRES
•Windows•• (PITR)
•
•Windows•• (PITR)
•
8.0
••2
•
••2
•
8.1
•CPU
••GIN:
•CPU
••GIN:
8.2
•HOT: •VACUUM
•
•HOT: •VACUUM
•
8.3
2010
Window•VACUUM• DBMS
Window•VACUUM• DBMS
8.
••• /
•
••• /
•
9.0 (2010/8 )
9.0 5
2 / 3
9.0.0
4
PostgreSQL 9.0
1.
2. ( )
3. VACUUM FULL
/
LISTEN/NOTIFY /
Windows 64-bit
( URL )http://developer.postgresql.org/pgdocs/postgres/release-9-0.html
http://lets.postgresql.jp/documents/technical/9.0/
1 2 3
5
1.
9.0 2
( )
VACUUM
/
PostgreSQL
••
==
6
vs.
WAL pg_standbyarchive_command
( v8.4)
READ WRITE READ WRITE
(v9.0)
READ WRITE READ WRITE
wal receiverwal sender WAL
8.4
9.0 WAL( )
WAL( 1 )
9.09.0
7
vs.
○
( )
○
DB( )DB
PK , DDL ※SQL
31010☆
○
2.32.09.0
pgpool-IISlony-I
※PK= , DDL=☆
8
(UNIQUE)
“ ” “ ”
(EXCLUDE)
“ ” “ ”
GPS
2.
9
text gist contrib/btree_gist
period ( 9.1 )
CREATE TABLE placement (
object text,
location box, --
EXCLUDE USING gist (location WITH &&)
);
CREATE TABLE reservation (
room text,
during period, -- { , }
EXCLUDE USING gist (room WITH =, during WITH &&)
);
&& “ ”
gist
10
3. VACUUM FULL
VACUUM FULL
8.4 “ ”
→
9.0 “ ”
→
VACUUM ( )
FULL
FULL+TRUNCATE
VACUUM FULL UPVACUUM FULL UP
11
PostgreSQL
,
,
,
,
9.0
8.4
-REINDEX
+CLUSTER
VACUUMFULL
VACUUM
VACUUM
VACUUM FULL (8.4) REINDEX
VACUUM FULL (9.0) CLUSTER REINDEXREINDEX
12
+
13
PostgreSQL 9.0
(HS)
(SR)WAL
8.3
8.0
9.0
2
14
(PG8.0)
archive_command= cp, scp
rsync
15
(PG8.0)
rsyncrestore_command= cp, scp
16
(PG8.3)
restore_command= pg_standby
DB DB
17
(PG9.0)
primary_conninfo
DB
OKrestore_command
= cp, scp
pg_standby
DB
18
SQL ( )
master/standby /
(standby master )
19
( OK)
SQL
20
vs.
( )
( )
cf. MySQL 5.5 : Semi-Synchronous Replication
? ?
9.1
9.0
21
WALWAL ?
① pg_current_xlog_location()
WAL / ② pg_last_xlog_receive_location()
③ pg_last_xlog_replay_location()
: ② ①
: ③ ①SQL
(32bit+32bit )
WAL
�
22
pg_dump
– max_standby_delay
– /
– pg_start_backup() / pg_control
23
24
:
postgresql.conf
wal_level→ hot_standby, archive_mode→ on
archive_command
WAL
vacuum_defer_cleanup_age
VACUUM ?
max_wal_senders→
max_connections
wal_sender_delay
25
:
postgresql.conf
hot_standby→ on?
max_standby_delay = 30s
recovery.conf
standby_mode = ‘on’?
off (WAL )
primary_conninfo
restore_command
WAL
trigger_file
pg_ctl stop→ start
recovery.conf
26
: wal_level ( )
minimal ( )
WALCOPY TO, CREATE INDEX, CLUSTER, VACUUM FULL
8.4 archive_mode = off
archive
8.4 archive_mode = on
hot_standbyarchive
WAL
WAL
archive_mode
27
max_standby_delay ( )
?
pg_dump
canceling statement due to conflict with recovery
terminating connection due to conflict with recovery
or
28
vacuum_defer_cleanup_age ( )
?max_standby_delayVACUUM
PostgreSQL = UPDATE, DELETE
HOTVACUUM autovacuum HOT
29
max_connections :
max_prepared_xacts : XA
max_locks_per_xact : �
max_connections =+ max_wal_senders
wal_level, archive_mode, max_wal_senders(pg_ctl reload)
30
31
EDIT $PGDATA1/postgresql.confEDIT $PGDATA1/pg_hba.confpg_ctl start -D $PGDATA1SELECT pg_start_backup(‘label’)rsync ( )
SELECT pg_stop_backup()
rsync ( )
chmod -R 0700mkdir $PGDATA2/pg_xlog
EDIT $PGDATA2/postgresql.confEDIT $PGDATA2/recovery.confpg_ctl start -D $PGDATA2
wal_level = hot_standbyarchive_mode = onarchive_command = 'cp %p /arclog/%f'max_wal_senders = 1
rsync -av –delete--exclude=pg_xlog --exclude=postmaster.pid$PGDATA1/* $BACKUP/pgdata
hot_standby = on※
restore_command = 'cp /arclog/%f %p'standby_mode = 'on'primary_conninfo = 'host=localhost'
host replication postgres ::1/128 trust
32
trigger_file
①
pg_ctl stop
pg_ctl startstandby_mode = ‘on’pg_ctl stop
②
/
pg_ctl startstandby_mode = ‘off’
( )
33
PostgreSQL “9.0”“ ”
“Let’s Postgres”
http://lets.postgresql.jp/documents/technical/9.0/1
SQL
=
34
35
PostgreSQL → 9.1
9.1
SQL/MED
SQL-DB
SQL/MED PostgreSQL
UPUP
SQL/MED DB
36
PostgreSQL 9.0