Web-based GIS Assignment 2

With pgAdmin running on my Ubuntu 14.04 desktop, and PostGIS setup on an Internet virtual machine (running Ubuntu 14.04 server), I connected pgAdmin to the server by double clicking on the server name (NYC) in the list.  It connected fine, but not without giving me a warning:

A quick Google search brought me to this Ask Ubuntu link:

http://askubuntu.com/questions/201537/pgadmin-iii-doesnt-work-due-to-the-server-lacks-instrumentation-functions

which says I need to install postgresql-contrib.  Here goes:

$ sudo aptitude install postgresql-contrib

It installed a single package without incident.  After clicking the "Fix It!" button, I can now connect to the remote server without warning.

To install pgAdmin on my desktop at home, I ran:

$ sudo aptitude install pgadmin3

it returned that:

The following NEW packages will be installed:
  pgadmin3 pgadmin3-data{a} pgagent{a} postgresql-client{a}
  postgresql-client-9.3{a} postgresql-client-common{a}

While it is no surprise that pgAdmin would depend on postgresql-client (which has the command line psql program), it is convenient. For one thing, it makes it easier to test out the connection to the remote database server, since the psql command:

$ psql -h [server name] [database name]

Is easy to remember.  I still don't know off the bat how to fill in the fields of the pgAdmin connection screen without looking things up.  Here is a screen shot of the New Server Registration screen:

I know what to put in the Host, Username, and Password fields, but I'm not sure about Name and Maintenance DB.  Is Name the name of the database, or is that Maintenance DB?

This documentation page proved most helpful:

http://www.pgadmin.org/docs/dev/connect.html

I put "nyc" (the name of the database we are using for class) in both the Name and Maintenance DB fields, and it connected to the server without incident.

Setting Up the Local Virtual Database Server for Testing

My next task, assigned to me at our last Code for NoVA meet up, is to look into loading OpenStreetMap (OSM) data into a PostGIS database. As cool as it is having a remote server out on the web running a database server 24/7 that I can connect to whenever I want, I'm not going run new "experiments" on that machine.  That's where local VirtualBox VMs come in handy.

I described my first attempt at setting up a PostGIS server in a previous post. As I've learned since, starting out with the command:

$ sudo aptitude install postgresql-9.3-postgis-2.1

is the best way to get going, since it installs postgresql itself and most everything else you need to get started. I still have the VM I made back then, so now I'm going to copy over the nyc database to it and configure it for remote access.

I'll repeat the process I used to move the nyc database from the VirtualBox VM we were given in our Web-based GIS class, described in my previous post, only this time I'll export it from my database server, so that I can skip the steps where I had to rename the owner of the database.

Here is what I did:

username@local_machine:~$ ssh [database server name]
username@dbserver:~$ pg_dump -c nyc > nyc.sql
username@dbserver:~$ exit
username@local_machine:~$ scp [database server name]:nyc.sql .

I could export the database as me, since I've been setup as a database user. I don't remember whether I did that on the VirtualBox VM, so now is a good time to learn some more PostgreSQL administration.  Here is a documentation page which has what I need:

http://www.postgresql.org/docs/9.3/static/database-roles.html

Trying to start psql on the VirtualBox server shows that I didn't add my user as a database user:

$ psql
psql: FATAL:  role "[username]" does not exist

Time to fix that:

username@postgis:~$ sudo -i
[sudo] password for username:
root@postgis:~# su - postgres
postgres@postgis:~$ createuser --superuser username
postgres@postgis:~$ exit
logout
root@postgis:~# exit
logout
username@postgis:~$ createdb nyc
username@postgis:~$ psql nyc < nyc.sql

It successfully ran the script and populated the nyc database. I could then run:

username@postgis:~$ psql nyc

and connect to the nyc database and run queries.

Now to enable remote connections:

username@postgis:~$ sudo vi /etc/postgresql/9.3/main/pg_hba.conf

and changed this line:

host  all  all  127.0.0.1/32  md5

to:

host  all  all  0.0.0.0/0     md5

then:

username@postgis:~$ sudo vi /etc/postgresql/9.3/main/postgresql.conf

and changed this line:

#listen_addresses = 'localhost'

to:

listen_addresses = '*'

username@postgis:~$ psql nyc
nyc=# alter user [user] with password '[password]';
ALTER ROLE
nyc=#

This is mostly a repetition of information in my previous post, but since I want to learn it, it bares repeating.

Finally, I'll connect from my desktop machine to the "remote" VirtualBox server, which has IP address 10.0.0.131 on my home network:

username@localmachine:~$ psql -h 10.0.0.131 nyc
Password:
psql (9.3.9)
SSL connection (cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.

nyc=#

Excellent!  I'm all set to explore importing OSM data into my database.