Addon:PostgreSQL

From Gramps
Jump to: navigation, search
Gramps-notes.png

Please use carefully on data that is backed up, and help make it better by reporting any comments or problems to the author, or issues to the bug tracker
Unless otherwise stated on this page, you can download this addon by following these instructions.
Please note that some Addons have prerequisites that need to be installed before they can be used.
This Addon/Plugin system is controlled by the Plugin Manager.
An Addons Offline Manual is available for review.

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.

Be sure to backup your Family Trees frequently.

The PostgreSQL addon provides PostgreSQL database support for Gramps 5.x or greater.

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 following explains how to install and use PostgreSQL on Ubuntu.

Prerequisites

Gnome-important.png
Note these instruction assume you are using Linux or development environment.

As the Gramps AIO & MacOS installers do not come with the Prerequisites and can not be installed easily. see 10491

Install the following packages:

 sudo apt-get install postgresql libpq-dev postgresql-client postgresql-client-common

Then install Psycopg (the PostgreSQL adapter for the Python programming language. Which fully implements the Python DB API 2.0 specifications.) on Ubuntu either using:

 sudo apt-get install python3-psycopg2

or if that does not work install from you can install from pip using:

 sudo apt install libpq-dev python3-dev python3-pip

and then

 pip3 install psycopg2

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

Menu: Edit>Preferences... - "Family Tree" - tab - example - defaults

Make sure that you have installed your PostgreSQL Addon.

In Gramps select Edit > Preferences from the menu and then select the Family Tree tab, 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.


PostgreSQL addon - login - dialog - example

When you try to load a family tree, you be be shown the Login dialog where you can enter the user name and password, these 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.

See also


Issues

See issues attached to the PostgreSQL tag.