Thursday, March 15, 2018

ASP.Net Core 2 with Postgresql on Linux Part 1: Installing pgAdmin4 on Ubuntu 16.04

For the last 15 years I have had a personal web-based app that I have used to learn new languages and frameworks. 

Starting with Microsoft's .Net 2.0 framework and then moving from Ruby on Rails 3.x, to MeteorJS, and then to .Net Core 1.x, this app has also been used with MySql, SQL Server, MongoDb, and Postgresql as the data access layer. 

Currently, I have ported the app to ASP.Net Core 2 with SQL Server on the Azure cloud. As I was poking around my github repo I thought why not use my Linux Ubuntu 16.04 laptop and port the current ASP.Net Core 2 app to my Linux with Postgresql and place it on Heroku

As I thought about it, my first logical step was to port the data from SQL Server to Postgresql. While I think I am proficient at the command prompt, I had to admit that having a GUI based tool would be a help. Having used pgAdmin on my Rails 3.x project, that is where I leaned. So, step one, get pgAdmin installed on my Ubuntu system. 

Thanks to the data from, this was a much easier transition. 

Here is what I did:

Installed pgAdmin4 using Python3.x (I preferred to avoid encoding related issues):
 $sudo apt-get install virtualenv python3-pip libpq-dev python3-dev  
 $virtualenv -p python3 pgadmin4  
 $cd pgadmin4  
 $source bin/activate  
 $pip3 install  
 $gedit lib/python3.x/site-packages/pgadmin4/  

Copied this to the file:
 import os  
 DATA_DIR = os.path.realpath(os.path.expanduser(u'~/.pgadmin/'))  
 LOG_FILE = os.path.join(DATA_DIR, 'pgadmin4.log')  
 SQLITE_PATH = os.path.join(DATA_DIR, 'pgadmin4.db')  
 SESSION_DB_PATH = os.path.join(DATA_DIR, 'sessions')  
 STORAGE_DIR = os.path.join(DATA_DIR, 'storage')  
 SERVER_MODE = False  

Created the following directories and change the permissions:
 $sudo mkdir "/var/log/pgadmin"  
 $sudo chmod a+wrx "/var/log/pgadmin"  
 $sudo mkdir "/var/lib/pgadmin"  
 $sudo chmod a+wrx "/var/lib/pgadmin"  

Created a file in my /home/mark folder named and copy this to the file (note that your user name will be the folder here):
 cd ~/pgadmin4  
 source bin/activate  
 python3 lib/python3.5/site-packages/pgadmin4/  

Made the file executable:
 $chmod +x /home/mark/  

Then, I installed postgres locally. First I created the file /etc/apt/sources.list.d/pgdg.list, and added a line for the repository:
 deb xenial-pgdg main  

I imported the repository signing key, and update the package lists:
 $wget --quiet -O - | \  
  sudo apt-key add -  
 $sudo apt-get update  

Next, I installed PostgreSQL on Ubuntu, using the apt-get (or other apt-driving) command:
 $apt-get install postgresql  

Finally, after the install of postgresql, I created a user and give them permissions:
 $su -s  
 $su - postgres  
 postgres=# CREATE USER yourUser PASSWORD 'userPassword';  
 postgres=# GRANT ALL ON SCHEMA test TO yourUser;  
 postgres=# GRANT ALL ON ALL TABLES IN SCHEMA test TO yourUser;  
 postgres=# \q  

Now, when I run the ./ command from a terminal and navigate to in my web browser, I see the following:

In Part 2, I will detail the process of moving the data from SQL Server to Postgresql.

No comments: