How to Install and Configure PostgreSQL on FreeBSD 13.2

Postgresql is an open source object-relational database system application with more than 30 years of active development and has earned a good reputation for reliability, feature durability, and performance. Postgres is one of the applications that emphasizes extensibility and SQL compliance.

PostgreSQL works based on the Postgres DBMS developed at the University of California, Berkeley. PostgreSQL ORDBMS supports most of the SQL standard features and the most modern technologies such as:
  • Complex Queries.
  • Foreign keys.
  • Triggers.
  • Representation.
  • Transactions.
  • Concurrency control with multiversioning.
PostgreSQL also allows users to add their own:
  • Data types
  • Functions
  • Operators
  • Aggregate functions
  • Indexing Methods
  • Programming languages
PostgreSQL was originally named POSTGRES, a reference to its origins as a successor to the Ingres database. The PostgreSQL program can also be used as a primary data store or data warehouse for many web, mobile, geospatial, and analytical applications. PostgreSQL can store structured and unstructured data in one product.

In this article we try to install PostgreSQL on a FreeBSD 13.2 server. The PostgreSQL version used is postgresql15.


1. PostgreSQL installation

On a FreeBSD system, installing PostgreSQL is very easy, the process is almost the same as installing other programs that run on a FreeBSD system. The following is how to install PostgreSQL client and server.

root@ns1:~ # cd /usr/ports/databases/postgresql15-client
root@ns1:/usr/ports/databases/postgresql15-client #
make install clean
root@ns1:~ #
cd /usr/ports/databases/postgresql15-server
root@ns1:/usr/ports/databases/postgresql15-server #
make install clean




The image above is some of the options that appear during the PostgreSQL installation process. This option can be activated or deactivated, depending on the needs of each user.


2. PostgreSQL configuration

After the installation process is complete, to activate PostgreSQL we have to edit the /etc/rc.conf file. Type the script below in the /etc/rc.conf file.

root@ns1:~ # ee /etc/rc.conf
postgresql_enable="YES"
postgresql_class="postgres"
postgresql_data="/var/db/postgres/data15"
postgresql_flags="-w -s -m fast"
postgresql_initdb_flags="--encoding=utf-8 --lc-collate=C"
postgresql_login_class="default"
postgresql_profiles=""

After that, we continue by editing the /etc/login.conf file, and enter the script below.

root@ns1:~ # ee /etc/login.conf
postgres:\
:lang=en_US.UTF-8:\
:setenv=LC_COLLATE=C:\
:tc=default:

Then initialize the PostgresQL database. Type the following command to initialize the PostgresQL database.

root@ns1:~ # /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 this locale configuration:
provider: libc
LC_COLLATE: C
LC_CTYPE: C.UTF-8
LC_MESSAGES: C.UTF-8
LC_MONETARY: C.UTF-8
LC_NUMERIC: C.UTF-8
LC_TIME: C.UTF-8
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /var/db/postgres/data15 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Jakarta
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: 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/data15 -l logfile start

After the PostgresQL database is initialized, continue with the following command, but this command can only run on the Postgres user, so you must be active on the Postgres user. Follow the steps below.

root@ns1:~ # su postgres
$
cd /var/db/postgres
$
/usr/local/bin/pg_ctl -D /var/db/postgres/data15 -l logfile restart
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started

$
exit
root@ns1:~ #

The explanation of the script above is, in the first script you log in as the Postgres user, the second script goes to the cd /var/db/postgres folder and the second script restarts the logfile while the last script is logged out as the Postgres user.

After exiting the Postgres user, edit the file /var/db/postgres/data13/postgresql.conf, and enter the script below.

root@ns1:~ # ee /var/db/postgres/data15/postgresql.conf
listen_addresses = '192.168.5.2' # what IP address(es) to listen on;
port = 5432

The IP for listen_addresses is adjusted to the Private IP of the FreeBSD server, because in writing this article the Private IP is used 192.168.5.2, so listen_addresses = '192.168.5.2' and the default PostgreSQL port is port 5432.

Then we also edit the /var/db/postgres/data13/pg_hba.conf file, and change the host script with the IP address that matches the FreeBSD server, here is an example of the script.

root@ns1:~ # ee /var/db/postgres/data15/pg_hba.conf
host all all 192.168.5.2/24 trust

Adjust the IP address with the script above. Then we restart the PostgresQL database program.

root@ns1:~ # service postgresql restart
2023-08-21 13:43:37.306 WIB [1439] LOG: ending log output to stderr
2023-08-21 13:43:37.306 WIB [1439] HINT: Future log output will go to log destination "syslog".


root@ns1:~ #
service postgresql status
pg_ctl: server is running (PID: 1439)
/usr/local/bin/postgres "-D" "/var/db/postgres/data15"

