Remote connection to a Postgres database running on a Eucalyptus instance.

This post describes how to setup a remote connection to a Postgres database running on a Eucalyptus instance.

All the steps covered in the post are generic and mostly independent from each other:

·       creating the Eucalyptus security group,

·       creating an instance from an image (CentOS 6.4),

·       installing the Postgres database,

·       creating a PostGIS extension,

·       setting up a remote connection to the Postgres server

·       connecting from a remote client (terminal or QGIS),

so feel free to read only those steps that are relevant to your own setup.

Step # 1: Create a security group:

euca-create-group  rmtpg -d remote_postgres_open_5432_22

euca-authorize rmtpg -p 22 -s 0.0.0.0/0 -P tcp

euca-authorize rmtpg -p 5432 -s 0.0.0.0/0 -P tcp

Step # 2: Create an instance:

euca-run-instances -n 1 -t m1.xlarge -k your.key -g rmtpg emi-1234ABC

Step # 3: SSH to your instance:

ssh -i your.key root_or_ec2-user@euca-123-123-123-123.eucalyptus.edu

Step # 4: Install Postgres

vi /etc/yum.repos.d/CentOS-Base.repo

     For more information : https://wiki.postgresql.org/wiki/YUM_Installation

Add the below line for [base] and [updates] sections:

exclude=postgresql*

vi /etc/yum.repos.d/CentOS-Base.repo ; add the line

yum localinstall http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm

yum install -y postgresql94-server

yum install  postgresql94-contrib

Step # 5: Install PostGIS (optional)

yum install postgis2_94

Step # 6: Initialize the database

service postgresql-9.4 initdb

Step # 7: Configure remote access

vi /var/lib/pgsql/9.4/data/pg_hba.conf 

edit file /var/lib/pgsql/data/pg_hba.conf and change authentication method from ident to trust .

Step # 8: Start the postgres

service postgresql-9.4 start

Step # 9: Login

su - postgres

Step # 10: Create a new Database

createdb yourdatabasename

Step # 11: Create a user

createuser youruser

Step # 12: Grant permissions to a user

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public to youruser;

Step # 13: Create PostGIS extension to your database

yourdatabasename=# create extension postgis;

(from terminal: su - postgres; psql; \c yourdatabasename; create extension postgis;)

Step # 14: Verify that the extension is there

\d             (you will see the spatial_ref_sys table name)

Step # 15: Enable client authentication for the specific IP

vi /var/lib/pgsql/9.4/data/pg_hba.conf 

append the line below with the network address of the machine from which you want to connect / with your QGIS client:

host all all 123.123.123.123/24 trust

Step # 16: Configure listen addresses

vi /var/lib/pgsql/9.4/data/postgresql.conf 

listen_addresses='*'

Step # 17: Restart PostgreSQL Server

service postgresql-9.4 restart

Step # 18: Update the iptables

iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d 123.123.123.123 --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT

     Postgres is using port 5432, you need to open it.

Step # 20: Restart firewall

 /etc/init.d/iptables restart

Step # 21: Test your setup from the terminal

psql –h euca-123-123-123-123.eucalyptus.edu -U youruser -d yourdatabasename

Step # 22: From the QGIS client, set the following:

Open the QGIS browser, Select PostGIS, New, 

postgis port="5432" username=“youruser” password=“yourpassword" host="euca-123-123-123-123.eucalyptus.edu" database=“yourdatabasename" name=“connectionname"

Connect

Notes regarding the remote Postgres connection are in part related to this post:

http://www.cyberciti.biz/tips/postgres-allow-remote-access-tcp-connection.html

Related topics:
Eucalyptus: http://en.wikipedia.org/wiki/Eucalyptus_(software) and https://www.eucalyptus.com
PostGIS: http://postgis.net/
QGIS: http://www.qgis.org/en/site/
Postgres: http://www.postgresql.org/
CentOS: https://www.centos.org/

A koliko je to dobrijem konjem oda do tog Minkena, Radosave?

I zbilja bismo pre nekim dobrim konjem nego nekim avionom koji treba da poleti sa surcinskog aerodroma. Prvog dana nam je let bio odlozen (magla) a drugog, kako nisam mogla da cekam za treci dan, trebalo je ici preko Ljubljane, sto se zbog odlaganja leta iz Bg iskomplikovalo jer nismo stigli na vreme u Ljubljanu pa je trebalo cekati do uvece tako da mi je ostalo nekih par sati u Sloveniji.
Srecom, imala sam drustvo i Isidora i ja smo otisle da prosetamo Ljubljanom, imale srece sa autobusom do centra grada, nekih dva sata setale po centru, popile po kafu i vratile se na aerodrom. Bice tu i po koja slika iz Ljubljane. Sta imam da pricam o utiscima... fina zemlja, ljudi znaju srpski ili se makar trude, ljubazni su bas, nekako je mali grad kad ti pomenu prestonicu alli je jako lepo sredjen. Meni su se licno najvise dopale stare a lepe i ocuvane zgrade, ima ih puno i bas su lepe. Eto. I imaju lepu reku i tako... Imaju cak i "mali Merkator", ... zapravo Merkator ali velicine prodavnice na cosku sto se meni opet dopalo :). I mnogo originalnih ideja za sredjivanje izloga.
Za dva sata dosta :)