Detail Installing iDempiere on Amazon's PostgreSQL RDS Database

Installing iDempiere on Amazon's PostgreSQL RDS Database

NOTE: as of 2015-07-22, the installation script will now install directly into an AWS RDS instance. NOTE: RDS is now on PostgreSQL 9.4. Ubuntu 14.04 is still on 9.3. To use psql to perform backups, etc.., you need to upgrade your psql using these instructions:

http://www.postgresql.org/download/linux/ubuntu/
Reference: http://stackoverflow.com/questions/12836312/postgresql-9-2-pg-dump-version-mismatch

NOTE: the below is no longer needed; however, it will be kept for reference for some period of time. The purpose of this section is to help you install iDempiere on Amazon's PostgreSQL RDS service. Unfortunately, getting iDempiere up and running on RDS requires a little more work as compared to using the PostgreSQL that comes with Linux. The reason is that RDS does not give you access to the 'postgres' superuser or 'superuser' privileges. iDempiere's installation scripts assume you have access to both. Please note this is a more advanced topic. There is much assumed there that a brand new person might not know or understand. Here are the steps to help you work around this issue:

Log into AWS and create a new PostgreSQL RDS instance. Note that you will want to use a VPC. Also note that RDS might not let you use an existing VPC. If so, just let the RDS creation process create a new one for you. Make note of its ID when done. I used the following values during my RDS creation:
1. Database User: harp4u - FYI stands for high availability read postgres attempt 4 :)
2. Database: harp4db
Create a new EC2 instance in the same VPC used/created in step 1. This will host your iDempiere application server (appserver).
Install iDempiere as if you were installing all components on the same machine (appserver + database).
Verify that iDempiere is running successfully.
Connect via ssh to your iDempiere server.
Issue:
1. ```
sudo service idempiere stop
```
Log into your RDS database using:
1. ```
psql -d harp4db -U harp4u -h harp4.c7uuuoadpu6z.us-east-1.rds.amazonaws.com
```
  1. -d is the database created in step 1
  2. -U is the db user created step 1
  3. -h is the host URL - this will come from step 1.

Issue the following commands:

CREATE ROLE adempiere WITH CREATEDB CREATEROLE LOGIN PASSWORD 'Silly'; -- note that 'Silly' is the same password given during the above iDempiere installation.

```
GRANT adempiere TO harp4u;
```
```
GRANT rds_superuser TO adempiere;
```
```
\q
```

Log into your RDS using your newly created adempiere role:

psql -d harp4db -U adempiere -h harp4.c7uuuoadpu6z.us-east-1.rds.amazonaws.com

Issue the following commands:

CREATE DATABASE idempiere WITH ENCODING='UNICODE';

ALTER ROLE adempiere SET search_path TO adempiere, pg_catalog;

```
\c idempiere
```
```
CREATE EXTENSION "uuid-ossp";
```
```
\q
```

Populate your iDempiere database:

psql -d idempiere -U adempiere -h harp4.c7uuuoadpu6z.us-east-1.rds.amazonaws.com -f /opt/idempiere-server/data/seed/Adempiere_pg.dmp &> importResults.txt

cd /opt/idempiere-server
Run the idempiere setup utility:
1. ```
sudo -u idempiere ./console-setup.sh
```
  1. Set the Database Exists = Y
  2. Change the DB URL to that of your RDS
sudo service idempiere start

If you wish to start over, perform the following commands:

```
sudo service idempiere stop
```

psql -d harp4db -U harp4u -h harp4.c7uuuoadpu6z.us-east-1.rds.amazonaws.com

ALTER DATABASE idempiere OWNER TO harp4u;

```
DROP DATABASE idempiere;
```
```
DROP ROLE adempiere;
```

Notes

If you are creating a production system, you should consider creating your initial iDempiere instance as a separate database and application server. The reason is that the script makes performance tweaks when it installs a dedicated appserver and/or database. You will ultimately throw away the initial database; however, if your application server is big enough, you want iDempiere to make the most of the server's potential.