- App Store
Source Installing Postgresql on RedHat, Ubuntu and OSX
While xTuple does publish a full Installer which provides PostgreSQL, the preferred method for implementing a production PostgreSQL server is to build PostgreSQL from source. Using the Linux distribution provided packages (apt, yum, rpm, etc) has the potential to introduce incompatibilities and curtail the migration options. The easiest way to support the customer is to keep things simple - and building PostgreSQL from source is simple. In this tutorial it is noted where the installation differs between RedHat/CentOS, Ubuntu and OSX.
The overall goal of this document is to allow someone with little or no Linux/OSX experience to install and configure PostgreSQL with confidence. A Guru once told me that “A 'Guru' is someone with five minutes more knowledge than you.” This document will start you on your journey to becoming a Guru.
All steps are done as 'root'. Ubuntu and OSX users can `sudo su -` and RedHat/CentOS users can `su -`.
Setting up the build environment
The first step is to make sure you have an appropriate environment to build PostgreSQL. Once completed, the framework for building and installing a vast majority of software from source will be enabled. Our requirements for building PostgreSQL are fairly simple. ( Taken from INSTALL located in the source)
First, make sure the necessary packages for development on your platform are installed.
For RedHat, do:
For Ubuntu, do:
Using the synaptic manager, install the developer tools.
For OSX, do:
Install the Xcode Tools, available on your OSX install CD 1, or download it from: http://developer.apple.com/technology/Xcode.html
Now that we have installed the necessary tools on our platform, make a directory to contain the source files. This directory may already exist. This is where the source for PostgreSQL will reside. It is recommended to keep it here, as it will be easier to reconfigure the compiled in options on the server if ever needed.
Change into the source directory.
Downloading , Extracting and building PostgreSQL
On all platforms, the ftp program should exist by default. Download PostgreSQL using ftp like so...
While still in the /usr/local/src directory, extract and uncompress the source code using the 'tar' command:
Change into the postgres source directory
If this is your first time building software from source, now may be a good time to peruse the documentation provided with the source code. The README and INSTALL docs in the source directory are very informative. Use the `more` or `less` command to read these.
The most important configure option is to build postgres with OpenSSL support. Execute `./configure -?` to read more about available options. From the command line below, we're specifying to install postgresql with OpenSSL and integer datetime support and to use default values for everything else. The default path for the application is /usr/local/pgsql. The `pgsql` directory is created by the install process later. The command `configure` configures the application for building, sets up the Makefiles with defaults and paths to other dependencies.
Once configure has completed without error, run `make`. This builds the application. On a reasonably new computer, this takes less than five minutes.
Run `make install`. This installs the binaries and supporting pieces to the appropriate path, in this case, the default path of /usr/local/pgsql.
Next, we change into the contrib directory to make additional items. The “contribs” are contributed by various entities – people, corporations, etc. There are several contribs that we are interested in installing later on, so we need to build them.
Run make all.
Run make install. These items end up in /usr/local/pgsql/share/contrib
This is a momentous occasion! PostgreSQL is built and installed. Pat yourself on the back. Alas, there is still more work to do before we can turn it on. Take a look in /usr/local/pgsql and do an `ls` command. Poke around, but don't get lost. You're about half way done.
Post Build Setup (You didn't think you could just build this thing and use it did you?)
Configuring the Start Scripts
Copy the startup script to the proper location. This is an important file, as it contains the hints to starting postgres in the correct path. This file needs to be edited if postgresql is installed in a non-default path. Linux is a handled differently than OSX.
For RedHat and Ubuntu, do:
Then make the startup script executable.
For RedHat, add the script to the Server's startup routine (init) with chkconfig like this:
For Ubuntu, add the script to the Server's startup routine (init) with update-rc.d like so:
For OSX, there are differing methods to start postgres on server start-up depending on your version of OSX.
If you're running 10.3 and possibly 10.4, use the SystemStarter methodology. Read through the source code too.
Then execute the install.sh script located in there. It may do you some good to read through the script and see what it's doing.
If you're running 10.5 and 10.6, the new and improved method for starting apps in 10.5 and 10.6 is to use a “launchd” script. Cut, paste and save the contents below as/Library/LaunchDaemons/org.postgresql.postgres.plist
For OSX, change the plist's permissions:
And on OSX, to start/stop it, issue: (Don't do this step yet - here for reference)
Add A Postgres User (service account)
Now, we need to add a postgres user. This user runs the postgresql server. Postgres will not run as root.
For RedHat and Ubuntu, do use the 'adduser' program. This will fail harmlessly if the user exists.
For OSX users first check to see if the user 'postgres' exists, if so, you're done.
For this step on OSX, I have created a shell script to create this user interactively, located on the last page.
If no result returned (user does not exist), find the next available UID
Use that number returned as NEWUID, it's the next UID. Below, replace NEWUID with the number.
Create the postgres users' environment:
*See: http://www.postgresql.org/docs/8.3/interactive/postgres-user.html for alternate OSX specific user creation options.
Add Paths to Binaries and Man Pages
Add the path information to the shell, so that postgres commands and manual pages can be more readily accessed.
For RedHat and Ubuntu, do:
For OSX, do:
Create a Logging Directory
Besides chewing up the available drive space, a log file provides useful debugging information and error collecting, and is highly configurable. The most popular way (and easier way) to configure logging is to use the stderr interface provided. You could also use the syslog facility. The options for controlling the logging are in the postgresql.conf file.
Make a logging directory and make sure postgres can read/write to it.
Create the PostgreSQL Database Cluster
This is the real heart and soul of the postgres database. It IS the postgres database. In high performance applications, this directory can exist on a separate array from the OS drives. For sake of simplicity, create the data directory under the pgsql directory, this can be named anything, but naming it 'data' is most common. This is your postgres database cluster. This directory contains the main configuration files for your database – postgresql.conf and pg_hba.conf. Until the `initdb` command runs, those files do not exist and you cannot start the server until they do.
Make a directory to contain the databases.
Change ownership of the data directory to postgres. PostgreSQL runs as the user 'postgres', not as root. This is important, as postgresql won't start if the data directory isn't owned by the postgres user.
Execute the initdb script. This prepares the cluster for use. This command is the same on all platforms. Our application uses UTF8. SQL-ASCII may be encountered in the field from older OpenMFG installations. If so, please contact us, it is not advisable to change the encoding without sufficient testing (some db data can be dropped if converted incorrectly). Postgresql runs as a non-privledged user 'postgres' in this case. PostgreSQL does not run as the root user.
Lets log in as the postgres user. As root, issue:
And create the db cluster with `initdb`
Darwin, BSD and Ubuntu users may need to modify the sysctl kernel parameters to allow more connections to the db. That is another topic.
Editing the Configuration Files
These steps should be done as the postgres user. As root, issue: `su – postgres` (no password needed), the postgresql.conf and pg_hba.conf configuration files are located in /usr/local/pgsql/data/
Using the 'nano' editor, (or vi), modify the postgresql.conf to allow the installation to listen for remote connections. Also, while we're in here let's configure the logging to create the log file in /var/log/pgsql/. The main cause of not being able to connect to a PostgreSQL database is because of a misconfiguration in this file.
Now, edit the pg_hba.conf and configure some network rules. Add the line in red to match your LAN address range. Set access from other computers to use md5 authentication. You can also set the other methods to md5, (and others) but for managability, leave the local connections set to 'trust' for now. The order of rules in this file matters.
See the blog post by Chris Ryan at: http://www.xtuple.org/node/2106 for much more information about the config files..
Finally... Start PostgreSQL
Add postgres to the server's startup routine and start it. (as the 'root' user)
For RedHat and Ubuntu to start postgres, call the init script directly. (as root)
For OSX, use the 'launchctl' program, start postgresql. (as root)
Check to see if postgresql is running with the command `ps aux | grep postgres`, you should get output similar to the following:
Load Additional Functions
Now that we actually have a live running PostgreSQL server, let's take this opportunity to actually work with it. Let's start by loading some additional functions into template1 of the postgres install. Template1, template0 and postgres are databases that are created by default and can be used (basically copied) to ensure that new databases contain the same functions and structures (it's a template!).
PGCrypto is required for xTuple to function properly, so that should be loaded first.
Make sure you always load up pgcrypto.
Connect to the Database
Now, we can connect to our freshly installed postgres database. The `psql` command is the postgres shell. Enter the following at the prompt:
If you get the above output, you've successfully connected and are ready to install the xTuple databases, but first let's install the postgresql procedural language - PL/pgSQL
Take a minute to admire your work... You've build postgresql from source, initialized a database cluster and connected to it, and all via the command line! Really, it's an accomplishment! Now you can install the xTuple Databases. Follow the steps located here: http://www.xtuple.org/InstallingTheDatabase
Here's an outline of what you've done (OSX Version)...
Interactive bash script to create postgres user on OSX from the shell.
After the install, docs are in: /usr/local/pgsql/doc/postgresql/html/index.html
#postgresql on IRC
- Key Phrases:
- Open Source ERP Software ,
- Open Source Software Development ,
- Help with ERP ,
- Best Business Solution ,
- Cross-Platform Business Software ,
- Enterprise Business Software ,
- ERP Best Practices ,
- ERP Database Software ,
- ERP for Mac ,
- ERP for Windows ,
- ERP for Linux ,
- Ubuntu ERP ,
- ERP Implementation ,
- ERP Solutions ,
- ERP Training ,
- Host Based Authentication ,
- Open Source Software ,
- PostgreSQL ,
- xTuple ERP Server Configuration