PostrgeSQL

This section covers various tips related to setting up and working with PostgreSQL from the perspective of an Omnis Studio programmer. I started using PostgreSQL in 2007 after talking to another Omnis Studio developer who was very happy with PostgreSQL and was using against some sizeable databases. (over a million records in some tables)

I had resisted using PostgreSQL because Omnis Studio did not have a DAM for connecting to PostgreSQL. That changed with Omnis Studio version 4.3 so I figured it was worth a try. The claim for PostgreSQL as opposed to MySQL is that PG was built from the ground up with all the features and then tuned for performance later on, whereas the MySQL was built for speed and then the features (transactions, stored procedures, etc.) were added afterwards.

Installing PostgreSQL on Mac OS X

This section covers the steps involved with installing and configuring PostgreSQL on Mac OS X.

Add postgres User

Before installing PostgresSQL you should add a postgres user to your computer's accounts. The installer will install one for you, but then you will have difficulty setting or changing the postgres user password.

1. System Preferences > Accounts
2. Click the + button below the list of users.
3. Enter the user name and short name as postgres
4. Enter a password that you won't forget or be sure to mark it down somewhere.
5. Mac OS X 10.5 Make sure the New Account type is set to Standard
   Mac OS X 10.4 Uncheck the Allow user to administer this computer checkbox
6. Click the Create Account button.
7. Close the Accounts window.

After adding the postgres user, check the NetInfo settings as follows:

1. Open the NetInfo Manager application.
   
   
   

   From the Finder press Shift+Cmnd+U to get to the Utilities folder.
   If you are in column view, press the left arrow key, then type n to scroll down to NetInfo Manager, then press Cmnd+down arrow to open the NetInfo Manager application.)

2. Select users in the middle pane.
3. Select postgres in the following pane.
4. Check the following values in the lower pane to make sure they match. Change them if they don't.
   
   home - /Users/postgres or /Library/PostgreSQL8
   shell - bin/bash
5. Quit NetInfo Manager when you are done.

Install PostgreSQL and pgAdmin

Get the following files off the internet and install them on your Mac OS X computer. (As of PostgreSQL 8)

Setup Bash Profile

You need to set up a .bash_profile file as follows:

1. Open the Terminal application.
2. Switch to the postgres user. Type su - postgres
3. You will be prompted to enter the password.
4. The terminal prompt should now end with postgres$
5. Type pico ~/.bash_profile
6. Enter the following text.
   
   

   export PATH=/Library/PostgreSQL8/bin:$PATH
   export PGDATA=/Library/PostgreSQL8/data

   
   
   PostgreSQL8 in the above text might be something different depending on the version you installed. Check on your computer in the HardDrive/Library/ folder to verify the name of your installed PostgreSQL folder.
7. Press Ctrl+x to Exit. Press y to save the changes. Press the Return key to save the file.
8. Type exit in the Terminal to return to the normal user.
   
   This file sets up the paths to make it easier to run your PG commands from the terminal.

Setup Shared Memory Settings

You need to change the Shared Memory Settings. This is done so that you can increase the shared_buffers parameter to greater than the standard 8MB. Shared buffers allow PostgreSQL to use much more memory and can improve performance quite dramatically.

Set up a sysctl.conf file as follows:

1. Open the Terminal application.
2. Go to the /etc directory. Enter cd /etc
3. Create/edit the file as the super user. Enter: sudo pico /etc/sysctl.conf
4. You will be prompted to enter the super user password.
5. Enter the following text in the file.
   
   

   kern.sysv.shmmax=524288000
   kern.sysv.shmmin=1
   kern.sysv.shmmni=64
   kern.sysv.shmseg=16
   kern.sysv.semmns=130
   kern.sysv.shmall=131072000
   kern.sysv.maxproc=2048
   kern.maxprocperuid=512

   
   
   
   

   shmmax is the key setting; it is in bytes. The value in this example is 500MB. If the machine has more memory to use, then this should be increased as well. shmall is the shmmax setting divided by 4.
   
   1 MB = 1048576 bytes

6. Exit and save the file. (Ctrl+X, y, Return)

PostgreSQL Access Settings

This file controls all access to the PostgreSQL server. In order for clients to connect to the server their IP address must be in the allowed list of users. The two common methods that you will see being used are md5 and trust. Md5 does md5 password authentication and should be used for just about all entries to this file. Trust allows clients to connect without password authentication, the only entry trust should ever have is for the local server machine.

