Pre-reqs for ICRISAT LIMS Install: PostgreSQL

(Ref Id: 1344566820)

The downloads for the Windows binaries at the PostgreSQL website are confusing. You have to hunt through several folders to find a Windows version. After trying several tries I found 8.3.19 binaries with no installer [1] but that is ok, you really do not need the installer to get PostgreSQL to run so long as you know how to start it. The download is about 23.5MB (it took me about 4 minutes to download).

PostgreSQL is a robust open source relational database management system that is widely used. You can find professional support for it fairly easily which, if you are planning to use PostgreSQL in a production environment, I recommend you look into. This is for a test installation of the IRCISAT BioInformatics LIMS so we do not need anything fancy. Right now it just needs to work for our testing.

According to the PostgreSQL README file that is conveniently located in the download page itself this version is supposedly for expert users only and it requires that Microsoft Visual C++ 8.0 runtimes be installed. I think the 'expert' stuff is a bunch of hoobaloo designed to make you freak out and use the EnterpriseDB version instead. Don't fall for that kind of stuff. Also, regarding the C++ runtimes, you may already have all of these runtimes installed and available on your system so it is worth just trying to run the database server first. You'll know if you cannot do the basics like creating a database, accessing it, etc. in short order.

A normal new user environment that insists on using a Windows host for the database server will most likely just use the EnterpriseDB single installer available at the PostgreSQL website. I just don't need it for my purposes, so I'll just use the binaries thank you very much.

For Windows there is a graphical utility for managing the database called pgAdmin or pgAdminIII that comes with the full EnterpriseDB installation. You can use perform almost all maintenance and configuration tasks but I hardly use it. I recommend that you become acquainted with performing maintenance and configuration tasks for PostgreSQL using the command line utilities instead. It will be easier and quicker if you eventually migrate the database server to a Unix/Linux environment in the future.

Before you can run the database you have to initialize a database. In order to do that you have to run initdb:

c:/temp/Postgresql-8.3.19/pgsql/bin>initdb "c:/temp/Postgresql-8.3.19/pgsql/data"

The files belonging to this database system will be owned by user "Joel".
This user must also own the server process.

The database cluster will be initialized with locale English_United States.1252.

The default database encoding has accordingly been set to WIN1252.
The default text search configuration will be set to "english".

fixing permissions on existing directory c:/temp/Postgresql-8.3.19/pgsql/data ..
. ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers/max_fsm_pages ... 32MB/204800
creating configuration files ... ok
creating template1 database in c:/temp/Postgresql-8.3.19/pgsql/data/base/1 ... o
k
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

"postgres" -D "c:/temp/Postgresql-8.3.19/pgsql/data"
or
"pg_ctl" -D "c:/temp/Postgresql-8.3.19/pgsql/data" -l logfile start

Note: I created the folder for the database files under the directory where I dumped the PostgreSQL binary files. This is not recommended and I just mainly did this so that I could eventually throw everything away after I'm done with my testing.

As the initdb output instructions mention running the database now means you have to run "postgres" -D [path to your database] to start the thing. You'll see the following:


LOG: database system was shut down at 2012-08-09 20:34:35 CDT
LOG: database system is ready to accept connections
LOG: autovacuum launcher started

At this point the PostgreSQL server is running and you will no longer have a prompt available on the command line. Open a different command prompt and run the following:


c:/temp/Postgresql-8.3.19/pgsql/bin>psql -l

I get the current databases that exist:



Name | Owner | Encoding
----------+--------------+----------
postgres | Joel | WIN1252
template0 | Joel | WIN1252
template1 | Joel | WIN1252

These are the default databases that initdb creates. You can ignore them for now. We want to create a database for the ICRISAT LIMS, so let's use createdb (note: I am fairly certain that there will be a configuration setting that will allow me to choose the database name. For now I will just give it an arbitrary name):

c:/temp/Postgresql-8.3.19/pgsql/bin>createdb icrisatlims

c:/temp/Postgresql-8.3.19/pgsql/bin>psql -l
List of databases
Name | Owner | Encoding
-------------+--------------+----------
icrisatlims | Joel | WIN1252
postgres | Joel | WIN1252
template0 | Joel | WIN1252
template1 | Joel | WIN1252
(4 rows)

Next make sure that we have the LIMSDbScript.sql script that comes inside of the ICRISAT zip file (I dumped it into the temp directory as well):


c:/temp/Postgresql-8.3.19/pgsql/bin>dir c:/temp/LIMSDbScript.sql
Volume in drive C is OS
Directory of c:/temp
05/10/2010 03:57 PM 24,476 LIMSDBScript.sql
1 File(s) 24,476 bytes
0 Dir(s) 11,324,448,768 bytes free

Now to create the database:


c:/temp/Postgresql-8.3.19/pgsql/bin>psql -d icrisatlims -f "c:/temp/LIMSDBScript.sql"

You will see a few warnings regarding grants that we can ignore for now. Let's log in:


psql -d icrisatlims

Finally run a single SQL to ensure that we actually can see some data:

icrisatlims=# select * from lims_crops;

crop_name | cotyledon_type
----------------+----------------
Arabidopsis | dicot
Barley | monocot
Chickpea | dicot
Finger millet | monocot
Foxtail millet | monocot
Groundnut | dicot
Lotus | dicot
Maize | monocot
Medicago | dicot
Pea | dicot
Pearl millet | monocot
Pigeonpea | dicot
Potato | dicot
Rice | monocot
Rye | monocot
Sorghum | monocot
Soybean | dicot
Wheat | monocot
(18 rows)

This looks successful. The ICRISAT folks did not include a sample database so the only data you get with this script are some crop records and a few DNA protocols that list some documents that are not included either.

The next tutorial will either be to ensure that the Java SDK is available for use or the Tomcat installation.

References

[1] http://www.postgresql.org/ftp/binary/v8.3.19/win32/

Go Back

Citation: Pre-reqs for ICRISAT LIMS Install: PostgreSQL. (2012). Retrieved Thu Sep 21 04:35:09 2017, from http://www.limsexpert.com/cgi-bin/bixchange/bixchange.cgi?pom=limsexpert3;iid=readMore;go=1344566820