Install PostgreSQL and set SHMMAX

FreeBSD Installation tips

Install PostgreSQL and set SHMMAX

Postby stephen » Mon Apr 12, 2010 9:03 am

There is a great howto install Postgresql found here
http://jasonk2600.wordpress.com/2010/01/11/installing-postgresql-on-freebsd/

cd /usr/ports/databases/postgresql84-server
make install

I get error
===> postgresql-server-8.4.0_1 cannot install: the port wants postgresql84-client but you have postgresql82-client installed.
*** Error code 1



If I try and deinstall

# make deinstall
===> Deinstalling for databases/postgresql82-client
===> Deinstalling postgresql-client-8.2.13
pkg_delete: package 'postgresql-client-8.2.13' is required by these other packages
and may not be deinstalled (but I'll delete it anyway):
mapserver-5.4.2_1

cd /usr/ports/databases/postgresql84-client
make install

cd /usr/ports/databases/postgresql84-server
make install



Change PostgreSQL SHMMAX

Modify these settings to improve performance of postgreSQL in FreeBSD

vi /etc/sysctl.conf
kern.ipc.shmall=32768
kern.ipc.shmmax=134217728
kern.ipc.semmap=256

vi /boot/loader.conf
set kern.ipc.semmni=256
set kern.ipc.semmns=512
set kern.ipc.semmnu=256

Managing Kernel Changes for postgreSQL
http://developer.postgresql.org/pgdocs/postgres/kernel-resources.html
FreeBSD

The default settings are only suitable for small installations (for example, default SHMMAX is 32 MB). Changes can be made via the sysctl or loader interfaces. The following parameters can be set using sysctl:

$ sysctl -w kern.ipc.shmall=32768
$ sysctl -w kern.ipc.shmmax=134217728
$ sysctl -w kern.ipc.semmap=256

To have these settings persist over reboots, modify /etc/sysctl.conf.

The remaining semaphore settings are read-only as far as sysctl is concerned, but can be changed before boot using the loader prompt:

(loader) set kern.ipc.semmni=256
(loader) set kern.ipc.semmns=512
(loader) set kern.ipc.semmnu=256

Similarly these can be saved between reboots in /boot/loader.conf.

You might also want to configure your kernel to lock shared memory into RAM and prevent it from being paged out to swap. This can be accomplished using the sysctl setting kern.ipc.shm_use_phys.

If running in FreeBSD jails by enabling sysctl's security.jail.sysvipc_allowed, postmasters running in different jails should be run by different operating system users. This improves security because it prevents non-root users from interfering with shared memory or semaphores in different jails, and it allows the PostgreSQL IPC cleanup code to function properly. (In FreeBSD 6.0 and later the IPC cleanup code does not properly detect processes in other jails, preventing the running of postmasters on the same port in different jails.)


Initialize the PostgreSQL database cluster for the first time. NOTE: The following command will create the initial database cluster in the /usr/local/pgsql/data directory by default.

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

Configure PostgreSQL to listen for database connections on all system IP addresses by adding the following line to /usr/local/pgsql/data/postgresql.conf.

listen_addresses = '*'

Configure PostgreSQL to use password hash authentication for all hosts and users connecting from the local network by adding the following line to the /usr/local/pgsql/data/pg_hba.conf file. NOTE: Replace 10.0.1.0/24 with your own network.

host all all 10.0.1.0/24 md5

Start the PostgreSQL DBMS for the first time and add a new super-user (with database and role creation rights) by executing the following commands.

# /usr/local/etc/rc.d/postgresql start
# su pgsql
$ createuser -sdrP username
Enter password for new role: ******
Enter it again: ******

$ exit



Testing

The PostgreSQL DBMS should now be up and running with the newly created super-user. Using a PostgreSQL client, such as pgadmin3 connect to the PostgreSQL server from another system using the username and password of the role previously created. New database schemas, roles, procedures, etc can now be created using the super-user.

References
http://www.postgresql.org/docs/8.4/static/index.html
http://www.pgadmin.org/
http://jasonk2600.wordpress.com/2010/01/11/installing-postgresql-on-freebsd/

Support Notes
postgresql start
gives error
postgres cannot access the server configuration file /usr/local/pgsql/data/postgresql.conf

Solution
There was no database so need to run initdb
# usr/local/etc/rc.d/postgresql initdb

Success: You can now start the database server using:
# /usr/local/bin/postgres -D /usr/local/pgsql/data
stephen
 
Posts: 507
Joined: Thu Feb 09, 2006 9:37 am
Location: Brisbane

Return to FreeBSD

Who is online

Users browsing this forum: No registered users and 1 guest

cron