1. Open the Terminal application.
2. Type su - postgres
3. Type pico /Library/PostgreSQL8/data/pg_hba.conf
4. Press Ctrl+v to scroll down a page at a time to the end of the file.
5. Add/Modify the lines to match your settings. trust indicates that the machine can change passwords.
   
   Here is an example of the access settings section in the file:
   
   

   # TYPE DATABASE USER CIDR-ADDRESS METHOD
   
   # "local" is for Unix domain socket connections only
   local all all trust
   # IPv4 local connections:
   host all all 127.0.0.1/32 trust
   host all all 192.168.1.0/24 md5
   # IPv6 local connections:
   host all all ::1/128 trust

6. Exit and save the file. (Ctrl+X, y, Return)

NOTE:The CIDR address as noted above has many combinations. Here is an example of the common one's you will see:

192.168.150.50 = 192.168.150.50/32
192.168.150.XXX = 192.168.150.0/24
192.168.XXX.XXX = 192.168.0.0/16
192.XXX.XXX.XXX = 192.0.0.0/8
XXX.XXX.XXX.XXX = 0.0.0.0/0 (This allows everything... probably should never use that)

PostgreSQL Configuration Settings

1. Open the Terminal application.
2. Type su - postgres
3. Type pico /Library/PostgreSQL8/data/postgresql.conf
4. Ctrl+v to scroll down the file. Ctrl+y to scroll up.
5. Edit the property variable as needed.
   
   
   

   On OS X parameters that have a # character in front of them are considered not in use. If a parameter that is required has one of these in front, remove the # character before change the setting.

6. Exit and save the file. (Ctrl+X, y, Return)

The following are common properties you may want to verify/change:

• listen_addresses - This value should always = '*'
• shared_buffers - This value should be 20-25% of the systems total RAM.
• temp_buffers - This value should be 50 MB for machines with 2GB of RAM or more and 20 MB for those with less
• work_mem - This value should be 50 MB for machines with 1GB of RAM or more and 20 MB for those with less
• maintenance_work_mem - This value should be 50 MB for machines with 1 GB of RAM or more and 20 MB for those with less
• effective_cache_size - This value should be roughly the amount of free ram(always round down to the nearest 50 MB) as seen in the Task Manager or Activity Monitor without PostgreSQL running. Good to take the reading before installing PostgreSQL.
• redirect_stderr - this value should be set to on
• log_directory - this value should be set to 'pg_log'
• log_filename - this value should be set to 'postgresql-%Y-%m-%d_%H%M%S.log
• log_rotation_age - this value should be set to 7d
• stats_start_collector - this value should be set to on
• stats_block_level - this value should be set to on
• stats_row_level - this value should be set to on
• autovacuum - this value should be set to on

1 MB = 1048576 bytes

Starting/Stoppping the Database Server

I recommend that you stop and start the PostgreSQL database server once you've got all the configuration stuff done. If there are any errors in starting the server it will be reported to you and you can solve the problem before you go any further.

To stop and start the server from the command line:

1. Open the Terminal
2. Type su - postgres
3. Type pg_ctl start or pg_ctl stop or pg_ctl restart
4. Optional - Leave the terminal window open to view stderr log messages as you execute queries against the server.

Creating a Database

Use the pgAdmin tool to create new databases.

Drop a Database

Use the pgAdmin tool to drop a database.

Backup up a Database

To backup a database from the command line on Mac OS X:

1. Open the Terminal
2. Type su - postgres
3. You can either do an uncompressed backup or a compressed backup
• Uncompressed - Type pg_dump -U postgres -o [DatabaseName] > [Path]
• Compressed - Type pg_dump -U postgres -o -F c -Z 9 [DatabaseName] > [Path]

[Database] is replaced with the database name.
[Path] is the path where you want to backup to go including the file name and extension.

For uncompressed use the file extension .sql
For compressed use the file extension .backup

Compressed backup example for the database 'Demo' as follows:
e.g For Database 'Demo' -> pg_dump -U postgres -o Demo > /Users/Shared/MyBackups/Demo.sql

Restoring a Database

To backup a database from the command line on Mac OS X:

1. Open the Terminal
2. Type su - postgres
3. You can either do an uncompressed backup or a compressed backup
4. Drop the existing database if it exists.
5. Create a new database with the same name
6. Depending on whether you are restoring from an uncompressed backup or a compressed backup do one of the following:
• Uncompressed - Type: psql [DatabaseName] < [Path]
• Compressed - Type: pg_restore -d [DatabaseName] [Path]

[Database] is replaced with the database name.
[Path] is the path where you want to backup to go including the file name and extension.

Uncompressed:
e.g For Database 'Demo' -> psql Demo < /Users/Shared/MyBackups/Demo.sql

Installing PostgreSQL on Windows

This section covers the steps involved with installing and configuring PostgreSQL on Mac OS X.