Varyonic

Archive for the ‘database administration’ Category

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

To change the default table engine add the following to my.cnf:

default-storage-engine=InnoDB

To check the engines being used:

SHOW TABLES STATUS

Each existing table has to be converted explicitly:

user='mydba'
db='mydb'
mysql -u ${user} -p -e "SHOW TABLES IN ${db};" \
| tail -n +2 | xargs -I '{}' echo "ALTER TABLE {} ENGINE=INNODB;" > alter_table.sql
mysql -u ${user} -p -D ${db} < alter_table.sql

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

I ran into this error extracting data using ODBC from an Access database. All access control within Access had been turned off or removed, but still the problem persisted. I tried using ‘repair’ utilities but to no avail. Eventually I split the database, copying all tables and indexes into a separate ‘back-end’ .mdb file and that resolved the issue.

Oracle XE is available for 32-bit architecture (i386) Linux only, although it may be possible to force installation on a 64-bit host. It will run in 512KB RAM but requires a 1GB swap space. Add:

deb http://oss.oracle.com/debian unstable main non-free

to /etc/apt/sources.list and then as root:

wget http://oss.oracle.com/el4/RPM-GPG-KEY-oracle  -O- | sudo apt-key add - 
apt-get update
apt-get install oracle-xe

‘libaio’ and ‘bc’ are in the repository, so dependencies will pull them in if the user doesn’t have them.

I stole this from VS Babu

  SELECT   a.TABLESPACE_NAME,
           a.BYTES bytes_used,
           b.BYTES bytes_free,
           b.largest,
           round( ( ( a.BYTES - b.BYTES ) / a.BYTES ) * 100, 2 ) percent_used
    FROM   (   SELECT   TABLESPACE_NAME, sum( BYTES ) BYTES
                 FROM   dba_data_files
             GROUP BY   TABLESPACE_NAME ) a,
           (   SELECT   TABLESPACE_NAME, sum( BYTES ) BYTES, max( BYTES ) largest
                 FROM   dba_free_space
             GROUP BY   TABLESPACE_NAME ) b
   WHERE   a.TABLESPACE_NAME = b.TABLESPACE_NAME
ORDER BY   ( ( a.BYTES - b.BYTES ) / a.BYTES ) DESC

This works for me for a mid-sized table.

ALTER TABLE foo ADD ( foo_id integer )
/
CREATE SEQUENCE foo_id_seq
/
CREATE OR REPLACE TRIGGER before_update_foo
   BEFORE INSERT OR UPDATE
   ON FOO
   REFERENCING OLD AS old NEW AS new
   FOR EACH ROW
DECLARE
   l_foo_id   foo.foo_id%TYPE;
BEGIN
   IF :new.foo_id IS NULL
   THEN
      SELECT   foo_id_seq.nextval INTO l_foo_id FROM dual;

      :new.foo_id := l_foo_id;
   END IF;
END;
/
UPDATE   foo
   SET   foo_id = NULL
/
COMMIT
/
CREATE UNIQUE INDEX foo_ak
   ON foo( foo_id )
/
ALTER TABLE foo MODIFY ( foo_id NOT NULL )
/
ALTER TABLE foo ADD (CONSTRAINT foo_ak UNIQUE (foo_id))
/

To see the current values use ‘show parameter sga_target’. To alter:

alter system set sga_max_size = 300M scope=spfile;
alter system set sga_target = 300M scope=spfile;

Restart database to effectuate change.

Both Tomcat and Oracle XML DB use port 8080 by default. Before installing JBoss I first changed the default port for XML DB as suggested here.

call dbms_xdb.cfg_update(updateXML(
            dbms_xdb.cfg_get()
          , '/xdbconfig/sysconfig/protocolconfig/httpconfig/http-port/text()'
          , 8081))
      /

I restarted the Oracle database and verified port 8081 was in use and not 8080.