How to Install PostgreSQL in FreeBSD

To Install PostgreSQL in FreeBSD

PostgreSQL is an open source relational database management system (DBMS). It can manage workloads of large internet facing applications with several concurrent users. It is mainly used for secure data storage. The postgreSQL supports transactions, subselects, triggers, views and sophisticated locking. Installation of PostgreSQL in FreeBSD is explained in this article.

Installation of PostgreSQL

Before installing the PostgreSQL, run the following command to update the mirrors.

root@linuxhelp:~ # freebsd-update fetch
Looking up update.FreeBSD.org mirrors... 4 mirrors found.
Fetching public key from update5.freebsd.org... done.
Fetching metadata signature for 10.3-RELEASE from update5.freebsd.org... done.
Fetching metadata index... done.
Fetching 2 metadata files... done.
Inspecting system... done.
Preparing to download files... done.
Fetching 379 patches.....10....20....30....40....50....60....70....80....90....100....110....120....130....140....150....160....170....180....190....200....210....220....230....240....250....260....270....280....290....300....310....320....330....340....350....360....370.... done.
Applying patches... done.
Fetching 9 files... done.

The following files will be added as part of updating to 10.3-RELEASE-p12:
/usr/src/contrib/libarchive/libarchive/test/test_write_disk_secure744.c
/usr/src/contrib/libarchive/libarchive/test/test_write_disk_secure745.c
/usr/src/contrib/libarchive/libarchive/test/test_write_disk_secure746.c
/usr/src/contrib/ntp/README.pullrequests
/usr/src/contrib/ntp/lib/isc/tsmemcmp.c

Then install the updated packages with the " update install" command.

root@linuxhelp:~ # freebsd-update install
Installing updates... done.

Now its time to install the PostgreSQL server and client.

root@linuxhelp:~ # pkg install postgresql93-server postgresql93-client
Updating FreeBSD repository catalogue...
Fetching meta.txz: 100%    944 B   0.9kB/s    00:01    
Fetching packagesite.txz: 100%    5 MiB 213.6kB/s    00:27    
Processing entries: 100%
New packages to be INSTALLED:
    postgresql93-server: 9.3.14_1
    postgresql93-client: 9.3.14_1

Number of packages to be installed: 2

The process will require 25 MiB more space.
5 MiB to be downloaded.

Proceed with this action? [y/N]: y
Fetching postgresql93-server-9.3.14_1.txz: 100%    3 MiB 150.7kB/s    00:22    
Fetching postgresql93-client-9.3.14_1.txz: 100%    2 MiB 107.7kB/s    00:19    
Checking integrity... done (0 conflicting)
[1/2] Installing postgresql93-client-9.3.14_1...
[1/2] Extracting postgresql93-client-9.3.14_1: 100%
[2/2] Installing postgresql93-server-9.3.14_1...
===>  Creating groups.
Creating group ' pgsql'  with gid ' 70' .
===>  Creating users
Creating user ' pgsql'  with uid ' 70' .

.
.
.
.
To set limits, environment stuff like locale and collation and other
things, you can set up a class in /etc/login.conf before initializing
the database. Add something similar to this to /etc/login.conf:
---
postgres:
    :lang=en_US.UTF-8:
    :setenv=LC_COLLATE=C:
    :tc=default:
---
and run `cap_mkdb /etc/login.conf' .
Then add ' postgresql_class=" postgres" '  to /etc/rc.conf.

======================================================================

To initialize the database, run

  /usr/local/etc/rc.d/postgresql initdb

You can then start PostgreSQL by running:

  /usr/local/etc/rc.d/postgresql start

For postmaster settings, see ~pgsql/data/postgresql.conf

NB. FreeBSD' s PostgreSQL port logs to syslog by default
    See ~pgsql/data/postgresql.conf for more info

======================================================================

To run PostgreSQL at startup, add
' postgresql_enable=" YES" '  to /etc/rc.conf

Next add the postgreSQL service to rc.conf file.

root@linuxhelp:~ # sysrc postgresql_enable=" YES" 
postgresql_enable:  ->  YES

Then initialize the postgreSQL server with the following command.

root@linuxhelp:~ # service postgresql initdb
The files belonging to this database system will be owned by user " pgsql" .
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 /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
creating configuration files ... ok
creating template1 database in /usr/local/pgsql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects'  descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... 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/postgres -D /usr/local/pgsql/data
or
    /usr/local/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

Once the above process gets completed, configure the postgreSQL server as shown below,

root@linuxhelp:~ # nano -c /usr/local/pgsql/data/postgresql.conf
.
.
.
listen_addresses = ' *' 
.
.
.
port = 5432
.
.

Done!

To Start PostgreSQL Service

Run the following command to start the postgresSQL service.

root@linuxhelp:~ # service postgresql start
LOG:  ending log output to stderr
HINT:  Future log output will go to log destination " syslog" .

Here you need to set user password for postgreSQL user.

root@linuxhelp:~ # passwd pgsql
Changing local password for pgsql
New Password:
Retype New Password:

Then utilize the " su" command to run the postgresql in command prompt.

Create the user and set password to create database.

root@linuxhelp:~ # su pgsql

$ createuser -sdrP linuxhelpuser
Enter password for new role:
Enter it again:
$ createdb -O linuxhelpuser linuxhelpdb
$ psql linuxhelpdb
psql (9.3.14)
Type " help"  for help.

linuxhelpdb=# list
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------+---------------+----------+---------+-------+-------------------
linuxhelpdb | linuxhelpuser | UTF8 | C | C |
postgres | pgsql | UTF8 | C | C |
template0 | pgsql | UTF8 | C | C | =c/pgsql +
| | | | | pgsql=CTc/pgsql
template1 | pgsql | UTF8 | C | C | =c/pgsql +
| | | | | pgsql=CTc/pgsql
(4 rows)