loc2log

I have Python 2.6.7 and PostgreSQL 8.4.9 on my ec2 machine - all "stock". When it came to interacting between those two, I opted for psycopg2 Python module, which implements Python DB API 2.0 specifications. The module seems to be quite mainstream, timely updated, and compliance with standards is a positive thing in my eyes. :-)

My current PostgreSQL install has

[ec2-user@ip ~]$ rpm -qa | grep postgres
postgresql-libs-8.4.9-1.13.amzn1.x86_64
postgresql-8.4.9-1.13.amzn1.x86_64
postgresql-server-8.4.9-1.13.amzn1.x86_64

In order to get the programmatic access, we need to get libpq-dev. Couldnot think of anything but libpq from postgresql-devel:

[ec2-user@ip ~]$ sudo yum install postgresql-devel

The other pre-requisite - python-dev, seemed to be there already (I might have installed it before):

[ec2-user@ip ~]$ rpm -qa | grep python-dev
python-devel-2.6-1.19.amzn1.noarch

The last requirement is to have pg_config in the PATH.

[ec2-user@ip ]$ which pg_config
/usr/bin/pg_config

confirmed - it is.

I installed from source as they claim easy_install would not resolve these dependencies anyway; but I am pretty confident that sudo easy_install psycopg2 would have worked just fine.

To install from the source, I downloaded the archive from the Download page. It happened to be the source package for psycopg2-2.4.4 - http://initd.org/psycopg/tarballs/PSYCOPG-2-4/psycopg2-2.4.4.tar.gz.

Then it was easy:

[ec2-user@ip ]$ tar -zxf psycopg2-2.4.4.tar.gz
[ec2-user@ip ]$ cd psycopg2-2.4.4
[ec2-user@ip ]$ sudo python setup.py install

The install command ended up with Writing /usr/lib64/python2.6/site-packages/psycopg2-2.4.4-py2.6.egg-info, and I could not spot errors, so I assume psycopg2-2.4.4 is installed just fine.

Let's give it a smoke test:

[ec2-user@ip-10-64-54-250 psycopg2-2.4.4]$ python
Python 2.6.7 (r267:88850, Jun 4 2011, 04:34:02)
[GCC 4.4.4 20100726 (Red Hat 4.4.4-13)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2
>>>

Well, so far so good (at least it imports).

Additional info

1. psycopg officiall web site - http://initd.org/psycopg/
2. psycopg PyPi - http://pypi.python.org/pypi/psycopg2

Having to click same input checkmarks at Ucoz "New / Edit Entry" forms on each save was quite annoying. So I decided to set some defaults.

When opening a form for "Add new entry" / "Edit" I wanted "Substitute line feeds by the tag <BR>" to be always unchecked, "Receive notifications about comments" to be always checked, and a bunch of other checkmarks to be always checked too.

... Read more »

Here is how to make a no frills installation of PostgreSQL to micro image Amazon Elastic Cloud Computing (ec2).

... Read more »

PostgreSQL's command line client (psql) is indispensable, but visual tools definitely have their perks. Here how I’ve got my local pgAdmin III working with my Amazon EC2 machine.

pgAdmin III - is a GUI client to administer and develop PostgreSQL databases. The official downloads page is at http://www.pgadmin.org/download/. I used pgAdmin III version 1.14.2 for MS Windows.

pgAdmin III installation is really trivial: On your local computer, download a version for your OS, unzip, start the msi. Follow the wizard. Done.

In order to connect to the postages instance in the cloud, I chose localhost tunnel ssh option as secure and not requiring any tweaks to the PostgreSQL config. I’ll be pretending localhost to the postgresql instance. Here is the official manual page for PostgreSQL tunnels http://www.postgresql.org/docs/8.4/interactive/ssh-tunnels.html

ssh client is needed to establish the tunnel. If you are on Linux or cygwin, make sure you have at your local machine your private key for your Amazon EC2 account in your ssh home:

ls -l /home/your_user_name/.ssh/id_rsa
-rw------- 1 your_user_name ... id_rsa

Note that .ssh must have -rwx------ (chmod 700) and id_rsa must have -rw------- (chmod 600) permissions, otherwise self-respecting ssh will refuse to give you a protected connection.

Now, let’s bring your_ec_public_ip’s port 5432 (the default PostgreSQL port), to your local machine’s (localhost) port 5432 using default ssh port 22 as the tunnel. Execute:

ssh -o TCPKeepAlive=yes -o ServerAliveInterval=300 -L 5432:localhost:5432 ec2-user@your_ec_public_ip -p 22

Your EC2 login shall pass and you shall see the remote console.

Now, to get the database, you start the pgAdmin III, and set a database server connection, pretending you have the database on your localhost. It shall be available as long as the tunnelled connection is alive.

P.S. If you are on MS Windows, and you've got only Putty on your hands, just complement your Amazon EC2 connection with tunnel like this:
I.e. Source port = L:5432, Destination = localhost:5432 And of course, you have already set a keepalive, so your connection would not drop while you are idle (errrr, thinking):