How to Install PostgreSQL 14 on FreeBSD 13

For new users, especially for those whom use GUI most of the time, managing software in FreeBSD isn't very intuitive. As much for my own benefit as anyone else's, now I am going to walk through the steps to getting PostgreSQL 14 installed and configured on a FreeBSD 13 system.

So why Postgres?

PostgreSQL is a amazing database platform. PostgreSQL is free, cross-platform, and gives an high-quality function set which, in my mind, exceeds the ones of its similar platform in the relational database area.

PostgreSQL offers all of the (mostly) standard-compliant SQL/relational database function you'll anticipate, plus a bunch of thrilling and innovative features. Some of worth mentioning features are json datatype (and additionally jsonb!), array datatype, and the brand new HStore type, which essentially allows the specification of a column as containing a list of key/value pairs. We’ll take a tour of PostgreSQL in every other posts, but first, let’s get the thing installed and running.

Installation steps for PostgreSQL on FreeBSD 13

First of all, I assume that you have FreeBSD 13 system up and running with internet connection. You should also run the setup as root user or user with sudo privileges. This post should help you with creating sudo user in FreeBSD.

As of this writing, the most up-to-date version of PostgreSQL is version 14.1.

Our first step is updating the available repositories catalogues.

$ sudo pkg update

If you would like to upgrade packages to the newer versions available in the repository, run:

$ sudo pkg upgrade

PostgreSQL Installation

Download and install PostgreSQL server and client packages using pkg package manager.

$ sudo pkg install postgresql14-server postgresql14-client

The system will respond with something like below:

Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
The following 13 package(s) will be affected (of 0 checked):

New packages to be INSTALLED:
	icu: 69.1,1
	libedit: 3.1.20210216,1
	libffi: 3.3_1
	liblz4: 1.9.3,1
	libxml2: 2.9.12
	llvm11: 11.0.1_3
	lua52: 5.2.4
	mpdecimal: 2.5.1
	perl5: 5.32.1_1
	postgresql14-client: 14.1
	postgresql14-server: 14.1
	python38: 3.8.12
	readline: 8.1.1

Number of packages to be installed: 13

The process will require 1 GiB more space.
167 MiB to be downloaded.

Proceed with this action? [y/N]: 

Press "Y" followed by "Enter" to continue with installation.

The installation process depends on your internet connection. Once the installation process finished, we can enable PostgreSQL service to start on system boot.

$ sudo sysrc postgresql_enable=yes

The system will respond with something like below:

postgresql_enable:  -> yes

It means the system will start PostgreSQL service every time the system boot.

Now, we need to initialize the database by running:

$ sudo /usr/local/etc/rc.d/postgresql initdb

The system will respond with something like below:

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 locales
  COLLATE:  C
  CTYPE:    C.UTF-8
  MESSAGES: C.UTF-8
  MONETARY: C.UTF-8
  NUMERIC:  C.UTF-8
  TIME:     C.UTF-8
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /var/db/postgres/data14 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Jakarta
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/data14 -l logfile start

Start the service

Finally, to start PostgreSQL service, run this command:

$ sudo /usr/local/etc/rc.d/postgresql start

Verify PostgreSQL Installation

Let's verify that we've installed PostgreSQL correctly. First we'll check if the service is running. Then we can check what version of PostgreSQL server is running.

$ sudo service postgresql status

If your installation is correct, you should see something like below:

pg_ctl: server is running (PID: 1487)
/usr/local/bin/postgres "-D" "/var/db/postgres/data14"

We can use the psql tool to connect with the PostgreSQL database server and printing its version:

$ psql --version

The output will be something like this:

psql (PostgreSQL) 14.1

That’s it. You've successfully installed PostgreSQL 14.1 to your FreeBSD 13 system and you can start using it.

Configuring Postgres for Use

The postgres user

While PostgreSQL become installed, a system user account named postgres was also created with an identical user account in postgres. By default, the postgres user account isn't configured with a password, so it isn't viable to log into the server the use of the postgres user account without first creating a password for it. This postgres account has an all-access pass on your PostgreSQL database server, permission-wise. The postgres user account has similarities to the sa account in SQL server.

The postgres database

PostgreSQL is installed with a default database postgres. For the most part, we use the postgres database for administration functions, and create new databases on the PostgreSQL server to suit our needs.

The psql Command Line Utility

PostgreSQL consists of psql, a command line application for managing your databases and server. While a GUI-based software such as pgadmin4 is often less complicated to use in the daily task, the command line utilty psql is always handy. psql gives total control of your postgres system from the terminal, together with the ability to execute SQL queries.

We will use psql to perform our preliminary configuration and to create an initial database super user.

Create super user account

In this step we will super-user account to deals with our database in the daily task.

To do this, we will get access to the postgres account through your system root user. Then we'll use that postgres account to create a brand new super-user account for your PostgreSQL installation which can be regulated more efficiently. As an example we will use robert as our new PostgreSQL super-user account.

$ sudo su -
# su - postgres
$ psql
psql (14.1)
Type "help" for help.

postgres=# CREATE USER robert
postgres-# WITH SUPERUSER CREATEDB CREATEROLE
postgres-# PASSWORD 'youshouldknowwhattodohere';

Notice in the above we can enter multiple lines of command in SQL shell. The SQL is not executed until semi-colon followed enter is found. Which means, the semi-colon will make the shell execute entered commands. After pressing "Enter" it will respond with something like this:

CREATE ROLE
postgres=#

Which means we've successfully created this new superuser account.

Login using our newly created account

Let's verify that everything is working correctly. Try to log in with psql using our new super-user account and create a quick test database:

$ psql -h localhost -U robert postgres
Password for user robert: 
psql (14.1)
Type "help" for help.

postgres=#  

Note in the above terminal session, we specified the postgres default database when we logged in, since there aren’t yet any other databases to connect to.

Create test database

Next test is to create test database test_database using our new super-user account:

postgres=# CREATE DATABASE test_database WITH OWNER robert;
CREATE DATABASE
postgres=# \connect test_database;
You are now connected to database "test_database" as user "robert".
test_database=#

Final Words

I hope that you now know how to install PostgreSQL 14.1 on FreeBSD 13. If you run into any issues or have any feedback feel free to drop a comment below.