Learn how to - How To Install PostgreSQL 11 on FreeBSD 12

Felix Williams Databases FreeBSD How to March 21, 2023

Learn how to – 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 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;

$ sudo /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

$ sudo /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

$ sudo 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

$ sudo 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.

$ sudo passwd  postgres
Changing local password for postgres
New Password:
Retype New Password:

You can also use

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

Step 5: Test PostgreSQL 11 database functionality

Add a test database user:

createuser test_dbuser

Grant created user an ownership to test_db:

createdb test_db -O test_dbuser

# 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

test_db=# exit
$ dropdb test_db;
$ exit

Step 6: Install pgAdmin on FreeBSD

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.

Cart

Learn how to – How To Install PostgreSQL 11 on FreeBSD 12