Hi I am trying to setup my computer as the server hosting a main database in PostgreSQL Will distant computers be able to access it ? Can you give me indications on how to do setup the whole system ? thanks!

asked 03 Oct '14, 14:21

lecrabe's gravatar image

lecrabe ♦
4163612
accept rate: 6%

edited 03 Oct '14, 14:21


As default Collect Earth is setup to use SQLite as the database that stores the collected data.

In order to use a server-based solution (PostgreSQL) so that several PCs running Collect Earth can share the same data, the user must set up the database connection.

INSTALL POSTGRESQL

Install PostgreSQL server in a computer that can be reached by the other PCs, whether through the internet or within the intranet.

After you install PostgreSQL run the pgAdmin III application that should also have been installed when the PostgreSQL was installed. You should login to the DB using the postgres user (usual passwords would be postgres or admin, but this depends on what you set when installing the database at first)

You should also create a new Login Role ( in the Login Roles branch under the server ), for instance you can call it collectearth with password collectearth ( this is set in the definition tab )

Step 1 : Create "collectearth" login roles alt text

Step 1.A : Set long name "collectearth"

alt text

Step 1.B : Set the privileges for the user

alt text

Step 2: Create database.

alt text

Step 2,A: Call it also "collectearth" and set "collectearth" as the owner

alt text

Step 2.B : In the " privileges" grant "collectearth" full control of the database

alt text

Step 3: Create a schema within your new database and call it "collect" , Very important to keep this name as other names will provoke an exception!

alt text

Configure the Database

By default PostgreSQL is setup so the database cannot be accessed from outside the localhost (the computer where the server is installed ). In Windows we need to change this ( check for more info here )

Go the folder where the PostgreSQL has been installed.

By default it should be : C:\Program Files\postgresql*VERSION_NUMBER*\data ( or C:\Program Files\postgresql*VERSION_NUMBER*\data\pg10 for Postgres 10)

Open the file called pg_hba.conf and append this line to the list of accepted connections :

host all all 0.0.0.0/0 md5

This means that the database accepts now connections from any IPs ( you can tweak this to accept connection only from you local network : see the documentation )

Now on the same folder open the file postgresql.conf and change the line where it say listen_addresses. set it to :

listen__addresses='*'

At this stage you need to restart PostgreSQL. Since it is installed as a service (usually) in Windows the fastest way is to just restart the computer.

At the end the Object browser in the pgAdmin should look like this:

alt text

SET UP COLLECT EARTH

Now you should open Collect Earth and got to the main menu Tools->Properties and then go to the operation mode tab.

In that tab fill the fields like in the image attached ( the XXX.XXX.XXX.XXX is your IP address. You can see the current IP address of your computer on the same dialog (see image)

alt text

You should check that the connection is working by clicking on the smartly titled "Test Connection" button!!

Click on Save & Apply changes and now you should see that Collect Earth has populated the collect schema in the Database with all the tables necessary for the Collect to store data.

NOW CHECK THAT OTHER USERS CAN CONNECT TO THE DATABASE If this does not happen, review that the PostgreSQL Database is reachable from outside your PC. If the database can be accessed from outside the PC then maybe there is a firewall configuration that is preventing the computer to connect to the PostgreSQL server.

FINALLY - CONFIGURE THE CEP FILE

If you want that the CEP file that you distribute to other users carry this information, so that you dont need to make them configure the DB connection manually, then you can modify the contents of the file "earth.properties" inside the CEP file (remember you can open a CEP file with an application like WinRAR or WinZip as it really is just a ZIP file)

You will need to substitue these lines (of course with your own data for the IP, DB username and DB password)

db_driver=POSTGRESQL
db_host=192.168.89.2
db_name=collectearth
db_password=collectearth
db_port=5432
db_username=collectearth
permanent link

answered 03 Oct '14, 17:58

sanchez_paus's gravatar image

sanchez_paus
161113
accept rate: 50%

edited 22 May '19, 10:38

Open%20Foris's gravatar image

Open Foris ♦♦
9995714

WARNING : If you have problems connecting to the database from other computers after you have followed all these steps make sure of:

  1. The router to which you are connected is not blocking the traffic to port 5432
  2. There is no firewall (like Windows Firewall) or anti-virus installed in your computer that is blocking traffic!
permanent link

answered 05 Mar '18, 11:37

Open%20Foris's gravatar image

Open Foris ♦♦
9995714
accept rate: 10%

I think that setting

host all all 0.0.0.0/0 trust

is not necessary and even can be security problem (when machine has public IP).

"Trust" means (see docs): "This method allows anyone that can connect to the PostgreSQL database server to login as any PostgreSQL user they wish, without the need for a password or any other authentication."

You can create for example user collect_client with password collect_pass (as is mentioned in CE docs) and set

host all all 0.0.0.0/0 md5
permanent link

answered 15 May '18, 07:32

jurafejfar's gravatar image

jurafejfar
32310
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×281

question asked: 03 Oct '14, 14:21

question was seen: 11,476 times

last updated: 22 May '19, 10:38