How do I test Viewpoint with a Postgres 9.6 database without disturbing my production database?



This document will show you how to test a Postgres database upgrade for Viewpoint. It will explain how to install Postgres on a separate host from Viewpoint so that you can leave your existing Viewpoint database intact just in case something goes wrong. The instructions were performed using Postgres 9.6 running on Centos 6 (which should be compatible with Red Hat 6).

 

In the instructions that follow "new-postgres-host" is the host where I will install Postgres 9.6 and "viewpoint-host" is the host where my existing Viewpoint is running.

Downloaded and installing RPMs

On the host where Postgres 9.6 is to be installed I downloaded the following RPMs.

#############
[root]# wget https://yum.postgresql.org/9.6/redhat/rhel-6-x86_64/postgresql96-server-9.6.19-1PGDG.rhel6.x86_64.rpm

[root]# wget https://yum.postgresql.org/9.6/redhat/rhel-6-x86_64/postgresql96-libs-9.6.19-1PGDG.rhel6.x86_64.rpm

[root]# wget https://yum.postgresql.org/9.6/redhat/rhel-6-x86_64/postgresql96-9.6.19-1PGDG.rhel6.x86_64.rpm

[root]# wget https://yum.postgresql.org/9.6/redhat/rhel-6-x86_64/postgresql96-contrib-9.6.19-1PGDG.rhel6.x86_64.rpm

[root]# wget https://yum.postgresql.org/9.6/redhat/rhel-6-x86_64/postgresql96-devel-9.6.19-1PGDG.rhel6.x86_64.rpm

#############

I then installed the RPMs in the following order. Note that the order is important.


#############
[root]# yum install postgresql96-libs-9.6.19-1PGDG.rhel6.x86_64.rpm -y
[root]# yum install postgresql96-9.6.19-1PGDG.rhel6.x86_64.rpm -y
[root]# yum install postgresql96-server-9.6.19-1PGDG.rhel6.x86_64.rpm -y
[root]# yum install  postgresql96-contrib-9.6.19-1PGDG.rhel6.x86_64.rpm -y
[root]# yum install  postgresql96-devel-9.6.19-1PGDG.rhel6.x86_64.rpm -y

#############

I then initialized the database

#############
[root]# service postgresql-9.6 initdb
#############


Configuring Postgres to allow connections from external hosts

I configured Postgres to allow connections from other hosts by editing the pb_hba.conf file. This is helpful because it allows you to run Viewpoint on a separate host from where your database is.

#############
[root]# vi /var/lib/pgsql/9.6/data/pg_hba.conf

#############

