Juan Chacon Free Software and Education Project: Admining a PostGIS Server

In a previous post I documented setting up a VirtualBox VM running Ubuntu 14.04 as a PostGIS server. In this post I'll investigate administering the server, including creating databases and database users. Much of the on-line documentation for PostgreSQL is geared toward running it on Windows, or else spends a lot of unnecessary time from my point of view covering all three platforms (Windows, Mac, and Linux). I'm using an Ubuntu server, so I want documentation for that. Here are a few resources that look promising:

Of these the first one looks most promising, so I'll start with that. From it, I learn that:

Configuration files are located at: /etc/postgresql/9.3/main
Main config file is: postgresql.conf
Inside this file:
- data_directory directive specifies where the databases are stored (on my machine it is set to /var/lib/postgresql/9.3/main).
- hba_file directive specifies the host-based authentication file (on my machnie set to /etc/postgresql/9.3/main/pg_hba.conf).
- ident_file directive specifies the ident authentication file (on my machine set to /etc/postgresql/9.3/main/pg_ident.conf).
- The default port is set to 5432, and changing this requires restarting the server.
- listen_addresses defaults to localhost. I changed it to:
  
  listen_addresses = '*'
  so the server would accept connections from other machines (my desktop machine in this case).
The following commands control the running server:
- $ sudo service postgresql stop
- $ sudo service postgresql start
- $ sudo service postgresql restart
- $ sudo service postgresql reload
After changing the listen_address value, I did:

$ sudo service postgresql restart
* Restarting PostgreSQL 9.3 database server [ OK ]
$
Note: In Ubuntu, the server is run as a service called postgresql (configured in /etc/init.d/postgresql).

Now let me see if I can connect to the database server, first locally and then remotely. Logged into the server via ssh, I did:

$ sudo -u postgres psql
psql (9.3.9)
Type "help" for help.

postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
postgres=# \q
$

The YouTube video I listed above suggests a slightly different approach:

$ sudo su postgres
$ psql -U postgres -d postgres
psql (9.3.9)
Type "help" for help.

postgres=#

The -U argument to psql is the user and the -d argument is the database (both postgres at this point). To be able to connect remotely, the postgres user is going to need a password:

postgres=# \password postgres
Enter new password:
Enter it again:
postgres=#

Now let me see if I can connect to the server remotely. From a terminal on my client machine:

$ psql -h 10.0.0.12 -p 5432 -U postgres -d postgres
psql: FATAL: no pg_hba.conf entry for host "10.0.0.5", user "postgres", database "postgres", SSL on
FATAL: no pg_hba.conf entry for host "10.0.0.5", user "postgres", database "postgres", SSL off
$

A bit of googling led me to a StackExchange post with a solution to this problem. Back on the server again, I did:

$ 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

and finally:

$ sudo service postgresql restart
* Restarting PostgreSQL 9.3 database server [ OK ]
$

Time to try again from the client machine:

$ psql -h 10.0.0.12 -p 5432 -U postgres -d postgres
Password for user postgres:
psql (9.4.4, server 9.3.9)
SSL connection (protocol: TLSv1.2, cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#

Success (and it appears to confirm that there is no problem mixing a 9.3 server with a 9.4 client)! I can now connect to the database server remotely, and I'm ready to start doing things with the database.

I'm also going to setup PostgreSQL's GUI management tool on my client machine, just in case that will make managing the server easier.

Setting Up pgAdmin on the Client Machine

There is a GUI administration application for PostgreSQL called pgAdmin. To install it on my client machine (my desktop), I:

$ sudo aptitude install pgadmin3
[sudo] password for [username]:
The following NEW packages will be installed:
libpq5{a} libwxbase3.0-0{a} libwxgtk3.0-0{a} pgadmin3 pgadmin3-data{a}
pgagent{a} postgresql-client{a} postgresql-client-9.4{a}
postgresql-client-common{a}
0 packages upgraded, 9 newly installed, 0 to remove and 2 not upgraded.
Need to get 11.3 MB of archives. After unpacking 47.1 MB will be used.
Do you want to continue? [Y/n/?] Y

Since my client machine is running Ubuntu 15.04, it installed the postgresql-client-9.4 package while the server is running version 9.3 of the server. Hopefully there won't be a problem for the newer client to connect to the slightly older server.

After which I could launch the application from the Ubuntu dash, and see this window: