Tunnel to Production PostgreSQL Database

As any responsible sys admin, I only allow local connections to my production database server. This means that if I need to access my DB from my local machine I would most likely need to use an SSH tunnel in order to connect.

For some reason, pgadmin3 no longer seems happy on Ubuntu 16.04 LTS and I am not able to make an SSH tunnel. In addition, it is a bit annoying that you are not able to save passwords with an SSH tunnel by default in pgadmin3, especially since my password is a long and random 50 character string.

The solution is pretty simple using the -L SSH flag.

ssh -L 15432:localhost:5432 $USER@$SERVER

This command creates a tunnel on my  production server, and forwards port 5432 (the default PostgreSQL port) to my local port 15432. This allows me to connect using pgadmin3 as if the database were running on my local machine.

Remote Root Access for MariaDB on Ubuntu 16.04 LTS

Typically, when you install MySQL on Ubuntu, it asks you for a root password which you can then use to log into the database. It seems that MariaDB uses a plugin instead to authenticate the root user. This means that the only way to log into MariaDB by default as root is locally with sudo mysql -u root

In order to give the root user a password and login the “traditional” way (which includes remote access via a tunnel) you have to do the following.

  1. Log into MariaDB as the Root user
    sudo mysql -u root
    
  2. Disable the Auth Plugin
    use mysql;
    update user set plugin='' where User='root';
    
  3. Create a password for the root user
    grant all privileges on *.* to 'root'@'127.0.0.1' identified by '$STRONG_PASSWORD';
    flush privileges;
    
  4. Restart MariaDB
    sudo service mysql Restart
    

You can now access the root account with a password, and also over an SSH tunnel remotely.

Backing up and Restoring MySQL with mysqldump

I backup and restore databases across servers every few months, but each time I have to resort to reading this very verbose documentation. The steps below are a no fuss way to do this each time.

Backup Server

SSH into the server with the database that you wish to backup run the following command.

mysqldump -u root -p $DB_NAME > $DB_NAME.sql

Copy the File to Destination Server

Using scp, we can securely transfer the backup

scp $DB_NAME.sql $USER@$SERVER:

Restore on Destination Server

SSH into the server with the database that you wish to restore. From the the previous step the backup file should now be located in the root directory.

  1. Create new database
    mysql -u root -p -e 'CREATE DATABASE $DB_NAME'
    
  2. Restore your backup
    mysql -u root -p $DB_NAME < $DB_NAME.sql
    

Recreating Foreign Keys with Alembic

Alembic is a great tool for keeping track of schema changes in python applications. I am using it to manage DB migrations for braindump along with Flask SQL Alchemy as my ORM. One challenge is managing proper foreign key constraints. By default if you define a foreign key relationship in your schema definition it will not generate the proper migration code.

For example, in braindump we have a one to many relationship between users and notes.

class User(UserMixin, db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(254), unique=True, index=True)
    password_hash = db.Column(db.String(256))
    confirmed = db.Column(db.Boolean, default=False)
    avatar_hash = db.Column(db.String(32))
    created_date = db.Column(db.DateTime(), default=datetime.utcnow)
    updated_date = db.Column(db.DateTime(), default=datetime.utcnow)

    notes = db.relationship(
        'Note', backref='author',
        lazy='dynamic', cascade="all, delete-orphan")

Even though we define the cascade behavior using SQLAlchemy. When we generate the migration with alembic we get something like this:

sa.ForeignKeyConstraint(['author_id'], ['users.id'], ),

Notice how we are missing the ondelete action. What we actually want is something like this:

sa.ForeignKeyConstraint(['author_id'], ['users.id'], ondelete='CASCADE')

Running the default migration will not create the proper relationship in your database and in our case we are not able to delete a user until we have deleted all of the related records as well. There are two ways to fix this. If you catch this before running your migration adding ondelete='CASCADE' will create the proper relationship. If you are like me, and do not catch this, then you will need to run a second migration to remove and recreate these keys. The migration code to do this is shown below:

from alembic import op
import sqlalchemy as sa

def upgrade():
    with op.batch_alter_table("notes") as batch_op:
        batch_op.drop_constraint(
            "notes_author_id_fkey", type_="foreignkey")
    op.create_foreign_key(
        "notes_author_id_fkey", "notes", "users",
        ["author_id"], ["id"], ondelete="CASCADE")

Now you have the proper foreign key constraints and the CASCADE action exists in the DB.

Types and Roles If Not Exists in PostgreSQL

For certain operations such as creating types and roles in PostgreSQL you are not able to use the handy IF NOT EXISTS parameter. This makes creating reusable scripts a bit more challenging, especially if you are going to be running these scripts more than once against the same database.

