NOHRSC Technology > NOHRSC GIS Applications

Help

Detailed, online help information on PostgreSQL can be obtained at www.postgresql.org. Additional useful information can be found on any search engine. Please feel free to contact Anders Nilsson at if these sources of information or the instructions below prove to be lacking.

PostgreSQL Scripts

Efficient and secure PostgreSQL servers rely on a number of database management tasks that are (normally) executed from a cron table. Minimum tasks include:

1. Running the vacuum command on a regular basis to free unused disk space. The vacuum command should invoke the analyze option to update table statistics (probably the most important task for database efficiency);
2. Taking frequent images of the current write ahead log, archiving full write ahead logs, and making periodic copies of the PostgreSQL table spaces. These tasks, along with their respective restore procedures, ensure that you can recover your database in the event of a failure; and
3. Periodically dumping your database schemas so that you can rebuild your database tables in the event of a database failure.

Please contact your PostgreSQL database administrator to verify that these minimum administrative considerations are in place and operating properly before proceeding.

PostgreSQL Parameters

You should not have to modify parameters in the postgresql.conf file. We're assuming that you already have PostgreSQL installed and that the installation is properly configured for normal River Forecast Center operations. IHABBS makes minimal use of the database and, as such, should not appreciably effect its performance.

In the event that you add additional PostgreSQL users to run IHABBS, you may find it necessary to modify either the pg_hba.conf and/or pg_ident.conf files. Modification of these files is beyond the scope of these instructions and should be attempted by your database and systems administrators.

PostgreSQL Client-Side Preparation

The following PostgreSQL environment variables must be considered installing and running IHABBS:

a) PGHOST=server_name;          - i.e. PGHOST=odb0
b) PHHOSTADDR=server_address;   - i.e. PGHOSTADDR=10.1.10.169
c) PGUSER=user_name;            - i.e. PGUSER=$USER

Setting PGDATABASE=database_name (i.e. PGDATABASE=operations) may make it more convenient to interact with the database outside of IHABBS. IHABBS uses its own configuration file to connect to a database and to set the schema search path.

We recommend setting PGCONNECT_TIMEOUT=0.

a) GISRS_DATABASE=database_name;   - i.e. GISRS_DATABASE=operations; and
b) GISRS_SCHEMA=schema_name;       - i.e. GISRS_SCHEMA=gisrs

All other environment variables more than likely are nonspecific to IHABBS.

The default configuration file used by IHABBS, "nohrsc.cfg" needs to specify the schema name as well as the database name used above. In nohrsc.cfg, add:

DATABASE   database_name   - i.e. DATABASE    operations
SCHEMA     schema_name     - i.e. SCHEMA      gisrs

PostgreSQL Runtime Library

Make sure the PostgreSQL runtime library is properly installed on your client machine. The runtime library should be indicated by the LD_LIBRARY_PATH environment variable (i.e., LD_LIBRARY_PATH=:/opt/postgres/lib). Please seek the assistance of your database and system administrators.

PostgreSQL Server-Side Preparation

You may wish to isolate IHABBS from your normal River Forecast Center operations. The following options allow for a certain level of isolation on an existing PostgreSQL server. If you wish to totally isolate IHABBS on its own server, please contact Anders Nilsson at for detailed instructions. Please refer to your PostgreSQL documentation for details before executing any of these suggestions. This document is not intended to be a complete description of referenced PostgreSQL commands. Rather, it is intended to serve as a guide.

NOTE: None of the following should be necessary if you are satisfied with your current PostgreSQL configuration. Perhaps only some of these suggestions are suitable to your needs.

1. Creating PostgreSQL Users for IHABBS:

   You may find it useful to create IHABBS specific PostgreSQL users. The following command line input safely creates an new PostgreSQL user (see PostgreSQL documentation for details):

      createuser -A -D user_name
   

   New users, at minimum, need to be added to pg_hba.conf (see PostgreSQL documentation on managing new users).

   PostgreSQL users are not the same as operating systems users. The PostgreSQL user is relevent to the PGUSER environment variable which defines the level of access to PostgreSQL resources (databases, schemas, tables, etc.).

   We use trusted user interface to PostgreSQL. PostgreSQL allows various methods of user verification that are beyond the scope of this document. Please refer to the PostgreSQL documentation if you use something other than trusted.

2. Initializing a location for the IHABBS PostgreSQL database

   • Creating Tablespaces for IHABBS with PostgreSQL 8+

     Provided that you have access to a number of physically seperated disk drives, you can isolate IHABBS database I/O from the rest of your database I/O by creating PostgreSQL tables spaces prior to creating the a new PostgreSQL database for IHABBS.

     First, identify your disk resouces paths. It may prove useful in the future if these paths are symbolic links. Then run psql \template1. This excutes an interface to PostgreSQL's "seed" database. Commands run under \template1 have lasting effect on future administrative tasks so use with caution.

     From the psql interface execute:

        CREATE TABLESPACE tablespace_name LOCATION 'tablespace_path';
     

     This command will make this disk resource available to future databases. Properly managed (at the system level, database creation level, and table creation level), tablespaces can allow you to manage PostgreSQL I/O.

     Then assign PostgreSQL level permissions on tablespaces to PostgreSQL users as follows (see PostgreSQL documentation for details):

        GRANT CREATE ON TABLESPACE tablespace_name TO user_name;
     

   • Specifying an alternate location for IHABBS with PostgreSQL 7.x

     Alternate locations for the database can be accomplished with earlier versions of PostgreSQL, but it is slightly trickier. First, an variable must be setup in the postgres superuser's (the user that will actually run the database) environment that specifies the location. For convenience and avoidance of confusion, it is recommended that this be named PGDATA or something similar.

     For the OB6 install on AWIPS, an area named PGDATA_LOCAL has already been specified. It used the following for creation on the command line:

        initlocation PGDATA_LOCAL    
     

3. Creating PostgreSQL Database for IHABBS:

   You may wish to have IHABBS occupy its own PostgreSQL database. If so, execute one of the following commands from psql \template1 interface:

   • Using the default database location :

        CREATE DATABASE database_name ;
     

   • Or, specifying a tablespace (location) with PostgresSQL 8+ :

        CREATE DATABASE database_name TABLESPACE tablespace_name;
     

   • Or, specifiying a location with PostgreSQL 7.x (in this case, on AWIPS):

        CREATE DATABASE database_name WITH LOCATION = 'PGDATA_LOCAL' ;
     

   Then assign assign PostgreSQL user level permissions as follows (see PostgreSQL documentation for details):

      GRANT TEMPORARY ON DATABASE database_name TO user_name;
   

   You can then quit the interface by typing:

      \q
   

4. Creating PostgreSQL Schema for IHABBS:

   You may wish to isolate IHABBS from the rest of your database by having it occupy its own PostgreSQL schema. This is not mandatory, as you can place the IHABBS install in the default schema (most likely "public"). From psql \database_name (select the database in which the new schema should reside) execute (see PostgreSQL documentation for details):

      CREATE SCHEMA schema_name;
   

   Assign PostgreSQL user permissions to the schema as follows ((see PostgreSQL documentation for details):

      GRANT USAGE  ON SCHEMA gisrs TO opps;
      GRANT CREATE ON SCHEMA gisrs TO opps;
   

National Weather Service National Operational Hydrologic Remote Sensing Center Office of Water Prediction 1735 Lake Drive W. Chanhassen, MN 55317
Contact NOHRSC Glossary Credits Information Quality Page last modified: Oct 27, 2022 - cloud	About Us Disclaimer Privacy Policy FOIA Career Opportunities