Project

General

Profile

Support #606

Install PostgreSQL 9.4 on FreeBSD

Added by Daniel Curtis over 9 years ago. Updated over 8 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Daniel Curtis
Category:
Database Server
Target version:
FreeBSD 9
Start date:
04/27/2015
Due date:
% Done:

100%

Estimated time:
1.00 h
Spent time:
2.50 h

Description

This is a guide for installing PostgreSQL 9.4 on FreeBSD 9.3

Setting up the Environment

  • Start by making sure everything is up to date:
    pkg update && pkg upgrade
portsnap fetch extract
  • Install portmaster:
    cd /usr/ports/ports-mgmt/portmaster
make install clean
pkg2ng

Install PostgreSQL 9.4

  • Install PostgreSQL:
    portmaster databases/postgresql94-server
  • Enable PostgreSQL at boot:
    echo 'postgresql_enable="YES"' >> /etc/rc.conf
  • Initialize the database:
    service postgresql initdb
  • Start PostgreSQL:
    service postgresql start
  • Edit the postgres config file:
    vi /usr/local/pgsql/data/postgresql.conf
    • And modify the following:
      listen_addresses = '*'
  • Edit the pg_hba config file:
    vi /usr/local/pgsql/data/pg_hba.conf
    • And modify the following:
      # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD only

# Local connections
local   all         all                               trust

# IPv4 local connections:
host    all         all         127.0.0.1/32          trust

# IPv6 local connections:
host    all         all         ::1/128               trust

# IPv4 network connections:
host    all         all         192.168.10.0/24       md5

# IPv6 network connections:
host    all         all         1234::abcd/64         md5
  • Restart postgresql:
    service postgresql restart

Set Administrator Password

  • Log into the database as the pgsql user:
    psql -U pgsql template1
  • And set the pgsql password:
    ALTER USER pgsql with password 'SuperSecretPassword';

\q

Create a new user and database

  • Switch to the pgsql user:
    su pgsql
    • Create the somepgdb database:
      createdb somepgdb
    • Then create the somepguser user:
      createuser -Pl --interactive somepguser
    • Grant all privileges to somepgdb to somepguser:
      psql template1
GRANT ALL PRIVILEGES ON DATABASE "somepgdb" to somepguser;
\q
  • Exit from the postgres user
    exit
  • Test the connection on a remote host:
    psql -h pg.example.com -U somepguser -W somepgdb

Running PostgreSQL in a Jail

PostgreSQL requires SysVIPC to be enabled to run properly.

  • Enable SystemV IPC
    echo 'security.jail.sysvipc_allowed=1' >> /etc/sysctl.conf
  • Edit the ezjail config for the jail:
    vi /usr/local/etc/ezjail/jail_example_com
    • And modify the jail parameters to add the allow.sysvipc=1 configuration:
      export jail_jail_example_com_parameters="allow.sysvipc=1"
  • Restart the jail:
    ezjail-admin restart jail.example.com

Resources

Also available in: Atom PDF