Mastering PostgreSQL Administration - The PostgreSQL Conference

111 Pages·2012·0.69 MB·English

by  Bruce Momjian

Mastering
PostgreSQL Administration
BRUCE MOMJIAN,
ENTERPRISEDB
April, 2009
Abstract
POSTGRESQL is an open-source, full-featured relational database.
This presentation covers advanced administration topics.
Creative Commons Attribution License http://momjian.us/presentations
Introduction
Installation
(cid:0)
Con(cid:12)guration
(cid:0)
Maintenance
(cid:0)
Monitoring
(cid:0)
Recovery
(cid:0)
Mastering PostgreSQL Administration 1
Installation
Click-Through installers
(cid:0)
(cid:150) MS Windows
(cid:150) Linux
(cid:150) OS/X
(cid:150) Solaris
Ports
(cid:0)
(cid:150) RPM
(cid:150) DEB
(cid:150) PKG
(cid:150) other packages
Source
(cid:0)
(cid:150) obtaining
(cid:150) build options
(cid:150) installing
Mastering PostgreSQL Administration 2
Initialization (initdb)
$ initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale C.
The default database encoding has accordingly been set to SQL_ASCII.
The default text search configuration will be set to "english".
creating directory /u/pg/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers/max_fsm_pages ... 32MB/204800
creating configuration files ... ok
creating template1 database in /u/pg/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects’ descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
Mastering PostgreSQL Administration 3
Initialization (continued)
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.
Success. You can now start the database server using:
postgres -D /u/pg/data
or
pg_ctl -D /u/pg/data -l logfile start
Mastering PostgreSQL Administration 4
pg_controldata
pg_control version number: 74
Catalog version number: 200502281
Database system identifier: 4766833642862247929
Database cluster state: shut down
pg_control last modified: 03/03/05 10:49:18
Current log file ID: 0
Next log file segment: 1
Latest checkpoint location: 0/A34010
Prior checkpoint location: 0/A2D5C0
Latest checkpoint’s REDO location: 0/A34010
Latest checkpoint’s UNDO location: 0/0
Latest checkpoint’s TimeLineID: 1
Latest checkpoint’s NextXID: 545
Latest checkpoint’s NextOID: 17233
Time of latest checkpoint: 03/03/05 10:49:18
Database block size: 8192
Blocks per segment of large relation: 131072
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum number of function arguments: 32
Date/time type storage: floating-point numbers
Maximum length of locale name: 128
LC_COLLATE: C
LC_CTYPE: C
Mastering PostgreSQL Administration 5
System Architecture
Main
Libpq
Postmaster
Postgres Postgres
Parse Statement
utility Utility
Traffic Cop
Command
Query e.g. CREATE TABLE, COPY
SELECT, INSERT, UPDATE, DELETE
Rewrite Query
Generate Paths
Optimal Path
Generate Plan
Plan
Execute Plan
Utilities Catalog Storage Managers
Access Methods Nodes / Lists
Mastering PostgreSQL Administration 6
Starting Postmaster
LOG: database system was shut down at 2009-01-27 10:32:05 EST
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
manually
(cid:0)
pg_ctl
(cid:0)
on boot
(cid:0)
Mastering PostgreSQL Administration 7
Stopping Postmaster
LOG: received smart shutdown request
LOG: autovacuum launcher shutting down
LOG: shutting down
LOG: database system is shut down
manually
(cid:0)
pg_ctl
(cid:0)
on shutdown
(cid:0)
Mastering PostgreSQL Administration 8
Connections
local (cid:151) unix domain socket
(cid:0)
host (cid:151) TCP/IP, both SSL or non-SSL
(cid:0)
hostssl (cid:151) only SSL
(cid:0)
hostnossl (cid:151) never SSL
(cid:0)
Mastering PostgreSQL Administration 9