Configure PostgreSQL Database on Your Linux-Based Host Operating System for UMDS for Consolidated SDDC

On a virtual machine with Ubuntu 14.04 Long Term Support (LTS) where you plan to install vSphere Update Manager Download Service, configure a PostgreSQL database instance.

Prerequisites

Create a virtual machine for vSphere Update Manager Download Service (UMDS). See Virtual Machine Specifications from the Planning and Preparation documentation.

Procedure

Open a Web browser and go to https://sfo01w01vc01.sfo01.rainpole.local/vsphere-client.


Setting	Value
User name	administrator@vsphere.local
Password	`vsphere_admin_password`

In the vSphere Web Client, right-click the sfo01umds01 virtual machine and select Open Console to open the remote console to the virtual machine.

At the command prompt, log in using the following credentials.

Setting	Value
User name	svc-umds
Password	`svc-umds_password`

Install Secure Shell (SSH) server, and end the session.
```
sudo apt-get update
sudo apt-get -y install SSH
exit
```

Log back in to the UMDS virtual machine using Secure Shell (SSH) client.

Open an SSH connection to the virtual machine sfo01umds01.

Setting	Value
User name	svc-umds
Password	`svc-umds_password`

Install and start PostgreSQL and its dependencies.
```
sudo apt-get -y install vim perl tar sed psmisc unixodbc postgresql postgresql-contrib odbc-postgresql
sudo service postgresql start
```
The installation operation creates a user account called postgres that is associated with the default Postgres role. The postgres account is used to establish a service account for the Update Manager Download Service.
Log in as postgres user, and create a database instance and a database user, by running the following commands.
When prompted, enter and confirm the umds_db_admin_password password.
```
sudo su - postgres
createdb umds_db
createuser -d -e -r umds_db_admin -P
```

Enable password authentication for the database user.

Navigate to the folder that contains the PostgreSQL configuration file pg_hba.conf.

Linux system	Default Location
Ubuntu 14.04	/etc/postgresql/`postgres_version`/main

cd /etc/postgresql/postgres_version/main

In the PostgreSQL configuration file, enable password authentication for the database user by inserting the following line right above local all all peer.

You can use the vi editor to make and save the changes.

#TYPE	DATABASE	USER	ADDRESS	METHOD
local	umds_db	umds_db_admin		md5

Log out as a PostgreSQL user by running the following command.
```
logout
```

Configure the PostgreSQL driver and the data source name (DSN) for a connection to the UMDS database.

Edit the ODBC configuration file.
```
sudo vi /etc/odbcinst.ini
```

Replace the file content with the following content and save the change using :wq.

[PostgreSQL]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=/usr/lib/x86_64-linux-gnu/odbc/psqlodbcw.so
Debug=0
CommLog=1
UsageCount=1

Edit the system file /etc/odbc.ini.
```
sudo vi /etc/odbc.ini
```

Replace the file content with the following content and save the change using :wq.

[UMDS_DSN]
;DB_TYPE = PostgreSQL
;SERVER_NAME = localhost
;SERVER_PORT = 5432
;TNS_SERVICE = <database_name>
;USER_ID = <database_username>
Driver = PostgreSQL
DSN = UMDS_DSN
ServerName = localhost
PortNumber = 5432
Server = localhost
Port = 5432
UserID = umds_db_admin
User = umds_db_admin
Database = umds_db

Create a symbolic link between the UMDS and the PostgreSQL by running the following command.
```
ln -s /var/run/postgresql/.s.PGSQL.5432 /tmp/.s.PGSQL.5432
```
Restart PostgreSQL.
```
sudo service postgresql restart
```