Smile Guide: PostgreSQL

Installation and Module Setup for Mac
Installing Postgres

There are several approaches to installing PostgreSQL. You have the option to install the Postgres desktop application (which we will guide you through in this tutorial) as well as the option to download the necessary packages from EDB.

1. Make sure to STOP your SmileCDR instance.
2. Open the following link: https://www.postgresql.org/download/macosx/
3. You’ll notice that there are several methods for installing PostgreSQL on your computer. The simplest approach for a Mac computer is to download the desktop application. Let’s click on the hyperlink under the Postgres.app heading: 
   
   
   

4. Click on downloads then downloads:

   
   

    

5. Open the download folder, then follow the instructions on your screen to drag it to your applications folder

6. Open Postgres by double clicking on the desktop app

   
   
   

7. You should now see the following screen: be sure to click initialize

8. Make sure to click on start at the top right corner to start the server (if it isn’t already running automatically):

   
   
   

9. Now we must configure your $PATH to use the command line tools included with Postgres.app. To do so, open a new Terminal window, copy and paste the following command into your Terminal, then press enter:
   sudo mkdir -p /etc/paths.d &&
   echo /Applications/Postgres.app/Contents/Versions/latest/bin | sudo tee /etc/paths.d/postgresapp
   

10. You should be prompted to type in your computer password. Do so, then press enter:

Creating a CDR Database

The next step will be to create a postgres database for the purpose of connecting to your Smile CDR instance’s Cluster Manager.

1. Open a new terminal window.
2. Run (copy and paste) the following commands in your terminal, one at a time (make sure to include the semicolons at the end of lines b, c and d):
   

   psql -U postgres
   CREATE DATABASE cdr;
   CREATE ROLE cdr LOGIN password 'SmileCDR';
   GRANT ALL PRIVILEGES ON DATABASE cdr TO cdr;

3. Now, we must initialize a new SQL Server database for the Cluster Manager. To do so

   a) Open the following link
   b) On the page that opens, hover over the large command until “copy” appears in the top right corner. Click on copy
   
   
   c) In your Terminal, paste the command and hit enter

4. Next, we’ll initialize a new PostgreSQL database for FHIR Storage (Relational). To do so:

   a)Open the following link 
   b)On the page that opens, hover over the large command until “copy” appears in the top right corner. Click on copy
   
   
   c) In your Terminal, paste the command and hit enter

Creating a Persistence Database

The next step will be to create a postgres database we can connect to your Smile CDR instance.

1. Open a new terminal window
2. Run (copy and paste) the following commands in your terminal, one at a time (make sure to include the semicolons at the end of lines b and c):
   a)psql -U postgres
   b)CREATE DATABASE cdr_persistence;
   

   c)GRANT ALL PRIVILEGES ON DATABASE cdr_persistence TO cdr;
   

Master Properties Configuration 

1. Locate your Smile CDR folder on your computer 
2. Open the classes folder
3. Open the file titled “cdr-config-Master.properties” in any text editor (for the purposes of this exercise, VS Code will be used)
4. Scroll down to # Cluster Manager Configuration and ensure that your properties are filled in as follows:
   module.clustermgr.config.db.driver                             =POSTGRES_9_4
   module.clustermgr.config.db.url                                =jdbc:postgresql://localhost:5432/cdr
   module.clustermgr.config.db.username                           =cdr
   module.clustermgr.config.db.password                           =SmileCDR
   module.clustermgr.config.db.schema_update_mode                 =UPDATE
   module.clustermgr.config.stats.heartbeat_persist_frequency_ms  =15000
   module.clustermgr.config.stats.stats_persist_frequency_ms      =60000
   module.clustermgr.config.stats.stats_cleanup_frequency_ms    = 300000
   
   
   
5. Scroll down to # Database Configuration and ensure your properties are filled in as follows:
   module.persistence.type                                        =PERSISTENCE_R4
   module.persistence.config.db.driver                            =POSTGRES_9_4
   module.persistence.config.db.url                               =jdbc:postgresql://localhost:5432/cdr_persistence
   module.persistence.config.db.hibernate.showsql                 =false
   module.persistence.config.db.username                          =cdr
   module.persistence.config.db.password                          =SmileCDR
   module.persistence.config.db.hibernate_search.directory        =./database/lucene_fhir_persistence
   module.persistence.config.db.schema_update_mode                =UPDATE
   module.persistence.config.dao_config.expire_search_results_after_minutes=60
   module.persistence.config.dao_config.allow_multiple_delete.enabled=false
   module.persistence.config.dao_config.allow_inline_match_url_references.enabled=false
   module.persistence.config.dao_config.allow_external_references.enabled=false
   
   
   
6. Save this file without changing its directory by holding the “command” and “s” keys. 
7. Voila! You’ve now created a PostgreSQL database and connected it to your Smile CDR instance. You can now restart Smile 

