# How to Install PostgreSQL on FreeBSD

> URL: https://www.atlantic.net/dedicated-server-hosting/how-to-install-postgresql-on-freebsd/ | Published: 2023-10-15 | Updated: 2024-06-01 | Author: Hitesh Jethva

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](https://www.atlantic.net/dedicated-server-hosting/) from Atlantic.Net!