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: 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: rm -rf /pgsql mkdir /pgsql chown pgsql:pgsql /pgsql su pgsql -c "initdb --encoding=UTF8 --data-checksums --auth=password --pwprompt --pgdata=/pgsql" Restore: psql -U pgsql -d postgres -f globals.pgsql pg_restore -C -U pgsql -d postgres bacula.pgsql
 * Stop everything that would record important data to the database.
 * Run:
 * Run:
 * Restore postgresql.conf and pg_hba.conf and update as needed; see PostgreSQL release notes.
 * service postgresql start

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

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
 * 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.

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+