On solution to this problem is to Just Do It™. DO allows you to execute anonymous code blocks. For example, suppose we had a simple task table with a custom type called task_status along with a reader role that has only select permissions. Using the pg_type and pg_role tables we can write a DO expression that will check to make sure that the elements do not exist before attempting to create them, which will prevent your script from erroring out in the event that they do exist.

First we will create the custom type.

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'task_status') THEN
        CREATE TYPE task_status AS ENUM ('todo', 'doing', 'blocked', 'done');
    END IF;
END
$$;

Then we can create our table.

CREATE TABLE IF NOT EXISTS
tasks (
  id           integer PRIMARY KEY,
  title        varchar(200), 
  status       task_status NOT NULL DEFAULT 'todo',
  created_date timestamp
);

Next, we can create our new role called reader and grant read only rights on the tasks table.

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'reader') THEN
        CREATE ROLE reader;
    END IF;
END
$$;
GRANT SELECT ON tasks TO reader;

The entire script is shown below.

You can run this script as many times as you wish and it will just work™, which means that if you are running full migrations then you do not have to do any manual overrides or comment anything out. Note that the reason why we do not have to wrap up the GRANT in a check is because if you duplicate a grant, PostgreSQL will return a notice but not an error.

Backing up Discourse with RDS

We are launching a community site with Discourse at CircleCI and I ran into some issues with performing backups.

Since we want to be #webscale, rather than installing Discourse using the easy Docker method, we opted to split up the services on AWS.

We are using EC2, RDS, ElasticCache, ELB and S3 for backups. When I initially launched PostgreSQL on RDS I just picked the latest version (9.4 at time of writing) and didn’t put much thought into it. The problem is that the version of pg_dump inside the Docker container provided by Discourse is 9.3.  Apparently you are not able to do a pg_dump when your client and server are mismatched.

When you try to do this, you get a sad error that states:

aborting because of server version mismatch

In order to fix this, we need to upgrade the client version on the Docker container (or alternatively avoid this all together by using the same version of PostgreSQL on RDS). Since it is easier to update the client than downgrade RDS, I opted to upgrade the client.

To do this:

  1. Log into the EC2 Instance that is running Discourse
  2. Go to the Discourse install location (mine is in /var/docker)
  3. Enter the container with:
    sudo ./launcher enter app
    
  4. Upgrade the version of PostgreSQL client
    apt-get install postgresql-9.4
    
  5. Swap out the pg_dump symbolic link for the newer version
        ln -s /usr/lib/postgresql/9.4/bin/pg_dump /usr/bin/pg_dump
    
  6. Perform your  backup

When you update the Docker container for the next release you will likely have to repeat these steps until Discourse gets to the same version of PostgreSQL as the one you chose on RDS.

Removing a Public Facing User Page in OS X Server Wiki

OS X Server has some pretty neat tools that are easy to set up and use for a team collaboration. The problem is that some of these tools are a bit quirky, especially when it comes to removing users or making sure that no data is accessible from the outside world.

For example, if you edit your user profile page, this change will be visible to the public world. There is no real way (that I can find) to hide it, so it is a little bit annoying. Even removing the user from the wiki does not fix this.

After doing some digging, it looks like all of this is controlled by a PostgreSQL database which makes it nice to try to figure out how to get rid of these pages. You can log into the PostgreSQL database on OS X server by opening up a terminal and running sudo -u _postgres psql template1 You can list all of the available databases by running \list and you should see one called collab. Connect to collab so that you can view the data inside and make some changes by running \c collab

You can see the entire scheme by running \dt and it will look something like this:

:                   List of relations
 Schema |            Name             | Type  | Owner
--------+-----------------------------+-------+--------
 public | blog_entity                 | table | collab
 public | document_entity             | table | collab
 public | entity                      | table | collab
 public | entity_acls                 | table | collab
 public | entity_acls_defaults        | table | collab
 public | entity_attrs                | table | collab
 public | entity_changesets           | table | collab
 public | entity_comment              | table | collab
 public | entity_lock                 | table | collab
 public | entity_preview              | table | collab
 public | entity_private_attrs        | table | collab
 public | entity_tag                  | table | collab
 public | entity_type                 | table | collab
 public | file_entity                 | table | collab
 public | filedata_entity             | table | collab
 public | filename_reservation        | table | collab
 public | global_settings             | table | collab
 public | groups                      | table | collab
 public | migration_entity            | table | collab
 public | migration_status            | table | collab
 public | migrationplaceholder_entity | table | collab
 public | notification                | table | collab
 public | page_entity                 | table | collab
 public | podcast_entity              | table | collab
 public | podcast_episode_entity      | table | collab
 public | preview_queue               | table | collab
 public | project_entity              | table | collab
 public | relationship                | table | collab
 public | savedquery_entity           | table | collab
 public | search_index                | table | collab
 public | search_stat                 | table | collab
 public | session                     | table | collab
 public | subscription                | table | collab

