= Installing Postgres = [#QuickReference Quick Reference] [[BR]] ---- [#Overview Overview] [[BR]] [#InstallingtheSoftware Installing the Software] [[BR]] [#ConfiguringTheDatabaseServer Configuring The Database Server] [[BR]] [#CreatingADataCluster Creating A Data Cluster] [[BR]] [#InitializingTheDatabaseServer Initializing The Database Server] [[BR]] [#StartingtheDatabase Starting the Database] [[BR]] [#CreatingtheEvergreenDatabase Creating the Evergreen Database] [[BR]] [#InstallingTheSchemaFromSource Installing The Schema From Source] [[BR]] [#RestoringFromADumpFile Restoring From A Dump File] [[BR]] [#ConnectingtheDatabaseServerToTheRestOfEvergreen Connecting the Database Server To The Rest Of Evergreen] [[BR]] [#References References] [[BR]] ---- == Overview == PostgreSQL is a powerful object-relational open source database server. Evergreen uses Postgres as its backend datastore. Earlier versions of Evergreen used version 8.2, currently (post v1.4) Evergreen uses Postgresql 8.3. The instructions below are for installing 8.3. See the references below for instructions on installing version 8.2 if you're using an older version of Evergreen. The assumption being made in this documentation, is that the database server is separate from the open-ils server. ---- == Installing the Software == Postgres is a common default install on most distros and might already be installed. As root (using either sudo or su), run apt to see: {{{ sudo apt-get update sudo apt-get install postgresql-8.3 }}} If postgres is already installed, apt will say so and make no changes, otherwise it will be installed with default settings. During installation,a postgres user will be added to the local system (ie the computer). This is a standard linux user account and it's permissions are created during setup. '''Do not change the permissions of this user! ''' This user runs the postgres processes and should have only the permissions necessary to do that. Also created during installation is a db role called postgres. This is internal to postgres and has nothing to do with the operating system. The postgres role provides complete access to all databases created on that db server. It's important to change the passwords for both the postgres account and postgres role for security purposes. {{{ sudo passwd postgres }}} You will be prompted to change postgres's password. After doing so, you can check that it worked by trying: {{{ su postgres }}} You should gain access to the postgres account with the password you entered. If not, you likely made a mistake and can try again with the passwd command. You change the db role via psql. Switch users to postgres: {{{ su postgres }}} and log into psql: {{{ psql }}} Run the following: {{{ ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'mypassword'; #include the quotes around mypassword }}} ---- == Configuring The Database Server == === Creating A Data Cluster === Postgres uses the term "database cluster" to describe the directory it uses to store data. During the apt install, a default directory is created, usually at: /var/lib/postgresql/8.3/main. || '''NOTE: ''' our servers have been partitioned to best serve the needs of Evergreen. The default location is not usually on a partition that has been sized appropriately for the Evergreen db. Generally, you will ''' not ''' be using the default install directory for your database cluster. || You will need to determine the appropriate partition to install the cluster on. Usually, this is /data. To be sure, you can use the disk free command to see: {{{ df -h }}} should result in something like: {{{ Filesystem Size Used Avail Use% Mounted on /dev/mapper/LogVol00-VolGroup03 4.7G 145M 4.4G 4% /usr/local /dev/mapper/LogVol00-VolGroup07 1.9G 1.1G 797M 57% /var /dev/mapper/LogVol00-VolGroup08 1.9G 53M 1.8G 3% /var/log /dev/mapper/LogVol00-VolGroup09 84G 41G 39G 52% /data }}} Looking at the above, clearly the /data patition is the largest (84G) compared to the others. ||''' NOTE: ''' Having located the largest partition, you will need to create directory for the data cluster, and give the postgres user full access to it. In the spirit of keeping things standardized, we have been using the /postgresql//main format. || {{{ sudo mkdir /data/postgresql/8.3/main sudo chown postgres:postgres /data/postgresql/8.3/main }}} === Initializing The Database Server === Now you need to initialize the data cluster. ''' Remember to include the -D switch to point it at the new directory you created above and not the default one! ''' {{{ su postgres initdb -D /data/postgresql/8.3/main -E UNICODE --locale=C }}} the -E UNICODE options tells Postgres to encode the db with Unicode, and the --locale=C option tells postgres to use ISO C for localization support. Now some configuration is required to ensure the best possible performace parameters for Evergreen. In: {{{ /data/postgresql/8.3/main/postgresql.conf }}} set the following parameters: {{{ # Conifer settings effective_cache_size = 4GB #shared_buffers = 512MB work_mem = 256MB #max_fsm_pages = 250000 max_fsm_pages = 750000 maintenance_work_mem = 1GB checkpoint_segments = 64 log_line_prefix = '<%u@%h %t>' log_statement = all autovacuum = on stats_row_level = on shared_buffers = 2048MB default_statistics_target = 100 }}} === Starting the Database === To start the database: {{{ su postgres pg_ctl -D /data/postgresql/8.3/main -l // }}} Notice the -D option is again set and aimed at the right data cluster. You must use the -D option every time you start or restart the server. The log file can be anywhere the postgres user can write to. If the file doesn't exist, it will be created automatically. pg_ctl lives in /usr/lib/postgresql/8.3/bin by default. You can add it to PATH by adding the following to your shell start-up file, likely ~/.bash_profile {{{ PATH=/usr/local/pgsql/bin:$PATH export PATH }}} ---- == Creating the Evergreen Database == === Installing The Schema From Source === All of the following commands should be executed as the postgres user, so start with: {{{ su postgres }}} First you need to create a new db user which can be used to run manage the Evergreen database: {{{ createuser -P -s evergreen }}} Next, you need to: 1) create the db, 2) add a few programming languages used by Evergreen and 3) add some contributed modules to postgresql that provide extra functionality used by Evergreen like the ability to do cross-tab queries and full text searching. {{{ createdb -E UNICODE evergreen createlang plperl evergreen createlang plperlu evergreen createlang plpgsql evergreen psql -f /usr/share/postgresql/8.3/contrib/tablefunc.sql evergreen psql -f /usr/share/postgresql/8.3/contrib/tsearch2.sql evergreen psql -f /usr/share/postgresql/8.3/contrib/pgxml.sql evergreen }}} || ''' NOTE: ''' the location of the contrib directory will change depending on how you installed postgres. If you can't find one of the contrib modules listed above, use a find command to locate them ie find / -name pgxml.sql || Now run eg_db_config.pl ''' on the machine where Open-ils is installed. ''' These instructions assume a two server install - one for Open-ils and Opensrf and one for postgres. eg_db_config.pl is found on the Open-ils machine. It will likely be in Open-ILS/src/support-scripts depending on where you installed the Open-ils source. If you can't find it, run "find" on eg_db_config.pl when you find it run: {{{ perl Open-ILS/src/support-scripts/eg_db_config.pl --create-schema }}} || ''' NOTE: ''' this script uses opensrf.xml to figure out where the db server is, so it's important to have already configured opensrf to run Evergreen before running this script || You should now have a clean install of the Evergreen schema loaded with the "example consortium." || ''' NOTE: ''' after making any changes to the database or schema, you should run autogen.sh on the oils server to update opac and Staff Client files. Do this on the oils server as the opensrf user with oils up and running || === Restoring From A Dump File === It Open-ils is running stop it first. Then, doing the following as the postgres user: {{{ su postgres }}} In psql: {{{ drop database evergreen; }}} check to see that there's an evergreen user before rebuilding the server. In psql, display the users: {{{ psql# \du }}} if you don't see an evergreen user: {{{ List of roles Role name | Superuser | Create role | Create DB | Connections | Member of -----------+-----------+-------------+-----------+-------------+----------- evergreen | yes | yes | yes | no limit | {} postgres | yes | yes | yes | no limit | {} (2 rows) }}} then create the evergreen user with: {{{ createuser -P -s evergreen }}} Next, recreate the database: {{{ createdb -E UNICODE evergreen }}} Edit postgresql.conf in the data cluster (probably in /data/postgresql/8.3/main) and change: {{{ checkpoint_segments = 128 }}} This is necessary to prevent errors during the restore due to the huge side of the database. To start the restore, run: {{{ pg_restore -d evergreen // }}} || ''' NOTE: ''' It's important not to run eg_db_config.pl if you use the -d option. This seems to cause conflicts during restore || Finally, after the restore is finished (which will take 6 - 8 hours likely) run, as postgres: {{{ VACUUM ANALYZE }}} Test that everything is there by running a few queries. || ''' NOTE: ''' after making any changes to the database or schema, you should run autogen.sh on the oils server to update opac and Staff Client files. Do this on the oils server as the opensrf user with oils up and running || ---- == Connecting the Database Server To The Rest Of Evergreen == === Installing JSON::XS === Again, assuming a two server setup with Open-ils on one and postgres on the other, you need to ensure that the database server can actually communicate with the oils server. To begin with, you need to install the JSON::XS perl module on the database server. This allows the database server to exchange JSON messages with the oils server. Make sure you've got a compiler installed first: {{{ which gcc }}} If you don't, install one: {{{ apt-get update apt-get install gcc }}} Next install the mod from CPAN: {{{ perl -MCPAN -e 'CPAN::Shell→install(“JSON::XS”)' }}} Accept the defaults. Once again, you should run autogen.sh on the oils server to update opac and Staff Client files. Do this on the oils server as the opensrf user with oils up and running. === Configuring Ports And Remote Access === For a complete discussion of iptables see [wiki:sysadminiptables iptables and network configuration]. For the specific ports needed by the postgres server see [wiki:sysadminiptables#NecessaryPorts-DatabaseServer Necessary Ports - Database Server] for other Postgres specific networking issues see [wiki:sysadminiptables#SpecialPostgresqlIssues Special Postgresql Issues]. ---- == References == http://evergreen-ils.org/dokuwiki/doku.php?id=server:1.4.0:install [[BR]] http://evergreen-ils.org/dokuwiki/doku.php?id=postgresql:8.2:debian [[BR]] http://developer.postgresql.org/pgdocs/postgres/auth-pg-hba-conf.html [[BR]] http://coffeecode.net/archives/156-Tuning-PostgreSQL-for-Evergreen-on-a-test-server.html [[BR]] http://www.postgresql.org/docs/8.3/static/index.html [[BR]] http://www.stuartellis.eu/articles/postgresql-setup/ [[BR]] ---- == Quick Reference == {{{ ############################################################### # INSTALL ############################################################### sudo apt-get update sudo apt-get install postgresql-8.3 #change key passwords sudo passwd postgres #Linux user account psql# ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'mypassword'; #include the quotes around mypassword #Configuring The Database Server #Creating A Data Cluster on data partition sudo mkdir /data/postgresql/8.3/main sudo chown postgres:postgres /data/postgresql/8.3/main #Initializing The Database Server - using the new cluster su postgres initdb -D /data/postgresql/8.3/main -E UNICODE --locale=C #now configure postgresql.conf as per the file snippet at the end of these instructions #start the database: su postgres pg_ctl -D /data/postgresql/8.3/main -l // ############################################################### # CREATE THE EVERGREEN DATABASE ############################################################### ### FROM SOURCE ### su postgres createuser -P -s evergreen createdb -E UNICODE evergreen createlang plperl evergreen createlang plperlu evergreen createlang plpgsql evergreen psql -f /usr/share/postgresql/8.3/contrib/tablefunc.sql evergreen psql -f /usr/share/postgresql/8.3/contrib/tsearch2.sql evergreen psql -f /usr/share/postgresql/8.3/contrib/pgxml.sql evergreen perl Open-ILS/src/support-scripts/eg_db_config.pl --create-schema #run on the oils server with oils running ### FROM A DUMP FILE ### #stop oils #drop evergreen db su postgres psql# drop database evergreen; createuser -P -s evergreen createdb -E UNICODE evergreen #edit postgresql.conf checkpoint_segments (see file snippet) #start the restore pg_restore -d evergreen // #when finished clean up su postgres VACUUM ANALYZE ############################################################### # INSTALL JSON::XS ############################################################### which gcc #if not installed: apt-get update apt-get install gcc perl -MCPAN -e 'CPAN::Shell?install(“JSON::XS”)' ############################################################### # NETWORKING ISSUES ############################################################### [wiki:sysadminiptables iptables and network configuration][[BR]] [wiki:sysadminiptables#NecessaryPorts-DatabaseServer Necessary Ports - Database Server] [[BR]] [wiki:sysadminiptables#SpecialPostgresqlIssues Special Postgresql Issues] [[BR]] ############################################################### # # File snippet: # /data/postgresql/8.3/main/postgresql.conf # ############################################################### # # # # Conifer settings # effective_cache_size = 4GB # #shared_buffers = 512MB # work_mem = 256MB # #max_fsm_pages = 250000 # max_fsm_pages = 750000 # maintenance_work_mem = 1GB # checkpoint_segments = 64 # log_line_prefix = '<%u@%h %t>' # log_statement = all # autovacuum = on # stats_row_level = on # shared_buffers = 2048MB # default_statistics_target = 100 # # checkpoint_segments = 128 # ############################################################### }}}