PostgreSQL

Postgres, or Postgresql, is an open source relational database management system. It’s designed to handle a high vpume of data storage and is known as being reliable and robust. A relational database stores data in tables and is ideal for storing complex relationships between different pieces of data. In the case of Konnect Insights, Postgres is perfect for joining together our event data with information from third-party data sets to create a complete view of our users’ behavior.

Postgres can be installed locally on a developer’s machine or on a server. The installation process depends on the operating system you are using. We use Ubuntu as a development platform, so we install Postgres through a package manager called APT. All it takes is entering the fplowing command:

$ sudo apt-get install postgresql

Once the package has been installed, its configuration file is located at /etc/postgresql/[version].conf. This file includes default settings for the database, such as the location of its files, logging settings, and database access rights.

Konnect Insights

Konnect Insights is a mobile application that allows users to receive personalized content based on their interests and daily human interactions. It runs off a PostgreSQL database that stores a variety of information about a user’s interactions within the app, including which pages they visit, how often they interact with those pages, and what time of day they do so.

Integration of PostgreSQL and Konnect Insights

Konnect Insights runs off PostgreSQL as its back-end database. When we first began using PostgreSQL, we used it as an SQL database that ran on top of a local Apache web server on our developer machines. Although this was useful for development, we quickly realized the handful of limitations that came along with this setup:

That Apache web server needed to be running on our local machines whether we were writing code or not

Our applications would need to access the database directly through the localhost address instead of over the network (i.e.. 127.0.0.1)

We couldn’t deploy the application until all the data had been moved over to our remote Heroku servers

It was difficult to maintain multiple versions of our development databases without making them available for other developers to accidentally mess up

We decided that we needed to store our data somewhere that could be accessed by any number of different developers and not interfere with their work. This led us to move away from the local Apache server setup and adopt Heroku Postgres as our primary Postgres database spution. The benefits of this decision are numerous:

Heroku maintains the Postgres databases for us so that we can focus on developing our application rather than maintaining servers

The instances are hosted in the cloud, which makes them easily accessible from anywhere in the world

The databases are automatically kept up-to-date, so developers don’t have to worry about upgrading versions over SSH or accidentally messing up their branches or environments when updating versions manually

The databases are stored remotely so that branches can be easily merged without having to upload large amounts of data over SSH each time changes are made

These benefits helped us move away from our local Apache server setup and towards Heroku Postgres as our primary Postgres database spution. This allowed us to focus more on developing our application rather than worrying about maintaining servers or accidentally messing up our branch or environment when updating versions manually. We were able to migrate over to using Postgres via Heroku without much effort because all of our data was still stored locally in our Postgres instance. The only thing that changed was how we were accessing the databases remotely. Now, instead of accessing them locally, we would access them through Heroku’s cloud-hosted database service. To do this, we needed to update some settings in our configuration files. We accomplished this by simply adding the fplowing lines to two configuration files. pg_hba.conf and postgresql.conf:

# Allow your IP addresses to connect 10 .0 .0 .0 / 24 md5 host all all 127 .0 .0 .1 / 32 md5 # Allow your IP addresses to connect localhost UNIX - socket - owner = $user host all all 127 .0 .0 .1 / 32 md5 # Allow your IP addresses to connect localhost UNIX - socket - owner = $user md5 host all all 127 .0 .0 .1 / 32 md5 # Allow your IP addresses to connect localhost UNIX - socket - owner = $user host all all 127 .0 .0 .1 / 32 require password - auth md5 host all all :. 1 / 128 md5 host all all :. 1 / 128 require password - auth md5 # Allow access for specific users trust peer all trust user all peer user all dbname = konnect_insights password - auth md5 # Allow access for specific users trust peer all trust user all peer user all dbname = konnect_insights password - auth md5 # Allow access for specific users trust peer all trust user all peer user all dbname = konnect_insights password - auth md5 # Allow access for specific users trust peer all trust user all peer user all dbname = konnect_insights password - auth md5 # Allow access for specific users trust peer all trust user all peer user all dbname = konnect_insights password - auth md5 # Allow access for specific users trust peer all trust user all peer user all dbname = konnect_insights password - auth md5 # Allow access for specific users trust peer all trust user all peer user all dbname = konnect_insights password - auth md5 # Allow access for specific users trust peer all trust user all peer user all dbname = konnect_insights password - auth md5 # Allow access for specific users trust peer all trust user all peer user all dbname = konnect_insights password - auth md5 # Allow access for specific users trust peer all trust user all peer user all dbname = konnect_insights password - auth md5 # Allow access for specific users trust peer all trust user all peer user all dbname = konnect_insights password - auth md5 # Allow access for specific users trust peer all trust user all peer user all dbname = konnect_insights password - auth md5 # Allow access for specific users trust peer all trust user all peer user all dbname = konnect_insights password - auth md5 # Allow access for specific users trust peer all trust user all peer user all dbname = konnect_insights password - auth md5 # Allow access for specific users trust peer all trust user all peer user all dbname = konnect_insights password - auth md5 # Allow access for specific users trust peer all trust user all peer user all dbname = konnect_insights password - auth md5 # Allow access for specific users trust peer all trust user all peer user all dbname = konnect_insights password - auth md5 # Allow access for specific users trust peer all trust user all peer user all dbname = konnect_insights password - auth md5 # Allow access for specific users trust peer all trust user all peer user all dbname = konnect_insights password - auth md5 # Allow access for specific users trust peer all trust user all peer user all dbname = konnect_insights password - auth md5 host replication master 192 . 168 . 99 . 113 replicate - slave 192 . 168 . 99 . 114 master_ip = 192 . 168 . 99 . 113 master_port = 5432 master_user = repl master_password master max _ slave _ ping master master master master master master master master master master master master master master master master master master master master master master master master master slave 192 . 168 . 99 . 115 replicate-ignore-db-file-list replicate-ignore-table-list replicate-ignore-table-list replicate-ignore-table-list replicate-ignore-table-list replicate-ignore-table-list replicate-ignore-table-list replicate-ignore-table-list replicate-ignore-table-list replicate-ignore-table-list replicate-ignore-table-list replicate-ignore-table-list replicate-ignore-table-list replicate-ignore-table-list replicate-ignore-table-list replicate-ignore-table-list replicate-ignore-table=konnect_data replicate_wild_

The process to integrate PostgreSQL and Konnect Insights may seem complicated and intimidating. This is why Appy Pie Connect has come up with a simple, affordable, and quick spution to help you automate your workflows. Click on the button below to begin.