[oi-dev] Future of PostgreSQL on OpenIndiana

[Nelson H. F. Beebe]

Andreas Wacknitz <a.wacknitz at gmx.de> asked on Sat, 26 Feb 2022
12:16:28 +0100 raised concern about OpenIndiana support of PostgreSQL
versions.

I have built many versions of PostgreSQL from source code from early
2008 to date, on several O/S platforms, most recently this morning,
when after seeing Andreas' message, I built and installed 14.2 from

	https://www.postgresql.org/ftp/source/v14.2/

on CentOS 7,9.2009, DragonFlyBSD 6.2.1, FreeBSD 14.0, Ubuntu 20.04,
macOS 12.2.1 (Sierra), OpenIndiana Hipster 2021.10, and Solaris 11.4.

I can report that builds of PostgreSQL have rarely failed; its authors
have done an outstanding job of ensuring portability to many operating
systems.  

My typical build recipe (here, for Hipster) looks like this:

	cd /path/to/build-directory
	tar xf $prefix/src/postgresql/postgresql-14.2.tar.bz2
	./configure --prefix=$prefix/ashare/postgresql/postgresql-14.2
	gmake all -j && gmake check -j && gmake install

	cd $prefix/bin
	cat > psql-14.2 <<EOF
	#! /bin/sh -
	PATH=/usr/uumath/ashare/postgresql/postgresql-14.2/bin:/usr/uumath/bin:/bin:/usr/bin
	export PATH

	LD_LIBRARY_PATH=/usr/uumath/ashare/postgresql/postgresql-14.2/lib:/usr/uumath/lib
	export LD_LIBRARY_PATH

	/usr/uumath/ashare/postgresql/postgresql-14.2/bin/psql "$@"
	EOF

	chmod a+x psql-14.2
	ln -s psql-14.2 psql

At my site, prefix is set to our local /usr/uumath tree, because the
default of /usr/local is usurped by several O/Ses in the BSD family
for their own package systems.

I wrap public access to psql in a link to a version-specific script,
psql-x.y that internally sets PATH and LD_LIBRARY_PATH to ensure that
PostgreSQL executables are not confused by libraries from other
versions of PostgreSQL installed on the system.

The installation path includes "/ashare/", for O/S
architecture-specific shared files, because our central fileservers
have client systems from multiple operating systems.

Database systems, such as PostgreSQL, MySQL, MariaDB, and MSSQL,
generally have version-specific formats of stored data, so migration
of a database to a new server version requires it either to be
recreated from scratch (which I often do for my bibliographic
archives), or dumped into a text file from the old server, and then
restored from that file into the new server.

For a commercial online database that must provide nonstop service,
this migration is problematic, but for many local applications of
databases, an outage of a few minutes to an hour or two for a rebuild,
or dump/restore, is acceptable.  I run our databases on multiple
servers, so if one is down for an upgrade, service is still available
from other servers.

So, for people on the oi-dev team, my view is that you have nothing to
fear from upgrading PostgreSQL, and you can easily provide binaries
for multiple versions of PostgreSQL as long as they are stored in
version-specific trees, with symlinks from /usr/bin.


-------------------------------------------------------------------------------
- Nelson H. F. Beebe                    Tel: +1 801 581 5254                  -
- University of Utah                    FAX: +1 801 581 4148                  -
- Department of Mathematics, 110 LCB    Internet e-mail: beebe at math.utah.edu  -
- 155 S 1400 E RM 233                       beebe at acm.org  beebe at computer.org -
- Salt Lake City, UT 84112-0090, USA    URL: http://www.math.utah.edu/~beebe/ -
-------------------------------------------------------------------------------