Varyonic

Posts Tagged ‘postgreSQL’

Lion comes with a _postgres system account and PostgreSQL 9.0.4 client tools already installed. Lion Server includes PostgreSQL server, which replaces the MySQL server distributed with earlier versions of OS X. If you want to add PostgreSQL server to a regular Lion installation the easiest way to do this is probably using the EnterpriseDB installer. However, I was inspired by a couple of posts at devoh.com to compile and install it manually:

curl -O http://ftp.postgresql.org/pub/source/v9.0.4/postgresql-9.0.4.tar.bz2
tar xzvf postgresql-9.0.4.tar.bz2
cd postgresql-9.0.4
./configure --enable-thread-safety --with-bonjour
make
sudo make install

To create a database:

sudo mkdir /usr/local/pgsql/data
sudo chown _postgres:_postgres /usr/local/pgsql/data
cd /usr/local/pgsql/bin
sudo -u _postgres ./initdb -D /usr/local/pgsql/data -U postgres -W -A md5 -E UNICODE

To manage PostgreSQL server with OS X launchctl create /Library/LaunchDaemons/org.postgresql.postmaster.plist

<?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>RunAtLoad</key>
    <true />

    <key>UserName</key>
    <string>_postgres</string>

    <key>GroupName</key>
    <string>_postgres</string>

    <key>EnvironmentVariables</key>
    <dict>
      <key>PGDATA</key>
      <string>/usr/local/pgsql/data</string>
    </dict>

    <key>ProgramArguments</key>
    <array>
      <string>/usr/local/pgsql/bin/postmaster</string>
    </array>

    <key>StandardOutPath</key>
    <string>/usr/local/pgsql/logfile</string>

    <key>StandardErrorPath</key>
    <string>/usr/local/pgsql/logfile</string>
  </dict>
</plist>

chmod 644 /Library/LaunchDaemons/org.postgresql.postmaster.plist

To load the new launch agent and start up the server for the first time:

sudo launchctl load /Library/LaunchDaemons/org.postgresql.postmaster.plist

If you need to restart the daemon, use the following commands:

sudo launchctl stop org.postgresql.postmaster
sudo launchctl start org.postgresql.postmaster

Installation of PostgreSQL on Ubuntu is straightforward, see http://library.linode.com/databases/postgresql/ubuntu-10.04-lucid

To create a simple database with its own user use the command line:

appenv=myapptst
psql <<EOF
  create user ${appenv} createdb password '*****';
EOF
createdb --username=postgres --owner ${appenv} ${appenv}

To access the database from Rails use pg

gem install pg

Entries in database.yml should look like

development:
  adapter: postgresql
  username: myappstst
  password: mypwd
  database: myapptst

One of the weaknesses of PostgreSQL compared to Oracle is the lack of a strong bulk loader utility like sqlldr. Natively Postgres offers only the COPY command, which does not include suppport for a bad file and reject log. EnterpriseDB have acknowledged the need for a better tool but it has not been a priority. There is, however, a Python based utility called pgloader available through pgFoundry that wraps COPY and provides a usable bulk loader. Using Python with Postgres requires psycopg2: for Windows an installable package is downloadable from Stickpeople,  for Ubuntu installation can performed with:

apt-get install tcllib libpgtcl-dev python-psycopg2

pgLoader itself is simply a collection of scripts in some folders so download and untar as follows:

wget http://pgfoundry.org/frs/download.php/2294/pgloader-2.3.2.tar.gz
tar zxvf pgloader-*.tar.gz