Michael: Created page with "Platform uses two databases, mysql (cacti) and postgresql (kusudb) ==== Accessing the Kusu DB ==== * PCM core services: database

  Access the PCM database   # export PGP..."

2012-12-07T12:17:52Z

Created page with "Platform uses two databases, mysql (cacti) and postgresql (kusudb) ==== Accessing the Kusu DB ==== * PCM core services: database <pre> Access the PCM database # export PGP..."

New page

Platform uses two databases, mysql (cacti) and postgresql (kusudb)

==== Accessing the Kusu DB ====
* PCM core services: database
<pre>
Access the PCM database
# export PGPASSWORD=`cat /opt/kusu/etc/db.passwd`
# psql -U apache kusudb
</pre>

===== List the database tables =====
<pre>
kusudb=> \d
</pre>

===== Exit/Quit the database =====
<pre>
kusudb=> \q
</pre>

===== Kusu Database Command =====
<pre>
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
</pre>

===== Backup the database =====
<pre>
# pg_dump -U apache kusudb > db.backup
Add access permissions to the
/var/lib/pgsql/data/pg_hba.conf file
local all postgres trust
</pre>
===== Restore the database =====
<pre>
# psql -U postgres kusudb < db.backup
</pre>

==== Update 'nics' and 'alteregos' table ====
<pre>
- pdsh -w comp[000-231] /sbin/ifconfig eth0 | grep -v inet6 | grep -E 'inet|HWaddr' >> comp-ip2.lis
- cat comp-ip2.lis | sort > comp-ip2-sorted.lis
- genfile.bash > nics-tab-toupdate.sql
- psql -U apache -d kusudb -f /home/test/viglen/nics-tab-toupdate.sql

Note: - replace nics with alteregos in 'nics-tab-toupdate.sql' for alteregos table.
- also, you may need to add ngid=nn (ie: ngid=15) to be more precise update
</pre>

<syntaxhighlight>
File: genfile.bash
#!/bin/bash

l=1
cat comp-ip2-sorted.lis | while read a
do
if [ ${l} -eq 1 ]
then
ip=`echo "${a}" | cut -f3 -d':' | cut -f1 -d' '`
l=0
else
mac=`echo "${a}" | awk '{print $NF}' | tr '[A-Z]' '[a-z]'`
l=2
fi

if [ ${l} -eq 2 ]
then
#n=`echo "${a}" | cut -f1 -d':'` # for checking/verifying purposes
#echo "# ${n}:" # for checking/verifying purposes
echo "update nics set mac='${mac}' where ip='${ip}';"
l=1
fi
done
</syntaxhighlight>

==== Updating Platform DB for ISF Integration ====
* Needed to reset postgres user DB password so I could add ria user in ISF (PCM 3.0)

1. Backup the old postgres configuration
<syntaxhighlight>
cd /var/lib/pgsql/9.0/data
cp pg_hba.conf ph_hba.conf.ORIG

# edit pg_hba.conf so it includes the following at the top (or just the following line)
local all postgres trust
</syntaxhighlight>

2. Restart postgres and update the postgres password
<syntaxhighlight>
[root@vhpchead data]$ /etc/init.d/postgresql-9.0 restart
Stopping postgresql-9.0 service: [ OK ]
Starting postgresql-9.0 service: [ OK ]

[postgres@vhpchead ~]$ psql -d kusudb
psql (9.0.4)
Type "help" for help.

kusudb=# alter user postgres with password 'Letmein';
ALTER ROLE
</syntaxhighlight>

Platform Kusu DB - Revision history

Michael: Created page with "Platform uses two databases, mysql (cacti) and postgresql (kusudb) ==== Accessing the Kusu DB ==== * PCM core services: database
Access the PCM database # export PGP..."