Managing OS Provided
PostgreSQL Packages

Presented by Keith Fiske / @keithf4

Database Administrator @ OmniTI
(pg_partman, pg_extractor, mimeo)



Follow along at
http://slides.keithf4.com/managing_pg_packages

OmniTI, Inc

  • Full-stack support for high-traffic websites & applications
    • Millions+ of users
    • Terabytes+ of data
    • High concurrency
  • We're hiring!

Start here...

http://www.postgresql.org/download/

  • Binary Packages
  • Source Code
  • Live CDs
  • Application Suites

The Debian Way

Debian 8.7.0 64-bit

Repository

  • Default OS package is rarely the most current (currently 9.4)
  • http://apt.postgresql.org (redirects to wiki page)
  • Up to date packages (9.2 - 9.6) for currently supported releases of Debian & Ubuntu
  • Also includes some third-party applications (pgadmin, pgbouncer, pg_partman, etc)

Packages

  • postgresql-9.6 - main server package
  • postgresql-client-9.6 - client only
  • postgresql-contrib-9.6 - core contrib modules
  • postgresql-server-dev-9.6 - development header files and libraries
  • postgresql-doc-9.6 - documentation
  • postgresql-common - cluster management
  • libpq5 - library files for using PG C libraries


Installing main server package will set up default instance

Version Pinning

  • Some non-versioned packages are associated with latest version installed
  • If 9.5 & 9.6 are installed, common & libpq5 are associated with 9.6
  • Pin version in apt
  • 
    $ cat /etc/apt/preferences.d/postgresql 
    
    Package: postgresql-* libpq5 libpq-dev
    Pin: version 9.4.6-1.*
    Pin-Priority: 501
                            

File Locations

  • Data directory - /var/lib/postgresql/9.6/<cluster_name>
  • Config files - /etc/postgresql/9.6/<cluster_name>
  • Binaries - /usr/lib/postgresql/9.6/bin
  • Libraries - /usr/lib/postgresql/9.6/lib
  • Shared (extensions) - /usr/share/postgresql/9.6
  • Logs - /var/log/postgresql/postgresql-9.6-<cluster_name>.log

Automatic Startup

  • Inside config directory, each instance has a start.conf
  • Only contains a single value
    • auto (default) - automatically start/stop this instance along with system
    • manual - only start this instance manually (using pg_ctlcluster or systemd)
    • disabled - do not allow startup with pg_ctlcluster (can be easily bypassed with pg_ctl)

Default users & access

  • System User - postgres
  • Roles - postgres (superuser)
  • 
    # "local" is for Unix domain socket connections only
    local   all             all                                     peer
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            md5
                            
  • By default, only local system users can log in via socket with no password and if matching db role exists
  • By default, all newly created roles connecting via local tcp must use password auth
  • All other access must be set up manually
  • Default is to only allow local tcp connections
  • 
    #listen_addresses = 'localhost'
                            

Cluster Management
(wrapper scripts)

  • pg_createcluster - replacement for initdb
    • Automatically creates new data directories and sets unique port
    • Easily supports multiple, major versions simultaneously
  • pg_ctlcluster - replacement for pg_ctl
  • pg_upgradecluster - replacement for pg_upgrade
    • Works great if everything "just works". Obfuscates issues when it doesn't.
    • Avoid and use pg_upgrade directly
    • Use pg_createcluster to manually make new instances
    • Manually edit ports to swap old/new version clusters around
  • pg_lsclusters - show all clusters managed
  • pg_renamecluster - rename debian managed cluster
  • psql - wrapper for original psql set to current major version

Managing Default Versions

  • Debian automatically sets the default version to be used as the latest one installed
  • update-alternatives can be used to manage this manually or automatically
  • 
    keith@debian:~$ sudo update-alternatives --get-selections
    [...]
    postmaster.1.gz    auto     /usr/share/postgresql/9.5/man/man1/postmaster.1.gz
    psql.1.gz          auto     /usr/share/postgresql/9.5/man/man1/psql.1.gz
    [...]
                            
    
    keith@debian:~$ sudo update-alternatives --list psql.1.gz
    /usr/share/postgresql/9.5/man/man1/psql.1.gz
    /usr/share/postgresql/9.6/man/man1/psql.1.gz
                            
  • --display option shows everything each of these alternatives manages

Managing Default Versions


