Project

General

Profile

Support #606

Install PostgreSQL 9.4 on FreeBSD

Added by Daniel Curtis almost 9 years ago. Updated over 7 years ago.

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

100%

Estimated time:
1.00 h
Spent time:

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

#1

Updated by Daniel Curtis almost 9 years ago

  • Description updated (diff)
  • Status changed from New to Resolved
  • % Done changed from 0 to 100
#2

Updated by Daniel Curtis almost 9 years ago

  • Status changed from Resolved to Closed
#3

Updated by Daniel Curtis almost 9 years ago

  • Description updated (diff)
#4

Updated by Daniel Curtis over 8 years ago

  • Description updated (diff)
#5

Updated by Daniel Curtis over 8 years ago

  • Description updated (diff)
#6

Updated by Daniel Curtis over 8 years ago

  • Description updated (diff)
#7

Updated by Daniel Curtis almost 8 years ago

  • Description updated (diff)
#8

Updated by Daniel Curtis almost 8 years ago

  • Description updated (diff)
#9

Updated by Daniel Curtis over 7 years ago

  • Description updated (diff)

Also available in: Atom PDF