ebook img

Mastering PostgreSQL Administration - Bruce Momjian PDF

109 Pages·2016·0.63 MB·English
Save to my drive
Quick download
Download
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.

Preview Mastering PostgreSQL Administration - 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

Description:
Jan 24, 2012 Outline. 1. Installation. 2. Configuration. 3. Maintenance. 4. Monitoring. 5. Recovery. Mastering, PostgreSQL Administration 2 / 109
See more

The list of books you might like

Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.