PostgreSQL

From Earlham Cluster Department

Jump to: navigation, search
=========== BACKUP YOUR DATA! =============
 As always, backup your data before
 upgrading. If the upgrade leads to a higher
 minor revision (e.g. 7.3.x -> 7.4), a dump
 and restore of all databases is
 required. This is *NOT* done by the port!


 Press ctrl-C *now* if you need to pg_dump.
 ===========================================

cd /usr/ports/databases/postgresql82-server/

make; make install


this will instal both the server and client ports


The port is set up to use autovacuum for new databases, but you might also want to vacuum and perhaps backup your database regularly. There is a periodic script, /usr/local/etc/periodic/daily/502.pgsql, that you may find useful. You can use it to backup and perfom vacuum on all databases nightly. Per default, it perfoms `vacuum analyze'. See the script for instructions. For autovacuum settings, please review ~pgsql/data/postgresql.conf.


To allow many simultaneous connections to your PostgreSQL server, you should raise the SystemV shared memory limits in your kernel. Here are example values for allowing up to 180 clients (configurations in postgresql.conf also needed, of course):

 options         SYSVSHM
 options         SYSVSEM
 options         SYSVMSG
 options         SHMMAXPGS=65536
 options         SEMMNI=40
 options         SEMMNS=240
 options         SEMUME=40
 options         SEMMNU=120


If you plan to access your PostgreSQL server using ODBC, please consider running the SQL script /usr/local/share/postgresql/odbc.sql to get the functions required for ODBC compliance.


Please note that if you use the rc script,

/usr/local/etc/rc.conf/postgresql, to initialize the database, unicode (UTF-8) will be used to store character data by default. Set postgresql_initdb_flags or use login.conf settings described below to alter this behaviour. See the start rc script for more info.


To set limits, environment stuff like locale and collation and other things, you can set up a class in /etc/login.conf before initializing the database. Add something similar to this to /etc/login.conf:


postgres:\

       :lang=en_US.UTF-8:\
       :setenv=LC_COLLATE=C:\
       :tc=default:


and run `cap_mkdb /etc/login.conf'.

Then add 'postgresql_class="postgres"' to /etc/rc.conf.


To initialize the database, run


 /usr/local/etc/rc.d/postgresql initdb


You can then start PostgreSQL by running:


 /usr/local/etc/rc.d/postgresql start


For postmaster settings, see ~pgsql/data/postgresql.conf


NB. FreeBSD's PostgreSQL port logs to syslog by default

   See ~pgsql/data/postgresql.conf for more info


To run PostgreSQL at startup, add 'postgresql_enable="YES"' to /etc/rc.conf

===> Installing rc.d startup script(s)

===> Registering installation for postgresql-server-8.2.4_1

===> SECURITY REPORT:

     This port has installed the following files, which may act as network
     servers and may therefore pose a remote security risk to the system.

/usr/local/bin/postgres


     This port has installed the following startup scripts, which may cause
     these network services to be started at boot time.

/usr/local/etc/rc.d/postgresql


     If there are vulnerabilities in these programs there may be a security
     risk to the system. FreeBSD makes no guarantee about the security of
     ports included in the Ports Collection. Please type 'make deinstall'
     to deinstall the port if this is a concern.


     For more information, and contact details about the security
     status of this software, see the following webpage:

http://www.postgresql.org/


- Run /usr/local/etc/rc.d/postgresql initdb to initialize the DB - vi /usr/local/pgsql/data/pg_hba.conf and add the following


local   all         all                                 trust
host    all         all         127.0.0.1/32            trust
host    all         all         159.28.234.0/24         trust
host    all         all         159.28.230.0/24         trust
hostssl all         all         12.161.108.5/32         trust
hostssl all         all         12.222.56.168/32        trust
hostssl all         all         159.28.57.98/32         trust

- vi /usr/local/pgsql/data/postgresql.conf (check cluster config)

- vi /usr/local/pgsql/data/postmaster.opts and add

/cluster/usr-local/bin/postgres -D /usr/local/pgsql/data
Personal tools
Namespaces
Variants
Actions
websites
wiki
this semester
Toolbox