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.