Installation and Module Setup for Windows 

Installing Postgres

1. Download the latest PostgreSQL package from the following link.
   
   

2. Double click on the downloaded package and start following the installation instructions. Once the below page appears, create a password for the database super user. Be sure to remember this password! For simplicity purposes you can choose “password.” Click Next
   

   
3. Continue the setup with all default values until the following page appears. Uncheck the option to launch stackbuilder and click on the Finish button in the bottom right corner to complete the installation process.
   
   
   
   
4. Launch the pgadmin 4 application from your computer’s Start menu.
5. Create a master password that you will remember, this will be used each time you open the pgAdmin application.
   
   
6. Now click on the servers and enter the password that you had setup for postgres during the installation process (“password” in this case).
   
   
   
7. Next up, we’ll be creating a new user that will have the privileges to create and manage databases within the service: 
   1. 1. Under “Servers,” click on PostgreSQL 14>Database>Right click on postgres. Then click on the Query Tool  (as shown in the image below):
         
         
      2. The following two SQL queries will create a role “newuser” and assign privileges to manage DB. To run these commands, copy and paste them into the Query Editor, then click on the play button (see image below):
         CREATE ROLE "newuser" WITH LOGIN PASSWORD 'password';
         ALTER ROLE newUser CREATEDB;
         
         
         

Creating a CDR Database

The next step will be to create a postgres database for the purpose of connecting to your Smile CDR instance’s Cluster Manager. 

1. In pgAdmin, under Servers, click on PostgreSQL 14 > right click on Databases > Create > Database
   
   
   
2. Make sure to create your new database with the following general settings, then click Save:
   
   
   
   

Creating a Persistence Database 

The next step will be to create a postgres database we can connect to your Smile CDR instance. 

1. In pgAdmin, under “Servers,”click on PostgreSQL 14 > right click on Databases > Create > Database
   
   

   

    

2. Make sure to create your new database with the following general settings, then click Save:

   
   

Master Properties Configuration

1. Stop your Smile CDR instance on Docker 
2. Create a folder and name it something like “SmilePostgresDB”
3. We must copy the classes folder from the Smile CDR Docker container onto your local machine. To do so:
   Open your command prompt application
   Navigate to the SmilePostgresDB folder you created in Step 2
   Type the following line, then hit enter:
   docker cp smilecdr:/home/smile/smilecdr/classes ./
4. Open your computer’s File Explorer application. Within the downloads folder you should now notice a folder called classes. Open this folder
5. Open the file called cdr-config-Master.properties in any text editor (i.e. Notepad, VS Code, etc.)
6. Scroll down to # Cluster Manager Configuration and ensure that your properties are filled in as follows:
   
   
   For simplicity purposes, you can copy and paste the following:
   module.clustermgr.config.db.driver                             =POSTGRES_9_4
   module.clustermgr.config.db.url                                =jdbc:postgresql://host.docker.internal:5432/cdr
   module.clustermgr.config.db.username                           =newuser
   module.clustermgr.config.db.password                           =password
   module.clustermgr.config.db.schema_update_mode                 =UPDATE
   module.clustermgr.config.stats.heartbeat_persist_frequency_ms  =15000
   module.clustermgr.config.stats.stats_persist_frequency_ms      =60000
   module.clustermgr.config.stats.stats_cleanup_frequency_ms      =300000
   
   
7. Scroll down to # Database Configuration and ensure your properties are filled in as follows:
   
8. 
   

   For simplicity purposes, you can copy and paste the following
   module.persistence.type                                        =PERSISTENCE_R4
   module.persistence.config.db.driver                            =POSTGRES_9_4
   module.persistence.config.db.url                               =jdbc:postgresql://host.docker.internal:5432/cdr_persistence
   module.persistence.config.db.hibernate.showsql                 =false
   module.persistence.config.db.username                          =newuser
   module.persistence.config.db.password                          =password
   module.persistence.config.db.hibernate_search.directory        =./database/lucene_fhir_persistence
   module.persistence.config.db.schema_update_mode                =UPDATE
   module.persistence.config.dao_config.expire_search_results_after_minutes=60
   module.persistence.config.dao_config.allow_multiple_delete.enabled=false
   module.persistence.config.dao_config.allow_inline_match_url_references.enabled=false
   module.persistence.config.dao_config.allow_external_references.enabled=false
   

    

9. Save this file by holding ctrl and s (do not change the directory to which it is saved)
   
10. Now we must copy back the classes folder from the Download’s directory on your local machine to the Smile CDR docker image. To do so:
    
    1. Open your command prompt
    2. Navigate to the SmilePostgresDB directory you created in Step 2
    3. Copy and paste the following line into your command prompt, then hit enter:

    docker cp ./classes smilecdr:/home/smile/smilecdr/

11. Voila! You’ve now created a PostgreSQL database and connected it to your Smile CDR instance. You can now restart Smile CDR.