Using Amazon's AWS PostgreSQL RDS

[Chuck Boecking]

Hi Everyone,

Amazon AWS recently launched support for PostgreSQL RDS. This is sort of a big deal for our community. It offers:

1. Multi data center synchronous replication
2. Automated backup and point-in-time restoration
3. Pre-configured performance tuning for the selected size server
4. I also believe that they will follow the MySQL RDS model and offer multi-read at some point in the near future.

All this goes to say that you can create a highly-scalable iDempiere instance without spending a month or two trying to learn HAProxy (webui load balancing) and PGPool2 (DB load balancing).

Here is the problem: the console-setup.sh script hard codes the use of the 'postgres' system username. Unfortunately, you cannot set 'postgres' as the PostgreSQL RDS system user because AWS reserves the use of this name. Therefore, I am stuck. I have found no way to get around this hurdle.

I would like to recommend:

1. We add dbSystemUser (next to dbSystemPassword) in the ConfigurationConsole
2. Add a dbSystemUser prompt to the console-setup.sh process. Default its value to 'postgres'.

I am guessing the biggest pain associated with this recommendation is that automated installations using console-setup.sh will break when we add the new parameter.

Does this seem like a reasonable recommendation? Let me know if you find a work around.

Thanks,

Chuck Boecking

www.chuckboecking.com

[norber...@multimageweb.com]

hi Chuck.

i got it. just create user postgres in IAS and assign appropriate policy. so i can get dmp to the RDS/PG.

i met next stopper related to C functions support. Functions like *uuid* can't be created because no permission. I tried load regular dmp file.

amazon guru's answers not really C support yet. But what is strange once when i restore dmp c functions was created and owned by rdadmin. but how does it happened? can't repeat same again. worst is can't get right to stay superuser. so manually can't create c functions.....

but i'm not really geek for pg so your request should help on all of this.

maybe we can call try Skype nbe222. i need to get it works ASAP.

norbert

This e-mail is confidential and may contain legally privileged information. It is intended only for the addressees and may not be reviewed or used in any way by other recipients. If you have received this e-mail in error, kindly notify us immediately by telephone or e-mail and delete the message and any attachments thereto from your system.

[Chuck Boecking]

Hi Norbert,

Thank you for the quick response. It seems to me that the lack of C support (for now) is a deal killer. Does anyone have an opinion on this topic? 

Have you tried using pgpool2? Are you interesting in using pgool2 to replicate the AWS features?

Regards,


Chuck Boecking
512.850.6068 (office and cell)
ch...@chuboe.com
ChuckBoecking.com
chuck.boecking (skype)
http://www.linkedin.com/pub/chuck-boecking/10/970/17b

--
You received this message because you are subscribed to a topic in the Google Groups "iDempiere" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/idempiere/4sCvrAu7zKE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to idempiere+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/0cc9d346-5478-4513-994c-1a349dcca385%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Carlos Antonio Ruiz Gomez]

Hi Chuck, I was able to create successfully an idempiere DB on amazon RDS postgresql.

Instance idemtest created with database idempiere and user adempiere

Amazon assigned the host idemtest.cxa7towo0htj.us-east-1.rds.amazonaws.com for my tests, so I needed to execute the next steps (the first two are not needed, just in case you want to recreate the db):

HOST=idemtest.cxa7towo0htj.us-east-1.rds.amazonaws.com

psql -d postgres -U adempiere -h $HOST -p 5432 -c "drop database idempiere"

psql -d postgres -U adempiere -h $HOST -p 5432 -c "create database idempiere WITH ENCODING='UNICODE' owner=adempiere"

psql -d idempiere -U adempiere -h $HOST -p 5432 -c 'CREATE EXTENSION "uuid-ossp"'

I found some problems with the normal dump that probably can be just ignored, but in case you want a ready-to-use amazon dump not showing errors, you can download the one I fixed from here:

http://sourceforge.net/projects/idempiere/files/v2.0/amazon-rds-dump/iDempiere_v2_amazonRDS.dmp.jar/download

Then I imported the database with:

psql -d idempiere -U adempiere -h $HOST -p 5432 -f Adempiere_pg_amazonRDS.dmp

And then I applied the pending scripts with:

syncApplied_2.0.sh idempiere "-h $HOST -p 5432"

For this you can use a modified version of https://bitbucket.org/CarlosRuiz_globalqss/idempiere-stuff/src/tip/script_to_sync_db/syncApplied_2.0.sh?at=stuff

just change the line 9 with something like:

USER="adempiere -h idemtest.cxa7towo0htj.us-east-1.rds.amazonaws.com -p 5432"

We don't need permissions for C - and our requirements (pl/pgsql and the uuid extension) are already supported.

And that's all.  Then you can use your console-setup.sh stating that the database already exists and pointing to where is located - note that precisely we implemented support for this case (not having postgresql password) with https://idempiere.atlassian.net/browse/IDEMPIERE-472

Regards,

Carlos Ruiz

[Chuck Boecking]

Much love and respect :)

Regards,
Chuck Boecking

[norber...@multimageweb.com]

Hi Carlos, 

thanks for helpful answer. i'm playing with RDS still, some questions are appears.

1. can i get RDS specific dmp from my prod database ? then import without error messages. or these errors messages are just cosmetic issues ?

2. i created succesfully uuid-ossp extension, new functions was visible - but after database dmp restore - functions are not visible anymore. when i try to create it again then error message appear: ERROR:  extension "uuid-ossp" already exists. that means extension installed. but functions are not visible. BUT that is maybe just "not knowing". 

3. are idempiere support any other dbname, user name then idempiere/adempiere ?

norbert

[Carlos Antonio Ruiz Gomez]

Hi Norbert,

1 & 2 -> There is a problem with the seed dmp that we must fix for next seed - on seed for pg9 is including the creation of uuid functions - which is unnecessary and creating a bug.
You can delete the extension and recreate it again and that will fix the issue.

3 -> (on postgres) the idempiere dbname can be changed without any problem - the adempiere schema name can be changed too, but you would need to take care of replacing adempiere by the new name on the seed dmp.

Regards,

Carlos Ruiz

On 28/12/13 05:57, norber...@multimageweb.com wrote: