Hi
I was just restoring the sample database from http://www.dhis2.org/download/resources/dhis2-demo.zip to a dhis2 instance for testing and I realize that this sql dump has ‘ALTER XXX SET OWNER TO dhis’ all over the place.
I don’t know where the script is to generate the sample database, but I presume it is doing something like ‘pg_dump --format=p dbname > default.sql’. If so, can we add a -O switch to pg_dump to prevent it creating all those SET OWNER commands? It can be awkward when your database doesn’t have a ‘dhis’ user.
Meanwhile, for anybody else who comes up against this, here is a simple workaround (to restore into a database called sl owned by user sl):
unzip -c dhis2-demo.zip | sed ‘s/OWNER TO dhis/OWNER TO sl/’ | psql sl
Note this ownership lark is only a problem with the plain text output format of pg_dump/pg_restore. The binary format allows you to determine the user at restore time. I still think the portability of plain text is worth the extra effort but lets remove the hard coded owner.
Bob
PS A related postgresql ownership gotcha: I typically work with the database as my bobj user. If I am creating objects eg. restoring from sql then these end up being owned by the bobj by default. A very useful postgres sql command is REASSIGN. Every now and then I find myself doing:
REASSIGN OWNED BY bobj to sl;