PostgreSQL On FreeBSD

Installing

Install databases/postgresql92-{server,client,doc,contrib}.

In /etc/boot.loader, raise these values:

kern.ipc.semmni=128
kern.ipc.semmns=4096
kern.ipc.semmsl=4000

In /etc/sysctl.conf, raise these values:

kern.ipc.shmmax=2147483648
kern.maxfiles=1048576
kern.maxfilesperproc=1000000
kern.threads.max_threads_per_proc=16384

In rc.conf:

postgresql_enable="YES"
postgresql_data="/pgsql"

Or to run multiple instances on the same installation, use this and adjust appropriately below:

postgresql_enable="YES"
postgresql_profiles="prod beta demo dev"
postgresql_prod_data="/pgsql-prod"
postgresql_beta_data="/pgsql-beta"
postgresql_demo_data="/pgsql-demo"
postgresql_dev_data="/pgsql-dev"

Upgrading PostgreSQL

Since upgrading includes dump, restore, and initializing a fresh database, look here for those. This assumes that your data dir is in /pgsql; adjust as needed.

PostgreSQL project calls tenths releases "major". For example, 9.3->9.4 is a major upgrade. To upgrade to the next major release, you must dump the data, upgrade, initdb, and then restore the dump.

Dump:

• Stop everything that would record important data to the database.
• Run:

mkdir /tmp/pgbackup
cd /tmp/pgbackup
pg_dumpall -U pgsql --globals-only -f globals.pgsql
pg_dump -Fc --serializable-deferrable --quote-all-identifiers -U pgsql -f bacula.pgsql bacula
cp /pgsql/postgresql.conf /pgsql/pg_hba.conf .

Upgrade:

service postgresql stop
pkg upgrade

Initdb:

• Run:

rm -rf /pgsql
mkdir /pgsql
chown pgsql:pgsql /pgsql
su pgsql -c "initdb --encoding=UTF8 --data-checksums --auth=password --pwprompt --pgdata=/pgsql"

• Restore postgresql.conf and pg_hba.conf and update as needed; see PostgreSQL release notes.
• service postgresql start

Restore:

psql -U pgsql -d postgres -f globals.pgsql
pg_restore -C -U pgsql -d postgres bacula.pgsql

To add PostGIS

If upgrading, this should be done between immediately before pg_restore.

• Configure and install databases/postgis20.
• Run the following to have PostGIS hooks installed when creating new databases. Or switch template1 to database you want to add it to.

createlang -U pgsql plpgsql template1
psql -U pgsql -qd template1 -f /usr/local/share/postgresql/contrib/postgis-2.0/postgis.sql
psql -U pgsql -qd template1 -f /usr/local/share/postgresql/contrib/postgis-2.0/spatial_ref_sys.sql
psql -U pgsql -qd template1 -f /usr/local/share/postgresql/contrib/postgis-2.0/rtpostgis.sql
psql -U pgsql -qd template1 -f /usr/local/share/postgresql/contrib/postgis-2.0/legacy.sql
psql -U pgsql -qd template1 -f /usr/local/share/postgresql/contrib/postgis-2.0/rtpostgis_legacy.sql

To add a database and user

Most packaged apps work under a dedicated database and user. To create database 'foo' and user 'foo' with full access:

CREATE USER bacula WITH PASSWORD 'xyz';
CREATE DATABASE bacula [OWNER bacula] [ENCODING 'SQL_ASCII' TEMPLATE template0];
\l+
GRANT ALL ON DATABASE bacula TO bacula;
\c bacula
\du+