Typica Postgres Database Setup

Posted: March 28, 2016 in coffee roasting, linux, postgres, tech, Typica
Tags: ,

The Typica software was thoughtfully built around the commercial-grade relational database (RDB) system PostgreSQL. Think of Oracle RDB without the thousands of dollars in licensing because it is open-source like Typica and thousands of other software packages. Open Source means the developer(s) make their code completely available so anyone can download it and compile it to run. In so doing the original authors hope these new people will contribute to code by finding and fixing bugs, suggest and/or write new features to improve, documentation, and perhaps even make packages ready to install available to other potential users.

The PostgreSQL software can run on just about any operating system: Windows, Macintosh OSX, Linux (including many home Network-Attached Storage products), Solaris, BSD, and the list could go on. This is due to both the open-source nature of the software and some investment from corporate sources that like the software alternative RDB.

I picked Linux. I will have been using Linux for 20 years come late 2016. It is completely open-source like Typica and PostgreSQL.

I picked my old Mac Mini (2006) to run the software. Apple hardware is generally top-notch. The OSX operating system used to be one of the best but lately I have been a little disenfranchised with direction that Apple has gone with it. I am referring to not just the color scheme, but some major changes that have been step backwards in terms of interoperability. If the OS is going to be hampered with bugs, the GUI ugly, and slow down my old system to molasses-in-January speeds…” well,” I said, “I might as well run Linux and at least get the speed back”. I loaded the Mint distribution last year and then loaded Plex Media Server to share the music library to the Onkyo amp. It is not very useful to me and this platform will make a great hosted database for the home network.

Why Hosted Database?

Why should anyone care about a “hosted database”? A hosted database means the database is running on a different system than the software that will use the datbase. The two systems will communicate over a network connection. This type of setup is not completely necessary since the database could run on the system running the software. However there are some distinct advantages for having a hosted database.

I choose to have a hosted database so that I can use the software from a couple of different systems and still access the same data. For instance, my fancy new Surface Pro should probably stay in the house away from the fire in the roaster and possible tumbles to the concrete floor. So it should not be doing the roast-logging, but it would be very useful for capturing my tasting notes.

So I will use an old laptop to start roast logging. It has been around a few blocks so it could die during this process and I will very likely need to replace it with something like a netbook. Or a retired thin client. Having a hosted database means I do not have to worry about getting the data off a broken system, but rather can install on a new system, connect to the database, and keep on truckin’.

Install and configure PostgreSQL

I will run you through the steps of installing PostgreSQL on a Linux system. If you choose Windows to host the system you will have a very different experience but you may still find this educational. Likewise a Mac host experience will be different though there will be some similar concepts.

If you are using Mint (linux distribution) or other similar Debian package based distro you should find these example instructions are easy to follow. If you are using a Redhat RPM based system you will find things very similar. However you will have to do a little digging to find the transliteration for those package names and “yum” commands versus my apt-get commands.

Install Packages

The system will need to have the PostgreSQL packages installed. The list of package would likely look like this:

  • libpq5:amd64
  • postgresql-client-common:all
  • postgresql-client-9.3:amd64
  • postgresql-common:all
  • postgresql-9.3:amd64
  • postgresql:all

But it is much simpler than. You can install the main package and all the dependencies with a single command. Make sure the system has internet access and from a terminal session enter the command:

"sudo apt-get install postgresql"

Keep your terminal open, you will need it for the following sections as well.

Allow Network access

The software will be secure by default and not allow incoming network connections because that is a risk for a system not properly secured. We will have to accept that risk and configure the database to listen and allow remote connections.

In your terminal, issue the command to change your working directory to “/etc/postgresql/9.3/main/”. Use the tab “auto-complete” ability as your version of PostgreSQL could be update from my example.

Save original CONF files!

I always recommend saving config files before making changes. I don’t always remember and when I don’t I wish I had… So here is your reminder — make an “original” file before making changes. In this particular directory this can be done with a single loop-style command:

sudo for i in *.conf; do cp $i _orig.$i ; done

This will loop around each file ending with “.conf” and make a copy starting with “_orig” as a filename prefix.

Modifying pg_hba.conf

