Postgres On Leopard Server

Most of this came from http://acts-as-blog.net/2008/3/27/building-postgresql-on-mac-os-x-leopard - Read this first. It doesn't go into setting the shared memory settings, this must be done or postgres won't perform. Even on good hardware postgres performance can be horrible if it's not configured properly. This is a work in progress,

Installed Xcode Tools from the Leopard install DVD to the default locations. Without these you can't 'make' the software.

Use the 'terminal' program. (Use the 'spotlight' to find it. )

'pwd' tells your current path

'ls -la' lists files in current directory

'nano' is an easy to use editor from the terminal

You should be able to ssh in to a mac from putty on windows or from ssh on a linux box.

Change to root via sudo su -

Download postgresql-8.3.x to /usr/local/src, if /usr/local/src does not exist, create it.

From Terminal...
ftp ftp.postgresql.org
login: anonymous
pass: your email
cd pub
cd latest
ls
bin
hash
ftp> get postgresql-8.3.5.tar.gz
local: postgresql-8.3.5.tar.gz remote: postgresql-8.3.5.tar.gz
227 Entering Passive Mode (65,19,161,90,175,37)
150 Opening BINARY mode data connection for postgresql-8.3.5.tar.gz (17547245 bytes).
####################################################....

Unzipped postgres

tar -zxvf postgres-8.3.5.tar.gz

Configure, make and make install it.

./configure --prefix=/Library/PostgreSQL –enable-thread-safety --with-krb5 --with-bonjour --with-openssl --with-gssapi

Find an unused user and group ID for the postgres user.

dscl . -list /Users UniqueID
dscl . -list /Groups PrimaryGroupID

Add a postgres user, 183 was chosen for the user and group id, as they were NOT returned at the above commands.

ERP:bin root# dscl . -create /Groups/postgres

ERP:bin root# dscl . -create /Groups/postgres RealName 'PostgreSQL Server'

ERP:bin root# dscl . -create /Groups/postgres PrimaryGroupID 183

ERP:bin root# dscl . -create /Groups/postgres

ERP:bin root# dscl . -create /Groups/postgres RealName 'PostgreSQL Server'

ERP:bin root# dscl . -create /Groups/postgres PrimaryGroupID 183

ERP:bin root# dscl . -create /Groups/postgres UserShell /usr/bin/false

ERP:bin root# dscl . -create /Groups/postgres Password '*'

ERP:bin root# dscl . -create /Groups/postgres NFSHomeDirectory /var/empty

ERP:bin root# dscl . -create /Users/postgres

ERP:bin root# dscl . -create /Users/postgres Password '*'

ERP:bin root# dscl . -create /Users/postgres RealName 'PostgreSQL Server'

ERP:bin root# dscl . -create /Users/postgres PrimaryGroupID 183

ERP:bin root# dscl . -create /Users/postgres UniqueID 183

ERP:bin root# dscl . -create /Users/postgres UserShell /usr/bin/false

ERP:bin root# dscl . -create /Users/postgres NFSHomeDirectory /Users/Shared/PostgreSQL

ERP:bin root# dscl . -create /Groups/postgres UserShell /usr/bin/false

ERP:bin root# dscl . -create /Groups/postgres Password '*'

ERP:bin root# dscl . -create /Groups/postgres NFSHomeDirectory /var/empty

ERP:bin root# dscl . -create /Users/postgres

ERP:bin root# dscl . -create /Users/postgres Password '*'

ERP:bin root# dscl . -create /Users/postgres RealName 'PostgreSQL Server'

ERP:bin root# dscl . -create /Users/postgres PrimaryGroupID 183

ERP:bin root# dscl . -create /Users/postgres UniqueID 183

ERP:bin root# dscl . -create /Users/postgres UserShell /usr/bin/false

ERP:bin root# dscl . -create /Users/postgres NFSHomeDirectory /Users/Shared/PostgreSQL

Check the user.

ERP:bin root# id postgres uid=183(postgres) gid=183(postgres) groups=183(postgres)

Create the following directories to create symlinks from the Postgres installation path.

ERP:bin root# mkdir -p /usr/local/{bin,man/man1,man/man7}

