Upgrade to PostgreSQL 15 on FreeBSD 13

This Howto explains the upgrade to PostgreSQL 15 on FreeBSD 13.

IMPORTANT: First take a snapshot of the virtual machine or save the entire server.

Get Information about the Environment

pkg info | grep sql

postgresql14-client-14.5  PostgreSQL database (client)
postgresql14-server-14.5  PostgreSQL is the most advanced open-source database available anywhere

Show Version of the PostgreSQL Database

/usr/local/bin/psql -U postgresql -d dbname -t --command "show server_version"
14.5

Output the Location of the Configuration Directories

pg_config

BINDIR = /usr/local/bin
DOCDIR = /usr/local/share/doc/postgresql
HTMLDIR = /usr/local/share/doc/postgresql
INCLUDEDIR = /usr/local/include
PKGINCLUDEDIR = /usr/local/include/postgresql
INCLUDEDIR-SERVER = /usr/local/include/postgresql/server
LIBDIR = /usr/local/lib
PKGLIBDIR = /usr/local/lib/postgresql
LOCALEDIR = /usr/local/share/locale
MANDIR = /usr/local/man
SHAREDIR = /usr/local/share/postgresql
SYSCONFDIR = /usr/local/etc/postgresql
PGXS = /usr/local/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE =  '--with-libraries=/usr/local/lib' '--with-includes=/usr/local/include' '--enable-thread-safety' '--disable-debug' '--without-gssapi' '--enable-nls' '--without-pam' '--with-openssl' '--prefix=/usr/local' '--localstatedir=/var' '--mandir=/usr/local/man' '--infodir=/usr/local/share/info/' '--build=amd64-portbld-freebsd13.0' 'build_alias=amd64-portbld-freebsd13.0' 'CC=cc' 
CFLAGS=-O2 -pipe  -fstack-protector-strong -fno-strict-aliasing ' 
LDFLAGS= -L/usr/local/lib -lpthread -L/usr/local/lib  -fstack-protector-strong ' 
LIBS=' 'CPPFLAGS=-I/usr/local/include' 
CXX=c++' 'CXXFLAGS=-O2 -pipe -fstack-protector-strong -fno-strict-aliasing  '
CPP=cpp' 'PKG_CONFIG=pkgconf' 'PKG_CONFIG_LIBDIR=/wrkdirs/usr/ports/databases/postgresql14-client/work/.pkgconfig:/usr/local/libdata/pkgconfig:/usr/local/share/pkgconfig:/usr/libdata/pkgconfig' 
'LDFLAGS_SL='
CC = cc

VERSION = PostgreSQL 14.5

Get a List of the PostgreSQL Databases

/usr/local/bin/psql -U postgresql -d wnk2022 -l

                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   
-----------+----------+----------+---------+---------+-----------------------
 wnk2022   | postgres | UTF8     | C       | C.UTF-8 | 
 postgres  | postgres | UTF8     | C       | C.UTF-8 | 
 template0 | postgres | UTF8     | C       | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres

Dump all PostgreSQL Databases

/usr/local/bin/pg_dump --create --encoding=UTF8 --column-inserts --dbname=wnk2022 --format=p --inserts --username=postgres --file=/usr/local/www/wnk20221014.sql

Check if the Dump Exists

