Addon:PostgreSQL

From Gramps
Revision as of 15:22, 22 February 2018 by Prculley (talk | contribs)
Jump to: navigation, search

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 and use PostgreSQL on Ubuntu.

Gnome-important.png
For Experts only!

Gramps is not designed to be used by multiple users at the same time. While using a PostgreSQL server remotely allows multiple users to connect to a db, doing so simultaneously WILL cause database corruption and loss of data. If you chose to set up for multiple user access, please backup frequently, and set up a system of your own to make sure that only one person is using Gramps at one time. Gramps or the family tree MUST be closed before allowing another user to access the database.

Prerequisites

 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.

Configuring postgresql.conf

In order to fix this issue we need to find postgresql.conf. On my Ubuntu it was located at /etc/postgresql/9.5/main/postgresql.conf

Open postgresql.conf file and replace line listen_addresses = 'localhost' with listen_addresses = '*'

Configuring pg_hba.conf

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 /etc/postgresql/9.5/main/pg_hba.conf 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.

 netstat -nlt
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).

Debugging

You may find the /var/log/postgresql/postgresql-9.5-main.log file to be useful.