dhis2-devs team mailing list archive

[Bob Jolliffe <bobjolliffe@xxxxxxxxx>]

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;