Mastering PostgreSQL Administration

1 . Mastering PostgreSQL Administration BRUCE MOMJIAN POSTGRESQL is an open-source, full-featured relational database. This presentation covers advanced administration topics. Creative Commons Attribution License http://momjian.us/presentations Last updated: September, 2018 1 / 112 

2 . Outline 1. Installation 2. Configuration 3. Maintenance 4. Monitoring 5. Recovery 2 / 112 

3 . 1. Installation ◮ Click-through Installers ◮ MS Windows ◮ Linux ◮ OS X ◮ Ports ◮ RPM ◮ DEB ◮ PKG ◮ other packages ◮ Source ◮ obtaining ◮ build options ◮ installing 3 / 112 

4 . Initialization (initdb) $ initdb /u/pgsql/data 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 "en_US.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are disabled. fixing permissions on existing directory /u/pgsql/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting dynamic shared memory implementation ... posix creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. You can now start the database server using: pg_ctl -D /u/pgsql/data -l logfile start 4 / 112 

5 . pg_controldata $ pg_controldata pg_control version number: 1002 Catalog version number: 201707211 Database system identifier: 6544633619067825437 Database cluster state: shut down pg_control last modified: Sun 15 Apr 2018 07:20:58 AM EDT Latest checkpoint location: 0/15C09E0 Prior checkpoint location: 0/15C0708 Latest checkpoint’s REDO location: 0/15C09E0 Latest checkpoint’s REDO WAL file: 000000010000000000000001 Latest checkpoint’s TimeLineID: 1 Latest checkpoint’s PrevTimeLineID: 1 Latest checkpoint’s full_page_writes: on Latest checkpoint’s NextXID: 0:555 Latest checkpoint’s NextOID: 12296 Latest checkpoint’s NextMultiXactId: 1 Latest checkpoint’s NextMultiOffset: 0 Latest checkpoint’s oldestXID: 548 Latest checkpoint’s oldestXID’s DB: 1 Latest checkpoint’s oldestActiveXID: 0 Latest checkpoint’s oldestMultiXid: 1 Latest checkpoint’s oldestMulti’s DB: 1 Latest checkpoint’s oldestCommitTsXid:0 Latest checkpoint’s newestCommitTsXid:0 Time of latest checkpoint: Sun 15 Apr 2018 07:20:58 AM EDT Fake LSN counter for unlogged rels: 0/1 Minimum recovery ending location: 0/0 Min recovery ending loc’s timeline: 0 Backup start location: 0/0 Backup end location: 0/0 End-of-backup record required: no wal_level setting: replica wal_log_hints setting: off max_connections setting: 100 5 / 112 

6 .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 6 / 112 

7 . Starting Postmaster 2018-04-15 07:23:18.172 EDT [12055] LOG: listening on IPv4 address "127.0.0.1", port 5432 2018-04-15 07:23:18.173 EDT [12055] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2018-04-15 07:23:18.185 EDT [12056] LOG: database system was shut down at 2018-04-15 07:22:54 EDT 2018-04-15 07:23:18.188 EDT [12055] LOG: database system is ready to accept connections ◮ manually ◮ pg_ctl start ◮ on boot 7 / 112 

8 . Stopping Postmaster 2018-04-15 07:23:47.317 EDT [12055] LOG: received fast shutdown request 2018-04-15 07:23:47.318 EDT [12055] LOG: aborting any active transactions 2018-04-15 07:23:47.318 EDT [12055] LOG: worker process: logical replication launcher (PID 12062) exited with 2018-04-15 07:23:47.319 EDT [12057] LOG: shutting down 2018-04-15 07:23:47.327 EDT [12055] LOG: database system is shut down ◮ manually ◮ pg_ctl stop ◮ on shutdown 8 / 112 

9 . Connections ◮ local — unix domain socket ◮ host — TCP/IP, both SSL or non-SSL ◮ hostssl — only SSL ◮ hostnossl — never SSL 9 / 112 

10 . Authentication ◮ trust ◮ reject ◮ passwords ◮ scram-sha-256 ◮ md5 ◮ password (cleartext) ◮ local authentication ◮ socket permissions ◮ ’peer’ socket user name passing ◮ host ident using local identd 10 / 112 

