PostgreSQL is a powerful open source relational database frequently used to create, read, update and delete Python web application data. Psycopg2 is a PostgreSQL database driver that serves as a Python client for access to the PostgreSQL server. This post explains how to install PostgreSQL on Ubuntu.
sudo apt-get install postgresql libpq-dev postgresql-client postgresql-client-common
Setting up a user and db
We now have PostgreSQL installed and the PostgreSQL service is running in the background. However, we need to create a user and a database instance to really start using it. Use the sudo command to switch to the new "postgres" account.
sudo -i -u postgres
Within the "postgres" account, create a user from the command line with the createuser command. PostgreSQL will prompt you with several questions. Answer "n" to superuser and "y" to the other questions.
createuser yourname -P --interactive
Creating a database for Gramps to use
Create a new database for your family tree. You can name it "Family Tree 1" or whatever you want.
createdb "Family Tree 1"
Now we can interact with Gramps.
Using PostgreSQL on Gramps
Make sure that you have installed your PostgreSQL Addon.
In Gramps "Edit/Preferences/Family Tree" menu item, select the "PostgreSQL" entry for "Database backend". That should allow you to enter the "Host" and "Port" settings. If you have the PostgreSQL server installed on the same machine as Gramps, you use "localhost" or "127.0.0.1" as your host name. If you are using a remote server, use that server's ip address or name. The default "port" for PostgreSQL is "5432", use that or the port configured for your PostgreSQL server.
Use the Gramps Family Tree Manager to create a new tree, it must have the same name as the one you created on the server.
When you try to open the tree, it will ask you for the user name and password, there are the same values used in the
createuser command above.
At this point, you should be able to use Gramps normally.
Remote Server: Configure PostgreSQL to allow remote connection
By default PostgreSQL is configured to be bound to “localhost”. We have to make a few changes on the remote server to allow remote access.
$ netstat -nlt Proto Recv-Q Send-Q Local Address Foreign Address State tcp 0 0 0.0.0.0:443 0.0.0.0:* LISTEN tcp 0 0 127.0.0.1:11211 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN tcp 0 0 127.0.0.1:3737 0.0.0.0:* LISTEN tcp6 0 0 :::22 :::* LISTEN
As we can see above port 5432 is bound to 127.0.0.1. It means any attempt to connect to the postgresql server from outside the machine will be refused. We can try hitting the port 5432 by using telnet.
In order to fix this issue we need to find postgresql.conf. On my Ubuntu it was located at
Open postgresql.conf file and replace line
listen_addresses = 'localhost'
listen_addresses = '*'
The system also is configured to limit remote logons for specific user(s).
In order to fix it, open pg_hba.conf and add following entry at the very end. On my Ubuntu it was located at
host all all 0.0.0.0/0 md5
host all all ::/0 md5
Now restart postgresql server.
sudo /etc/init.d/postgresql start
and use netstat to see if we are set up for remote access.
Proto Recv-Q Send-Q Local Address Foreign Address State tcp 0 0 127.0.0.1:11211 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN tcp 0 0 127.0.0.1:2812 0.0.0.0:* LISTEN tcp6 0 0 ::1:11211 :::* LISTEN tcp6 0 0 :::22 :::* LISTEN tcp6 0 0 :::5432 :::* LISTEN tcp6 0 0 ::1:25 :::* LISTEN
Here we can see that “Local Address” for port 5432 has changed to 0.0.0.0.
On a remote machine it should be possible to set up Gramps with the IP address or hostname of the PostgreSQL server and the port number (default value is 5432).
You may find the
/var/log/postgresql/postgresql-9.5-main.log file to be useful.