keith@debian:~$ sudo update-alternatives --config psql.1.gz
There are 2 choices for the alternative psql.1.gz (providing /usr/share/man/man1/psql.1.gz).

  Selection    Path                                          Priority   Status
------------------------------------------------------------
* 0            /usr/share/postgresql/9.6/man/man1/psql.1.gz   96        auto mode
  1            /usr/share/postgresql/9.5/man/man1/psql.1.gz   95        manual mode
  2            /usr/share/postgresql/9.6/man/man1/psql.1.gz   96        manual mode

Press enter to keep the current choice[*], or type selection number:
                        

SystemD

  • Wrappers within wrappers
  • pg_ctlcluster redirects to systemctl so SystemD should be aware of services stopping starting even if you don't use it
    • Did not do this in early versions of Debian 8, so be sure to update to latest version
    
    systemctl list-units | grep postgres
    
    sudo systemctl start postgresql@9.5-main 
    sudo systemctl stop postgresql@9.5-main 
                            
  • If you need to stop via a mode other than default, use pg_ctlcluster
    • Default in 9.6: -m fast
    • Default in <= 9.4: -m smart
    • 
      pg_ctlcluster 9.4 main stop -m fast
                                      

The CentOS Way

CentOS 6.8 64-bit

Repository

Packages

  • postgresql96 - client package
  • postgresql96-server - main server package
  • postgresql96-contrib - core contrib modules
  • postgresql96-libs - shared libraries
  • postgresql96-devel - development header files and libraries
  • postgresql96-docs - documentation

Setup

  • Default instance is not created upon server installation
  • 
    sudo service postgresql-9.6 initdb
    sudo chkconfig postgresql-9.6 on
    sudo service postgresql-9.6 start
    
    sudo service postgresql-9.6 stop -m fast
                            
  • To create additional instances, use default initdb to designate new data directories

File Locations

  • Data directory - /var/lib/pgsql/9.6/data
  • Config files - Contained in data directory
  • Binaries - /usr/pgsql-9.6/bin
  • Libraries - /usr/pgsql-9.6/lib
  • Shared (extensions) - /usr/pgsql-9.6/lib
  • Logs
    • Database logs - pg_log in data directory
    • Startup log - /var/lib/pgsql/9.6/pgstartup.log

Default users & access

  • System User - postgres
  • Roles - postgres (superuser)
  • 
    # "local" is for Unix domain socket connections only
    local   all             all                                     ident
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            ident
                            
  • By default, only local system users can log in with no password and if matching db role exists
  • By default, for tcp connections, contact client ident server for for matching database role.
  • All other access must be set up manually
  • Default is to only allow local tcp connections
  • 
    #listen_addresses = 'localhost'
                            