11 . Authentication (continued) ◮ remote authentication ◮ host ident using pg_ident.conf ◮ kerberos ◮ gss ◮ sspi ◮ pam ◮ ldap ◮ radius ◮ cert 11 / 112 

12 . Access ◮ hostname and network mask ◮ database name ◮ role name (user or group) ◮ filename or list of databases, role ◮ IPv6 12 / 112 

13 . pg_hba.conf Default # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 trust # Allow replication connections from localhost, by a user with the # replication privilege. #local replication postgres trust #host replication postgres 127.0.0.1/32 trust #host replication postgres ::1/128 trust 13 / 112 

14 . pg_hba.conf Example # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 trust # disable connections from the gateway machine host all all 192.168.1.254/32 reject # enable local network host all all 192.168.1.0/24 scram-sha-256 # require SSL for external connections, but do not allow the superuser hostssl all postgres 0.0.0.0/0 reject hostssl all all 0.0.0.0/0 scram-sha-256 14 / 112 

15 . Permissions ◮ Host connection permissions ◮ Role permissions ◮ create roles ◮ create databases ◮ table permissions ◮ Database management ◮ template1 customization ◮ system tables ◮ disk space computations 15 / 112 

16 . Data Directory $ ls -CF base/ pg_ident.conf pg_serial/ pg_tblspc/ postgresql.auto.conf global/ pg_logical/ pg_snapshots/ pg_twophase/ postgresql.conf pg_commit_ts/ pg_multixact/ pg_stat/ PG_VERSION postmaster.opts pg_dynshmem/ pg_notify/ pg_stat_tmp/ pg_wal/ pg_hba.conf pg_replslot/ pg_subtrans/ pg_xact/ 16 / 112 

17 . Database Directories $ ls -CF global/ 1136 1214_fsm 1261_vm 2671 2846 2967 6000_vm 1136_fsm 1214_vm 1262 2672 2846_vm 3592 6001 1136_vm 1232 1262_fsm 2676 2847 3592_vm 6002 1137 1233 1262_vm 2677 2964 3593 pg_control 1213 1260 2396 2694 2964_vm 4060 pg_filenode.map 1213_fsm 1260_fsm 2396_fsm 2695 2965 4060_vm pg_internal.init 1213_vm 1260_vm 2396_vm 2697 2966 4061 1214 1261 2397 2698 2966_vm 6000 $ ls -CF base/ 1/ 12406/ 12407/ 16384/ $ ls -CF base/16384 112 1249_fsm 2606_vm 2652 2699 3081 3598_vm 113 1249_vm 2607 2653 2701 3085 3599 12242 1255 2607_fsm 2654 2702 3118 3600 12242_fsm 1255_fsm 2607_vm 2655 2703 3118_vm 3600_fsm 12242_vm 1255_vm 2608 2656 2704 3119 3600_vm 12244 1259 2608_fsm 2657 2753 3164 3601 12246 1259_fsm 2608_vm 2658 2753_fsm 3256 3601_fsm … 17 / 112 

18 . Transaction/WAL Directories $ ls -CF pg_wal/ 000000010000000000000001 archive_status/ $ ls -CF pg_xact/ 0000 18 / 112 

19 . Configuration Directories $ ls -CF share/ conversion_create.sql postgres.bki snowball_create.sql extension/ postgres.description sql_features.txt information_schema.sql postgresql.conf.sample system_views.sql pg_hba.conf.sample postgres.shdescription timezone/ pg_ident.conf.sample psqlrc.sample timezonesets/ pg_service.conf.sample recovery.conf.sample tsearch_data/ 19 / 112 

20 .2. Configuration https://www.flickr.com/photos/mwichary/ 20 / 112 

21 . postgresql.conf # ----------------------------- # PostgreSQL configuration file # ----------------------------- # # This file consists of lines of the form: # # name = value # # (The "=" is optional.) Whitespace may be used. Comments are introduced with # "#" anywhere on a line. The complete list of parameter names and allowed # values can be found in the PostgreSQL documentation. # # The commented-out settings shown in this file represent the default values. # Re-commenting a setting is NOT sufficient to revert it to the default value; # you need to reload the server. 21 / 112 