Scroll down past all of the comments (the lines that start with #) and find the following lines near the bottom of the file. These lines only allow connections from 127.0.0.1 and require an ident server.

 

#############
host    all             all             127.0.0.1/32            ident
host    all             all             ::1/128                 ident

#############

I changed the lines so they look like the following. Note that the "0.0.0.0/0" and "::0/0" allow connections from external hosts.  "md5" allows password authentication (as opposed to requiring an ident server)

 

#############
host    all             all             0.0.0.0/0            md5
host    all             all             ::0/0                   md5

#############

I then configured postgres to listen for connections from external hosts (as opposed to just localhost)

#############
[root]# vi /var/lib/pgsql/9.6/data/postgresql.conf

#############

I uncommented the listen_addresses line and set it to '*'

#############
listen_addresses = '*'

#############

I opened port 5432 in the firewall

#############
[root]# iptables-save > /tmp/iptables.mod
[root]# vi /tmp/iptables.mod

#############
                
I added the following lines immediately *before* the line matching  "-A INPUT -j REJECT --reject-with icmp-host-prohibited"

#############
-A INPUT -p tcp --dport 5432 -j ACCEPT
                
[root]# iptables-restore < /tmp/iptables.mod                
[root]# service iptables save
#############


Starting the service and creating the new Viewpoint database

I then enabled and started the postgres service

#############
[root]# chkconfig postgresql-9.6 on
[root]# service postgresql-9.6 start

#############

The next step is to create the Viewpoint database. Note that first you have to switch to the postgres user which was creating during the RPM install process. This user has admin rights for the database. The root user does not by default. As the postgres user you can then open a postgres prompt

#############
[root]# su - postgres
[postgres]$ psql

#############

In the postgres prompt run the following commands to create a moab_viewpoint user and a moab_viewpoint database. Enter \q to exit the postgres prompt and return to the bash shell.

#############
> CREATE USER moab_viewpoint WITH PASSWORD 'changeme!';
> CREATE DATABASE moab_viewpoint WITH OWNER=moab_viewpoint;
> \q
[postgres]$ exit
#############


Creating and applying dump of existing pre-9.6 Viewpoint database

Then I created a dump of my existing Viewpoint database. Again, my existing Viewpoint is on a separate host then the host where the postgres 9.6  server I just installed is. So I ssh into the viewpoint host and create the dump.

#############
[root]# ssh root@viewpoint-host

[root]# service httpd stop
[root@viewpoint-host]# su - postgres
[postgres@viewpoint-host]$ pg_dump moab_viewpoint > /tmp/moab_viewpoint.sql

#############

I then copied /tmp/moab_viewpoint.sql to the host where I am installing postgres 9.6

#############
[postgres@viewpoint-host]$ scp /tmp/moab_viewpoint.sql root@new-postgres-host:/tmp
[postgres@viewpoint-host]$ exit
[root@viewpoint-host]$ exit

#############

Back on the host where I am installing postgres 9.6 I applied the dump to the newly created (and currently empty) Viewpoint database

#############
[root]# su - postgres
[postgres]$ psql moab_viewpoint < /tmp/moab_viewpoint.sql
[postgres]$ exit
#############


Test that the Postgres 9.6 database is reachable from the viewpoint host

 

It would be a good idea to test that the new Postgres 9.6 database can be connected to from the viewpoint host. This will verify that we set up postgres to listen for external connections properly and that port 5432 is open.

#############

[root]# ssh root@viewpoint-host

[root@viewpoint-host]# psql -h new-postgres-host -p 5432 -U moab_viewpoint moab_viewpoint

#############

This should open a Postgres prompt. Run \dt to list the tables

#############

> \dt

#############

If the dump worked correctly you should see a list of tables similar to the following.

#############

                     List of relations
 Schema |           Name           | Type  |     Owner      
--------+--------------------------+-------+----------------
 public | auth2_user               | table | moab_viewpoint
 public | django_content_type      | table | moab_viewpoint
 public | django_migrations        | table | moab_viewpoint
 public | django_session           | table | moab_viewpoint
 public | iris_job                 | table | moab_viewpoint
 public | iris_permission          | table | moab_viewpoint
 public | iris_template            | table | moab_viewpoint
 public | iris_templatedescription | table | moab_viewpoint
 public | iris_templatesection     | table | moab_viewpoint
 public | iris_widget              | table | moab_viewpoint
 public | iris_widgetproperty      | table | moab_viewpoint
(11 rows)

#############

If you see this then you successfully applied the dump the new database and you can connect to this database from the Viewpoint host. Exit the Postgres prompt by entering \q but keep a shell open on the Viewpoint host as we will use it the next step to configure Viewpoint.

#############

> \q

#############

Configuring Viewpoint to the new 9.6 database (without touching the old database)

On the host where Viewpoint (e.g. viewpoint-host) is running edit the Viewpoint configuration file. Change VIEWPOINT_DATABASE_HOST to be the host where Postgres 9.6 was just installed. Change the VIEWPOINT_DATABASE_PASSWORD to be the value you used when you created the user in the steps above.

#############
[root@viewpoint-host]# vim /opt/viewpoint/etc/viewpoint.cfg

VIEWPOINT_DATABASE_NAME = moab_viewpoint
VIEWPOINT_DATABASE_USER = moab_viewpoint
VIEWPOINT_DATABASE_PASSWORD = changeme! <-------------change this line
VIEWPOINT_DATABASE_HOST = new-postgres-host <-------------- change this line
VIEWPOINT_DATABASE_PORT = 5432

#############

Restart Viewpoint

#############
[root@viewpoint-host]# service httpd start

#############

Log into Viewpoint and make sure your old application templates are there. I recommend creating a new application template just to see if it gets stored in the new postgres 9.6 database. Name this template something like "Postgres Template Test".

Now query the new Postgres 9.6 database and make sure the new template is there.

#############
[root@viewpoint-host]# psql -h new-postgres-host -U moab_viewpoint -p 5432 moab_viewpoint

#############

At the Postgres prompt enter the following query to see if your template is in the database.

#############
> select name, date, owner from iris_template where name = 'Postgres Template Test';

#############

If it works you will see something like this. Do not worry if you see more than one row. If you make changes to the template it will save the old versions in the database as separate rows in this table.


#############
          name          |             date              |   owner    
------------------------+-------------------------------+------------
 Postgres Template Test | 2020-08-28 12:17:31.295854-06 | moab-admin

#############

To exit the Postgres prompt type "\q". This will bring you back to the bash shell.

#############
> \q

#############

If you made it this far then you have successfully installed Postgres 9.6 and connected Viewpoint to it. For further testing I recommend creating jobs from your existing templates to make sure they still work.

 

Troubleshooting

 

If Viewpoint cannot connect to the database or if the dump was not applied correctly you will probably see a "500 Internal Server Error" in the web browser. For more information see these logs

#############

[root]# cat /var/log/viewpoint/wsgi.log

...

[28/Aug/2020 21:10:48] ERROR [auth.middleware:83] could not connect to server: No route to host
        Is the server running on host "new-postgres-host" (192.168.1.94) and accepting
        TCP/IP connections on port 5432?
#############

To troubleshoot this repeat the steps in the section above that shows how to test that the Postgres 9.6 database is reachable from the viewpoint host and that the dump was applied correctly.


References

https://www.postgresql.org/download/linux/redhat/  

http://docs.adaptivecomputing.com/9-1-3/installGuide/installRH6/installRH6.htm#topics/hpcSuiteInstall/rpm/installing/installingViewpoint.htm#PostgreSQL






Last update:
2020-09-01 23:47
Author:
Nate Seeley
Revision:
1.13
Average rating:0 (0 Votes)

You cannot comment on this entry

Chuck Norris has counted to infinity. Twice.