Partial restore using PgAdmin?

Does anyone have experience with partial/incremental restore of postgresql dbs?

3. I’m having some issues with PGadmin’s restore utility. It doesn’t seem
to want to restore to a database which is currently active or already has
data in it. I’ve found the workaround is to create a new db with a different
name, restore to that, then take the other one off-line, rename it … old and
rename the new one. That’s odd. MySQL utilities don’t seem to have a
problem with that. This is important so that I can refresh my local copy of
the database to work off-line. Do you have any other suggestions?

Knut

Very likely could be foreign key references.

I think MySQL may drop the foreign key references and then recreate
them, but postgres does not.

I am not sure this is exactly the same as a partial restore as they
mention that they drop the db and then recreate it, which should not
be possible with a partial restore.

Obviously seeing the logs from postgres would be useful.

···

On Tue, Mar 22, 2011 at 9:09 AM, Knut Staring <knutst@gmail.com> wrote:

Does anyone have experience with partial/incremental restore of postgresql dbs?

3. I’m having some issues with PGadmin’s restore utility. It doesn’t seem
to want to restore to a database which is currently active or already has
data in it. I’ve found the workaround is to create a new db with a different
name, restore to that, then take the other one off-line, rename it … old and
rename the new one. That’s odd. MySQL utilities don’t seem to have a
problem with that. This is important so that I can refresh my local copy of
the database to work off-line. Do you have any other suggestions?

Knut

_______________________________________________
Mailing list: DHIS 2 developers in Launchpad
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : DHIS 2 developers in Launchpad
More help : ListHelp - Launchpad Help

--
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+260974901293

Folks --
     I wanted to feed back some experiences from the Ghana
implementation work which has gone on for the last 3 weeks here and also
during Denis Adeletey's visit to Oslo.
     * If you click Save on just about any form when your session has
timed out, it appears as if the Save has succeeded and the timeout
relogin does not occur until the next menu item is selected. The user
will already be annoyed at losing work, but to discover it some time
later than when the added element turns up missing will be doubly
frustrating.
     * The form editor still has an inconsistent, inconvenient
interface with unpredictable behaviors. I know we are using it less
often due to Word/Excel import, but it is virtually impossible to make
even a minor change to an imported form using the form editor.
     * The floating window (data entry and form editor) is still
awkward and too frequently in the way. Can we go to a taskpane
approach?
     * There ought to be "Can change password?" and "Password forced
change frequency" attributes to the user table and associated
functionality. The user should not need edit authority on the user
object to change their own password, but the ability to do so should be
controllable in the case of intentionally shared passwords.
     * There ought to be some group-based assignments of datasets to
org units and roles. This is currently a tedious and error prone
process.
     * There ought to be better group editing capabilities. It ought
to be possible to start with an existing groupset, clone it, then merge
groups, edit group names, and transfer assigned objects (indicators,
data elements, org units) from one group value to another.
     * The category combo options mechanism has got to be more amenable
to change. Right now, you have to deassign the category combo from all
data elements (and indicators and validation rules) that use it, delete
the combo, delete and recreate the category, recreate the combo,
reassign the data element. In the meantime, the data has become
dissociated from its categories. I know that there are a lot of
difficulties with changing options, especially in the distributed
context, but at least it ought to be possible to add a new category
option to a category involved in a category combo without destroying
anything.
     * There need to be some extra text, numeric and object fields in
the org unit table to which the implementer can assign field names and
required status, and which show up on the org unit maintenance form.
One org code is never enough to match up with other systems, there is
always a town or postal code or some other address/contact piece to be
added. You sinned against the users by simply discarding fields from
the org unit table when you changed its structure.
     * There needs to be access to the organization table for
calculating indicators, for example, # of facilities of type x per 1000
population. I know this doesn't deal adequately with the time
dimension, but the whole system doesn't deal adequately with data
elements that change slowly or infrequently. Perhaps the way to deal
with this is to automatically create a data element with daily frequency
that tracks the current value of an org unit attribute and changes in a
stepwise way.
     * The user interface should be amenable to more customization,
even on a per user basis. The DHIS2 logo and flag ought to be locations
fillable from a set of povided and uploaded images. There ought to be a
place for a "daily" message.
     * There needs to be more granularity of administrative privileges
and they ought to be assignable on an org unit basis. Right now, we
have to do too much administration at the national and regional level
because the authorities are too powerful and unscoped.
     * I have a lot of reservations about the impending demise of
calculated variables. I understand the impulse to separate inputs from
outputs. But without calculated variables, the utility of the dataset
report will be lost; the user will need to understand two different
technologies to create the same output. Also, sometimes the data
collected on the form is more granular than that which is to be put into
the data cube, but not yet so numerous or repetitive as to require a
line listing. In the case of Ghana, we have a form where the current
use and inventory of each vaccine is recorded by lot number and
doses/vial. For each vaccine, we have a maximum of 4 lot
number-doses/vial combinations. The number we want to save is the
current use and inventory of doses of each vaccine, a simple calculation
but soon not to be available to us.
     I hope this raises some discussion and that some of it gets on the
