In the last two posts we installed FreeBSD, configured networking, updated the system, installed bash and had a quick look at kernel parameters. In this post we’ll install PostgreSQL from packages. Of course you can also install PostgreSQL from source code, but this will be the topic for the next post.
We already used the “pkg” command in the last post for installing bash. The very same command is used when it comes to installing PostgreSQL from packages:
[root@freebsd ~]$ pkg search postgresql R-cran-RPostgreSQL-0.6.2_3 R Interface to the 'PostgreSQL' Database System exim-postgresql-4.94_4 High performance MTA for Unix systems on the Internet libgda5-postgresql-5.2.10 Provides postgresql access for the libgda5 library opensmtpd-extras-table-postgresql-6.7.1,1 PostgreSQL table support for OpenSMTPD p5-PostgreSQL-PLPerl-Call-1.007 Simple interface for calling SQL functions from PostgreSQL PL/Perl p5-PostgreSQL-PLPerl-Trace-1.001_1 Simple way to trace execution of Perl statements in PL/Perl p5-Test-postgresql-0.09_3 Perl extension of postgresql runner for tests pgtcl-postgresql10-2.1.1_2 TCL extension for accessing a PostgreSQL server (PGTCL-NG) pgtcl-postgresql11-2.1.1_2 TCL extension for accessing a PostgreSQL server (PGTCL-NG) pgtcl-postgresql12-2.1.1_2 TCL extension for accessing a PostgreSQL server (PGTCL-NG) pgtcl-postgresql13-2.1.1_2 TCL extension for accessing a PostgreSQL server (PGTCL-NG) pgtcl-postgresql95-2.1.1_2 TCL extension for accessing a PostgreSQL server (PGTCL-NG) pgtcl-postgresql96-2.1.1_2 TCL extension for accessing a PostgreSQL server (PGTCL-NG) postgresql-jdbc-42.2.18 Java JDBC implementation for PostgreSQL postgresql-libpgeasy-3.0.4_3 Easy-to-use C interface to PostgreSQL postgresql-libpqxx-7.2.0 New C++ interface for PostgreSQL postgresql-odbc-13.00.0000 PostgreSQL ODBC driver postgresql-plproxy-2.10.0 Function-based sharding for PostgreSQL postgresql-relay-1.3.2_1 Multiplex multiple PostgreSQL databases to one relay postgresql-repmgr-5.2.0 PostgreSQL replication manager postgresql10-client-10.15 PostgreSQL database (client) postgresql10-contrib-10.15 The contrib utilities from the PostgreSQL distribution postgresql10-docs-10.15 The PostgreSQL documentation set postgresql10-plperl-10.15 Write SQL functions for PostgreSQL using Perl5 postgresql10-plpython-10.15 Module for using Python to write SQL functions postgresql10-pltcl-10.15 Module for using Tcl to write SQL functions postgresql10-server-10.15 PostgreSQL is the most advanced open-source database available anywhere postgresql11-client-11.10 PostgreSQL database (client) postgresql11-contrib-11.10 The contrib utilities from the PostgreSQL distribution postgresql11-docs-11.10 The PostgreSQL documentation set postgresql11-plperl-11.10 Write SQL functions for PostgreSQL using Perl5 postgresql11-plpython-11.10 Module for using Python to write SQL functions postgresql11-pltcl-11.10 Module for using Tcl to write SQL functions postgresql11-server-11.10 PostgreSQL is the most advanced open-source database available anywhere postgresql12-client-12.5 PostgreSQL database (client) postgresql12-contrib-12.5 The contrib utilities from the PostgreSQL distribution postgresql12-cstore_fdw-1.7.0 Columnar store for analytics with PostgreSQL postgresql12-docs-12.5 The PostgreSQL documentation set postgresql12-ogr_fdw-1.0.12 PostgreSQL foreign data wrapper for OGR postgresql12-orafce-3.13.4 Oracle's compatibility functions and packages for PostgreSQL postgresql12-pg_dirtyread-2.3 Read dead but unvacuumed tuples from a PostgreSQL relation postgresql12-pg_repack-1.4.6 Reorganize tables in PostgreSQL databases with minimal locks postgresql12-pg_similarity-20160917_2 Set of functions and operators for executing similarity queries postgresql12-plperl-12.5 Write SQL functions for PostgreSQL using Perl5 postgresql12-plpgsql_check-1.15.1 PostgreSQL extension to check PL/pgSQL code postgresql12-plpython-12.5 Module for using Python to write SQL functions postgresql12-pltcl-12.5 Module for using Tcl to write SQL functions postgresql12-prefix-1.2.9 Prefix Range module for PostgreSQL postgresql12-rum-1.3.7 Inverted index with additional information in posting lists postgresql12-server-12.5 PostgreSQL is the most advanced open-source database available anywhere postgresql12-tds_fdw-2.0.2 PostgreSQL foreign data wrapper to connect to TDS databases postgresql12-zhparser-0.2.0_2 PostgreSQL extension for full-text search of Chinese postgresql13-client-13.1 PostgreSQL database (client) postgresql13-contrib-13.1_1 The contrib utilities from the PostgreSQL distribution postgresql13-docs-13.1 The PostgreSQL documentation set postgresql13-plperl-13.1 Write SQL functions for PostgreSQL using Perl5 postgresql13-plpython-13.1 Module for using Python to write SQL functions postgresql13-pltcl-13.1 Module for using Tcl to write SQL functions postgresql13-server-13.1_1 PostgreSQL is the most advanced open-source database available anywhere postgresql95-client-9.5.24 PostgreSQL database (client) postgresql95-contrib-9.5.24 The contrib utilities from the PostgreSQL distribution postgresql95-docs-9.5.24 The PostgreSQL documentation set postgresql95-mysql_fdw-2.5.4 PostgreSQL foreign data wrapper for MySQL postgresql95-plperl-9.5.24 Write SQL functions for PostgreSQL using Perl5 postgresql95-plpython-9.5.24 Module for using Python to write SQL functions postgresql95-pltcl-9.5.24 Module for using Tcl to write SQL functions postgresql95-server-9.5.24 PostgreSQL is the most advanced open-source database available anywhere postgresql96-client-9.6.20 PostgreSQL database (client) postgresql96-contrib-9.6.20 The contrib utilities from the PostgreSQL distribution postgresql96-docs-9.6.20 The PostgreSQL documentation set postgresql96-plperl-9.6.20 Write SQL functions for PostgreSQL using Perl5 postgresql96-plpython-9.6.20 Module for using Python to write SQL functions postgresql96-pltcl-9.6.20 Module for using Tcl to write SQL functions postgresql96-server-9.6.20 PostgreSQL is the most advanced open-source database available anywhere prometheus-postgresql-adapter-0.6.0 Use PostgreSQL as a remote storage database for Prometheus py37-postgresql-1.1.0_5 Python 3 compatible PostgreSQL database driver and tools rubygem-azure_mgmt_postgresql-0.17.2 Microsoft Azure PostgreSQL Client Library for Ruby
As you can see, all currently supported major versions of PostgreSQL are available (Note: 9.5 is our of support since this month). Very similar to the packages you find on Linux the, FreeBSD separates PostgreSQL into several packages:
- The PostgreSQL server itself: postgresqlXX-server
- The PostgreSQL extensions (except the procedural languages): postgresqlXX-contrib
- The PostgreSQL client: postgresqlXX-client
- The procedural languages: postgresql-XX-plxxx
- The PostgreSQL documentation: postgresql-XX-docs
- Various third party extensions, like e.g. mysql_fdw or plpgsql_check
Of course we want to go with the latest release (13.2 currently) so lets check what is available for PostgreSQL 13:
[root@freebsd ~]$ pkg search postgresql13 pgtcl-postgresql13-2.1.1_2 TCL extension for accessing a PostgreSQL server (PGTCL-NG) postgresql13-client-13.1 PostgreSQL database (client) postgresql13-contrib-13.1_1 The contrib utilities from the PostgreSQL distribution postgresql13-docs-13.1 The PostgreSQL documentation set postgresql13-plperl-13.1 Write SQL functions for PostgreSQL using Perl5 postgresql13-plpython-13.1 Module for using Python to write SQL functions postgresql13-pltcl-13.1 Module for using Tcl to write SQL functions postgresql13-server-13.1_1 PostgreSQL is the most advanced open-source database available anywhere
The standard stuff is there, but compared to PostgreSQL 12 a lot of packages seem to be missing, and it is not the latest PostgreSQL release (still 13.1). Anyway, lets install the server and client:
[root@freebsd ~]$ pkg install postgresql13-server postgresql13-client
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
The following 10 package(s) will be affected (of 0 checked):
New packages to be INSTALLED:
icu: 68.2,1
libedit: 3.1.20191231,1
libffi: 3.3_1
libxml2: 2.9.10_2
llvm11: 11.0.1
lua52: 5.2.4
perl5: 5.32.1_1
postgresql13-client: 13.1
postgresql13-server: 13.1_1
python37: 3.7.9_1
Number of packages to be installed: 10
The process will require 986 MiB more space.
164 MiB to be downloaded.
Proceed with this action? [y/N]: y
[1/10] Fetching postgresql13-server-13.1_1.txz: 100% 13 MiB 2.7MB/s 00:05
[2/10] Fetching postgresql13-client-13.1.txz: 100% 3 MiB 1.4MB/s 00:02
[3/10] Fetching llvm11-11.0.1.txz: 100% 107 MiB 3.1MB/s 00:36
[4/10] Fetching libxml2-2.9.10_2.txz: 100% 826 KiB 845.7kB/s 00:01
[5/10] Fetching python37-3.7.9_1.txz: 100% 16 MiB 3.4MB/s 00:05
[6/10] Fetching libffi-3.3_1.txz: 100% 39 KiB 40.0kB/s 00:01
[7/10] Fetching perl5-5.32.1_1.txz: 100% 14 MiB 2.5MB/s 00:06
[8/10] Fetching lua52-5.2.4.txz: 100% 175 KiB 178.7kB/s 00:01
[9/10] Fetching libedit-3.1.20191231,1.txz: 100% 134 KiB 137.4kB/s 00:01
[10/10] Fetching icu-68.2,1.txz: 100% 10 MiB 2.2MB/s 00:05
Checking integrity... done (0 conflicting)
[1/10] Installing libffi-3.3_1...
[1/10] Extracting libffi-3.3_1: 100%
[2/10] Installing libedit-3.1.20191231,1...
[2/10] Extracting libedit-3.1.20191231,1: 100%
[3/10] Installing libxml2-2.9.10_2...
[3/10] Extracting libxml2-2.9.10_2: 100%
[4/10] Installing python37-3.7.9_1...
[4/10] Extracting python37-3.7.9_1: 100%
[5/10] Installing perl5-5.32.1_1...
[5/10] Extracting perl5-5.32.1_1: 100%
[6/10] Installing lua52-5.2.4...
[6/10] Extracting lua52-5.2.4: 100%
[7/10] Installing postgresql13-client-13.1...
[7/10] Extracting postgresql13-client-13.1: 100%
[8/10] Installing llvm11-11.0.1...
[8/10] Extracting llvm11-11.0.1: 100%
[9/10] Installing icu-68.2,1...
[9/10] Extracting icu-68.2,1: 100%
[10/10] Installing postgresql13-server-13.1_1...
===> Creating groups.
Creating group 'postgres' with gid '770'.
===> Creating users
Creating user 'postgres' with uid '770'.
===> 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
===========================================
[10/10] Extracting postgresql13-server-13.1_1: 100%
=====
Message from python37-3.7.9_1:
--
Note that some standard Python modules are provided as separate ports
as they require additional dependencies. They are available as:
py37-gdbm databases/py-gdbm@py37
py37-sqlite3 databases/py-sqlite3@py37
py37-tkinter x11-toolkits/py-tkinter@py37
=====
Message from postgresql13-client-13.1:
--
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 postgresql13-server-13.1_1:
--
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
~pgsql/data/postgresql.conf.
If you plan to access your PostgreSQL server using ODBC, please
consider running the SQL script /usr/local/share/postgresql/odbc.sql
to get the functions required for ODBC compliance.
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 ~pgsql/data/postgresql.conf
NB. FreeBSD's PostgreSQL port logs to syslog by default
See ~pgsql/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 only be enabled during
the initdb phase, by adding the "--data-checksums" flag to
the postgres_initdb_flags rcvar. 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
The output is quite verbose and gives some interesting information. The first one is this:
“If you have many tables and many clients running, consider raising
kern.maxfiles using sysctl(8), or reconfigure your kernel
appropriately.”
This is something you should consider setting in production environments, but not important for now. The next one is this:
“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
~pgsql/data/postgresql.conf.”
I would be surprised if autovacuum is not enabled but informing about that does not harm. There is a reference to a script you might want to use for periodic backups and vacuuming of your databases. Notice that this script is only doing logical backups using pg_dump and pg_dumpall. Doing physical backups is a must for production workloads, so please do not rely on this script only.
The PostgreSQL packages for FreeBSD also come with an init script to initialize and start PostgreSQL:
“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.”
Lets go ahead an initialize PostgreSQL with all the defaults:
[root@freebsd ~]$ /usr/local/etc/rc.d/postgresql initdb Cannot 'initdb' postgresql. Set postgresql_enable to YES in /etc/rc.conf or use 'oneinitdb' instead of 'initdb'.
Ok, this message is pretty clear:
[root@freebsd ~]$ echo "postgresql_enable="YES"" >> /etc/rc.conf
[root@freebsd ~]$ /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 locale "C".
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating directory /var/db/postgres/data13 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Europe/Vaduz
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
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/data13 -l logfile start
As you can see the default for $PGDATA is “/var/db/postgres/data13”:
[root@freebsd ~]$ ls /var/db/postgres/data13 PG_VERSION pg_dynshmem pg_multixact pg_snapshots pg_tblspc postgresql.auto.conf base pg_hba.conf pg_notify pg_stat pg_twophase postgresql.conf global pg_ident.conf pg_replslot pg_stat_tmp pg_wal pg_commit_ts pg_logical pg_serial pg_subtrans pg_xact
Lets start PostgreSQL:
[root@freebsd ~]$ /usr/local/etc/rc.d/postgresql start 2021-02-19 01:48:01.925 CET [10715] LOG: ending log output to stderr 2021-02-19 01:48:01.925 CET [10715] HINT: Future log output will go to log destination "syslog". [root@freebsd ~]$ ps aux | grep postgres postgres 10715 0.0 1.4 176276 28460 - Ss 01:48 0:00.02 /usr/local/bin/postgres -D /var/db/postgres/data13 postgres 10717 0.0 1.4 176276 28476 - Ss 01:48 0:00.00 postgres: checkpointer (postgres) postgres 10718 0.0 1.4 176276 28496 - Ss 01:48 0:00.00 postgres: background writer (postgres) postgres 10719 0.0 1.4 176276 28496 - Ss 01:48 0:00.00 postgres: walwriter (postgres) postgres 10720 0.0 1.4 176728 28776 - Ss 01:48 0:00.00 postgres: autovacuum launcher (postgres) postgres 10721 0.0 0.8 30632 17420 - Ss 01:48 0:00.00 postgres: stats collector (postgres) postgres 10722 0.0 1.4 176668 28740 - Ss 01:48 0:00.00 postgres: logical replication launcher (postgres) root 10724 0.0 0.0 532 344 0 R+ 01:48 0:00.00 grep postgres
The same as in Linux: The packages created a postgres user and a postgres group:
[root@freebsd ~]$ su - postgres
$ id -a
uid=770(postgres) gid=770(postgres) groups=770(postgres)
$ psql
psql (13.1)
Type "help" for help.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 13.1 on amd64-portbld-freebsd12.2, compiled by FreeBSD clang version 10.0.1 ([email protected]:llvm/llvm-project.git llvmorg-10.0.1-0-gef32c611aa2), 64-bit
(1 row)
postgres=#
As we enabled PostgreSQL in /etc/rc.conf it should come up by default once the box is rebooted:
[root@freebsd ~]# init 6 [root@freebsd ~]# Connection to 192.168.22.17 closed by remote host. Connection to 192.168.22.17 closed. dwe@ltdwe:~$ ssh [email protected] Password for [email protected]: Last login: Fri Feb 19 01:33:55 2021 from 192.168.22.1 FreeBSD 12.2-RELEASE-p3 GENERIC Welcome to FreeBSD! Release Notes, Errata: https://www.FreeBSD.org/releases/ Security Advisories: https://www.FreeBSD.org/security/ FreeBSD Handbook: https://www.FreeBSD.org/handbook/ FreeBSD FAQ: https://www.FreeBSD.org/faq/ Questions List: https://lists.FreeBSD.org/mailman/listinfo/freebsd-questions/ FreeBSD Forums: https://forums.FreeBSD.org/ Documents installed with the system are in the /usr/local/share/doc/freebsd/ directory, or can be installed later with: pkg install en-freebsd-doc For other languages, replace "en" with a language code like de or fr. Show the version of FreeBSD installed: freebsd-version ; uname -a Please include that output and any error messages when posting questions. Introduction to manual pages: man man FreeBSD directory layout: man hier Edit /etc/motd to change this login announcement. root@freebsd:~ # ps aux | grep postgres postgres 799 0.0 1.1 176140 23524 - Ss 01:56 0:00.02 /usr/local/bin/postgres -D /var/db/postgres/data13 postgres 801 0.0 1.1 176140 23568 - Ss 01:56 0:00.00 postgres: checkpointer (postgres) postgres 802 0.0 1.1 176192 23604 - Ss 01:56 0:00.00 postgres: background writer (postgres) postgres 803 0.0 1.1 176192 23604 - Ss 01:56 0:00.00 postgres: walwriter (postgres) postgres 804 0.0 1.2 176700 24372 - Ss 01:56 0:00.00 postgres: autovacuum launcher (postgres) postgres 805 0.0 0.6 30836 12536 - Ss 01:56 0:00.00 postgres: stats collector (postgres) postgres 806 0.0 1.2 176640 24236 - Ss 01:56 0:00.00 postgres: logical replication launcher (postgres) root 887 0.0 0.0 532 344 0 R+ 01:56 0:00.00 grep postgres root@freebsd:~ #
All fine, PostgreSQL is ready to be used.
You may have noticed that the binaries are not installed in a version dependent directory:
root@freebsd:~ $ which pg_ctl /usr/local/bin/pg_ctl root@freebsd:~ $ which postgres /usr/local/bin/postgres
What will happen if we install another version of PostgreSQL?
root@freebsd:~ # pkg install postgresql12-server postgresql12-client
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
The following 2 package(s) will be affected (of 0 checked):
New packages to be INSTALLED:
postgresql12-client: 12.5
postgresql12-server: 12.5
Number of packages to be installed: 2
The process will require 53 MiB more space.
15 MiB to be downloaded.
Proceed with this action? [y/N]: y
[1/2] Fetching postgresql12-server-12.5.txz: 100% 12 MiB 1.5MB/s 00:09
[2/2] Fetching postgresql12-client-12.5.txz: 100% 3 MiB 2.7MB/s 00:01
Checking integrity... done (3 conflicting)
- postgresql12-server-12.5 conflicts with postgresql13-server-13.1_1 on /usr/local/bin/initdb
- postgresql12-client-12.5 conflicts with postgresql13-client-13.1 on /usr/local/bin/clusterdb
- postgresql12-client-12.5 conflicts with postgresql13-client-13.1 on /usr/local/bin/clusterdb
Checking integrity... done (0 conflicting)
Conflicts with the existing packages have been found.
One more solver iteration is needed to resolve them.
The following 4 package(s) will be affected (of 0 checked):
Installed packages to be REMOVED:
postgresql13-client: 13.1
postgresql13-server: 13.1_1
New packages to be INSTALLED:
postgresql12-client: 12.5
postgresql12-server: 12.5
Number of packages to be removed: 2
Number of packages to be installed: 2
Proceed with this action? [y/N]:
This is maybe not what you want. If you decide to go with packages on FreeBSD you are stuck with one version of PostgreSQL. If we want to have the flexibility to run more than one version of PostgreSQL you need to install PostgreSQL from source code. This will be the topic for the next post.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)