Managing OS Provided
PostgreSQL Packages

Presented by Keith Fiske / @keithf4

Senior Database Engineer @ Crunchy Data Solutions, Inc.
(pg_partman, pg_extractor, mimeo)

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

Crunchy Data Solutions, Inc

Industry leader in providing enterprise PostgreSQL support and open source solutions
Crunchy Certified PostgreSQL

100% Open Source PostgreSQL
Common Criteria EAL 2+ Certified

We're hiring!

https://www.crunchydata.com/
DBAs, Systems Engineers, Container Experts

Start here...

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

Binary Packages
Source Code
Live CDs
Application Suites

The Debian Way

Debian 9.5.0 64-bit

Repository

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

Packages

postgresql-10 - main server package
postgresql-client-10 - client only
postgresql-server-dev-10 - development header files and libraries
postgresql-doc-10 - documentation
postgresql-contrib - core contrib modules (previous versions had version tag on pkg name)
postgresql-common - cluster management
libpq5 - library files for using PG C libraries

Installing main server package will automatically 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/10/<cluster_name>
Config files - /etc/postgresql/10/<cluster_name>
Binaries - /usr/lib/postgresql/10/bin
Libraries - /usr/lib/postgresql/10/lib
Shared (extensions) - /usr/share/postgresql/10
Logs - /var/log/postgresql/postgresql-10-<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 highest version installed
update-alternatives can be used to manage this manually or automatically


keith@debian:~$ sudo update-alternatives --get-selections | grep postgres

postmaster.1.gz                auto     /usr/share/postgresql/10/man/man1/postmaster.1.gz
psql.1.gz                      auto     /usr/share/postgresql/10/man/man1/psql.1.gz


keith@debian:~$ sudo update-alternatives --list psql.1.gz

/usr/share/postgresql/10/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/10/man/man1/psql.1.gz    100       auto mode
  1            /usr/share/postgresql/10/man/man1/psql.1.gz    100       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@10-main 
sudo systemctl stop postgresql@10-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 7.5 64-bit

Repository

Default OS package is 9.2 (EOL)
https://www.postgresql.org/download/linux/redhat//

Provides form that gives instructions for all RPM-based distros (RHEL, CentOS, Scientific, Oracle, etc) & and all supported PG versions


yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

Packages

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

Setup

Default instance is not created upon server installation
Default service managed by SystemD


/usr/pgsql-10/bin/postgresql-10-setup initdb
sudo systemctl enable postgresql-10
sudo systemctl start postgresql-10

sudo systemctl stop postgresql-10

To create additional instances, use default initdb to designate new data directories

File Locations

Data directory - /var/lib/pgsql/10/data
Config files - Contained in data directory
Binaries - /usr/pgsql-10/bin
Libraries - /usr/pgsql-10/lib
Shared (extensions) - /usr/pgsql-10/lib
Logs

Database logs - log in data directory (pg_log for 9.6-)

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            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 highest one installed and update-alternatives can be used to manage this


[keith@centos alternatives]$ pwd
/var/lib/alternatives
[training@localhost alternatives]$ ls -l
total 196
-rw-r--r--. 1 root root  400 Jan 24  2018 cdrecord
-rw-r--r--. 1 root root  114 Aug 29 16:26 cifs-idmap-plugin
-rw-r--r--. 1 root root   63 Aug 29 16:28 cups_backend_smb
-rw-r--r--. 1 root root   63 Aug 29 16:28 google-chrome
-rw-r--r--. 1 root root 4411 Aug 29 16:29 java
-rw-r--r--. 1 root root  253 Aug 29 16:29 jre_1.7.0
-rw-r--r--. 1 root root  264 Aug 29 16:29 jre_1.7.0_openjdk
-rw-r--r--. 1 root root  243 Aug 29 16:29 jre_1.8.0
-rw-r--r--. 1 root root  258 Aug 29 16:29 jre_1.8.0_openjdk
-rw-r--r--. 1 root root  424 Aug 29 16:29 jre_openjdk
-rw-r--r--. 1 root root   57 Aug 29 16:27 ld
-rw-r--r--. 1 root root  531 Aug 20 14:18 libjavaplugin.so.x86_64
-rw-r--r--. 1 root root  101 Aug 29 16:26 libnssckbi.so.x86_64
-rw-r--r--. 1 root root   86 Aug 29 16:30 libwbclient.so.0.14-64
-rw-r--r--. 1 root root  283 Jan 24  2018 mkisofs
-rw-r--r--. 1 root root  689 Jan 24  2018 mta
-rw-r--r--. 1 root root  103 Feb 12  2018 pax
-rw-r--r--. 1 root root   91 Oct 17 19:12 pgsql-clusterdb
-rw-r--r--. 1 root root  130 Oct 17 19:12 pgsql-clusterdbman
-rw-r--r--. 1 root root   88 Oct 17 19:12 pgsql-createdb
-rw-r--r--. 1 root root  127 Oct 17 19:12 pgsql-createdbman
-rw-r--r--. 1 root root   60 Oct 17 19:12 pgsql-createlang
-rw-r--r--. 1 root root   86 Oct 17 19:12 pgsql-createlangman
-rw-r--r--. 1 root root   94 Oct 17 19:12 pgsql-createuser
-rw-r--r--. 1 root root  133 Oct 17 19:12 pgsql-createuserman
-rw-r--r--. 1 root root   82 Oct 17 19:12 pgsql-dropdb
-rw-r--r--. 1 root root  121 Oct 17 19:12 pgsql-dropdbman
-rw-r--r--. 1 root root   56 Oct 17 19:12 pgsql-droplang
-rw-r--r--. 1 root root   82 Oct 17 19:12 pgsql-droplangman
-rw-r--r--. 1 root root   88 Oct 17 19:12 pgsql-dropuser
-rw-r--r--. 1 root root  127 Oct 17 19:12 pgsql-dropuserman
-rw-r--r--. 1 root root  149 Oct 17 19:12 pgsql-ld-conf
-rw-r--r--. 1 root root  103 Oct 17 19:12 pgsql-pg_basebackup
-rw-r--r--. 1 root root  142 Oct 17 19:12 pgsql-pg_basebackupman
-rw-r--r--. 1 root root   85 Oct 17 19:12 pgsql-pg_dump
-rw-r--r--. 1 root root   94 Oct 17 19:12 pgsql-pg_dumpall
-rw-r--r--. 1 root root  133 Oct 17 19:12 pgsql-pg_dumpallman
-rw-r--r--. 1 root root  124 Oct 17 19:12 pgsql-pg_dumpman
-rw-r--r--. 1 root root   94 Oct 17 19:12 pgsql-pg_restore
-rw-r--r--. 1 root root  133 Oct 17 19:12 pgsql-pg_restoreman
-rw-r--r--. 1 root root   76 Oct 17 19:12 pgsql-psql
-rw-r--r--. 1 root root  115 Oct 17 19:12 pgsql-psqlman
-rw-r--r--. 1 root root   91 Oct 17 19:12 pgsql-reindexdb
-rw-r--r--. 1 root root  130 Oct 17 19:12 pgsql-reindexdbman
-rw-r--r--. 1 root root   88 Oct 17 19:12 pgsql-vacuumdb
-rw-r--r--. 1 root root  127 Oct 17 19:12 pgsql-vacuumdbman
-rw-r--r--. 1 root root  856 Aug 20 14:11 print
-rw-r--r--. 1 root root  181 Aug 20 14:14 xinputrc