roadmap.
Cheers, Roger

Hi Roger, thanks for your feedback.

Folks –

 I wanted to feed back some experiences from the Ghana

implementation work which has gone on for the last 3 weeks here and also

during Denis Adeletey’s visit to Oslo.

 *  If you click Save on just about any form when your session has

timed out, it appears as if the Save has succeeded and the timeout

relogin does not occur until the next menu item is selected. The user

will already be annoyed at losing work, but to discover it some time

later than when the added element turns up missing will be doubly

frustrating.

I tested this again now and cannot reproduce, here it is logging out right away.

  • The form editor still has an inconsistent, inconvenient

interface with unpredictable behaviors. I know we are using it less

often due to Word/Excel import, but it is virtually impossible to make

even a minor change to an imported form using the form editor.

Could this be due to the non-standard and verbose html produced by Word and Excel? If so its not really much we can do. We have had reasonably good feedback on the editor when its used to create forms “from scratch”.

  • The floating window (data entry and form editor) is still

awkward and too frequently in the way. Can we go to a taskpane

approach?

Yes agree its getting a bit in the way and its not really useful since users mostly have the paper forms in front of them and don’t need to know the period/orgunit at all times. But this is required by Indian users… I have now put in a close button which means that the users can discard it.

  • There ought to be “Can change password?” and "Password forced

change frequency" attributes to the user table and associated

functionality. The user should not need edit authority on the user

object to change their own password, but the ability to do so should be

controllable in the case of intentionally shared passwords.

I am not sure if I agree… The approach we are pushing is having one user account per user. We are soon introducing new functionalities such as messaging, feedback, alerts etc where it is useful to be able to identify and address users individually. I could be good if you can share your reasons for having shared accounts.

Forcing password changes should be considered but will entail a lot of trouble in offline settings. Could maybe be combined with a automatic password restore function for online deployments.

  • There ought to be some group-based assignments of datasets to

org units and roles. This is currently a tedious and error prone

process.

Yes, we are introducing a new dataset editor in the upcoming 2.2 release where you can manage associations for all datasets and orgunits with sharing the same “parent”. Please have a look and see if it suits your needs. (Data set - Data set assignment editor).

  • There ought to be better group editing capabilities. It ought

to be possible to start with an existing groupset, clone it, then merge

groups, edit group names, and transfer assigned objects (indicators,

data elements, org units) from one group value to another.

OK can you please elaborate why you want to clone group sets? Have you had a look at the Data element group editor / Indicator group editor functions in Data dictionary module?

  • The category combo options mechanism has got to be more amenable

to change. Right now, you have to deassign the category combo from all

data elements (and indicators and validation rules) that use it, delete

the combo, delete and recreate the category, recreate the combo,

reassign the data element. In the meantime, the data has become

dissociated from its categories. I know that there are a lot of

difficulties with changing options, especially in the distributed

context, but at least it ought to be possible to add a new category

option to a category involved in a category combo without destroying

anything.

Yes agreed. We will put this on the road map for version 2.3.

https://blueprints.launchpad.net/dhis2/+spec/update-category

  • There need to be some extra text, numeric and object fields in

the org unit table to which the implementer can assign field names and

required status, and which show up on the org unit maintenance form.

One org code is never enough to match up with other systems, there is

always a town or postal code or some other address/contact piece to be

added. You sinned against the users by simply discarding fields from

the org unit table when you changed its structure.

OK. Exactly what fields would you like to be added? Of related fields we currently have code, coordinates, URL, contact person, address, email, phone number.

  • There needs to be access to the organization table for

calculating indicators, for example, # of facilities of type x per 1000

population. I know this doesn’t deal adequately with the time

dimension, but the whole system doesn’t deal adequately with data

elements that change slowly or infrequently. Perhaps the way to deal

with this is to automatically create a data element with daily frequency

that tracks the current value of an org unit attribute and changes in a

stepwise way.

Yes agreed. We have been discussing this for a while and I think we will improve the indicator engine to handle this. Ie. you will be able to add [number of orgunits below this orgunit] directly in the indicator formula. We are also planning to add [number of days in aggregation period] in the same way to deal with things such as bed occupancy rates.

https://blueprints.launchpad.net/dhis2/+spec/extended-indicator-engine

Have you had a look at the Organisation unit distribution report (reporting - organisation unit report) ? This will give you aggregated reports with counts of orgunits/facilities filtered on groupsets/groups and “parent” organisation unit in the hierarchy.

  • The user interface should be amenable to more customization,

even on a per user basis. The DHIS2 logo and flag ought to be locations

fillable from a set of povided and uploaded images.

