====== Preparing PostgreSQL for BPS ======

This tutorial shows the setup of a BPS2 installation on a PostgreSQL server running on Windows. Basicly for Servers on linux or another operating system the procedure is the same, but the directory path namings and file permissions are different different; check the PostgreSQL documentation in case of questions.

What I will do in this tutorial is just prepare one production database, the quality and test databases would be setup similar. Please read in [[db-setup|Database setup]] the general picture about instances for a BPS installation. 

I am going to name the database //prod//, and the tablespaces //prod_//something in this example. So the steps involved are to create

    * A database named //prod//
    * A tablespace for data named //prod_dat//
    * A tablespace for indexes named //prod_inx//

For a minimal system it might be acceptable to use one single tablespace for all indexes and data. Whatever tablespace setup you choose depends also on the hardware available; so it will likely bring a better performance having data and index on two different drives. Even when you only have one single drive it is advisable to use two tablespaces, because it might be easier then to move the index later to a different drive when the system grows. 

===== Creating directories for the tablespace =====

First I need to make a new directory for each tablespace. Remember that PG is running as a service under the user account postgres; this means if I make those directories anywhere on the drive, I need to set the access security of the directory so that the postgres user can read and write.

Have a look at the permissions of one of the default tablepace directories, im my installation they are under //C:\PostgreSQLdata//.

While basically the tablespace directory can reside on any disk as long as these settings are made correctly, I will put them side by side to the default tablespaces. This has the nice side effect that the correct permissions are already inherited from the parent data directory. So I create the tablespaces here for my own convenience:

{{ bps2:pgsqlprep:img-1000.png |The tablespace directiories}}

Start up the pgAdmin III graphical administration program (of cause you could as well use the psql command line interpreter to do all following tasks - but in that case it is valid to assume you are an expert and know how the following tasks by manual commands):

{{ bps2:pgsqlprep:img-1001.png |pgAdmin III}}

Double click on the database to open it. A logon window will show up where I must enter the superuser password (the one defined during installation). If only authorized users have access to the server it is safe to check the option //Store password// for convenience, so you don't have to enter the password again later:

{{ bps2:pgsqlprep:img-1002.png |Connect to Server}}

{{ bps2:pgsqlprep:img-1003.png |Saving Passwords}}

===== Creating the tablespaces =====

Right click on //Tablespaces// and then on //New Tablespace// to create the first new taplespace.

Enter the tablespace name and the full path of the tablespace directory we created before. You may as well leave the owner field empty in case you want to use the default postgres user as owner. 

{{ bps2:pgsqlprep:img-1005.png |Enter Location of the Directory}}

Repeat the process for the index tablespace //prod_inx//.

===== Creating the database =====

Finally we are ready to create the database now that the tablespaces are in place. Right click on //Databases// and then on //New Database// to create the database.

Enter the database name and and select the desired superadministrator as owner. Select the data tablespace as default tablespace for this database:

{{ bps2:pgsqlprep:img-1006.png |New Database}}

Now the basic infrastructure for the BPS setup is prepared. A postgres user with name //prod// will be created as application owner by the database initialisation scripts of the BPS software, so you need not to that here in PgAdmin III.

{{ bps2:pgsqlprep:img-1007.png |Database Is Created}}

Everything is prepared now for the next step, creating the application objects within the database. 

===== What next =====

Continue now with: [[createdb|Creating the BPS datastore]]