How to install PostgreSQL 11 on FreeBSD 12

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 can be used to manage large data sets. It has features to ensure fault tolerance and data integrity. Check the PostgreSQL 11 version page for new features.

PostgreSQL database servers can range from serving small websites to distributed and complex applications. It supports all RDBMS functions and additional support for object-oriented database models.

Install PostgreSQL 11 on FreeBSD 12

I want to start installing the FreeBSD 12 server and run it over an Internet connection. The installer should also be run as root or as a user with sudo privileges.

Step 1: Update all package repository directories

The first step in business is to update the available remote repository catalog.

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

If you want to upgrade the package to a newer version available in the repository, run:

sudo pkg upgrade

Step 2: Install PostgreSQL 11 on FreeBSD 12

Download and install the PostgreSQL server and client packages using the following command pkg Package manager.

sudo pkg install postgresql11-server postgresql11-client

After installation, start and enable the PostgreSQL service to start at 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 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 remote connections

By default, the PostgreSQL database server is accessed only from the local host.

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

To enable remote connections, install the vim text editor to edit the configuration file.

sudo pkg install vim

Open file /var/db/postgres/data11/postgresql.conf And scroll down to Connect and authenticate Sections are around 54 rows.

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

Uncomment listen_address Side by side as shown below.

listen_addresses = '*'

Wilka * Tells the PostregreSQL service to listen on all interfaces. But you can limit it to a specific IP address.

listen_addresses = '192.168.1.20'

Restart the 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 the PostgreSQL administrator password

When installing a PostgreSQL server, postgres users and groups are created by default. You need to reset the user’s password to a password you can remember.

# passwd  postgresChanging local password for postgresNew Password:Retype New Password:

You can also use

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

Step 5: Test the PostgreSQL 11 database functionality

Add a test database user:

su - postgrescreateuser test_dbuser

Grant creation user ownership test_db:

createdb test_db -O test_dbuser

# psql test_db psql (11.1) Type "help" for helptest_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 TABLEtest_db=# insert into test_table (id,first_name,last_name) values (1,'John','Doe'); INSERT 0 1

Show tabular data

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

Drop test list

test_db=# DROP TABLE test_table;DROP TABLE

Delete test database

$ dropdb test_db;

Step 6: Install pgAdmin

pgAdmin makes it easy to manage your PostgreSQL database server. Install using the guide at the following link:

How to install pgAdmin4 on FreeBSD 12

That’s it for today. Like to use PostgreSQL 11 and pgAdmin on FreeBSD 12.