Managing Default Versions

  • Similar to Debian, CentOS automatically sets the default version to be used as the latest one installed and update-alternatives can be used to manage this
  • 
    [keith@centos alternatives]$ pwd
    /var/lib/alternatives
    [keith@centos alternatives]$ ls -l
    total 160
    -rw-r--r--. 1 root root  624 May 11 16:06 ip6tables.x86_64
    -rw-r--r--. 1 root root 1469 May 11 16:06 iptables.x86_64
    -rw-r--r--. 1 root root 2731 May 11 17:33 java
    -rw-r--r--. 1 root root  168 May 11 17:33 jre_1.6.0
    -rw-r--r--. 1 root root  169 May 11 17:33 jre_1.7.0
    -rw-r--r--. 1 root root  265 May 11 17:33 jre_openjdk
    -rw-r--r--. 1 root root   63 May 11 17:33 libnssckbi.so.x86_64
    -rw-r--r--. 1 root root  115 May 11 16:07 links
    -rw-r--r--. 1 root root  215 May 11 16:05 mail
    -rw-r--r--. 1 root root  689 May 11 17:33 mta
    -rw-r--r--. 1 root root   91 May 17 16:45 pgsql-clusterdb
    -rw-r--r--. 1 root root  130 May 17 16:45 pgsql-clusterdbman
    -rw-r--r--. 1 root root   88 May 17 16:45 pgsql-createdb
    -rw-r--r--. 1 root root  127 May 17 16:45 pgsql-createdbman
    -rw-r--r--. 1 root root   94 May 17 16:45 pgsql-createlang
    -rw-r--r--. 1 root root  133 May 17 16:45 pgsql-createlangman
    -rw-r--r--. 1 root root   94 May 17 16:45 pgsql-createuser
    -rw-r--r--. 1 root root  133 May 17 16:45 pgsql-createuserman
    -rw-r--r--. 1 root root   82 May 17 16:45 pgsql-dropdb
    -rw-r--r--. 1 root root  121 May 17 16:45 pgsql-dropdbman
    -rw-r--r--. 1 root root   88 May 17 16:45 pgsql-droplang
    -rw-r--r--. 1 root root  127 May 17 16:45 pgsql-droplangman
    -rw-r--r--. 1 root root   88 May 17 16:45 pgsql-dropuser
    -rw-r--r--. 1 root root  127 May 17 16:45 pgsql-dropuserman
    -rw-r--r--. 1 root root  150 May 17 16:45 pgsql-ld-conf
    -rw-r--r--. 1 root root  103 May 17 16:45 pgsql-pg_basebackup
    -rw-r--r--. 1 root root  142 May 17 16:45 pgsql-pg_basebackupman
    -rw-r--r--. 1 root root   85 May 17 16:45 pgsql-pg_dump
    -rw-r--r--. 1 root root   94 May 17 16:45 pgsql-pg_dumpall
    -rw-r--r--. 1 root root  133 May 17 16:45 pgsql-pg_dumpallman
    -rw-r--r--. 1 root root  124 May 17 16:45 pgsql-pg_dumpman
    -rw-r--r--. 1 root root   94 May 17 16:45 pgsql-pg_restore
    -rw-r--r--. 1 root root  133 May 17 16:45 pgsql-pg_restoreman
    -rw-r--r--. 1 root root   76 May 17 16:45 pgsql-psql
    -rw-r--r--. 1 root root  115 May 17 16:45 pgsql-psqlman
    -rw-r--r--. 1 root root   91 May 17 16:45 pgsql-reindexdb
    -rw-r--r--. 1 root root  130 May 17 16:45 pgsql-reindexdbman
    -rw-r--r--. 1 root root   88 May 17 16:45 pgsql-vacuumdb
    -rw-r--r--. 1 root root  127 May 17 16:45 pgsql-vacuumdbman
    -rw-r--r--. 1 root root  861 May 11 16:06 print
                            

Managing Default Versions



[keith@centos ~]$ sudo update-alternatives --display pgsql-psql
pgsql-psql - status is auto.
 link currently points to /usr/pgsql-9.6/bin/psql
