Difference between revisions of "Addon:PostgreSQL"
|Line 123:||Line 123:|
Revision as of 23:39, 21 October 2019
This is a Third-party Addon.
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
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.
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
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
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
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 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.
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.
See issues attached to the PostgreSQL tag.