The schema is pretty complicated and has some really interesting relationships. My first thought was to just remove all instances of a user, but this turned out to be very complex because pretty much all of these tables depend on each other.

The best way to remove a page is to fake the application out by marking the item as “deleted” in the entity table.

For example, you can find the entity that you want to hide by running:

 select * from entity where long_name like 'Lev%';

This will show us all of the things that I have done in the wiki. Find the specific thing that you want. If you are looking for a user Profile page, this has the entity_type_fk of com.apple.entity.Page. You can grab the uid of this item from the first column and then run a simple update statement to mark the item as deleted.

update entity set is_deleted = 't' where uid = 'YOUR UID'; 

This item will no longer show up in the UI and you can have a truly “private” wiki again. The data model is pretty interesting and is worth looking at if you have nothing to do.

Connect Libreoffice Base to MySQL

I think LibreOffice Base has so much underutilized potential as a rapid application development platform, business intelligence platform and just a general reporting platform. Not to mention the fact that registered data objects can be used in all of the other LibreOffice applications to make really amazing documents and improve the work-flow of any office. Anyone who has actually used MS Access knows how powerful it can be used to be for these types of purposes. The most recent version of Access seems to have lost a lot of the features that made it useful. This is okay since most power-users are still using Access 2003.

LibreOffice Base is not nearly as powerful as Access, specifically from a usability perspective. My biggest frustration with getting started with LibreOffice Base is the obscure and somewhat cryptic documentation around the platform that makes way too many assumptions about what someone new to LibreOffice actually knows. My hope is to provide some practical tutorials with real world use cases. So let’s get started by connecting Base to an existing MySQL database.

In my opinion, the built in HSQL engine has a somewhat weird syntax and is generally not worth learning unless you are not planning on ever actually writing any SQL and only using the built in wizards. I would prefer to work with MySQL databases because they are ubiquitous, use a “standard” syntax and very powerful. In addition most practical office use cases will involve a central database and not a local database.

Preparing Your MySQL Server

This is the part of the documentation that I find most obscure and confusing, so here is how to do it using LibreOffice 4.3 running on Ubuntu 14.04 LTS. The steps here will be slightly different depending on if you are developing on a local database or a remote database. If you are using a local MySQL database please feel free to skip this section.

I do most of my database development inside of Linux Containers which essentially makes my databases “remote”. In order to allow remote connections we need to make a few changes to the default MySQL configuration. Please note that if you are doing these steps on a live production system you will need to be extra careful with users, permissions, ports that are opened, etc.. This falls outside of the scope of this tutorial but the rule of thumb is that if your database accepts connections from the outside world you should white list each IP address that will be connecting to it and block all others. The easiest way to do this in my opinion is with your firewall.

By default MySQL only runs on the local host and is not accessible from remote hosts. To change this setting you need to edit the my.cnf file.

1) Open up my.cnf which is found in /etc/mysql/my.cnf

2) Find the bind-address and change if from the local host to the IP address of the server.

bind-address 10.0.1.30

3) Restart MySQL

sudo service mysql restart

Install the MySQL JDBC Driver

On Ubuntu 14.04 this is very easy and can be done by running the following command:

sudo apt-get install libmysql-java

Configure the Class Path in LibreOffice

Open up any LibreOffice App and go to Tools -> Options

On the right hand side navigate to LibreOffice -> Advanced

Select on the Class Path… button and load the new driver that was installed in the previous step. In order to do this you will need to select **Add Archive… **and select /usr/share/java/mysql.jar

Once this has been loaded restart LibreOffice

Connect to your Database

Now comes the fun part. Now that we have taken care of all of the previous steps the rest is easy. To connect to your database open up LibreOffice Base.

  1. In the Database Wizard select Connect an existing database and chose the MySQL option from the dropdown menu.
  2. Select Connect using JDBC and hit next
  3. Enter the database name, server IP, port number and select next
  4. Enter the username for an existing user in your database and select next
  5. If you wish to use this database in other LibreOffice applications you should select the **Yes, register the database for me **radio button.
  6. Select Finish

Congratulations! Now you can rock some custom queries, fancy forms, and TSP reports using Base. We will go through how to do all of that an more in future posts.