Next we will edit the “hba” file. This file contains how this host is allowed to share it’s network with PostgreSQL. Before we can edit the file we must know what your subnet is for your local network. This could get dicey for you if your local subnet is completely IPv6. Hopefully if it is you know this and do not need this guide for the details but the general information.

Find your IP Address and subnet information

So assuming your have no idea what I’m talking about, run this command on your host system to see what your IP address is:  “ip -4 addr show”. The output will look similar to below:

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
inet 192.168.1.109/24 brd 192.168.1.255 scope global eth0

The information we are looking for is associated with device #2 and not the localhost address. The second line contains the IP address (192.168.1.109) and the broadcast (192.168.1.255). This tells us that we will use 192.168.1.0 when we edit the file, next. By using the zero we will specify the entire group of addresses expected, from 1 to 254.

Edit the file

If you have edited files on unix before use your favorite editor and add the following line to the very bottom of the file.

host all all 192.168.1.0/24 md5

If you have never used a file editor in Unix I would love to introduce you to VIM (VIM is not incorrect Roman numerals for 994). But that would stretch this already length blog post into something else entirely. Let’s use this shortcut of using echo to append to the file. For this to work you MUST absolutely use two Greater-Thans, and you should modify the subnet to match yours if it is different. The magic editorless command is:

sudo echo "host all all 192.168.1.0/24 md5" >>pg_hba.conf

One last thing to note. If you have an overly complex network you might need to repeat the network discovery and add an additional line if the clients that are connecting to your database show an entirely different network than the host. And pray that the two networks are routing to each other.

Modify the postgresql.conf

This file must also have one line added to it. This line is simple and intructs the database to listen on any network interface available.  The line can appear at the bottom of the file, so the magic echo command will save you have to learn a Unix file editor if you have not already made a choice for VIM (the right choice) or a wrong choice.

sudo echo " listen_addresses='*' " >>postgresql.conf
Restart the database software
sudo service postgresql restart

Setup User access

The database will run as user “postgres” (more than likely). I am going to recommend that you connect to the database as a user called “roasty” or at least “typica”. However, you could simplify and run make this connection as yourself with your own credentials. It will save one small step with the backups later on, but it is small step and for good security practices I recommend you do not. Just make sure you document the password somewhere.

Create local user (if not yourself)

Choose your username now and run the following command (substituting your username for “roasty” every time you see it in this guide.

sudo useradd roasty

Database operations

We will use the sudo and su commands to become the database operator which is probably postgres. Then we will issue commands to create the database “typica” and grant access to the “roasty” account and yourself, assuming you will be doing the backup operations under your account.

Become the database operator and then execute the “psql” process which allow you perform the necessary operations within the database software.

sudo su - postgres
psql

You are now currently “in” the PostGreSQL database software. You should feel like you zipping around on a lightcycle right now. No? Huh… Right, then, first step is to create a database called “typica”. Be sure to end each line with a semi-colon before pressing enter. If you forget just press the semi-colon on the new line and then press enter again. It still works.

create database typica;

You worked up a sweat creating a database I’m sure, so take a break and have some coffee. No, seriously, the hardest part is definitely passed us so you deserve a break. When you come back we will make sure the user(s) for the software and your backups have a password and grant the necessary privileges to the database you created.

create user roasty with password 'passwordwhichImsureisaboutcoffee';
grant all privileges on typica to roasty;
CREATE USER yourusername WITH PASSWORD 'evenbetterpassword';
GRANT ALL PRIVILEGES ON DATABASE typica TO yourusername;
grant USAGE on SCHEMA public to yourusername;
grant USAGE on SCHEMA typica to yourusername;
grant USAGE on SCHEMA typicafeatures to yourusername;
ALTER ROLE yourusername WITH Superuser;

After the last line you can exit by pressing Ctl-D.

As you can see the “yourusername” had to get a lot of extra roles so that he could perform the database export for backups. This could be done as the database operator (postgres) to save this step and limit access to yourusername but what I have in mind for backups is easier managed under your user account.

Database is Ready!

The database is ready for the software to connect! Remember this hostname or IP address, the name of the database, and the password and username your have specified and go start the software. It will ask  you for this information.

 

Advertisements
Comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s