ls -altr /usr/local/www/*.sql
-rw-r--r--  1 root  wheel  122116150 Oct 14 10:57 /usr/local/www/wnk20221014.sql

Postgresql Data Dir: /var/db/postgres/data14/

Stop The PostgreSQL Service

service postgresql stop pkg delete -fy postgresql14-server-14.5; pkg delete -fy postgresql14-client-14.5

Checking integrity... done (0 conflicting)
Deinstallation has been requested for the following 1 packages (of 0 packages in the universe):

Installed packages to be REMOVED:
        postgresql14-server: 14.5

Number of packages to be removed: 1

The operation will free 43 MiB.
[1/1] Deinstalling postgresql14-server-14.5...
[1/1] Deleting files for postgresql14-server-14.5: 100%
==> You should manually remove the "postgres" user. 
==> You should manually remove the "postgres" group 
Checking integrity... done (0 conflicting)
Deinstallation has been requested for the following 1 packages (of 0 packages in the universe):

Installed packages to be REMOVED:
        postgresql14-client: 14.5

Number of packages to be removed: 1

The operation will free 13 MiB.
[1/1] Deinstalling postgresql14-client-14.5...
[1/1] Deleting files for postgresql14-client-14.5: 100%
pkg install postgresql15-server

The following 2 package(s) will be affected (of 0 checked):

New packages to be INSTALLED:
        postgresql15-client: 15.0
        postgresql15-server: 15.0

Number of packages to be installed: 2

The process will require 57 MiB more space.
17 MiB to be downloaded.

Proceed with this action? [y/N]

[1/2] Fetching postgresql15-server-15.0.pkg: 100%   14 MiB  14.3MB/s    00:01    
[2/2] Fetching postgresql15-client-15.0.pkg: 100%    3 MiB   3.0MB/s    00:01    
Checking integrity... done (0 conflicting)
[1/2] Installing postgresql15-client-15.0...
[1/2] Extracting postgresql15-client-15.0: 100%
[2/2] Installing postgresql15-server-15.0...
=> Creating groups.
Using existing group 'postgres'.
=> Creating users
Using existing user 'postgres'.
=> Creating homedir(s)

  = BACKUP YOUR DATA! =
  As always, backup your data before
  upgrading. If the upgrade leads to a higher
  major revision (e.g. 9.6 -> 10), a dump
  and restore of all databases is
  required. This is *NOT* done by the port!
  See https://www.postgresql.org/docs/current/upgrading.html
  =======
[2/2] Extracting postgresql15-server-15.0: 100%
=====
Message from postgresql15-client-15.0:

--
The PostgreSQL port has a collection of "side orders":

postgresql-docs
  For all of the html documentation

p5-Pg
  A perl5 API for client access to PostgreSQL databases.

postgresql-tcltk
  If you want tcl/tk client support.

postgresql-jdbc
  For Java JDBC support.

postgresql-odbc
  For client access from unix applications using ODBC as access
  method. Not needed to access unix PostgreSQL servers from Win32
  using ODBC. See below.

ruby-postgres, py-psycopg2
  For client access to PostgreSQL databases using the ruby & python
  languages.

postgresql-plperl, postgresql-pltcl & postgresql-plruby
  For using perl5, tcl & ruby as procedural languages.

postgresql-contrib
  Lots of contributed utilities, postgresql functions and
  datatypes. There you find pg_standby, pgcrypto and many other cool
  things.

etc...
==
Message from postgresql15-server-15.0: -- For procedural languages and postgresql functions, please note that you might have to update them when updating the server. If you have many tables and many clients running, consider raising kern.maxfiles using sysctl(8), or reconfigure your kernel appropriately. The port is set up to use autovacuum for new databases, but you might also want to vacuum and perhaps backup your database regularly. There is a periodic script, /usr/local/etc/periodic/daily/502.pgsql, that you may find useful. You can use it to backup and perform vacuum on all databases nightly. Per default, it performs `vacuum analyze'. See the script for instructions. For autovacuum settings, please review ~postgres/data/postgresql.conf. Please note that if you use the rc script, /usr/local/etc/rc.d/postgresql, to initialize the database, unicode (UTF-8) will be used to store character data by default. Set postgresql_initdb_flags or use login.conf settings described below to alter this behaviour. See the start rc script for more info. To set limits, environment stuff like locale and collation and other things, you can set up a class in /etc/login.conf before initializing the database. Add something similar to this to /etc/login.conf: --- postgres:\ :lang=en_US.UTF-8:\ :setenv=LC_COLLATE=C:\ :tc=default: --- and run `cap_mkdb /etc/login.conf'. Then add 'postgresql_class="postgres"' to /etc/rc.conf. To initialize the database, run /usr/local/etc/rc.d/postgresql initdb You can then start PostgreSQL by running: /usr/local/etc/rc.d/postgresql start For postmaster settings, see ~postgres/data/postgresql.conf NB. FreeBSD's PostgreSQL port logs to syslog by default See ~postgres/data/postgresql.conf for more info NB. If you're not using a checksumming filesystem like ZFS, you might wish to enable data checksumming. It can be enabled during the initdb phase, by adding the "--data-checksums" flag to the postgresql_initdb_flags rcvar. Otherwise you can enable it later by pg_checksums. Check the initdb(1) manpage for more info and make sure you understand the performance implications. ====================================================================== To run PostgreSQL at startup, add 'postgresql_enable="YES"' to /etc/rc.conf
/usr/local/etc/rc.d/postgresql initdb

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 this locale configuration:
  provider:    libc
  LC_COLLATE:  C
  LC_CTYPE:    C.UTF-8
  LC_MESSAGES: C.UTF-8
  LC_MONETARY: C.UTF-8
  LC_NUMERIC:  C.UTF-8
  LC_TIME:     C.UTF-8
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /var/db/postgres/data15 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: 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:

    /usr/local/bin/pg_ctl -D /var/db/postgres/data15 -l logfile start
su - postgres /usr/local/bin/pg_ctl -D /var/db/postgres/data15 -l logfile start psql created user abc password 'abc'; psql -U postgres -f /usr/local/www/db2105_20221023_1632.sql > /usr/local/www/db2210load.txt

Published: Oct. 23, 2022

Change Content