How to Install PostgreSQL on FreeBSD

PostgreSQL is an open-source, high-performance database management system suitable for large databases. It is used as a data store for mobile, geospatial, web, and analytics applications. It is cross-platform, offers complex data types, and supports Java, Python, Perl, Ruby, Go, C, C#, and many more languages. It can handle complex workloads while maintaining data integrity and reliability.

Step 1 – Install PostgreSQL

By default, the PostgreSQL package is included in the FreeBSD official repository. You can search all available PostgreSQL packages using the following command.

pkg search postgresql

You will see the following list:

postgresql15-server-15.4       PostgreSQL is the most advanced open-source database available anywhere
postgresql15-tds_fdw-2.0.3     PostgreSQL foreign data wrapper to connect to TDS databases
postgresql15-zhparser-2.2      PostgreSQL extension for full-text search of Chinese
postgresql16-client-16.0       PostgreSQL database (client)
postgresql16-contrib-16.0      The contrib utilities from the PostgreSQL distribution
postgresql16-docs-16.0         The PostgreSQL documentation set
postgresql16-plperl-16.0       Write SQL functions for PostgreSQL using Perl5
postgresql16-plpython-16.0     Module for using Python to write SQL functions
postgresql16-pltcl-16.0        Module for using Tcl to write SQL functions
postgresql16-server-16.0       PostgreSQL is the most advanced open-source database available anywhere
prometheus-postgresql-adapter-0.6.0_14 Use PostgreSQL as a remote storage database for Prometheus
py39-postgresql-1.3.0          Python 3 compatible PostgreSQL database driver and tools
rubygem-azure_mgmt_postgresql-0.17.2 Microsoft Azure PostgreSQL Client Library for Ruby

Now, install the PostgreSQL server and client package using the following command:

pkg install postgresql16-server postgresql16-client nano-7.2

Once both packages are installed, enable the PostgreSQL service to start at system reboot.

sysrc postgresql_enable=yes

Next, initialize the PostgreSQL database with the following command:

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

Output:

creating directory /var/db/postgres/data16 ... 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

Next, start the PostgreSQL service.

service postgresql start

You can verify the PostgreSQL service status using the following command:

service postgresql status

By default, PostgreSQL listens on port 5432. You can check it with the following command:

sockstat -l4 -P tcp

Output:

USER     COMMAND    PID   FD PROTO  LOCAL ADDRESS         FOREIGN ADDRESS      
postgres postgres   2870  7  tcp4   127.0.0.1:5432        *:*

Step 2 – Configure PostgreSQL Authentication

By default, PostgreSQL supports different authentication methods such as md5, RADIUS, PAM, LDAP, and more. In this section, we will set up the password-based authentication using MD5.

First, edit the PostgreSQL configuration file.

nano /var/db/postgres/data16/pg_hba.conf

Find and change the following lines from trust to md5:

# 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

Save and close the file, then restart the PostgreSQL service to apply the changes.

service postgresql restart

Step 3 – Create a User and Database in PostgreSQL

First, connect to the Postgres with the following command:

su - postgres
psql

Next, set the password for the default postgres user.

\password postgres

Next, create a new database named testdb and a user named testuser.

create database testdb;
create user testuser with encrypted password 'password';

Next, grant all the privileges on testdb database to testuser.

grant all privileges on database testdb to testuser;

Next, verify your created users using the following command:

\du

Output:

                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 testuser  | 

Next, create a table named mytable with the following command:

create table mytable (id int, name text, site text);

Next, insert some data into the table.

insert into mytable (id,name,site) values (1,'myserver','atlantic.net');

Next, retrieve the data from your table.

select * from mytable;

Output:

 id |   name   |     site     
----+----------+--------------
  1 | myserver | atlantic.net
(1 row)

You can now exit from the PostgreSQL shell with the following command.

\q

Conclusion

In this post, we explained how to install PostgreSQL on FreeBSD. We also showed you how to create a database, user, and table in PostgreSQL. You can now easily use PostgreSQL as a database backend for your application. Try deploying a PostgreSQL server on dedicated server hosting from Atlantic.Net!