Loading Data into Oracle with SQL Loader

This post will be, what I hope, an easy guide on how to use SQL loader to load data into an Oracle database. This tasks is a required component in DBST652 at UMUC so hopefully this post will be able to help out future students who are learning the magic of oracle. With that in mind, this post is geared toward UMUC students who are taking this course (especially when it comes to connecting to the server etc.)

SQL loader is a neat little utility for loading datasets into tables in Oracle. In my few months of using Oracle, I have found it to be overall cumbersome when compared to other DBSM like MS SQL Server, or even MySQL. Oracle SQL Developer is slow and clunky and a pain to use, iSQLPlus is an “enterpirse” version of phpmyadmin and although it works, it is also a pain to use. The only good thing about Oracle is that it is pretty simple to connect through a terminal and it has some neat utilities such as SQL Loader.

So let’s dig right in.

To use SQL Loader you need three things.

  1. A text editor
  2. A control file
  3. A Terminal Application

The terminal app allows you to connect to a SQL database and run the SQL loader utility, a text editor allows you to create your control file, and your control file contains the data that you want to load into a table.

For this example we are going to load a data set into a table called EMPLOYEE which contains information about an employee at a company. The EMPLOYEE table has not yet been created in our database – first we must create the table. A simple query for that would be:

CREATE TABLE EMPLOYEE
(empid int,
FirstName char(50),
LastName char(50),
MiddleName char(10),
Address char(50),
City Char(50),
State Char(2),
ZipCode number,
PhoneNumber varchar(20));

Creating the Control File for SQL Loader

First, open up your favorite text editor and create the control file. The control file typically looks something like this.

LOAD DATA 
INFILE *
APPEND
INTO TABLE EMPLOYEE
FIELDS TERMINATED BY ‘|’
OPTIONALLY ENCLOSED BY ‘”‘ AND ‘”‘
TRAILING NULLCOLS (
EmpID, 
FirstName, 
LastName, 
MiddleName,
Address, 
City, 
State,
ZipCode,
PhoneNumber)
begindata
1 | “John” | “Smith” | “I” | “5210 Smitty Ln.” | “Cincinnati” | “OH” | 45221 | “513-120-1234” | 
2 | “Betty” | “Crocker” | “J” | “1200 Candy Ln.” | “Cincinnati” | “OH” | 45212 | 
“513-122-2345” | 
3 | “Paul” | “Jones” | “A” | “3450 Revolution Blvd.” | “Cincinnati” | “OH” | 45332 | 
“513-345-3452” | 
4 | “Peter” | “Burges” | “B” | “435 Hollins Ave. ” | “Cincinnati” | “OH” | 45223 | 
“513-223-4353” | 
5 | “Mary” | “Brown” | “J” | “5636 5th St. ” | “Cincinnati” | “OH” | 45221 | “513-435-3435″|

The text highlighted in orange above is the name of the table that you are going to be putting the data into. 

The text highlighted in blue above are the names of the columns in the table that you are going to be adding the data into. 

The text highlighted in green above is the data that you are going to be loading. Each field is separate by a “|” pipe and corresponds to the name of the column that the data is going to be written into. 

When you have loaded all of your data we are ready to move on to the next step. Open up a terminal app and connect to your database server. [For UMUC this is typically dbcourse2.umuc.edu] 

ssh [email protected]

Open up the pico text editor on your server (most UNIX servers have PICO, most Linux servers will have nano — it’s the same application)

Copy your control file into pico and hit Ctrl+O to save the file. A good naming convention is to name the file based on the table that it is going to control. In this case we will call our file **employee.ctl **. Once you have saved the file close pico by hitting Ctrl+X and you will be back to the command line. Now we are ready to use SQL loader to load the data.

Loading the Data Using SQL Loader

Most command line oracle activity takes place by invoking sqlplus and running queries. SQL loader can be run directly from the main terminal without going into sql plus first. You can invoke SQL loader and load the control file by running the following command in the same directory where the control file is loaded. In our case we saved the control file into the home directory so we do not need to change the directory.

sqlldr userid=userid
control=employee.ctl

SQL loader will now prompt for a password, and if everything was successful you will see the following output.

We can now log into sqlplus and run a SELCT * FROM EMPLOYEE to see all of our data. That’s it!

Keep in mind that this is a very simple example and the goal was to get up and running with SQL Loader. The control file can be used to point to other files with larger data sets (instead of adding the data directly in the control file) which reduces the amount of time it takes to format the text properly.

If you have a large data set , SQL Loader is the best way to go. It sure is a lot better than writing a thousand insert statements!