+1-757-461-3022 x124

Installing PostBooks®... the long way

Friday, September 24, 2010

While we do publish PostBooks® Installers that will quickly and easily install PostBooks®, some people like to make the installation a fun little project by installing the individual components. And really, it's not a bad way for you to practice installing, or to get hands on experience with some of the pieces that we try to hide from you in the packaged installers. This will be like taking a long walk, but in the end, it's the journey that counts. Now, if you've been following my writings/ramblings for a while, or if you have met me in person, you know that I'm fiercly aligned with the Penguin Nation, that is, I use, recommend, endorse, and otherwise try to sell everyone on using some flavor of Linux. Well, In this blog, you'll notice I don't say "Now, go to 'terminal' and..." .

Yep — ALL GUI. How's THAT for a surprise?

The other surprise is that I wrote this using screen shots from Windows 2000.

Two roads diverged in a wood, and I — 
I took the one less traveled by,
And that has made all the difference.
—  Excerpt from The Road Not Taken by Robert Frost (1915)

First of all, get all of your ingredients for your trail mix together and settle in with a large beverage.We're going to assume that you have a PostgreSQL free environment - No Finks, Yums, Apt-Gets, xTuple Installers, etc... We're starting fresh and clean here.

=== UPDATE === UPDATE === UPDATE === UPDATE === UPDATE === UPDATE ===

Anyplace where I'm asking you to download a 3.5.4 database or use a 3.5.4 client, these instructions should hold true for installing a more recent version. Just substitute the version numbers, because I'm too lazy to update this whole page.

Get the most recent PostBooks® databases here: http://sourceforge.net/projects/postbooks/files/03%20PostBooks-databases/

And the GUI Client for your platform of choice here: http://sourceforge.net/projects/postbooks/files/02%20PostBooks-GUIclient-only/

Oh, one other thing... Running PostgreSQL on Windows 2000 is a really bad idea. Evidently, it's possible, but is highly frowned upon...

Also, be careful with PG 9.0 as of xtuple 3.6x and prior. When this was initially written, I hadn't done a whole lot of thorough testing with PG 9.0, but it will be awesome once we've ferretted out our issues.

=== UPDATE === UPDATE === UPDATE === UPDATE === UPDATE === UPDATE ===

