Support #606
Updated by Daniel Curtis over 8 years ago
{{>toc}}
This is a guide for installing PostgreSQL 9.4 on FreeBSD 9.3
h2. Setting up the Environment
* Start by making sure everything is up to date:
<pre>
pkg update && pkg upgrade
portsnap fetch extract
</pre>
* Install portmaster:
<pre>
cd /usr/ports/ports-mgmt/portmaster
make install clean
pkg2ng
</pre>
h2. Install PostgreSQL 9.4
* Install PostgreSQL:
<pre>
portmaster databases/postgresql94-server
</pre>
* Enable PostgreSQL at boot:
<pre>
echo 'postgresql_enable="YES"' >> /etc/rc.conf
</pre>
* Initialize the database:
<pre>
service postgresql initdb
</pre>
* Start PostgreSQL:
<pre>
service postgresql start
</pre>
* Edit the postgres config file:
<pre>
vi /usr/local/pgsql/data/postgresql.conf
</pre>
#* And modify the following:
<pre>
listen_addresses = '*'
</pre>
* Edit the pg_hba config file:
<pre>
vi /usr/local/pgsql/data/pg_hba.conf
</pre>
#* And modify the following:
<pre>
# 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
</pre>
* Restart postgresql:
<pre>
service postgresql restart
</pre>
h3. Create a new user and database
* Switch to the pgsql user:
<pre>
su pgsql
</pre>
#* Create the somepgdb database:
<pre>
createdb somepgdb
</pre>
#* Then create the somepguser user:
<pre>
createuser -Pl --interactive somepguser -P
</pre>
#* Grant all privileges to somepgdb to somepguser:
<pre>
psql template1
GRANT ALL PRIVILEGES ON DATABASE "somepgdb" to somepguser;
\q
</pre>
* Exit from the postgres user
<pre>
exit
</pre>
* Test the connection on a remote host:
<pre>
psql -h pg.example.com -U somepguser -W somepgdb
</pre>
h2. Running PostgreSQL in a Jail
PostgreSQL requires SysVIPC to be enabled to run properly.
* Enable SystemV IPC
<pre>
echo 'security.jail.sysvipc_allowed=1' >> /etc/sysctl.conf
</pre>
* Edit the ezjail config for the jail:
<pre>
vi /usr/local/etc/ezjail/jail_example_com
</pre>
#* And modify the jail parameters to add the allow.sysvipc=1 configuration:
<pre>
export jail_jail_example_com_parameters="allow.sysvipc=1"
</pre>
* Restart the jail:
<pre>
ezjail-admin restart jail.example.com
</pre>
h2. Resources
* http://www.freebsddiary.org/postgresql.php
* https://jasonk2600.wordpress.com/2010/01/11/installing-postgresql-on-freebsd/