Shell script to create the proper links – Save the following as symlinks.sh and then run ./symlinks.sh

  POSTGRES_DIR=/Library/PostgreSQL DEST_DIR=/usr/local SYMLINK_DIRS="bin man/man1 man/man7"
    for CUR_DIR in $SYMLINK_DIRS; do
      for CUR_FILE in $POSTGRES_DIR/$CUR_DIR/*; do
      sudo ln -nfs $CUR_FILE $DEST_DIR/$CUR_DIR${CUR_FILE#$POSTGRES_DIR/$CUR_DIR}
    done
  done

Run 'which psql' to make sure the path is /usr/local/bin/psqlCreate a Postgres Data Directory.

ERP:~ root# mkdir -p /Users/Shared/PostgreSQL

Change ownership of the data dir to the postgres user.

ERP:~ root# chown postgres:postgres /Users/Shared/PostgreSQL

Initialize the database.

ERP:~ root# sudo -u postgres initdb -U mfgadmin -E UTF8 -D /Users/Shared/PostgreSQL

This is where we started running into difficulty and needed to look into the shared memory defaults in /etc. Initially, it would not complete this step successfully, allocating only for 20 connections. We've seen this on PPC Macs, Intel Macs, Tiger, Leopard, 10.5, 10.4, 10.3. Should be something Apple can fix. There is a 4GB limit to date. Maybe someone from Apple can explain this better. It requires tweaking the kernel memory settings by hand.

Since we need a postgres.conf to make changes to, we let it be for now, we need to configure logging.

In /etc/syslog.conf added:

local3.* /var/log/postgres/postgres.log

In /etc/newsyslog.conf added:

/var/log/postgres/postgres.log 644 5 * @T00 J

Create a directory to hold the logfile:

ERP:etc root# mkdir /var/log/postgres

Create the logfile:

ERP:etc root# touch /var/log/postgres/postgres.log

Created /etc/sysctl.conf to support a larger Postgres Installation- FOR REFERENCE ONLY, DO NOT INSTALL WILLY NILLY.

kern.sysv.shmmax=2097152000     # This is 1048576 x 2000.  1048576 = 1Mb.  This will change during tuning.
kern.sysv.shmmin=1
kern.sysv.shmmni=64
kern.sysv.shmseg=16
kern.sysv.semmns: 87381         # This wasn't set properly by the OS.
kern.sysv.shmall=524288000      # This is 2097152000 divided by 4.
kern.sysv.maxproc=2048
kern.maxprocperuid=512

And reboot.

Check settings:

ERP:etc root# sysctl -a | grep sem

kern.exec: unknown type returned kern.sysv.semume: 10 kern.sysv.semmsl: 87381 kern.sysv.semmnu: 87381 kern.sysv.semmns: 87381 kern.sysv.semmni: 87381 kern.posix.sem.max: 10000 security.mac.sysvsem_enforce: 1 security.mac.posixsem_enforce: 1

ERP:etc root# sysctl -a | grep shm

kern.exec: unknown type returned kern.sysv.shmall: 524288000 kern.sysv.shmseg: 16 kern.sysv.shmmni: 64 kern.sysv.shmmin: 1 kern.sysv.shmmax: 2097152000 machdep.pmap.hashmax: 8 security.mac.sysvshm_enforce: 1 security.mac.posixshm_enforce: 1

Continue tweaking postgresql.conf and memory settings in /etc/sysctl.conf. Running pgbench between changes with:

ERP:data root# pgbench -c 100 -t 1000 -s 100 -v -U mfgadmin pgbench

Pgbench db was initialized with:

ERP:data root# pgbench -i -s 100 -U postgres -d pgbench

Initial pgbench results:

starting vacuum accounts...end. transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 100 number of transactions per client: 1000 number of transactions actually processed: 100000/100000 tps = 357.630390 (including connections establishing) tps = 358.125787 (excluding connections establishing)

Final pgbench results:

Final sysctl.conf settings:

ERP:etc root# cat sysctl.conf

kern.sysv.shmmax=4194304000

kern.sysv.shmmin=1

kern.sysv.shmmni=64

kern.sysv.shmseg=16

kern.sysv.semmns: 87381

kern.sysv.shmall=1048576000

kern.sysv.maxproc=2048

kern.maxprocperuid=512

Create a /Library/LaunchDaemons/org.postgresql.postmaster.plist file:

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd"> <plist version="1.0"> <dict> <key>Label</key> <string>org.postgresql.postmaster</string> <key>UserName</key> <string>_postgres</string> <key>OnDemand</key> <false/> <key>ProgramArguments</key> <array> <string>/usr/local/bin/postmaster</string> <string>-D</string> <string>/Volumes/erpdata/data</string> <string>-c</string> <string>log_connections=YES</string> </array> <key>ServiceDescription</key> <string>PostgreSQL Server</string> </dict> </plist>

This launches the databases in /Volumes/erpdata/data as the postgres user.

To manually load or unload the plist file:

ERP:etc root# launchctl load /Library/LaunchDaemons/org.postgresql.postmaster.plist

ERP:etc root# launchctl unload /Library/LaunchDaemons/org.postgresql.postmaster.plist

To start postgres manually as root:

ERP:etc root# sudo -u postgres pg_ctl start -D /Volumes/erpdata/data

To stop postgres manually as root:

ERP:etc root# sudo -u postgres pg_ctl start -D /Volumes/erpdata/data