Restoring your postgresql database from the cloud

Posted: April 17, 2016 in coffee roasting, linux, postgres, tech, Typica
Tags: ,

The inevitable “oompf” happened much sooner than expected: the Mac Mini linux server died.

I hadn’t even fully tested my backup method! Nothing like trial by fire to make the theory real.

If you don’t want to read the whole article and just want to know, “did it work?” Yes, it worked perfectly.

I had a very old Dell laptop (older than my Mac Mini, actually) that I was setting up for my Typica compile environment. I also used it to verify some of my commands in the previous articles for installing and setting up the postgres database under linux. The only thing it hadn’t done was actually create a database or connect to the cloud. To sum up the situation, it was perfectly poised to now have my database restored, though far from ideal.

The steps were simple:

  1. Install PostgreSQL database software
  2. basic configuration of postgresql (network connectivity)
  3. create the typica database instance
  4. create local user and grant it access to rights to the typica database instance
  5. restore the database-dump file from the cloud

Steps 1-4 are outlined in my initial blog about creating the database. The only thing missing is running the command to restore the data from the dump file.

First, let me explain about the dump file. The dump file is not a copy of the database as it runs. The dump file is actually a text file containing SQL statements that will completely re-create the database. Restoring from these dump files is fairly simple and straight forward. A very large file may take some time, but mine was extremely short.

Once you have created the database and granted your user the permissions to the database, you would need to download your dump file from your cloud backup and copy it to your new database system. I simply navigated to the latest dumpfile in my Google Drive and downloaded it to my regular Windows system. I used PSCP (from Putty) to secure-copy the file dump to my new database host.

Back on the database host I exited my psql command where I created the database and setup the user privileges to it.

I ran three commands. The first two commands were verify I had “read” permissions by listing the directory contents and a “tail” on the dump file to make sure the postgres user had read privilege to the file. The second command is the postgres command to restore the file. Here is the session nearly verbatim:

postgres@roastlogger ~ $ psql
psql (9.3.12)
Type "help" for help.

postgres=# create database typica;
postgres=# create user roasty with password 'coffeecoffecoffeecoffee';
postgres=# grant all privileges on database typica to roasty;
postgres=# \q

postgres@roastlogger ~ $ ls ~kevin
Desktop Downloads Music Public Templates Videos
Documents gdrive Pictures src typica.export.201604100718

postgres@roastlogger ~ $ tail ~kevin/typica.export.201604100718
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;

-- PostgreSQL database dump complete

postgres@roastlogger ~ $ psql typica < ~kevin/typica.export.201604100718

And I cut the output short for you. It re-created the database in a very short time.

I then launched my recently compiled Typica program and it immediately asked for how to connect to a database since the previous database is kaput. I input the configuration items and was back in the running.

I think the Mini died from  at the power supply during a recent storm. It was over six years old so I will be replacing it with a new model QNAP home-nas. The QNap will be able to run Samba, PostGreSQL, and Plex with no issues (I also bought ram to upgrade it to max supported RAM).


Leave a Reply

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

You are commenting using your 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