OK. Are you aware that the user can pick from a set of 5 predefined “skins” / styles (settings - user settings) ? The flag can also be selected from a list of countries in system settings. If you have some sort of “official” flags you can send them to us and we will include them.

There ought to be a

place for a “daily” message.

We are introducing messaging functionality in the 2.2 release where privileged users can send messages to any other user. This messages will visible and accessible in the dashboard.

  • There needs to be more granularity of administrative privileges

and they ought to be assignable on an org unit basis. Right now, we

have to do too much administration at the national and regional level

because the authorities are too powerful and unscoped.

This was our experience in Kenya also recently. We have now made it possible for ie. district users to manage assignments of datasets for their own facilities (in edit organisation unit) and to add new facilities below their district. The current privileges management is quite fine-grained (some say too fine-grained) and almost all actions in the system are assignable to user roles. Can you please provide examples of what privileges would you like to be assignable?

  • I have a lot of reservations about the impending demise of

calculated variables. I understand the impulse to separate inputs from

outputs. But without calculated variables, the utility of the dataset

report will be lost; the user will need to understand two different

technologies to create the same output. Also, sometimes the data

collected on the form is more granular than that which is to be put into

the data cube, but not yet so numerous or repetitive as to require a

line listing. In the case of Ghana, we have a form where the current

use and inventory of each vaccine is recorded by lot number and

doses/vial. For each vaccine, we have a maximum of 4 lot

number-doses/vial combinations. The number we want to save is the

current use and inventory of doses of each vaccine, a simple calculation

but soon not to be available to us.

The decision to remove calculated data elements does not really imply that we will not support calculated values. Its more a technical matter in that the current implementation of calculated data element is buggy and the purpose of the object is duplicating that of the indicator object. We are well aware of the need for basic logistics support in the system. The plan is to make it possible to include indicators directly in custom forms (and hence in data set report) to cater for such calculations that you mention above.

Also please note that when using section based forms the dataset report now gives you the totals and totals for each category option in each category combination. We should maybe make those reports available also for data sets which have custom forms.

I hope this raises some discussion and that some of it gets on the

roadmap.

Yes, thanks again for your feedback and we are looking forward to hearing more from the Ghana process.

best regards, Lars

···

On Sat, Apr 9, 2011 at 7:47 PM, Friedman, Roger (CDC/CGH/DGHA) (CTR) rdf4@cdc.gov wrote:

Folks --
    I exported metadata and tried to import it into a clean database.
It imported the first 38 data elements of 750, nothing else; the error
occurred where the data element name included >=. The log showed a SQL
error which led me to replace all instance of >= in the file with
&gt;&#61; (all the <s had been replaced with &lt;). It then hung while
importing category options. I believe it could be a similar issue with
< or >. Unfortunately, I am not near that machine now and can't send
the log.
    Also, on the metadata export screen, there was a reference to the
file ajax-loading-small.gif (or something close, again I'm away from the
computer involved) which could not be found.
Thanks, Roger

Hi Roger

Can you send me a copy of the metadata export when you get back to a computer.

Regards
Bob

···

On 14 April 2011 18:02, Friedman, Roger (CDC/CGH/DGHA) (CTR) <rdf4@cdc.gov> wrote:

Folks --
I exported metadata and tried to import it into a clean database.
It imported the first 38 data elements of 750, nothing else; the error
occurred where the data element name included >=. The log showed a SQL
error which led me to replace all instance of >= in the file with
&gt;&#61; (all the <s had been replaced with &lt;). It then hung while
importing category options. I believe it could be a similar issue with
< or >. Unfortunately, I am not near that machine now and can't send
the log.
Also, on the metadata export screen, there was a reference to the
file ajax-loading-small.gif (or something close, again I'm away from the
computer involved) which could not be found.
Thanks, Roger

_______________________________________________
Mailing list: DHIS 2 developers in Launchpad
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : DHIS 2 developers in Launchpad
More help : ListHelp - Launchpad Help

Here's the export file
The file is ajax-loading-bar-small.gif

Export_meta_20110414.zip (345 KB)

···

-----Original Message-----
From: Friedman, Roger (CDC/CGH/DGHA) (CTR)
Sent: Thursday, April 14, 2011 1:02 PM
To: DHIS 2 developers
Subject: Import/Export Issue

Folks --
    I exported metadata and tried to import it into a clean database.
It imported the first 38 data elements of 750, nothing else; the error
occurred where the data element name included >=. The log showed a SQL
error which led me to replace all instance of >= in the file with
&gt;&#61; (all the <s had been replaced with &lt;). It then hung while
importing category options. I believe it could be a similar issue with
< or >. Unfortunately, I am not near that machine now and can't send
the log.
    Also, on the metadata export screen, there was a reference to the
file ajax-loading-small.gif (or something close, again I'm away from the
computer involved) which could not be found.
Thanks, Roger