Install PostgreSQL on Amazon AMI

install_postgresql.sh

#!/bin/bash
###############################################
# To use: 
# https://raw.github.com/gist/2776351/???
# chmod 777 install_postgresql.sh
# ./install_postgresql.sh
###############################################
echo "*****************************************"
echo " Installing PostgreSQL"
echo "*****************************************"
sudo yum -y install postgresql postgresql-server postgresql-devel postgresql-contrib postgresql-docs
sudo service postgresql initdb
# Use MD5 Authentication
sudo sed -i.bak -e 's/ident$/md5/' -e 's/peer$/md5/' /var/lib/pgsql9/data/pg_hba.conf
#start
sudo /sbin/chkconfig --levels 235 postgresql on
sudo service postgresql start

# http://imperialwicket.com/aws-install-postgresql-on-amazon-linux-quick-and-dirty

psql: FATAL: database "ec2-user" does not exist

Just remove the following

# Use MD5 Authentication
sudo sed -i.bak -e 's/ident$/md5/' -e 's/peer$/md5/' /var/lib/pgsql9/data/pg_hba.conf

because it changes the way postgres will authenticate to the local server. Just skip it for the moment.

Now, you could change the postgres password:

$ sudo -u postgres psql postgres
postgres=# \password postgres

You'll see:

Enter new password:
Enter it again:

Then go back to ec2-user and now change the pg_hba.conf to activate the MD5 Authentication:

# Use MD5 Authentication
sudo sed -i.bak -e 's/ident$/md5/' -e 's/peer$/md5/' /var/lib/pgsql9/data/pg_hba.conf

Restart the service, so the changes to be applied:
sudo service postgresql start

Finally, you will be able to login using your new password for postgres user:
psql -U postgres

Useful script ... And FYI - with psql (PostgreSQL) 9.2.23, the recommended way to initialise the database cluster is sudo postgresql-setup initdb instead of sudo service postgresql initdb