DB Cloud Computing: Postgresql Setup on Amazon EC2 using Fedora 8

Postgresql Setup on Amazon EC2 using Fedora 8

Prerequisites: Familiar with creating and launching instances using Amazon EC2, and familiar with editing config files in linux.

Links to documentation on Amazon EC2 and how to create and launch instances:

http://developer.amazonwebservices.com/connect/kbcategory.jspa?categoryID=84

click on Technical documentation link.

Thanks goes to this article: http://www.postgresonline.com/journal/index.php?/archives/45-An-Almost-Idiots-Guide-to-PostgreSQL-YUM.html

There are some things missing from that article that I have addressed here.

1) Create Ami instance from pre-existing instance and launch instance.

Use ami-0abe5a63 for example, see url: http://www.nonhostile.com/mono-on-amazon-ec2.asp

url: http://developer.amazonwebservices.com/connect/entry.jspa?externalID=1616&categoryID=101

2) Logon to your instance using an SSH client (in windows you can use Putty)

3) Login as root

4) Edit /etc/yum/pluginconf.d/fastestmirror.conf and add to the end: exclude=postgresql*

(differs from the instructions at http://yum.pgsqlrpms.org/howtoyum.php to prevent your YUM update from getting postgresql from other sources)

5) Select the appropriate repository config file for your OS and choose 8.3 from here and navigating thru: http://yum.pgsqlrpms.org/reporpms/repoview/letter_p.group.html

Note the install file - should look something like http://yum.pgsqlrpms.org/reporpms/8.3/pgdg-fedora-8.3-4.noarch.rpm

6) Do a wget of the appropriate one: e.g.
wget http://yum.pgsqlrpms.org/reporpms/8.3/pgdg-fedora-8.3-4.noarch.rpm

7) Next install the rpm config file with:
rpm -ivh pgdg-fedora-8.4-1.noarch.rpm

8) Install the things that you want. These are the ones we tend to install

yum install postgresql

yum install postgresql-server

yum install postgis

yum install pgadmin3

Note:On Fedora 8, postgresql default location is /var/lib/pgsql/data

9) Create a user postgres

Add command prompt : useradd postgres

10) Set new user’s password

Add command prompt: passwd postgres

11) Initialize postgreSql

At command prompt:

chown postgres /var/lib/pgsql/data

su postgres

initdb –D /var/lib/pgsql/data

12) Configure postgresql for local access

From command line ( start up vi and add the 2 entries):

vi /var/lib/pgsql/data/pg_hba.conf
- add:local all all trust
host all all 127.0.0.1 255.255.255.255 trust
host all all 0.0.0.0 255.255.255.255 reject

13) To configure remote access, from command line, pass in range of external ips to allow access to:

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

listen_addresses = ‘*’

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

host all all [start ip range] [end ip range] trust
14) Assuming you have the AMI tools installed on your desktop, from the command line authorize the default port of 5432, by doing the following:

c:\amitools\ec2-authorize default –p 5432

Note: Now you can use the Postgres Admin UI Tools (pgAdmin III) from the machines that you granted remote access to. PgAdmin III allows you to administer Postgresql via a GUI. You can get this tool by installing Postgresql on your Windows desktop(s). I recommend this since it makes administration much easier.

15) Restart Postgresql, from command line (logged in as root):

service postgresql restart

16) Create a db if you like, from command line:

su – postgres

createdb db_name

17) Create a postgresql username with password for new db.

From command line: (-U postgres , means username postgres)

createuser –P –U postgres

18) Restart postgresql

19) Test your connection

login as root, then from command line:

psql –U db_username db_name