{"id":15777,"date":"2021-02-20T09:33:56","date_gmt":"2021-02-20T08:33:56","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-installation-from-packages\/"},"modified":"2021-02-20T09:33:56","modified_gmt":"2021-02-20T08:33:56","slug":"postgresql-on-freebsd-installation-from-packages","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-installation-from-packages\/","title":{"rendered":"PostgreSQL on FreeBSD, installation from packages"},"content":{"rendered":"<p>In the last two posts we <a href=\"https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-getting-started\/\" target=\"_blank\" rel=\"noopener\">installed FreeBSD<\/a>, <a href=\"https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-updating-configuration\/\" target=\"_blank\" rel=\"noopener\">configured networking, updated the system, installed bash and had a quick look at kernel parameters<\/a>. In this post we&#8217;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.<\/p>\n<p><!--more--><\/p>\n<p>We already used the &#8220;pkg&#8221; command in the last post for installing bash. The very same command is used when it comes to installing PostgreSQL from packages:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\n[root@freebsd ~]$ pkg search postgresql\nR-cran-RPostgreSQL-0.6.2_3     R Interface to the 'PostgreSQL' Database System\nexim-postgresql-4.94_4         High performance MTA for Unix systems on the Internet\nlibgda5-postgresql-5.2.10      Provides postgresql access for the libgda5 library\nopensmtpd-extras-table-postgresql-6.7.1,1 PostgreSQL table support for OpenSMTPD\np5-PostgreSQL-PLPerl-Call-1.007 Simple interface for calling SQL functions from PostgreSQL PL\/Perl\np5-PostgreSQL-PLPerl-Trace-1.001_1 Simple way to trace execution of Perl statements in PL\/Perl\np5-Test-postgresql-0.09_3      Perl extension of postgresql runner for tests\npgtcl-postgresql10-2.1.1_2     TCL extension for accessing a PostgreSQL server (PGTCL-NG)\npgtcl-postgresql11-2.1.1_2     TCL extension for accessing a PostgreSQL server (PGTCL-NG)\npgtcl-postgresql12-2.1.1_2     TCL extension for accessing a PostgreSQL server (PGTCL-NG)\npgtcl-postgresql13-2.1.1_2     TCL extension for accessing a PostgreSQL server (PGTCL-NG)\npgtcl-postgresql95-2.1.1_2     TCL extension for accessing a PostgreSQL server (PGTCL-NG)\npgtcl-postgresql96-2.1.1_2     TCL extension for accessing a PostgreSQL server (PGTCL-NG)\npostgresql-jdbc-42.2.18        Java JDBC implementation for PostgreSQL\npostgresql-libpgeasy-3.0.4_3   Easy-to-use C interface to PostgreSQL\npostgresql-libpqxx-7.2.0       New C++ interface for PostgreSQL\npostgresql-odbc-13.00.0000     PostgreSQL ODBC driver\npostgresql-plproxy-2.10.0      Function-based sharding for PostgreSQL\npostgresql-relay-1.3.2_1       Multiplex multiple PostgreSQL databases to one relay\npostgresql-repmgr-5.2.0        PostgreSQL replication manager\npostgresql10-client-10.15      PostgreSQL database (client)\npostgresql10-contrib-10.15     The contrib utilities from the PostgreSQL distribution\npostgresql10-docs-10.15        The PostgreSQL documentation set\npostgresql10-plperl-10.15      Write SQL functions for PostgreSQL using Perl5\npostgresql10-plpython-10.15    Module for using Python to write SQL functions\npostgresql10-pltcl-10.15       Module for using Tcl to write SQL functions\npostgresql10-server-10.15      PostgreSQL is the most advanced open-source database available anywhere\npostgresql11-client-11.10      PostgreSQL database (client)\npostgresql11-contrib-11.10     The contrib utilities from the PostgreSQL distribution\npostgresql11-docs-11.10        The PostgreSQL documentation set\npostgresql11-plperl-11.10      Write SQL functions for PostgreSQL using Perl5\npostgresql11-plpython-11.10    Module for using Python to write SQL functions\npostgresql11-pltcl-11.10       Module for using Tcl to write SQL functions\npostgresql11-server-11.10      PostgreSQL is the most advanced open-source database available anywhere\npostgresql12-client-12.5       PostgreSQL database (client)\npostgresql12-contrib-12.5      The contrib utilities from the PostgreSQL distribution\npostgresql12-cstore_fdw-1.7.0  Columnar store for analytics with PostgreSQL\npostgresql12-docs-12.5         The PostgreSQL documentation set\npostgresql12-ogr_fdw-1.0.12    PostgreSQL foreign data wrapper for OGR\npostgresql12-orafce-3.13.4     Oracle's compatibility functions and packages for PostgreSQL\npostgresql12-pg_dirtyread-2.3  Read dead but unvacuumed tuples from a PostgreSQL relation\npostgresql12-pg_repack-1.4.6   Reorganize tables in PostgreSQL databases with minimal locks\npostgresql12-pg_similarity-20160917_2 Set of functions and operators for executing similarity queries\npostgresql12-plperl-12.5       Write SQL functions for PostgreSQL using Perl5\npostgresql12-plpgsql_check-1.15.1 PostgreSQL extension to check PL\/pgSQL code\npostgresql12-plpython-12.5     Module for using Python to write SQL functions\npostgresql12-pltcl-12.5        Module for using Tcl to write SQL functions\npostgresql12-prefix-1.2.9      Prefix Range module for PostgreSQL\npostgresql12-rum-1.3.7         Inverted index with additional information in posting lists\npostgresql12-server-12.5       PostgreSQL is the most advanced open-source database available anywhere\npostgresql12-tds_fdw-2.0.2     PostgreSQL foreign data wrapper to connect to TDS databases\npostgresql12-zhparser-0.2.0_2  PostgreSQL extension for full-text search of Chinese\npostgresql13-client-13.1       PostgreSQL database (client)\npostgresql13-contrib-13.1_1    The contrib utilities from the PostgreSQL distribution\npostgresql13-docs-13.1         The PostgreSQL documentation set\npostgresql13-plperl-13.1       Write SQL functions for PostgreSQL using Perl5\npostgresql13-plpython-13.1     Module for using Python to write SQL functions\npostgresql13-pltcl-13.1        Module for using Tcl to write SQL functions\npostgresql13-server-13.1_1     PostgreSQL is the most advanced open-source database available anywhere\npostgresql95-client-9.5.24     PostgreSQL database (client)\npostgresql95-contrib-9.5.24    The contrib utilities from the PostgreSQL distribution\npostgresql95-docs-9.5.24       The PostgreSQL documentation set\npostgresql95-mysql_fdw-2.5.4   PostgreSQL foreign data wrapper for MySQL\npostgresql95-plperl-9.5.24     Write SQL functions for PostgreSQL using Perl5\npostgresql95-plpython-9.5.24   Module for using Python to write SQL functions\npostgresql95-pltcl-9.5.24      Module for using Tcl to write SQL functions\npostgresql95-server-9.5.24     PostgreSQL is the most advanced open-source database available anywhere\npostgresql96-client-9.6.20     PostgreSQL database (client)\npostgresql96-contrib-9.6.20    The contrib utilities from the PostgreSQL distribution\npostgresql96-docs-9.6.20       The PostgreSQL documentation set\npostgresql96-plperl-9.6.20     Write SQL functions for PostgreSQL using Perl5\npostgresql96-plpython-9.6.20   Module for using Python to write SQL functions\npostgresql96-pltcl-9.6.20      Module for using Tcl to write SQL functions\npostgresql96-server-9.6.20     PostgreSQL is the most advanced open-source database available anywhere\nprometheus-postgresql-adapter-0.6.0 Use PostgreSQL as a remote storage database for Prometheus\npy37-postgresql-1.1.0_5        Python 3 compatible PostgreSQL database driver and tools\nrubygem-azure_mgmt_postgresql-0.17.2 Microsoft Azure PostgreSQL Client Library for Ruby\n<\/pre>\n<p>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:<\/p>\n<ul>\n<li>The PostgreSQL server itself: postgresqlXX-server<\/li>\n<li>The PostgreSQL extensions (except the procedural languages): postgresqlXX-contrib<\/li>\n<li>The PostgreSQL client: postgresqlXX-client<\/li>\n<li>The procedural languages: postgresql-XX-plxxx<\/li>\n<li>The PostgreSQL documentation: postgresql-XX-docs<\/li>\n<li>Various third party extensions, like e.g. mysql_fdw or plpgsql_check<\/li>\n<\/ul>\n<p>Of course we want to go with the latest release (13.2 currently) so lets check what is available for PostgreSQL 13:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\n[root@freebsd ~]$ pkg search postgresql13\npgtcl-postgresql13-2.1.1_2     TCL extension for accessing a PostgreSQL server (PGTCL-NG)\npostgresql13-client-13.1       PostgreSQL database (client)\npostgresql13-contrib-13.1_1    The contrib utilities from the PostgreSQL distribution\npostgresql13-docs-13.1         The PostgreSQL documentation set\npostgresql13-plperl-13.1       Write SQL functions for PostgreSQL using Perl5\npostgresql13-plpython-13.1     Module for using Python to write SQL functions\npostgresql13-pltcl-13.1        Module for using Tcl to write SQL functions\npostgresql13-server-13.1_1     PostgreSQL is the most advanced open-source database available anywhere\n<\/pre>\n<p>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:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\n[root@freebsd ~]$ pkg install postgresql13-server postgresql13-client\nUpdating FreeBSD repository catalogue...\nFreeBSD repository is up to date.\nAll repositories are up to date.\nThe following 10 package(s) will be affected (of 0 checked):\n\nNew packages to be INSTALLED:\n        icu: 68.2,1\n        libedit: 3.1.20191231,1\n        libffi: 3.3_1\n        libxml2: 2.9.10_2\n        llvm11: 11.0.1\n        lua52: 5.2.4\n        perl5: 5.32.1_1\n        postgresql13-client: 13.1\n        postgresql13-server: 13.1_1\n        python37: 3.7.9_1\n\nNumber of packages to be installed: 10\n\nThe process will require 986 MiB more space.\n164 MiB to be downloaded.\n\nProceed with this action? [y\/N]: y\n[1\/10] Fetching postgresql13-server-13.1_1.txz: 100%   13 MiB   2.7MB\/s    00:05    \n[2\/10] Fetching postgresql13-client-13.1.txz: 100%    3 MiB   1.4MB\/s    00:02    \n[3\/10] Fetching llvm11-11.0.1.txz: 100%  107 MiB   3.1MB\/s    00:36    \n[4\/10] Fetching libxml2-2.9.10_2.txz: 100%  826 KiB 845.7kB\/s    00:01    \n[5\/10] Fetching python37-3.7.9_1.txz: 100%   16 MiB   3.4MB\/s    00:05    \n[6\/10] Fetching libffi-3.3_1.txz: 100%   39 KiB  40.0kB\/s    00:01    \n[7\/10] Fetching perl5-5.32.1_1.txz: 100%   14 MiB   2.5MB\/s    00:06    \n[8\/10] Fetching lua52-5.2.4.txz: 100%  175 KiB 178.7kB\/s    00:01    \n[9\/10] Fetching libedit-3.1.20191231,1.txz: 100%  134 KiB 137.4kB\/s    00:01    \n[10\/10] Fetching icu-68.2,1.txz: 100%   10 MiB   2.2MB\/s    00:05    \nChecking integrity... done (0 conflicting)\n[1\/10] Installing libffi-3.3_1...\n[1\/10] Extracting libffi-3.3_1: 100%\n[2\/10] Installing libedit-3.1.20191231,1...\n[2\/10] Extracting libedit-3.1.20191231,1: 100%\n[3\/10] Installing libxml2-2.9.10_2...\n[3\/10] Extracting libxml2-2.9.10_2: 100%\n[4\/10] Installing python37-3.7.9_1...\n[4\/10] Extracting python37-3.7.9_1: 100%\n[5\/10] Installing perl5-5.32.1_1...\n[5\/10] Extracting perl5-5.32.1_1: 100%\n[6\/10] Installing lua52-5.2.4...\n[6\/10] Extracting lua52-5.2.4: 100%\n[7\/10] Installing postgresql13-client-13.1...\n[7\/10] Extracting postgresql13-client-13.1: 100%\n[8\/10] Installing llvm11-11.0.1...\n[8\/10] Extracting llvm11-11.0.1: 100%\n[9\/10] Installing icu-68.2,1...\n[9\/10] Extracting icu-68.2,1: 100%\n[10\/10] Installing postgresql13-server-13.1_1...\n===&gt; Creating groups.\nCreating group 'postgres' with gid '770'.\n===&gt; Creating users\nCreating user 'postgres' with uid '770'.\n===&gt; Creating homedir(s)\n\n  =========== BACKUP YOUR DATA! =============\n  As always, backup your data before\n  upgrading. If the upgrade leads to a higher\n  major revision (e.g. 9.6 -&gt; 10), a dump\n  and restore of all databases is\n  required. This is *NOT* done by the port!\n  See https:\/\/www.postgresql.org\/docs\/current\/upgrading.html\n  ===========================================\n[10\/10] Extracting postgresql13-server-13.1_1: 100%\n=====\nMessage from python37-3.7.9_1:\n\n--\nNote that some standard Python modules are provided as separate ports\nas they require additional dependencies. They are available as:\n\npy37-gdbm       databases\/py-gdbm@py37\npy37-sqlite3    databases\/py-sqlite3@py37\npy37-tkinter    x11-toolkits\/py-tkinter@py37\n=====\nMessage from postgresql13-client-13.1:\n\n--\nThe PostgreSQL port has a collection of \"side orders\":\n\npostgresql-docs\n  For all of the html documentation\n\np5-Pg\n  A perl5 API for client access to PostgreSQL databases.\n\npostgresql-tcltk \n  If you want tcl\/tk client support.\n\npostgresql-jdbc\n  For Java JDBC support.\n\npostgresql-odbc\n  For client access from unix applications using ODBC as access\n  method. Not needed to access unix PostgreSQL servers from Win32\n  using ODBC. See below.\n\nruby-postgres, py-psycopg2\n  For client access to PostgreSQL databases using the ruby &amp; python\n  languages.\n\npostgresql-plperl, postgresql-pltcl &amp; postgresql-plruby\n  For using perl5, tcl &amp; ruby as procedural languages.\n\npostgresql-contrib\n  Lots of contributed utilities, postgresql functions and\n  datatypes. There you find pg_standby, pgcrypto and many other cool\n  things.\n\netc...\n=====\nMessage from postgresql13-server-13.1_1:\n\n--\nFor procedural languages and postgresql functions, please note that\nyou might have to update them when updating the server.\n\nIf you have many tables and many clients running, consider raising\nkern.maxfiles using sysctl(8), or reconfigure your kernel\nappropriately.\n\nThe port is set up to use autovacuum for new databases, but you might\nalso want to vacuum and perhaps backup your database regularly. There\nis a periodic script, \/usr\/local\/etc\/periodic\/daily\/502.pgsql, that\nyou may find useful. You can use it to backup and perform vacuum on all\ndatabases nightly. Per default, it performs `vacuum analyze'. See the\nscript for instructions. For autovacuum settings, please review\n~pgsql\/data\/postgresql.conf.\n\nIf you plan to access your PostgreSQL server using ODBC, please\nconsider running the SQL script \/usr\/local\/share\/postgresql\/odbc.sql\nto get the functions required for ODBC compliance.\n\nPlease note that if you use the rc script,\n\/usr\/local\/etc\/rc.d\/postgresql, to initialize the database, unicode\n(UTF-8) will be used to store character data by default.  Set\npostgresql_initdb_flags or use login.conf settings described below to\nalter this behaviour. See the start rc script for more info.\n\nTo set limits, environment stuff like locale and collation and other\nthings, you can set up a class in \/etc\/login.conf before initializing\nthe database. Add something similar to this to \/etc\/login.conf:\n---\npostgres:\n        :lang=en_US.UTF-8:\n        :setenv=LC_COLLATE=C:\n        :tc=default:\n---\nand run `cap_mkdb \/etc\/login.conf'.\nThen add 'postgresql_class=\"postgres\"' to \/etc\/rc.conf.\n\n======================================================================\n\nTo initialize the database, run\n\n  \/usr\/local\/etc\/rc.d\/postgresql initdb\n\nYou can then start PostgreSQL by running:\n\n  \/usr\/local\/etc\/rc.d\/postgresql start\n\nFor postmaster settings, see ~pgsql\/data\/postgresql.conf\n\nNB. FreeBSD's PostgreSQL port logs to syslog by default\n    See ~pgsql\/data\/postgresql.conf for more info\n\nNB. If you're not using a checksumming filesystem like ZFS, you might\n    wish to enable data checksumming. It can only be enabled during\n    the initdb phase, by adding the \"--data-checksums\" flag to\n    the postgres_initdb_flags rcvar.  Check the initdb(1) manpage\n    for more info and make sure you understand the performance\n    implications.\n\n======================================================================\n\nTo run PostgreSQL at startup, add\n'postgresql_enable=\"YES\"' to \/etc\/rc.conf\n<\/pre>\n<p>The output is quite verbose and gives some interesting information. The first one is this:<br \/>\n&#8220;If you have many tables and many clients running, consider raising<br \/>\nkern.maxfiles using sysctl(8), or reconfigure your kernel<br \/>\nappropriately.&#8221;<\/p>\n<p>This is something you should consider setting in production environments, but not important for now. The next one is this:<\/p>\n<p>&#8220;The port is set up to use autovacuum for new databases, but you might<br \/>\nalso want to vacuum and perhaps backup your database regularly. There<br \/>\nis a periodic script, \/usr\/local\/etc\/periodic\/daily\/502.pgsql, that<br \/>\nyou may find useful. You can use it to backup and perform vacuum on all<br \/>\ndatabases nightly. Per default, it performs `vacuum analyze&#8217;. See the<br \/>\nscript for instructions. For autovacuum settings, please review<br \/>\n~pgsql\/data\/postgresql.conf.&#8221;<\/p>\n<p>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.<\/p>\n<p>The PostgreSQL packages for FreeBSD also come with an init script to initialize and start PostgreSQL:<br \/>\n&#8220;Please note that if you use the rc script,<br \/>\n\/usr\/local\/etc\/rc.d\/postgresql, to initialize the database, unicode<br \/>\n(UTF-8) will be used to store character data by default.  Set<br \/>\npostgresql_initdb_flags or use login.conf settings described below to<br \/>\nalter this behaviour. See the start rc script for more info.&#8221;<\/p>\n<p>Lets go ahead an initialize PostgreSQL with all the defaults:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\n[root@freebsd ~]$ \/usr\/local\/etc\/rc.d\/postgresql initdb\nCannot 'initdb' postgresql. Set postgresql_enable to YES in \/etc\/rc.conf or use 'oneinitdb' instead of 'initdb'.\n<\/pre>\n<p>Ok, this message is pretty clear:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\n[root@freebsd ~]$ echo \"postgresql_enable=\"YES\"\" &gt;&gt; \/etc\/rc.conf\n[root@freebsd ~]$ \/usr\/local\/etc\/rc.d\/postgresql initdb\nThe files belonging to this database system will be owned by user \"postgres\".\nThis user must also own the server process.\n\nThe database cluster will be initialized with locale \"C\".\nThe default text search configuration will be set to \"english\".\n\nData page checksums are disabled.\n\ncreating directory \/var\/db\/postgres\/data13 ... ok\ncreating subdirectories ... ok\nselecting dynamic shared memory implementation ... posix\nselecting default max_connections ... 100\nselecting default shared_buffers ... 128MB\nselecting default time zone ... Europe\/Vaduz\ncreating configuration files ... ok\nrunning bootstrap script ... ok\nperforming post-bootstrap initialization ... ok\nsyncing data to disk ... ok\n\ninitdb: warning: enabling \"trust\" authentication for local connections\nYou can change this by editing pg_hba.conf or using the option -A, or\n--auth-local and --auth-host, the next time you run initdb.\n\nSuccess. You can now start the database server using:\n\n    \/usr\/local\/bin\/pg_ctl -D \/var\/db\/postgres\/data13 -l logfile start\n<\/pre>\n<p>As you can see the default for $PGDATA is &#8220;\/var\/db\/postgres\/data13&#8221;:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\n[root@freebsd ~]$ ls \/var\/db\/postgres\/data13\nPG_VERSION              pg_dynshmem             pg_multixact            pg_snapshots            pg_tblspc               postgresql.auto.conf\nbase                    pg_hba.conf             pg_notify               pg_stat                 pg_twophase             postgresql.conf\nglobal                  pg_ident.conf           pg_replslot             pg_stat_tmp             pg_wal\npg_commit_ts            pg_logical              pg_serial               pg_subtrans             pg_xact\n<\/pre>\n<p>Lets start PostgreSQL:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: [1,4]\">\n[root@freebsd ~]$ \/usr\/local\/etc\/rc.d\/postgresql start \n2021-02-19 01:48:01.925 CET [10715] LOG:  ending log output to stderr\n2021-02-19 01:48:01.925 CET [10715] HINT:  Future log output will go to log destination \"syslog\".\n[root@freebsd ~]$ ps aux | grep postgres\npostgres 10715   0.0  1.4 176276 28460  -  Ss   01:48     0:00.02 \/usr\/local\/bin\/postgres -D \/var\/db\/postgres\/data13\npostgres 10717   0.0  1.4 176276 28476  -  Ss   01:48     0:00.00 postgres: checkpointer  (postgres)\npostgres 10718   0.0  1.4 176276 28496  -  Ss   01:48     0:00.00 postgres: background writer  (postgres)\npostgres 10719   0.0  1.4 176276 28496  -  Ss   01:48     0:00.00 postgres: walwriter  (postgres)\npostgres 10720   0.0  1.4 176728 28776  -  Ss   01:48     0:00.00 postgres: autovacuum launcher  (postgres)\npostgres 10721   0.0  0.8  30632 17420  -  Ss   01:48     0:00.00 postgres: stats collector  (postgres)\npostgres 10722   0.0  1.4 176668 28740  -  Ss   01:48     0:00.00 postgres: logical replication launcher  (postgres)\nroot     10724   0.0  0.0    532   344  0  R+   01:48     0:00.00 grep postgres\n<\/pre>\n<p>The same as in Linux: The packages created a postgres user and a postgres group:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: [1,2,4]\">\n[root@freebsd ~]$ su - postgres\n$ id -a\nuid=770(postgres) gid=770(postgres) groups=770(postgres)\n$ psql \npsql (13.1)\nType \"help\" for help.\n\npostgres=# select version();\n                                                                               version                                                                               \n---------------------------------------------------------------------------------------------------------------------------------------------------------------------\n PostgreSQL 13.1 on amd64-portbld-freebsd12.2, compiled by FreeBSD clang version 10.0.1 (git@github.com:llvm\/llvm-project.git llvmorg-10.0.1-0-gef32c611aa2), 64-bit\n(1 row)\n\npostgres=# \n<\/pre>\n<p>As we enabled PostgreSQL in \/etc\/rc.conf it should come up by default once the box is rebooted:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: [1,4,28]\">\n[root@freebsd ~]# init 6\n[root@freebsd ~]# Connection to 192.168.22.17 closed by remote host.\nConnection to 192.168.22.17 closed.\ndwe@ltdwe:~$ ssh root@192.168.22.17\nPassword for root@freebsd.it.dbi-services.com:\nLast login: Fri Feb 19 01:33:55 2021 from 192.168.22.1\nFreeBSD 12.2-RELEASE-p3 GENERIC \n\nWelcome to FreeBSD!\n\nRelease Notes, Errata: https:\/\/www.FreeBSD.org\/releases\/\nSecurity Advisories:   https:\/\/www.FreeBSD.org\/security\/\nFreeBSD Handbook:      https:\/\/www.FreeBSD.org\/handbook\/\nFreeBSD FAQ:           https:\/\/www.FreeBSD.org\/faq\/\nQuestions List: https:\/\/lists.FreeBSD.org\/mailman\/listinfo\/freebsd-questions\/\nFreeBSD Forums:        https:\/\/forums.FreeBSD.org\/\n\nDocuments installed with the system are in the \/usr\/local\/share\/doc\/freebsd\/\ndirectory, or can be installed later with:  pkg install en-freebsd-doc\nFor other languages, replace \"en\" with a language code like de or fr.\n\nShow the version of FreeBSD installed:  freebsd-version ; uname -a\nPlease include that output and any error messages when posting questions.\nIntroduction to manual pages:  man man\nFreeBSD directory layout:      man hier\n\nEdit \/etc\/motd to change this login announcement.\nroot@freebsd:~ # ps aux | grep postgres\npostgres 799  0.0  1.1 176140 23524  -  Ss   01:56   0:00.02 \/usr\/local\/bin\/postgres -D \/var\/db\/postgres\/data13\npostgres 801  0.0  1.1 176140 23568  -  Ss   01:56   0:00.00 postgres: checkpointer  (postgres)\npostgres 802  0.0  1.1 176192 23604  -  Ss   01:56   0:00.00 postgres: background writer  (postgres)\npostgres 803  0.0  1.1 176192 23604  -  Ss   01:56   0:00.00 postgres: walwriter  (postgres)\npostgres 804  0.0  1.2 176700 24372  -  Ss   01:56   0:00.00 postgres: autovacuum launcher  (postgres)\npostgres 805  0.0  0.6  30836 12536  -  Ss   01:56   0:00.00 postgres: stats collector  (postgres)\npostgres 806  0.0  1.2 176640 24236  -  Ss   01:56   0:00.00 postgres: logical replication launcher  (postgres)\nroot     887  0.0  0.0    532   344  0  R+   01:56   0:00.00 grep postgres\nroot@freebsd:~ # \n<\/pre>\n<p>All fine, PostgreSQL is ready to be used. <\/p>\n<p>You may have noticed that the binaries are not installed in a version dependent directory:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\nroot@freebsd:~ $ which pg_ctl\n\/usr\/local\/bin\/pg_ctl\nroot@freebsd:~ $ which postgres\n\/usr\/local\/bin\/postgres\n<\/pre>\n<p>What will happen if we install another version of PostgreSQL?<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: [1,20,21,22,28,29,30]\">\nroot@freebsd:~ # pkg install postgresql12-server postgresql12-client\nUpdating FreeBSD repository catalogue...\nFreeBSD repository is up to date.\nAll repositories are up to date.\nThe following 2 package(s) will be affected (of 0 checked):\n\nNew packages to be INSTALLED:\n        postgresql12-client: 12.5\n        postgresql12-server: 12.5\n\nNumber of packages to be installed: 2\n\nThe process will require 53 MiB more space.\n15 MiB to be downloaded.\n\nProceed with this action? [y\/N]: y\n[1\/2] Fetching postgresql12-server-12.5.txz: 100%   12 MiB   1.5MB\/s    00:09    \n[2\/2] Fetching postgresql12-client-12.5.txz: 100%    3 MiB   2.7MB\/s    00:01    \nChecking integrity... done (3 conflicting)\n  - postgresql12-server-12.5 conflicts with postgresql13-server-13.1_1 on \/usr\/local\/bin\/initdb\n  - postgresql12-client-12.5 conflicts with postgresql13-client-13.1 on \/usr\/local\/bin\/clusterdb\n  - postgresql12-client-12.5 conflicts with postgresql13-client-13.1 on \/usr\/local\/bin\/clusterdb\nChecking integrity... done (0 conflicting)\nConflicts with the existing packages have been found.\nOne more solver iteration is needed to resolve them.\nThe following 4 package(s) will be affected (of 0 checked):\n\nInstalled packages to be REMOVED:\n        postgresql13-client: 13.1\n        postgresql13-server: 13.1_1\n\nNew packages to be INSTALLED:\n        postgresql12-client: 12.5\n        postgresql12-server: 12.5\n\nNumber of packages to be removed: 2\nNumber of packages to be installed: 2\n\nProceed with this action? [y\/N]: \n<\/pre>\n<p>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.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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&#8217;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.<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[2256,77],"type_dbi":[],"class_list":["post-15777","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-freebsd","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>PostgreSQL on FreeBSD, installation from packages - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-installation-from-packages\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL on FreeBSD, installation from packages\" \/>\n<meta property=\"og:description\" content=\"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&#8217;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.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-installation-from-packages\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-02-20T08:33:56+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"15 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-installation-from-packages\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-installation-from-packages\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL on FreeBSD, installation from packages\",\"datePublished\":\"2021-02-20T08:33:56+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-installation-from-packages\/\"},\"wordCount\":632,\"commentCount\":0,\"keywords\":[\"FreeBSD\",\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-installation-from-packages\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-installation-from-packages\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-installation-from-packages\/\",\"name\":\"PostgreSQL on FreeBSD, installation from packages - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2021-02-20T08:33:56+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-installation-from-packages\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-installation-from-packages\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-installation-from-packages\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL on FreeBSD, installation from packages\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL on FreeBSD, installation from packages - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-installation-from-packages\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL on FreeBSD, installation from packages","og_description":"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&#8217;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.","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-installation-from-packages\/","og_site_name":"dbi Blog","article_published_time":"2021-02-20T08:33:56+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"15 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-installation-from-packages\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-installation-from-packages\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL on FreeBSD, installation from packages","datePublished":"2021-02-20T08:33:56+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-installation-from-packages\/"},"wordCount":632,"commentCount":0,"keywords":["FreeBSD","PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-installation-from-packages\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-installation-from-packages\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-installation-from-packages\/","name":"PostgreSQL on FreeBSD, installation from packages - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2021-02-20T08:33:56+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-installation-from-packages\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-installation-from-packages\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-on-freebsd-installation-from-packages\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL on FreeBSD, installation from packages"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/15777","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=15777"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/15777\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=15777"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=15777"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=15777"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=15777"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}