If there is nothing wrong with the above script settings and the PostgresQL database program should be able to run on FreeBSD.


3. Create a postgres password

By default, when the postgresql database installation process is complete, postgresql will automatically create a database with the name "postgres", and the user for the "postgres" database is named "postgres". The user and database "postgres" have not been given a password. Here's how to give a password to a Postgres user.

root@ns1:~ # passwd postgres
Changing local password for postgres
New Password:
router
Retype New Password:
router

Or you can also create a Postgres password in another way, like the example below.

root@ns1:~ # su - postgres
$
psql -c "alter user postgres with password 'router'"
ALTER ROLE
$

In the example script above we have created a Postgres password with the name "router".


4. How to Use PostgreSQL15 Database

After the Postgres user has been given a password, now we try to create a table in the "Postgres" database. The following is an example of creating a table in the "postgres" database.

root@ns1:~ # su - postgres
$
psql postgres
psql (15.3)
Type "help" for help.


postgres=#
CREATE TABLE person (
person_id BIGINT,
name VARCHAR(255),
age INT,
city VARCHAR(255)
);

CREATE TABLE
postgres=#
INSERT INTO person VALUES (1001, 'M. Jaka Setiawan', 9, 'Tegal');
INSERT 0 1
postgres=#
INSERT INTO person VALUES (1002, 'Iwan Setiawan', 42, 'Bekasi');
INSERT 0 1
postgres=#
INSERT INTO person VALUES (1003, 'Siti Umaroh', 41, 'Tegal');
INSERT 0 1
postgres=#
INSERT INTO person VALUES (1004, 'Kanaka Robih Setiawan', 11, 'Bekasi');
INSERT 0 1
postgres=#
select * from person;
person_id | name | age | city
-----------+-----------------------+-----+--------
1001 | M. Jaka Setiawan | 9 | Tegal
1002 | Iwan Setiawan | 42 | Bekasi
1003 | Siti Umaroh | 41 | Tegal
1004 | Kanaka Robih Setiawan | 11 | Bekasi
(4 rows)


postgres=#

Now we try again by creating a database, user, table and password. Let's give an example by creating a user "puncakanjani" database with the name "gunungrinjani". Follow the steps below.

root@ns1:~ # su - postgres
$
createuser gunungrinjani
$
createdb puncakanjani -O gunungrinjani
$
psql puncakanjani
psql (15.3)
Type "help" for help.


puncakanjani=#

The description of the script above is.
1. The first script "su - postgres" enters the user "postgres"
2. 2nd script, create a user with the name Gunungrinjani
3. 3rd script, creates a Puncakanjani database for the Gunungrinjani user
4. 4th script, psql is entered into the user Gunungrinjani
5. The Puncakanjani script=# means it is active in the Puncakanjani database

The next step is to create a password for the Gunungrinjani user.

puncakanjani=# alter user gunungrinjani with password 'sembalun';
ALTER ROLE

The command above is to create a password for the Gunungrinjani user with the password "sembalun". After the password has been successfully created, now we practice how to create a table. Here's an example.

puncakanjani=# create table tabelgunung ( id int,first_name text, last_name text );
CREATE TABLE
puncakanjani=#
insert into tabelgunung (id,first_name,last_name) values (1,'gunung','argopuro');
INSERT 0 1
puncakanjani=#
insert into tabelgunung (id,first_name,last_name) values (1,'gunung','rinjani');
INSERT 0 1
puncakanjani=#
insert into tabelgunung (id,first_name,last_name) values (1,'gunung','raung');
INSERT 0 1

Look at the table we have created above.

puncakanjani=# select * from tabelgunung;
id | first_name | last_name
----+------------+-----------
1 | gunung | argopuro
1 | gunung | rinjani
1 | gunung | raung
(3 rows)


puncakanjani=#

Delete tabelgunung.

puncakanjani=# DROP TABLE tabelgunung;
DROP TABLE

Delete database puncakanjani.

puncakanjani=# exit
$
dropdb puncakanjani

Delete user  gunungrinjani.

$ dropuser gunungrinjani

PostgreSQL is a relational database management system that is powerful, rich in features and widely used in various applications. PostgreSQL is an attractive choice for many companies, including giants like Apple, Netflix, Instagram, IMDB, and others.


According to statistical data, in 2023 PostgreSQL ranks fourth in the world among DBMS in terms of popularity, behind Oracle, MySQL and Microsoft SQL server. Additionally, PostgreSQL is the most popular open source DBMS.
Iwan Setiawan

I Like Adventure: Mahameru Mount, Rinjani Mount I Like Writer FreeBSD

Post a Comment

Previous Post Next Post