Connect to remote postgreSQL database using Ruby

Ever wrote a script, meaning you needed to connect to a remote PG database using PORO (plain old ruby object)?

This can be quite tricky as I found out. Here is a how to.

Let’s say you are parsing some page on the interwebs.

require 'nokogiri'
require 'open-uri'
require 'pg'

@doc = Nokogiri::HTML(open("http://www.some-url.com/products"))

attributes = @doc.css("td[class=some class] a @href")

attrArray = attributes.map { |attr| attr.value}


@newDocArray = Array.new
@nameArray = Array.new

attrArray.each_with_index do |link, index|
 newDoc = Nokogiri::HTML(open("http://www.some-url.com/products/#{link}"))
 
 nameDoc = newDoc.css("div h1")
 newDoc = newDoc.xpath("//div[@class='cardpage']")
 
 newDoc.search("form").remove
 newDoc.search("a").remove

 @nameArray << nameDoc.map { |attr| attr.inner_text}[0]
 @newDocArray << newDoc.map { |attr| attr.inner_text}[0]

end

Now you need to prepare you postgres config on the remote machine to allow connections from your current local machine (per default postgres allows only localhost connections, meaning from remote machine to itself).

There is an amazing tutorial on this page – http://www.cyberciti.biz/faq/postgresql-remote-access-or-connection/

I will sum it up:

Edit $ nano /var/lib/pgsql/data/pg_hba.conf It might be in some other different folder. If that is the case, just search for “pg_hba.conf” – find / -name pg_hba.conf

Now search for your own ip and paste it in pg_nba.conf

host all all 192.168.1.0 255.255.255.0 trust

(Replace 192.168.1.0 with your obtained ip. I left DNS mask unchanged.

Now find and edit $ nano /var/lib/pgsql/data/postgresql.conf

Changing this value – tcpip_socket = true . Now comes the important part – if you don’t find any tcpip_socket value, then DON’T add it! Instead search for

# – Connection Settings –

listen_addresses = ‘*’
port = 5432

And change listen_addresses = ‘localhost’  to listen_addresses = ‘*’ .

That’s it! Restart # /etc/init.d/postgresql restart postgreSQL and try connecting $ psql -h 82.102.42.56 -U username -d database where:

-h 82.102.42.56 is the remote server ip where PG db is running
-U username is the username of the PG user
-d database is the database name

Now we’re all ready to rock and roll!

@conn = PGconn.connect("82.102.42.56", 5432, '', '', "my_database", "my_user", "my_topsecret_password")

def prepareInsertProduct
    	@conn.prepare("insert_product", "insert into products (name, description,) values ($1, $2)")
	end

def addProduct(name, description)
    @conn.exec_prepared("insert_product", [name, description])
end

def connect(arrayContent, arrayNames)

	prepareInsertProduct

	length = arrayContent.length

	arrayContent.each_with_index do |contentValue, index|		
		name = arrayNames[index]
		description = contentValue

		addProduct(name, description)

		print "Processing entry - #{index}"
		print "\r"
	end

end

connect(@newDocArray, @nameArray)