22 . postgresql.conf (Continued) # This file is read on server startup and when the server receives a SIGHUP # signal. If you edit the file on a running system, you have to SIGHUP the # server for the changes to take effect, run "pg_ctl reload", or execute # "SELECT pg_reload_conf()". Some parameters, which are marked below, # require a server shutdown and restart to take effect. # # Any parameter can also be given as a command-line option to the server, e.g., # "postgres -c log_connections=on". Some parameters can be changed at run time # with the "SET" SQL command. # # Memory units: kB = kilobytes Time units: ms = milliseconds # MB = megabytes s = seconds # GB = gigabytes min = minutes # TB = terabytes h = hours # d = days 22 / 112 

23 . Configuration File Location # The default values of these variables are driven from the -D command-line # option or PGDATA environment variable, represented here as ConfigDir. #data_directory = ’ConfigDir’ # use data in another directory # (change requires restart) #hba_file = ’ConfigDir/pg_hba.conf’ # host-based authentication file # (change requires restart) #ident_file = ’ConfigDir/pg_ident.conf’ # ident configuration file # (change requires restart) # If external_pid_file is not explicitly set, no extra PID file is written. #external_pid_file = ’’ # write an extra PID file # (change requires restart) 23 / 112 

24 . Connections and Authentication #listen_addresses = ’localhost’ # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to ’localhost’; use ’*’ for all # (change requires restart) #port = 5432 # (change requires restart) max_connections = 100 # (change requires restart) #superuser_reserved_connections = 3 # (change requires restart) #unix_socket_directories = ’/tmp’ # comma-separated list of directories # (change requires restart) #unix_socket_group = ’’ # (change requires restart) #unix_socket_permissions = 0777 # begin with 0 to use octal notation # (change requires restart) #bonjour = off # advertise server via Bonjour # (change requires restart) #bonjour_name = ’’ # defaults to the computer name # (change requires restart) 24 / 112 

25 . Security and Authentication #authentication_timeout = 1min # 1s-600s #ssl = off #ssl_ciphers = ’HIGH:MEDIUM:+3DES:!aNULL’ # allowed SSL ciphers #ssl_prefer_server_ciphers = on #ssl_ecdh_curve = ’prime256v1’ #ssl_dh_params_file = ’’ #ssl_cert_file = ’server.crt’ #ssl_key_file = ’server.key’ #ssl_ca_file = ’’ #ssl_crl_file = ’’ #password_encryption = md5 # md5 or scram-sha-256 #db_user_namespace = off #row_security = on # GSSAPI using Kerberos #krb_server_keyfile = ’’ #krb_caseins_users = off 25 / 112 

26 . TCP/IP Control #tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds; # 0 selects the system default #tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds; # 0 selects the system default #tcp_keepalives_count = 0 # TCP_KEEPCNT; 26 / 112 

27 . Memory Usage shared_buffers = 128MB # min 128kB # (change requires restart) #huge_pages = try # on, off, or try # (change requires restart) #temp_buffers = 8MB # min 800kB #max_prepared_transactions = 0 # zero disables the feature # (change requires restart) # Caution: it is not advisable to set max_prepared_transactions nonzero unless # you actively intend to use prepared transactions. #work_mem = 4MB # min 64kB #maintenance_work_mem = 64MB # min 1MB #replacement_sort_tuples = 150000 # limits use of replacement selection sort #autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_me #max_stack_depth = 2MB # min 100kB dynamic_shared_memory_type = posix # the default is the first option # supported by the operating system: # posix # sysv # windows # mmap # use none to disable dynamic shared memory # (change requires restart) 27 / 112 

28 . Memory Usage (Continued) Postgres Postgres Postgres Backend Backend Backend Recovery Query and Checkpoint Operations Transaction Durability PostgreSQL Shared Buffer Cache Write−Ahead Log fsync Kernel Disk Buffer Cache fsync Disk Blocks 28 / 112 

29 . Sizing Shared Memory Postgres Session (work_mem) Postgres Session (work_mem) Postgres Session (work_mem) R Shared Buffer Cache (shared_buffers) A Kernel Disk Buffer Cache Page Out M Free Swap Page In (bad) Kernel 29 / 112