PostgreSQL or Postgres is a powerful object-relational high-performance database management system (ORDBMS) published under a flexible BSD-style license. PostgreSQL is well suited for large databases and has many advanced features.
In this tutorial, I will show you how to install and configure a PostgreSQL database server on FreeBSD. We will install the latest version of PostgreSQL 11 on the FreeBSD 12.0 system.
For this guide, we will use FreeBSD 12 with 2 GB of RAM memory and 2 CPUs. If you have a large deployment, you will need more than that. You will also need the root privileges for package installation.
Firstly, we will update the packages repositories and upgrade all
packages to the latest version using the pkg package management tool for FreeBSD.
pkg update
pkg upgrade
In this step, we're going to install the latest stable version PostgreSQL 11. By default, the FreeBSD repository provides multiple versions of PostgreSQL package. You can use the following command to check all available version of PostgreSQL packages.
pkg search postgresql
You will get multiple versions of PostgreSQL database server.
Now install the PostgreSQL 11 package using the command below.
pkg install postgresql11-server postgresql11-client
Next, we need to add the PostgreSQL service to the system boot and initialize the database before starting the service. Add the PostgreSQL to the system boot using the command below.
sysrc postgresql_enable=yes
Now initialize the PostgreSQL database, and then start the service, using the following commands
/usr/local/etc/rc.d/postgresql initdb
service postgresql start
service postgresql status
Verify the output of the last command gives a similar output:
root@bsdstation:/home/joe # service postgresql status
pg_ctl: server is running (PID: 1122)
/usr/local/bin/postgres "-D" "/var/db/postgres/data11"
The PostgreSQL service is up and running on FreeBSD 12.0.
Additionally you can check the system port used by the PostgreSQL
service using the sockstat command below. You should get the port 5432 is used by the PostgreSQL service.
sockstat -l4 -P tcp
In this step, we're going to set up the authentication method for
PostgreSQL. PostgreSQL supports different authentication methods such as
trust authentication (default), password-based authentication, Kerberos,
GSSAPI, LDAP, RADIUS, and PAM. For this guide, we're going to set up the
password-based authentication using MD5. Go to the /var/db/postgresql/data11 directory, edit the
pg_hba.conf file using a text editor.
cd /var/db/postgres/data11
vi pg_hba.conf
Now change the authentication method for all local connection to
md5 as below, and add a line to allow
external access to the database if needed.
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# External connections
host all all 192.168.66.1/32 md5
Save and close the file, and then restart the PostgreSQL service,
enabling the password-based authentication using md5 for the PostgreSQL server.
service postgresql restart
In this step, we're going to set up a new user and database on
PostgreSQL. We're going to create a new password for default user postgres, and create a new user and database.
Log in to the 'postgres' user using the command below.
su - postgres
Now login to the interactive PostgreSQL shell psql.
psql
Then create a new password for the postgres user.
\password postgres
TYPE THE PASSWORD
Next, we will create a new user called joe with the database joe_db. And the give privileges for the user to
the database by running the following queries.
CREATE DATABASE joe_db;
CREATE USER joe WITH ENCRYPTED PASSWORD 'password123#';
GRANT ALL PRIVILEGES ON DATABASE joe_db TO joe;
You can now exit from the PostgreSQL interactive shell with \q. As a result, the password for the default
postgres user has been created. And the
new user and database have been set up.
Log in to the postgres user and then
run the psql command to get into the
PostgreSQL interactive shell.
su - postgres
psql
Show list users and database on the PostgreSQL server using the
following queries. You will get the new user joe and the database joe_db on the result.
\du
\l
Type \q to exit from the psql shell.
Next, we will log in using the created user joe to the database joe_db using the command below.
psql -U joe -d joe_db -W
Type the joe password to continue. Now
we will proceed to create a new table user_table and insert some data into it.
CREATE TABLE user_table (id INT, name TEXT, site TEXT);
INSERT INTO user_table (id, name, site) VALUES ( 1 , 'Joe the Man', 'jjbigorra.netlify.app');
Show content of tables using the following query.
SELECT * FROM user_table;
Finally, the installation and configuration of PostgreSQL 11 on the FreeBSD 12.0 system has been completed successfully.