Welcome to today’s article on how to install PostgreSQL 11 on FreeBSD 12. PostgreSQL is the most advanced open-source object-relational database system that allows you to manage extensive datasets. It comes with features that guarantee fault-tolerance and data integrity. Check PostgreSQL 11 release page for the new features.

The application of PostgreSQL database server can range from serving small websites to distributed and complex applications. It has support for all the RDBMS features and additional support for object-oriented database models.

Install PostgreSQL 11 on FreeBSD 12

Before you can start the installation, I expect you to have FreeBSD 12 server up and running with internet connection.You should also run the setup as root user or user with sudo privileges.

Step 1: Update all package repository catalogues

The first action of the business is updating the available remote repositories catalogues.

$ sudo pkg update
Updating FreeBSD repository catalogue…
FreeBSD repository is up to date.
All repositories are up to date.

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

sudo pkg upgrade

Step 2: Installing PostgreSQL 11 on FreeBSD 12

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

sudo pkg install postgresql11-server postgresql11-client

After the installation, start and enable PostgreSQL service to start on system boot.

sudo sysrc postgresql_enable=yes

Then initialize the database by running;

# /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/data11 … ok
creating subdirectories … ok
selecting default max_connections … 100
selecting default shared_buffers … 128MB
selecting dynamic shared memory implementation … posix
creating configuration files … ok
running bootstrap script … ok
performing post-bootstrap initialization … ok
syncing data to disk … ok
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/data11 -l logfile start

Start the service

# /usr/local/etc/rc.d/postgresql start
2019-02-01 21:45:15.425 UTC [1586] LOG: listening on IPv6 address "::1", port 5432
2019-02-01 21:45:15.426 UTC [1586] LOG: listening on IPv4 address "127.0.0.1", port 5432
2019-02-01 21:45:15.430 UTC [1586] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-02-01 21:45:15.436 UTC [1586] LOG: ending log output to stderr
2019-02-01 21:45:15.436 UTC [1586] HINT: Future log output will go to log destination "syslog".

Step 3: Allow for remote connections

By default, access to PostgreSQL database server is only from localhost.

# sockstat -4 -6 | grep 5432
postgres postgres 1586 3 tcp6 ::1:5432 :
postgres postgres 1586 5 tcp4 127.0.0.1:5432 :

To enable remote connections, install vim text editor for editing the configuration file.

sudo pkg install vim

Open the file /var/db/postgres/data11/postgresql.conf and scroll down to the CONNECTIONS AND AUTHENTICATION section, around line 54.

sudo vim /var/db/postgres/data11/postgresql.conf

Uncomment the listen_address and line and change to look like below.

listen_addresses = '*'

The wilcard * tells PostregreSQL service to listen on all interfaces. But you can limit to specific IP address.

listen_addresses = '192.168.1.20'

Restart PostgreSQL service

#  service postgresql restart
2019-02-02 05:37:14.791 UTC [2649] LOG: listening on IPv6 address "::", port 5432
2019-02-02 05:37:14.792 UTC [2649] LOG: listening on IPv4 address "0.0.0.0", port 5432
2019-02-02 05:37:14.797 UTC [2649] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-02-02 05:37:14.821 UTC [2649] LOG: ending log output to stderr
2019-02-02 05:37:14.821 UTC [2649] HINT: Future log output will go to log destination "syslog".

The service should now be bound to all network interfaces

# sockstat -4 -6 | grep 5432
postgres postgres 2649 3 tcp6 *:5432 *:*
postgres postgres 2649 5 tcp4 *:5432 *:*

Step 4:  Set PostgreSQL admin password

postgres user and group is created by default when you install PostgreSQL server. You’ll need to reset the password for this user to one you can remember.

# passwd  postgres
Changing local password for postgres
New Password:
Retype New Password:

You can also use

$ su - postgres
$ psql -c "alter user postgres with password 'StrongPassword'"
ALTER ROLE

Step 5:  Test PostgreSQL 11 database functionality

Add a test database user:

su - postgres
createuser test_dbuser

Grant created user an ownership to test_db:

createdb test_db -O test_dbuser

Login to test_db database:

# psql test_db
psql (11.1)
Type "help" for help
test_db=#

Set user password:

test_db=# alter user test_dbuser with password 'MyDBpassword';
ALTER ROLE

Create a table and add some dummy data.

test_db=# create table test_table ( id int,first_name text, last_name text ); 
CREATE TABLE
test_db=# insert into test_table (id,first_name,last_name) values (1,'John','Doe');
INSERT 0 1

Show table data

test_db=#  select * from test_table;
id | first_name | last_name
----+------------+-----------
1 | John | Doe
(1 row)

Drop the test table

test_db=# DROP TABLE test_table;
DROP TABLE

Drop the test database

$ dropdb test_db;

Step 6:  Install pgAdmin

pgAdmin makes it easy for you to manage your PostgreSQL database server. Install it using our guide in the link below:

How to Install pgAdmin4 on FreeBSD 12

That’s it for today. Enjoy using PostgreSQL 11 and pgAdmin on FreeBSD 12.