Forum OpenACS Q&A: Postgres DB migration for OpenACS 5.4.1

Collapse
1: Postgres DB migration for OpenACS 5.4.1
Posted by Ratnakar Sagare on 04/16/08 08:54 AM
Hi,
I am running my intranet web server with Openacs 5.1.5(with postgres 7.3.3). I am switching now to latest version of openacs i.e. 5.4.1.
My queries are:
How can I restore the old postgres DB (dmp) file into newly installed DB?
Is Openacs 5.4.1 is compatible with postgres 7.3.3? or else how can I migrate from older postgres version to newer one?

Thanks in advance

Ratnakar

Collapse
2: Re: Postgres DB migration for OpenACS 5.4.1 (response to 1)
Posted by Dave Bauer on 04/16/08 02:43 PM
OpenACS 5.4.1 is supported on PostgreSQL 8.1.x and 8.2.x.

The PostgreSQL documentation explains how to upgrade to a newer version.
http://www.postgresql.org/docs/8.1/interactive/install-upgrading.html

You will also need to change your postgresql.conf file to work with OpenACS 5.4.1.

http://www.openacs.org/xowiki/How_to_install_in_Postgres_8%2ex

Collapse
3: Re: Postgres DB migration for OpenACS 5.4.1 (response to 2)
Posted by Ratnakar Sagare on 04/16/08 03:20 PM
Thanks Dave,

I'll definitely try that

Ratnakar

Collapse
4: Re: Postgres DB migration for OpenACS 5.4.1 (response to 2)
Posted by Ratnakar Sagare on 04/18/08 01:11 PM
Hi
I'd gone through the Postgresql doc; and created the dmp file of old DB.
I copied this file into new server & tried to restore it using the psql commands, it gives error as below:

\connect: FATAL: Ident authentication failed for user "postgres"

Any Ideas about this ?

PS. 'postgres' UID on both systems is different. Is that causing this issue ?

Thanks

Collapse
5: Re: Postgres DB migration for OpenACS 5.4.1 (response to 4)
Posted by Stefan Sobernig on 04/18/08 01:41 PM
Ratnakar,

Access policy defaults in postgresql installation enforce identity constraints, i.e. the _postgresql user_ == _connecting system user_ ... i assume you try to by calling sth. like:

~# psql -U postgres [...]

BUT not being logged-in as postgres user (or under camouflage = sudo).

1. make sure you are logged-in/ camouflaged as the postgresql user in your system environment

2. change the access policy in pg_hba.conf from ident to something other, i.e. trust or the like

//stefan

Collapse
6: Re: Postgres DB migration for OpenACS 5.4.1 (response to 4)
Posted by Dave Bauer on 04/18/08 03:27 PM
Use the -O or --no-owner option to pg_dump

--no-owner
Do not output commands to set ownership of objects to match the
original database. By default, pg_dump issues ALTER OWNER or
SET SESSION AUTHORIZATION statements to set ownership of created
database objects. These statements will fail when the script is
run unless it is started by a superuser (or the same user that
owns all of the objects in the script). To make a script that
can be restored by any user, but will give that user ownership
of all the objects, specify -O.

This option is only meaningful for the plain-text format. For
the archive formats, you may specify the option when you call
pg_restore.

Collapse
7: Re: Postgres DB migration for OpenACS 5.4.1 (response to 6)
Posted by Ratnakar Sagare on 04/24/08 11:37 AM
I could partially restore the data from dmp file using the '-O ' option.

Now I can login with old users' credentials & able to see all the subsites.

But I could not see the data in applications like 'Photo album' etc.

Are there any patches available for upgrading postgres from 7.3.3

Thanks
Ratnakar

Collapse
8: Re: Postgres DB migration for OpenACS 5.4.1 (response to 1)
Posted by Dave Bauer on 04/24/08 02:22 PM
Make sure the database is created with the same encoding.

Default for 7.3 would be SQL_ASCII and default for 8.x would be UTF-8 so make sure you created the new database to be imported into with the same encoding.

If that does not help, you'll need to capture the output of psql -f when you reload the database and check it for errors.

With the error information we can probably give you some more help.