How do you manage your business?
Need Help? PostBooks® Setup Service
How to set up a local PostgreSQL server and install the PostBooks® database
Before proceeding with the steps on this page, be aware these are not instructions to test xTuple. If you are simply looking to learn more about xTuple's functionality, you can watch a series of videos and request a private demo here. If, however, you are looking to create your own private database and you prefer to run the database locally, please follow the instructions below to install the xTuple PostBooks® database on Windows, Mac or Linux.
Quick Overview of Local Installation Process
This quick overview assumes you are not using the cloud-based Free Demo.
- Download and extract the xTuple ERP client application to a local directory
- Download and install PostgreSQL (check our Compatibility Matrix to determine which PostgreSQL version to install)
- Download and install plv8.
- Initialize PostgreSQL for xTuple, i.e., add the super user 'admin' and the group 'xtrole' (see below)
- Edit postgresql.conf to include
plv8.start_proc = 'xt.js_init'
- Create a database using UTF-8 encoding
- Update database to allow plv8
- Download and restore an xTuple database on your new database
Before using xTuple, check the Compatibility Matrix for the latest details on which PostgreSQL version will work best with your xTuple ERP version.
For additional information, read senior database administrator Perry Clark's blog post Installing PostBooks... the long way. Please visit postgresql.org for in-depth documentation and other resources.
xTuple ERP uses encryption for credit card processing, and because of this, the PostgreSQL "pgcrypto" module is required to support encryption in xTuple ERP. Be sure to include the pgcrypto module when installing PostgreSQL. If you do not, you will encounter errors.
PLEASE NOTE: Beginning with xTuple ERP version 4.9.0, all supported PostgreSQL versions must have the plv8 extension added to them. For more information, please see our detailed instructions on installing plv8.
The following long-version steps are required to both initialize your PostgreSQL instance to support the xTuple Database and load the database schema. To skip these details, see the short-version Command Line Examples section below. Or skip to the pgAdmin section below to learn about initializing and loading your database using pgAdmin, a free GUI database administration tool.
The next step is to establish the user 'admin' and the group 'xtrole' on your PostgreSQL instance. This is accomplished by executing the 'init.sql' script. You can download init.sql immediately from the xTuple project on GitHub.
The complete text of the 'init.sql' file is as follows:
-- Create the database roles that xTuple software needs for bootstrapping
CREATE ROLE xtrole WITH NOLOGIN;
CREATE ROLE admin WITH PASSWORD 'admin'
IN ROLE xtrole;
If you are initializing the database server instance from the command line, the 'init.sql' script must be loaded by the 'postgres' superuser. This is not required if you are using pgAdmin to initialize the database server.
Please also note that since the default password the admin user is 'admin' — you'll want to change it immediately. You may create a different admin user with a different name if you choose to. However, do this with caution as all documentation examples refer to the default admin user.
When finish executing the init.sql script, next create a new PostgreSQL database to contain the xTuple Database schema. Use UTF-8 encoding when creating the database. Name the database anything you wish. Shorter, easier to remember names are preferred.
After you create the database you will need to connect to the database and issue the following command, which tells the database to allow use of plv8:
psql dbname> create extension if not exists plv8;
Once the database has been created and plv8 is enabled, you are ready to load the xTuple schema. You can download the xTuple database backup files from the project site on GitHub, as follows
empty.backup — Empty database with no data, but all tables and structures are created
quickstart.backup — Database contains a basic Chart of Accounts and also the Account Assignments required to run the full range of transactions
demo.backup — Database (if available) contains a suite of sample data built on top of the 'quickstart' database
Like the init.sql script, the database schema can be loaded on the command line. Alternately, you may use GUI tools like pgAdmin III to execute the script and load the schema. For information on loading the databases using pgAdmin, please see the pgAdmin section below.
The '.backup' format of the xTuple Database schemas is a compressed format used by the pg_restore binary. This format may be loaded seamlessly using pgAdmin. To load a .backup file using pgAdmin, connect to the database you created. Right-click on the database object and select the option 'Restore'. On the resulting screen, use the ellipses to navigate to the location of the .backup file on your local machine. With the .backup file selected, simply click OK.
To learn more about the psql utility or the pgAdmin application, please consult the PostgreSQL documentation.
Command Line Examples
The following examples demonstrate the steps needed to initialize, create, and load an xTuple Database. Name the database anything you wish as long as it does not conflict with the rules for naming PostgreSQL databases. However, we recommend that you choose a simple name with all lowercase characters. For example, we have used a database named 'production' in the following example.
With a clean PostgreSQL instance installed, you can use the following commands to get started
psql -U postgres -f init.sql template1 createdb -U admin production
psql production> create extension if not exists plv8; pg_restore -U admin -d production quickstart.backup -v
The first command line example uses the 'psql' utility to load the 'init.sql' script. This script creates the user 'admin' and the 'xtrole' group. The first option ('-U postgres') tells the system to connect as the postgres user. This user is typically the default PostgreSQL superuser. The next option ('-f init.sql') tells psql to read the init.sql script and execute the commands. The last option ('template1') tells psql to connect to which database.
You are not required to run the init.sql script against the 'template1' database. You may also run it against another database you create. Also: In newer versions of PostgreSQL, the 'postgres' database is the new default template.
By default, the init.sql script will create the 'admin' user with the password of "admin" — change the password as soon as you have your xTuple system installed and running.
The second command line example uses 'createdb' to create a new database. Notice that this command uses the same first two options as used in the psql command to specify the user to connect as. Note that now we are using the option '-U admin' to indicate we want to connect as the admin user created previously. The last option is the name of the new database we want to create (e.g., 'production').
The third command line example enabled the plv8 extension in the database. You must have already followed the instructions for installing plv8 on your system.
The final command loads the schema for the xTuple 'quickstart' Database. You can download the xTuple database backup files from the project site on GitHub.
The .backup file format is a compressed format and is used by the pg_restore binary. The -d switch enables you to specify the database into which the restore will be performed (i.e., the 'production' database in this example.) Next, we specify the name of the .backup file with the path to its location if necessary. Finally, we specify -v for verbose output.
It is important to remember that if you configured PostgreSQL to listen on a port other than the default port of 5432, you will need to specify this with '-p XXXX' where XXXX is the port number.
If you want a .sql file instead of a .backup, you can easily do this using pg_restore, as follows:
pg_restore -f quickstart.sql quickstart.backup
This example says use pg_restore to create a file called 'quickstart.sql' from the file called 'quickstart.backup'.
This completes the command line examples section.
Using pgAdmin To Get Started
pgAdmin is a free, cross-platform GUI tool for administering PostgreSQL databases. In this section. We assume you have PostgreSQL already installed and that you have followed the instructions for installing plv8. The following screenshots and narrative describe how to get xTuple ERP running on your PostgreSQL server.
Your two main objectives are
- Configuring PostgreSQL for xTuple
- Creating and Loading (i.e., restoring) the xTuple database
Configuring PostgreSQL for xTuple
Since you already have PostgreSQL and plv8 installed, you must initialize the server, so xTuple databases will run successfully on it, by following these steps (with instructions and screenshots below)
- Link pgAdmin to your PostgreSQL server
- Create a group called "xtrole"
- Create a user "admin"
- Put the user "admin" in the group "xtrole"
This section assumes you already installed PostgreSQL on your local drive (also known as "localhost" or "127.0.0.1").
Create a link between your pgAdmin application and your PostgreSQL database. If that link already exists, then you can skip this step. If not, select the "File" menu option and then select the "Add Server" option.
Add New Server
When presented with the new server screen, enter the information as shown in the next screenshot. You have flexibility in some options. However, the following must be used
- Host = localhost or 127.0.0.1
- Port = 5432
- Username = postgres
- Password = Password used for postgres user when you installed PostgreSQL
Once you have the connection between pgAdmin and PostgreSQL completed, the next step is to configure PostgreSQL so xTuple databases will run successfully. First, create the "xtrole" group by right-clicking on Group Roles and selecting the "New Group Role" option.
When creating the new "xtrole" group, you only need to enter a minimal amount of information. Enter the same information as is shown in the next screenshot. The Role name should be lower case, and use "Inherits rights from parent roles" as the only selection under Role Privileges. No password is required, and no additional information is needed under the other tabs shown.
Group Role for xtrole
Now that you have the "xtrole" group role defined, the next step is to create the "admin" user and place the user in the "xtrole" group. The next screenshot shows how you can access the screen for creating a new login role.
The screen for creating a login role for "admin" looks similar to the screen viewed before creating the "xtrole" group. However, this time you need to add more information and select more options. You have flexibility over some of the options you choose, but the following should be the same as shown in the screenshot
- Role name = admin
- Role Privileges = Select all
If you do not need the account to expire at a certain point, then leave the expiration date blank. That will keep the account open indefinitely.
Login Role for admin User
Once the admin user's properties have been defined, select the Role membership tab. On this screen you make the user "admin" a member of the group "xtrole". The following screenshot shows the end result of this action. By using the double arrows ">>" you can move "admin" from not being a member in "xtrole" to being a member in "xtrole"
Admin in Group xtrole
This is all you need to initialize PostgreSQL for xTuple. You can now load xTuple ERP databases onto the server and connect to them using your xTuple ERP client application.
Creating and Loading (i.e., restore) the xTuple Database
The final "getting started" step is to create a database and load (i.e., restore) an xTuple ERP backup file into it. The next screenshot shows how right-clicking on the "Databases" element enables you to access the "New Database" option.
List of Databases
Name your database anything you wish. In our example, we call our new database "dbTest". When creating a new database with pgAdmin, be sure to use the following values, as shown in the screenshot
- Owner = admin
- Encoding = UTF8
- Template = template1
Leave everything else with the default values pgAdmin provides. There is no need to enter additional information under any of the other tabs.
Create New Database
The database just created now appears in the list of databases, as seen in the next screenshot.
Make sure that plv8 is enabled in this database with the following sql command:
create extension if not exists plv8;
Your final step is to load an xTuple database backup file into the database created. To do this, you must first download the database backup files from the PostBooks project site on GitHub.
As the next screenshot shows, you can reach the "Restore" option by right-clicking on the new database just created. The "Restore" option is needed to load the xTuple .backup file.
Restore Backup File
The Restore screen will looks like the next screenshot. To do
- Browse your computer for the xTuple .backup file you downloaded
- Select the .backup file so the path to it appears in the "Filename" field
Leave the other options not-selected, except for the last one "verbose messaging" which can be helpful during the load process. Select OK to begin the restore process.
The restore may take several minutes to complete. This is normal. At the end of the process, you will see log messages which look like those shown in the following screenshot. Depending upon the circumstances of your PostgreSQL installation (e.g., whether previous databases have been installed there, etc.), the messages pgAdmin reports may vary. The ideal scenario on a fresh PostgreSQL install is for 0 errors to be reported and an exit code = 0. However, as the next screenshot shows, pgAdmin may report a number of errors and a non-zero exit code. If this is your result, review the list of errors by using the scroll bar in the "Restore Database" screen. Scroll to the top and review the list of errors which were reported. Most, if not all, of the errors can safely be ignored.
Here are some examples of error messages which can safely be ignored. The first error reports that the PostgreSQL procedural language plpgsql is already installed, and so it does not need to be restored
pg_restore: creating PROCEDURAL LANGUAGE plpgsql pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 2121; 2612 48797691 PROCEDURAL LANGUAGE plpgsql pg_restore: [archiver (db)] could not execute query: ERROR: language "plpgsql" already exists Command was: CREATE PROCEDURAL LANGUAGE plpgsql;
You may also see other messages such as the following; both indicate functionality already exists and does not need to be restored from the .backup file
Example 1: pg_restore: [archiver (db)] Error from TOC entry 18; 1255 7469922 FUNCTION armor(bytea) admin pg_restore: [archiver (db)] could not execute query: ERROR: function "armor" already exists with same argument types Command was: CREATE FUNCTION armor(bytea) RETURNS text AS '$libdir/pgcrypto', 'pg_armor' LANGUAGE c IMMUTABLE STRICT; Example 2: pg_restore: [archiver (db)] Error from TOC entry 20; 1255 7470043 FUNCTION crypt(text, text) admin pg_restore: [archiver (db)] could not execute query: ERROR: function "crypt" already exists with same argument types Command was: CREATE FUNCTION crypt(text, text) RETURNS text AS '$libdir/pgcrypto', 'pg_crypt' LANGUAGE c IMMUTABLE STRICT;
You should review any error messages received when restoring an xTuple database backup file. In many cases, errors reported can safely be ignored. Use common sense when reviewing error messages. If you receive an error message which concerns you, search the xTuple forums. Others in the community may have seen the same errors and shared what to do about them, if anything.
Once the restore is completed, select the OK button.
To see that the restore operation loaded the xTuple database successfully, simply refresh your pgAdmin view. Then expand the new database element to see the xTuple schema (i.e., tables, functions, etc.) listed under the Schema element. The following screenshot shows the "api" and "public" schema found in every xTuple database.
Database Schema Loaded Successfully
Now, login to your database
If you haven't downloaded it already, download the xTuple desktop client. Then use the following login options to login:
- Server = localhost or 127.0.0.1
- Database = dbTest (or whatever you named yours)
- Port = 5432
- Username = admin
- Password = admin
You can use the same steps described above to add more databases to your PostgreSQL server. However, if you are running a production database, we recommend that you run only the production database on the server — and don't load other databases onto the same PostgreSQL instance.