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
Login to test_db database:
# 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.