/usr/pgsql-9.6/bin/psql - priority 960
/usr/pgsql-9.5/bin/psql - priority 950
Current `best' version is /usr/pgsql-9.6/bin/psql.

[keith@centos ~]$ sudo update-alternatives --config pgsql-psql

There are 2 programs which provide 'pgsql-psql'.

  Selection    Command
-----------------------------------------------
*+ 1           /usr/pgsql-9.6/bin/psql
   2           /usr/pgsql-9.5/bin/psql

Enter to keep the current selection[+], or type selection number:
                        

--auto option sets alternatives to automatically choose the one with highest priority

Service Management

  • Starting with 9.0, one cluster per major version supported by default. Previously, only single cluster supported.
  • Each major version has its own init.d - /etc/init.d/postgresql-9.6
  • Make copy(s) of default init.d for managing multiple clusters
  • 
    PGVERSION=9.6.1
    PGMAJORVERSION=`echo "$PGVERSION" | sed 's/^\([0-9]*\.[0-9]*\).*$/\1/'`
    NAME=`basename $0`
    PGENGINE=/usr/pgsql-${PGMAJORVERSION}/bin
    PGPORT=5432
    PGDATA=/var/lib/pgsql/${PGMAJORVERSION}/data
    PGLOG=/var/lib/pgsql/${PGMAJORVERSION}/pgstartup.log
    lockfile="/var/lock/subsys/${NAME}.${PGPORT}"
    pidfile="/var/run/${NAME}.pid"
                            
    
    # Override defaults from /etc/sysconfig/pgsql if file is present
    [ -f /etc/sysconfig/pgsql/${NAME} ] && . /etc/sysconfig/pgsql/${NAME}
    export PGDATA
    export PGPORT
                            

The FreeBSD Way

FreeBSD 11.0 64-bit

Repository

  • Binary Packages or Compiled Ports
    • pkg command for managing packages
    • portmaster is useful for managing ports
  • Generally both kept up to date, but Ports usually ahead of Packages
    • FreeBSD 10.3 pkg only goes up to PG 9.5
  • Either method is managed the same after install

Packages

  • postgresql96-server - main server package
  • postgresql96-client - client package
  • postgresql96-contrib - core contrib modules
  • postgresql96-docs - documentation

Setup

  • Default instance is not created upon server installation
  • Enable postgres in /etc/rc.conf
  • 
    postgresql_enable="YES"
                            
    
    sudo /usr/local/etc/rc.d/postgresql initdb
    sudo service postgresql start
    
    sudo service postgresql stop -m fast
                            

File Locations

  • Data directory - /usr/local/pgsql/data
    • Changed in 9.6 - /var/db/postgres/data96
  • Config files - Contained in data directory
  • Binaries - /usr/local/bin
  • Libraries - /usr/local/lib/postgresql
  • Shared (extensions) - /usr/local/share/postgresql
  • Logs - syslog (recommend changing to stderr in data directory pg_log)

Default users & access

  • System User - pgsql (changed to "postgres" as of 9.6)
  • Roles - pgsql (superuser) (changed to "postgres" in 9.6)
  • 
    # "local" is for Unix domain socket connections only
    local   all             all                                     trust
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            trust
                            
  • By default, all local system users can log in with no password
  • By default, all local tcp connections can log in with no password
  • All other access must be set up manually
  • Default is to only allow local tcp connections
  • 
    #listen_addresses = 'localhost'
                            

Managing Multiple Versions
& Upgrades

  • Multiple minor AND major version packages not supported at the same time. Installing a new packages will uninstall the old.
  • Only supported upgrade is pg_dump -> shutdown -> install new packages -> pg_restore
    • Alternative:
      1. source install new version to custom location
      2. manually create new data directory structure
      3. pg_upgrade instance to new data directory location
      4. install new packages & start new cluster
  • Until multiple major packages are supported, recommend manual, source installation for high availability production systems.

Non-Default Data Location

  • If using packages, recommend NOT using service initdb (no version folder)
  • Following file shows optional settings for rc.conf to control main PostgreSQL service
  • 
    /usr/ports/databases/postgresql95-server/files/postgresql.in
                            
    
    postgresql_data="/usr/local/pgsql/data/9.5"
    postgresql_flags="-w -s -m fast"
    postgresql_initdb_flags="--encoding=utf-8 --lc-collate=C"
                            
  • rc.conf does not support multiple cluster definitions

Running Multiple Instances

  • Similar to CentOS, make copies of /usr/local/etc/rc.d/postgresql file
  • 
    REPLACE:
    eval postgresql_data=${postgresql_data:-"~${postgresql_user}/data"}
    
    WITH:
    postgresql_data="/usr/local/pgsql/95/newdb"
                            
  • Running PostgreSQL in FreeBSD Jails

Your Way!

Compile From Source

  • Debian Required Packages
    • build-essential, libreadline-dev, zlib1g-dev
  • CentOS Required Packages
    • sudo yum groupinstall 'Development Tools'
    • readline-devel, zlib-devel
  • FreeBSD Required Packages
    • gmake
  • Install Bison & Flex if compiling source from git repo

Compile Options

  • Security
    • with-openssl, with-gssapi, with-ldap, with-pam
  • Languages
    • with-perl, with-python, with-tcl
  • Development:
    • enable-debug, enable-cassert
  • Build most contrib modules
  • 
    make world
    make install-world                        
                            

Service Scripts

  • Source provides several sample startup scripts in
    ../contrib/start-scripts/
  • Linux, FreeBSD, OSX

Install Location

  • configure --prefix=/opt/pgsqlxx{x}
    • Can do for each minor version, but then have to reinstall all extensions every patch
  • Symlink general location to specific version
  • 
    ln -s /opt/pgsql95 pgsql
                            
  • Add /opt/pgsql/bin to PATH
  • Data directory should be versioned, but version should be folder on mount point. Allows pg_upgrade --link
  • 
    keith@server:~ % zfs list
    NAME                      USED  AVAIL  REFER  MOUNTPOINT
    tank                     2.60T   937G   144K  /tank
    tank/pgdata               396M   937G   280M  /tank/pgdata
    
    keith@server:/tank/pgdata % ls -l
    total 9
    drwx------  19 pgsql  pgsql  26 Apr 14 14:33 94
    drwx------   2 pgsql  pgsql   2 May 16 20:34 95