Support #606: Install PostgreSQL 9.4 on FreeBSD - FreeBSD Administration - ALT Project Management

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

Table of contents
Setting up the Environment
Install PostgreSQL 9.4
- Set Administrator Password
- Create a new user and database
Running PostgreSQL in a Jail
Resources

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" 
```