this is my first time installing postgreSQL on Mac OS X and i'm happy to say that it was successful.
i have to tweak some more things though, like allowing remote login (ssh) for the "postgres" account. i'll post the solution once i solve this problem.
i have installed postgreSQL before in redhat, MS$ and ubuntu by source (make, make install..) and by package but never tried installing it thru BSD "ports". there's always a first time so might as well post how i did it.
since leopard is built on Darwin, guys from MacPorts ported the BSD Ports to the latest OS X. this made life easier for me since i'm fairly new to OS X and it takes care of the dependencies.
specs:
MacBook Pro
Mac OS X, Version 10.5.5 (Darwin 9.5)
2.6GHz Intel Core 2 Duo
4GB 667 MHz DDR2 SDRAM
steps:
1] if you haven't installed MacPorts yet, install it first. you can find a pretty intensive guide at MacPorts Guide.
now don't get too excited and install postgreSQL right away. ensure first that you have the latest ports by doing:
2] search for the postgres ports by:
3] install the latest version 8.3.4:
or if with documentation:
i can't say how long the installation will take, i left mine overnight..at the end it will spew out this message:
4] if you want postgres to start everytime you power on your mac then by all means:
note: this is optional. you can start/stop postgres manually.
5] create directory for you database "defaultdb":
note: don't fret if you don't have the directories yet.. "-p" option of mkdir will create the directories in the specified path. you can also change the "defaultdb" to whichever name you like for your database.
6] change ownership of folder "defaultdb" to postgres
note: looking at the command above "chown postgres:postgres" means that the "postgres" user need not be created manually. it was already created while installing.
7] initialize "defaultdb" database:
8] there are two ways to install pgAdmin - by MacPorts or just download pgAdmin3 1.8.4 dmg image here
below are my own "tweaking" steps..
9] i can't create files at postgres home folder so:
10] change password of postgres account:
a] switch to user postgres
b] create .profile file to add "/opt/local/lib/postgresql83/bin/" to $PATH and some alias
12] run .profile by
18] edit postgresql configuration file postgresql.conf:
i have to tweak some more things though, like allowing remote login (ssh) for the "postgres" account. i'll post the solution once i solve this problem.
i have installed postgreSQL before in redhat, MS$ and ubuntu by source (make, make install..) and by package but never tried installing it thru BSD "ports". there's always a first time so might as well post how i did it.
since leopard is built on Darwin, guys from MacPorts ported the BSD Ports to the latest OS X. this made life easier for me since i'm fairly new to OS X and it takes care of the dependencies.
specs:
MacBook Pro
Mac OS X, Version 10.5.5 (Darwin 9.5)
2.6GHz Intel Core 2 Duo
4GB 667 MHz DDR2 SDRAM
steps:
1] if you haven't installed MacPorts yet, install it first. you can find a pretty intensive guide at MacPorts Guide.
now don't get too excited and install postgreSQL right away. ensure first that you have the latest ports by doing:
sudo port -d selfupdate
2] search for the postgres ports by:
port search postgresqlsearch results:
postgresql7 databases/postgresql7 7.4.21 The most advanced open-source database available anywhere
postgresql80 databases/postgresql80 8.0.18 The most advanced open-source database available anywhere
postgresql80-doc databases/postgresql80-doc 8.0.18 Documentation for the postgresql database
postgresql80-server databases/postgresql80-server 8.0.18 run postgresql80 as server
postgresql81 databases/postgresql81 8.1.14 The most advanced open-source database available anywhere
postgresql81-doc databases/postgresql81-doc 8.1.14 Documentation for the postgresql database
postgresql81-server databases/postgresql81-server 8.1.14 run postgresql81 as server
postgresql82 databases/postgresql82 8.2.10 The most advanced open-source database available anywhere
postgresql82-doc databases/postgresql82-doc 8.2.10 Documentation for the postgresql database
postgresql82-server databases/postgresql82-server 8.2.10 run postgresql82 as server
postgresql83 databases/postgresql83 8.3.4 The most advanced open-source database available anywhere.
postgresql83-doc databases/postgresql83-doc 8.3.4 Documentation for the postgresql database
postgresql83-server databases/postgresql83-server 8.3.4 run postgresql83 as server
postgresql_autodoc databases/postgresql_autodoc 1.25 Automatic documentation generator for postgresql databases
caml-postgresql devel/caml-postgresql 1.8.2 OCaml-interface to the PostgreSQL-database
postgresql-jdbc java/postgresql-jdbc 8.0-311 PostgreSQL JDBC driver
py-postgresql-exception python/py-postgresql-exception 0.2 exceptions for the py-postgresql modules
py-postgresql-greentrunk python/py-postgresql-greentrunk 0.1 greentrunk interface to postgresql
py-postgresql-layout python/py-postgresql-layout 0.3 layout for the py-postgresql modules
py-postgresql-pqueue python/py-postgresql-pqueue 0.1 pure python implementation of the pq protocol
py-postgresql-proboscis python/py-postgresql-proboscis 0.1 postgresql database connector in pure python
3] install the latest version 8.3.4:
sudo port install postgresql83 postgresql83-server
or if with documentation:
sudo port install postgresql83 postgresql83-server postgresql83-doc
i can't say how long the installation will take, i left mine overnight..at the end it will spew out this message:
###########################################################
# A startup item has been generated that will aid in
# starting postgresql83-server with launchd. It is disabled
# by default. Execute the following command to start it,
# and to cause it to launch at startup:
#
# sudo launchctl load -w /Library/LaunchDaemons/org.macports.postgresql83-server.plist
###########################################################
---> Installing postgresql83-server 8.3.4_0
To create a database instance, after install do
sudo mkdir -p /opt/local/var/db/postgresql83/defaultdb
sudo chown postgres:postgres /opt/local/var/db/postgresql83/defaultdb
sudo su postgres -c '/opt/local/lib/postgresql83/bin/initdb -D /opt/local/var/db/postgresql83/defaultdb'
To tweak your DBMS, consider increasing kern.sysv.shmmax by adding an increased kern.sysv.shmmax .. to /etc/sysctl.conf
---> Activating postgresql83-server 8.3.4_0
---> Cleaning postgresql83-server
4] if you want postgres to start everytime you power on your mac then by all means:
sudo launchctl load -w /Library/LaunchDaemons/org.macports.postgresql83-server.plist
note: this is optional. you can start/stop postgres manually.
5] create directory for you database "defaultdb":
sudo mkdir -p /opt/local/var/db/postgresql83/defaultdb
note: don't fret if you don't have the directories yet.. "-p" option of mkdir will create the directories in the specified path. you can also change the "defaultdb" to whichever name you like for your database.
6] change ownership of folder "defaultdb" to postgres
sudo chown postgres:postgres /opt/local/var/db/postgresql83/defaultdb
note: looking at the command above "chown postgres:postgres" means that the "postgres" user need not be created manually. it was already created while installing.
7] initialize "defaultdb" database:
sudo su postgres -c '/opt/local/lib/postgresql83/bin/initdb -D /opt/local/var/db/postgresql83/defaultdb'
8] there are two ways to install pgAdmin - by MacPorts or just download pgAdmin3 1.8.4 dmg image here
below are my own "tweaking" steps..
9] i can't create files at postgres home folder so:
sudo chown postgres:postgres /opt/local/var/db/postgresql83/note: i said home folder since when i do a "sudo su - postgres" and "pwd" i find myself in "/opt/local/var/db/postgresql83/". "su -" will change directory to target postgres' home directory. RTM.
10] change password of postgres account:
sudo passwd postgres11] i know i'm lazy so i proceeded on creating a ".profile" at postgres' home directory:
a] switch to user postgres
su - postgres
b] create .profile file to add "/opt/local/lib/postgresql83/bin/" to $PATH and some alias
vi .profile
export PATH=/opt/local/bin:/opt/local/sbin:/opt/local/lib/postgresql83/bin/:$PATH
export MANPATH=/opt/local/share/man:$MANPATH
alias ll='ls -alhG'
alias lf='ls -alhFG'
12] run .profile by
. .profile13] now i can issue postgres commands like psql, createdb, createuser without specifying the full path of the executables. make a test database:
createdb test14] create language plpgsql in template1:
createlang -U postgres plpgsql template115] i always use the contrib dblink. contribs are usually at the "share" folder so after searching i was able to locate it at "macports/software" directories. to create "dblink" functions in "test" database.
psql test < /opt/local/var/macports/software/postgresql83/8.3.4_0/opt/local/share/postgresql83/contrib/dblink.sql16] edit pg_hba.conf
vi defaultdb/pg_hba.conf17] allow incoming connections from our LAN. CIDR-ADDRESS column will depend on your subnet. YMMV. in my case:
host all all 172.20.0.0/16 md5note: i chose "md5" authentication. if you don't know what you're doing just substitute this with "trust".
18] edit postgresql configuration file postgresql.conf:
vi defaultdb/postgresql.conf19] update or uncomment the following lines:
listen_addresses = '*'20] updating pg_hba.conf and postgresql.conf requires restarting postgres. if you're in postgres' home directory issue:
log_destination = 'syslog'
log_rotation_size = 10MB
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
log_hostname = on
pg_ctl stop -D defaultdb21] create a non superuser. this one i'm going to use for my web applications.
then
pg_ctl start -D defaultdb
createuser -P -e pgapache
Enter password for new role:that's pretty much what i've done with my postgreSQL installation.. enjoy.
Enter it again:
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE pgapache PASSWORD 'md575f98f7f3815db249340343362e00568' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
