Re: [Users] New engine install on remote DB fails uuid-ossp extension is not loaded
Hi Ian You're right in that superuser privileges are required to make this work. But it would rarely happen that remote DB admin would give you a user with super privileges. The solution we've come up with is this: 1. Use (or tell remote DB admin to do so) the psql command to load the extension functions to template1 DB on remote DB server: psql -U postgres -d template1 -f /usr/share/pgsql/contrib/uuid-ossp.sql 2. Now, all newly created databases will include extension functions. template1 is a special DB in postgres. In fact, when you create a new DB, it is actually copied from template1 with a new name. This way, we can still work with the user that has no superuser privileges in the remote DB server. - Original Message - From: Ian Levesque i...@crystal.harvard.edu To: Yair Zaslavsky yzasl...@redhat.com Cc: users@ovirt.org Sent: Wednesday, August 15, 2012 12:30:16 AM Subject: Re: [Users] New engine install on remote DB fails uuid-ossp extension is not loaded Here's what the install log tells me: 2012-08-14 11:56:12::INFO::engine_validators::425::root:: Successfully created temp database on server cmcd-web. 2012-08-14 11:56:12::INFO::engine_validators::443::root:: Checking that uuid extension is loaded by default on the remote server 2012-08-14 11:56:12::DEBUG::common_utils::350::root:: running sql query 'SELECT uuid_generate_v1();' on db server: 'cmcd-web'. 2012-08-14 11:56:12::DEBUG::common_utils::286::root:: cmd = /usr/bin/psql -h cmcd-web -p 5432 -U ovirt -d ovirt_engine_test -c SELECT uuid_generate_v1(); 2012-08-14 11:56:12::DEBUG::common_utils::291::root:: output = 2012-08-14 11:56:12::DEBUG::common_utils::292::root:: stderr = ERROR: function uuid_generate_v1() does not exist So, the installer is trying to create a database ovirt_engine_test and succeeding. But until you run `psql -U postgres -d database_name -f /usr/share/pgsql/contrib/uuid-ossp.sql`, the function uuid_generate_v1() won't be available. Therefore, the temporary database the installer is using to determine compatibility fails. The problem ended up being that the postgres role which ovirt is using needs to have superuser privileges. Once I granted superuser to the ovirt role I created, all worked well. ~irl ___ Users mailing list Users@ovirt.org http://lists.ovirt.org/mailman/listinfo/users ___ Users mailing list Users@ovirt.org http://lists.ovirt.org/mailman/listinfo/users
Re: [Users] New engine install on remote DB fails uuid-ossp extension is not loaded
On Aug 15, 2012, at 3:50 AM, Alex Lourie wrote: Hi Ian You're right in that superuser privileges are required to make this work. But it would rarely happen that remote DB admin would give you a user with super privileges. The solution we've come up with is this: 1. Use (or tell remote DB admin to do so) the psql command to load the extension functions to template1 DB on remote DB server: psql -U postgres -d template1 -f /usr/share/pgsql/contrib/uuid-ossp.sql 2. Now, all newly created databases will include extension functions. template1 is a special DB in postgres. In fact, when you create a new DB, it is actually copied from template1 with a new name. This way, we can still work with the user that has no superuser privileges in the remote DB server. Thanks for your reply. Though, to be fair, a DB admin might also object to including the uuid-ossp extension functions by default on the template DB. That said, it's certainly preferred over full superuser privs :) Cheers, Ian ___ Users mailing list Users@ovirt.org http://lists.ovirt.org/mailman/listinfo/users
Re: [Users] New engine install on remote DB fails uuid-ossp extension is not loaded
On Aug 14, 2012, at 12:58 PM, Yair Zaslavsky wrote: On 08/14/2012 07:12 PM, Ian Levesque wrote: Hello, I'm trying to install oVirt 3.1, using an external postgres db running on RHEL6 (postgresql-server-8.4.12-1.el6_2.x86_64). I'm getting the following error during engine-setup: Error: uuid-ossp extension is not loaded into the DB. Verify with the DB admin that uuid-ossp extension is loaded into newly created databases and rerun the setup. The database log indicates: ERROR: function uuid_generate_v1() does not exist at character 8 HINT: No function matches the given name and argument types. You might need to add explicit type casts. STATEMENT: SELECT uuid_generate_v1(); I did some searching, and it appeared that I might need to add some postgres-contrib functions: psql -U postgres -d engine -f /usr/share/pgsql/contrib/uuid-ossp.sql Unfortunately, it appears that the install script wants to create a temporary database and expects these functions to exist on its newly-created temp DB, not an already-existing database. Indeed this step is required for postgresql 8.4.x (you have to install this script). I did not encounter an issue with this script. I ran psql -U postgres -d engine -f /usr/share/pgsql/contrib/uuid-ossp.sql And the script execution went smoothly (this scripts runs instructions of create or replace function so it is re-entrant). Can you please elaborate more on the error you're getting? Here's what the install log tells me: 2012-08-14 11:56:12::INFO::engine_validators::425::root:: Successfully created temp database on server cmcd-web. 2012-08-14 11:56:12::INFO::engine_validators::443::root:: Checking that uuid extension is loaded by default on the remote server 2012-08-14 11:56:12::DEBUG::common_utils::350::root:: running sql query 'SELECT uuid_generate_v1();' on db server: 'cmcd-web'. 2012-08-14 11:56:12::DEBUG::common_utils::286::root:: cmd = /usr/bin/psql -h cmcd-web -p 5432 -U ovirt -d ovirt_engine_test -c SELECT uuid_generate_v1(); 2012-08-14 11:56:12::DEBUG::common_utils::291::root:: output = 2012-08-14 11:56:12::DEBUG::common_utils::292::root:: stderr = ERROR: function uuid_generate_v1() does not exist So, the installer is trying to create a database ovirt_engine_test and succeeding. But until you run `psql -U postgres -d database_name -f /usr/share/pgsql/contrib/uuid-ossp.sql`, the function uuid_generate_v1() won't be available. Therefore, the temporary database the installer is using to determine compatibility fails. Ian ___ Users mailing list Users@ovirt.org http://lists.ovirt.org/mailman/listinfo/users