List of stuff you'll need:
(1) The latest PostgreSQL 8.4 Installer (or even PostgreSQL 9.0 - TESTING ONLY! REALLY! i.e. NOT FOR PRODUCTION. There are bugs, you've been warned.)

Find the EnterpriseDB PostgreSQL Installer for your beloved server's OS and the architecture (32 or 64 bit)
http://www.enterprisedb.com/products/pgdownload.do

(2) The PostBooks® Databases and Initialization File (please see update above)
Initialization File (init.sql)
3.5.4 Quickstart Database
3.5.4 Empty Database
3.5.4 Demo Database

(3) The PostBooks® 3.5.4 Client for your client OS's.
3.5.4 Windows Client
3.5.4 Mac Universal Client
3.5.4 Linux Client

And that's it for the main ingredients.

Oh, I suppose we should install these things... Thought it would be that easy huh? Quick little hike... No, No, No, Padawans! Your journey has just begun. First as root, or Administrator, or Admin, or RunAs start off by starting up the the EnterpriseDB PostgreSQL installer.

Click the 'Next' button.

Select your installation directory — The default is usually fine for simple installations. Click 'Next'.

Select where you want your PGDATA directory to live. You could select a fancy RAID, or SAN, or other storage device. In this case, we're just accepting the default. Click 'Next'.

Enter in a password for the PostgreSQL service user name 'postgres'. The postgresql service does not run as a privleged user (i.e. root, Admin, etc.). Click 'Next'.

Select the port that the PostgreSQL service will listen on. 5432 is the default, but you can set it to something different if there are no conflicts. Click 'Next'.

Select your Locale. Your locale should be UTF-8 by default, you can research it if you need another locale. Click 'Next'.

If you've got this far, the installation will commence once you click 'Next'.

PostgreSQL is installing, using all of the information you have supplied. DO NOT CLICK 'Cancel'! (Unless you don't want to install the greatest database ever invented with the greatest ERP system ever invented...)

UNCHECK the StackBuilder checkbox and click 'Finish'. You can play with StackBuilder later, it is pretty cool.

Once installation is complete, you should have a running PostgreSQL! Go into your menu and look for PostgreSQL 8.4 or 9.0 and Launch PgAdmin3. Click on the little power plug icon and add a new host. Your installation may have the local installation already setup, but if not, you can enter your information here. 'localhost' refers to THE computer you are sitting infront of. You can also specify a remote host to connect to. Remember your 'postgres' user password? You'll need that too. Click 'OK' once you've entered everything in.

This is what you should be presented with if all is right with the world — if you had this without doing the previous step, congratulations! Take a quick little break. Now we have a little bit of setup work to do before we can load the PostBooks® databases.

Expand out the Database Cluster tree in the left hand pane. Look for the template1 database. If you don't see it listed, go to File -> Options, click on the 'Display' tab check the 'Show System Objects in the treeview' check box at the top of the screen and click 'OK'. You can restart pgAdmin, or click the REFRESH button (NOT THE RECYCLE TRASHCAN) next to the PLUG icon. Once you get the template1 database showing, select the template1 database and click the SQL button (6th button from the left). This will bring up the Query window.

Once the Query window is displayed, we need to load up pgcrypto so that the credit card encryption and other things work properly in PostBooks®. Click on File -> Open, if it asks about saving text, click NO. The file we're looking for is in the 'contrib' directory of wherever PostgreSQL has been installed. In this case, the path is shown at the top of the image. On Linux this is usually in the /opt directory, on Mac, it ends up in Applications/. Once you have found pgcrypto.sql, open it up, and run it — Click the green 'Play' button. Oh — did you notice at the top of the image the drop down list where it says what database you're connected to 'template1 on postgres@localhost:5432'? That's good to know that we're in the right database applying this script. We'll do the same process with the initdb.sql script that you downloaded.

Load up the initdb.sql script also and press 'Play' again. If you read it, you'll see that it's creating a GROUP named 'xtrole', and a USER named 'admin' with the PASSWORD of 'admin'. Once you've run this, great. Close the Query window and go back to the main database tree view.

Right click on the word 'Database' in the tree view and select 'New Database'.

Let's create a database named 'empty'. The Owner should be 'admin', the Encoding should be 'UTF8' (always), the Template will be 'Template1'. Leave everything else and click the 'OK' button.

Back on the main tree view, you should now see that you have a database named 'empty'. Now we're going to restore the PostBooks® 'Empty' database version here. Right click on the 'empty' database and click 'Restore'.

Navigate to whereever you downloaded the database files to and select the postbooks_empty-3.5.4.backup database and click the 'Open' button.

The 'Restore Database' dialog will display. Click the 'OK' button to restore this database to the 'empty' container.

Once the restore has completed, you should see a dialog like this. The highlighted WARNING above is normal. Those warnings are there because the pgcrypto functions already exist in the 'empty' database container because we used 'template1' when we created it. This is really quite ok. Click 'Cancel', it's a little quirky, but clicking 'OK' again will try to do the restore over itself again. Click 'Cancel', trust me. You can repeat the create db and restore process for the quickstart and demo databases if you so desire. Hey!!! Guess what, we're almost done, we can at least fire up a client and check out that it all works now!!!

Extract the client from it's zipped up state and get it put someplace accessible on your drive or network. Start the client by clicking on the executable, and click the 'Options' button.

Enter in your database information — If you're playing along at home, Server = localhost, Database = empty, and the Port = 5432. Click the 'Save' button.

Now, you can enter your user name and password of admin/admin and click the 'Login' button.

Success! You've made it to the end of your first install! Pat yourself on the back, rest your eyes, pet your dog, scratch your cat, and kiss your spouse! You've certainly come a long way, but of course, there's additional READING to do, and some other things that you must do. I suppose you will want to connect from other computers, so you'll need to learn how to modify the pg_hba.conf and postgresql.conf files and perhaps set your OS's firewall rules to allow access to the PostgreSQL port — You can learn how to do all that by reading all of my blog posts and some others on our site. Here are some VERY useful links, you should check them all out. These will make ALL the difference!

Installing PostgreSQL from Source

The PostgreSQL 8.4 Documentation

And if you've found this walk-through the installation useful, please leave some comments below!

Perry Clark

Senior Database Engineer
Database Administration for global, onsite, and internal systems. Design, implement and maintain database backup infrastructure and methodologies including backup test and validation. Troubleshooting and tuning database performance. Imagine and implement methods for metrics reporting relating to all deployed databases. Knowledgeable in most aspects of computer networking, security, operating systems, SQL databases and applications. Intimate knowledge of xTuple ERP. Manufacturing Specialties: Practical knowledge of various manufacturing processes: including CNC, CAD/CAM, metal forming/shaping, machining, welding, deburring, shaping, polishing, and various plastics processing techniques.