Mastering PostgreSQL Administration - Bruce Momjian

109 Pages·2016·0.63 MB·English

by  Bruce Momjian

Mastering PostgreSQL Administration
BRUCEMOMJIAN
POSTGRESQL is anopen-source,full-featured relationaldatabase. Thispresentation
covers advancedadministrationtopics.
https://momjian.us/presentations CreativeCommonsAttributionLicense
Lastupdated:June2022
1/117
Outline
1. Installation
2. Configuration
3. Maintenance
4. Monitoring
5. Recovery
2/117
1. Installation
•
Click-throughInstallers
•
MSWindows
•
Linux
•
OSX
•
Ports
•
RPM
•
DEB
•
PKG
•
otherpackages
•
Source
•
obtaining
•
buildoptions
•
installing
3/117
Initialization (initdb)
$ initdb/u/pgsql/data
The filesbelongingto thisdatabase systemwillbe owned byuser "postgres".
This usermust alsoown theserver process.
The databaseclusterwill beinitializedwith locale"en_US.UTF-8".
The defaultdatabaseencodinghas accordinglybeenset to "UTF8".
The defaulttextsearch configurationwill be setto "english".
Data pagechecksumsare disabled.
fixingpermissionson existingdirectory/u/pgsql/data...ok
creatingsubdirectories...ok
selectingdefaultmax_connections...100
selectingdefaultshared_buffers... 128MB
selectingdynamicshared memoryimplementation...posix
creatingconfigurationfiles... ok
runningbootstrapscript ...ok
performingpost-bootstrapinitialization... ok
syncingdata todisk ... ok
WARNING:enabling"trust" authenticationfor localconnections
You canchange thisby editingpg_hba.confor usingthe option-A, or
--auth-localand--auth-host,the nexttime yourun initdb.
Success.You cannow startthe databaseserver using:
pg_ctl-D /u/pgsql/data-l logfilestart
4/117
pg_controldata
$ pg_controldata
pg_controlversionnumber: 1002
Catalogversionnumber: 201707211
Databasesystemidentifier: 6544633619067825437
Databaseclusterstate: shut down
pg_controllastmodified: Sun 15 Apr2018 07:20:58AM EDT
Latestcheckpointlocation: 0/15C09E0
Priorcheckpointlocation: 0/15C0708
Latestcheckpoint’sREDO location: 0/15C09E0
Latestcheckpoint’sREDO WALfile: 000000010000000000000001
Latestcheckpoint’sTimeLineID: 1
Latestcheckpoint’sPrevTimeLineID: 1
Latestcheckpoint’sfull_page_writes:on
Latestcheckpoint’sNextXID: 0:555
Latestcheckpoint’sNextOID: 12296
Latestcheckpoint’sNextMultiXactId: 1
Latestcheckpoint’sNextMultiOffset: 0
Latestcheckpoint’soldestXID: 548
Latestcheckpoint’soldestXID’sDB: 1
Latestcheckpoint’soldestActiveXID: 0
Latestcheckpoint’soldestMultiXid: 1
Latestcheckpoint’soldestMulti’sDB:1
Latestcheckpoint’soldestCommitTsXid:0
Latestcheckpoint’snewestCommitTsXid:0
Time oflatest checkpoint: Sun 15 Apr2018 07:20:58AM EDT
Fake LSNcounterfor unloggedrels: 0/1
Minimumrecoveryending location: 0/0
Min recoveryendingloc’s timeline: 0
Backupstart location: 0/0
Backupend location: 0/0
…
5/117
System Architecture
Main
Libpq
Postmaster
Postgres Postgres
Parse Statement
Traffic Cop utility CoUmtimlitaynd
SELECT, INSEQRTu, eUrPyDATE, DELETE e.g. CREATE TABLE, COPY
Rewrite Query
Generate Paths
Optimal Path
Generate Plan
Plan
Execute Plan
Utilities Catalog Storage Managers
Access Methods Nodes / Lists
6/117
Session Creation
postmaster fork() postgres postgres
Program (Text) Program (Text) Program (Text)
Data Data Data
Shared Memory Shared Memory Shared Memory
Stack Stack Stack
7/117
Starting Postmaster
2018-04-1507:23:18.172EDT[12055] LOG: listeningon IPv4address "127.0.0.1",port 5432
2018-04-1507:23:18.173EDT[12055] LOG: listeningon Unixsocket "/tmp/.s.PGSQL.5432"
2018-04-1507:23:18.185EDT[12056] LOG: databasesystem wasshut downat 2018-04-1507:22:54EDT
2018-04-1507:23:18.188EDT[12055] LOG: databasesystem isready toaccept connections
•
manually
•
pg_ctl start
•
onboot
8/117
Stopping Postmaster
2018-04-1507:23:47.317EDT[12055] LOG: receivedfast shutdownrequest
2018-04-1507:23:47.318EDT[12055] LOG: abortingany activetransactions
2018-04-1507:23:47.318EDT[12055] LOG: workerprocess: logicalreplicationlauncher(PID12062) exitedwith exitcode 1
2018-04-1507:23:47.319EDT[12057] LOG: shuttingdown
2018-04-1507:23:47.327EDT[12055] LOG: databasesystem isshut down
•
manually
•
pg_ctl stop
•
onshutdown
9/117
Connections
•
local— unix domainsocket
•
host— TCP/IP, bothSSL ornon-SSL
•
hostssl— onlySSL
•
hostnossl— neverSSL
10/117