"AWS: Install PostgreSQL 9.0 on Amazon Linux update"

[dhackney]

Thank you for posting the instructions on installing PostgreSQL 9.0 on AWS Linux.

Your efforts are greatly appreciated by non-RDS-PostgreSQL novices such as me.

I updated the instructions for v9.6 and added two sections:

1. set the EC2 data volume to auto-mount on boot
2. set PostgreSQL to start at boot using cron (the chkconfig method did not work for me, I suspect due to the non-default data location)

Please note the following differences to your setup:

1. I use nano as an editor
2. I used the default port: 5432

The relevant changed portion follows:

---

7. Connect to your EC2 instance, and execute (“/dev/sdb” must match the assigned device, and I am going to mount at “/pgdata”)

sudo su -
yes | mkfs -t ext3 /dev/sdb
mkdir /pgdata
mount /dev/sdb /pgdata
exit
exit

Open a new SSH session on the instance

Set the volume to auto-mount on EC2 instance reboot:
(this section uses AWS docs here: https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-using-volumes.html )

Get the file system type of the mounted device:

mount

Display the available devices:

df

Get the UUID of the device:

ls -al /dev/disk/by-uuid/

Backup the fstab file:

sudo cp /etc/fstab /etc/fstab.orig

Edit the fstab file:

sudo nano /etc/fstab

Sample fstab entry:

UUID=de9a1ccd-a2dd-44f1-8be8-0123456abcdef       /pgdata   ext3    defaults,nofail        0       2

Test the mounts:

sudo mount -a

Update the yum repositories

I want to install the latest stable postgresql from pgrpms.org. We could just download the rpm and manually install from the file, but that inevitably results in some dependency issues. I prefer to configure an alternate yum repository for a particular keyword. So we need to update the configuration for the Amazon repositories (be sure to update both “main” and “updates”, and do not forget the asterisk).

nano /etc/yum.repos.d/amzn-main.repo

[At the bottom of the "[amzn-main]" section, after "enabled=1", add "exclude=postgresql*"]

nano /etc/yum.repos.d/amzn-updates.repo

At the bottom of the "[amzn-updates]" section, after "enabled=1", add "exclude=postgresql*"]

Download the postgresql 9.6 repo:

rpm -ivh https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-6-x86_64/pgdg-ami201503-96-9.6-2.noarch.rpm

Update the repo manager with the new repo:

yum update

Install and configure PostgreSQL 9.6 on Amazon Linux

After updating the yum repository configurations, “yum install postgresql” should provide us with the latest postgresql packages from pgrmps.org. Notice that a few dependencies will come from the amazon repositories, but most of the pertinent postgresql* packages are coming from pgrpms. It is extremely likely that you do not need all of these packages. Limit the installation however you feel is appropriate.

I usually install something like the following:

yum install postgresql96 postgresql96-contrib postgresql96-devel postgresql96-server

Now we need to initialize the database cluster, edit the configuration and start the server. First remove the /pgdata/lost+found directory. PostgreSQL’s initdb will fail to initialize a database cluster in /pgdata/ when there are files/directories present. Then we will change ownership of the /pgdata directory to the postgres user and group, and change to the postgres user. As the postgres user, we can configure and launch the server.

Be careful with this
Remove the lost+found to enable install:

sudo rm -rf /pgdata/lost+found

Change the user:group ownership for the postgresql data directory:

sudo chown -R postgres:postgres /pgdata

Change to root Linux user:

sudo su -

Change to postgres Linux user:

su postgres -

Initialize the database:

/usr/pgsql-9.6/bin/initdb -D /pgdata

The following commands require the postgres Linux user
To change to the postgres Linux user:

sudo su - postgres

For the postgres Linux user, the console prompt should be:

-bash-4.2$

Edit the postgresql.conf file (be sure you are still using the postgres user):

nano /pgdata/postgresql.conf

Update the lines:

#listen_addresses = 'localhost' ...
#port = 5432 ...

To read:

listen_addresses = '*' ...
port = 5432

Edit the pg_hba.conf file (be sure you are still using the postgres user):

nano /pgdata/pg_hba.conf

Update the bottom of the file to read:

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD
# "local" is for Unix domain socket connections only
local   all             postgres                                trust
# IPv4 local connections:
host    all             power_user      0.0.0.0/0               md5  
host    all             other_user      0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Note: for PostgreSQL clients such as Navicat, set the role password as "encrypted" in the client for any PostreSQL USER with a METHOD set to md5 in the pg_hba.conf file

Start the server:

/usr/pgsql-9.6/bin/pg_ctl -D /pgdata -l logfile start

Create users for external access
Create the power_user as a superuser:

/usr/pgsql-9.6/bin/createuser power_user
Shall the new role be a superuser? (y/n) y

Alternatively, use psql to create the power_user:

/usr/pgsql-9.6/bin/psql -p 5432
CREATE ROLE power_user WITH SUPERUSER LOGIN;
ALTER USER power_user WITH PASSWORD 'aVerySecurePassword';

Create the other_user as a non-superuser, who can not create databases or roles:

/usr/pgsql-9.6/bin/createuser other_user
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

Connect to the database as postgres, and set the new user passwords (Be sure you are still logged in as postgres). Also create a database for the other_user:

/usr/pgsql-9.6/bin/psql -p 5432
postgres=# ALTER USER power_user WITH PASSWORD 'aVerySecurePassword';
postgres=# ALTER USER other_user WITH PASSWORD 'anEquallySecurePassword';
postgres=# CREATE DATABASE other_user WITH OWNER other_user;

Create a password for the postgres user:

ALTER USER postgres WITH PASSWORD 'aVerySecurePassword';

Exit psql:

\q

or

\quit

Create a .pgpass file for user ec2-user to enable auto-login for the default AWS EC2 Linux user:

Change to the postgres Linux user:

sudo su - postgres

Create a .pgpass file:

nano /home/ec2-user/.pgpass

Enter this text:

# file to provide logon info to postgreSQL
# format:
# hostname:port:database:username:password
#
localhost:5432:my_database:ec2-user:securePassword

Set permissions on the .pgpass file:

chmod 600 /home/ec2-user/.pgpass

Set the database to start on instance reboot:

sudo chkconfig postgresql-9.6 on

Confirm database is set to start on reboot:

chkconfig --list postgresql-9.6

If that does not work (it did not work for me), use this to start postgresql on boot:

Create shell script to start postgresql:

sudo nano /etc/init.d/start-postgresql.sh

Enter this text:

#! /bin/bash
#
# starts postgresql using non-default data directory
#
sudo su -c "/usr/pgsql-9.6/bin/pg_ctl start -D /pgdata" postgres > 'start-postgresql.log'

Set the shell script to execute:

sudo chmod +x /etc/init.d/start-postgresql.sh

Set the script to run on boot:

sudo crontab -e

Add this line:

@reboot /etc/init.d/start-postgresql.sh

Save and exit the file:

[esc]:wq

Misc:

To restart the DB:

sudo su - postgres
/usr/pgsql-9.6/bin/pg_ctl restart -D /pgdata

To reload the hba.conf configuration:

sudo su - postgres
/usr/pgsql-9.6/bin/pg_ctl reload -D /pgdata

To start psql:

sudo -u postgres psql

[imperialwicket]

@dhackney Thanks! I'm inclined to just post your newer article as a separate post with all the right keywords, and give you credit for bringing it back to current. Seem ok for you?

[dhackney]

@imperialwicket

You are very welcome. Thanks again for your work in putting this together initially.

RE: Seem ok for you?

Sure

Note: I updated the comment today to include some additional detail that I captured while going through the process again.