Managing Default Versions



$ sudo update-alternatives --display pgsql-psql
pgsql-psql - status is auto.
 link currently points to /usr/pgsql-10/bin/psql
/usr/pgsql-10/bin/psql - priority 1000
/usr/pgsql-9.6/bin/psql - priority 960
Current `best' version is /usr/pgsql-10/bin/psql.

$ sudo update-alternatives --config pgsql-psql

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

  Selection    Command
-----------------------------------------------
*+ 1           /usr/pgsql-10/bin/psql
   2           /usr/pgsql-9.6/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.
Starting with CentOS7, all services managed by systemd. Each major version gets its own service


$ pwd
/usr/lib/systemd/system

$ ls -l | grep postgres
-rw-r--r--. 1 root root 1728 Aug  9 05:35 postgresql-10.service
-rw-r--r--. 1 root root 1730 Aug  9 05:27 postgresql-9.6.service

Make copy(s) of default service files for managing multiple clusters of same major version
Just need to change data directory that service points to


Environment=PGDATA=/var/lib/pgsql/10/my_other_db/

The FreeBSD Way

FreeBSD 11.2 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 11.2 pkg is still at PG9.6 but ports has PG11 (just released last week)

Either method is managed the same after install

Packages

postgresql10-server - main server package
postgresql10-client - client package
postgresql10-contrib - core contrib modules
postgresql10-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 - /var/db/postgres/data##
- 9.6 - /var/db/postgres/data96
- 10 - /var/db/postgres/data10
- Pre-9.6 - /usr/local/pgsql/data
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 - postgres (Pre-9.6 was pgsql)
Roles - postgres (superuser) (Pre-9.6 was pgsql)


# "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:

source install new version to custom location
manually create new data directory structure
pg_upgrade instance to new data directory location
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

For 9.6+, rc.d init file shows optional settings for rc.conf to control main PostgreSQL service


/usr/local/etc/rc.d/postgresql


postgresql_data="/usr/local/pgsql/data/10
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:
# set defaults
eval postgresql_data=${postgresql_data:-"~${postgresql_user}/data"}

WITH:
postgresql_data="/usr/local/pgsql/10/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 --setopt=group_package_types=mandatory,default,optional groupinstall "Development Tools"

By default, CentOS7 only set optional packages for this group, so normal groupinstall doesn't install anything
readline-devel, zlib-devel

FreeBSD Required Packages

gmake
As of FreeBSD11.2, requires readline package and setting local includes path to find readline headers


                                pkg install readline
                                ./configure --with-includes=/usr/local/include

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 96 
drwx------   2 pgsql  pgsql   2 May 16 20:34 10

PostgreSQL Home Page - postgresql.org
Planet PostgreSQL Community News Feed - planet.postgresql.org
PostgreSQL Extension Network - pgxn.org
Crunchy Data Solutions, Inc - crunchydata.com
Me! - keithf4.com / @keithf4
Make cool presentations like this - reveal.js

Managing OS ProvidedPostgreSQL Packages

Crunchy Data Solutions, Inc

Start here...

The Debian Way

Repository

Packages

Version Pinning

File Locations

Automatic Startup

Default users & access

Cluster Management (wrapper scripts)

Managing Default Versions

Managing Default Versions

SystemD

The CentOS Way

Repository

Packages

Setup

File Locations

Default users & access

Managing Default Versions

Managing Default Versions

Service Management

The FreeBSD Way

Repository

Packages

Setup

File Locations

Default users & access

Managing Multiple Versions & Upgrades

Non-Default Data Location

Running Multiple Instances

Your Way!

Compile From Source

Compile Options

Service Scripts

Install Location

Managing OS Provided
PostgreSQL Packages

Cluster Management
(wrapper scripts)

Managing Multiple Versions
& Upgrades