Backup Data Base without data

Support - Assistance technique
1

Hello.

We have a production server in 2.36.13.2 version. We want to migrate the server to 2.40.7.1 version but for privacy reasons we cannot acces the server directly and we have to tell another team how to perform the migration.
We are trying to build up a test environment so we can easily guide the other team in the process.
First step we are trying to acomplish is to replicate the production system in our testing environment and we need the other team to perform a backup in the production environment without user data, just internal tables and configuration tables DHIS needs to work.
Is this posible?
First try has been to use this sentence:
sudo -u dhis pg_dump -s dhis2 > backup_file.sql

But this does not produce a good file for restoring. It empties all the data, also the internal tables needed for restoring. For example it empties the flyway_schema_history table where the updates made to the database are stored.

How can I perform a dump of the database excluding the tables with the data entered by users?

Thank you.

3 Likes
2

Hi @JaimeLopez !

I think I have a suggestion for you. :smiley: I understand that you want a “replica” of the production environment so you can test the migration and help them safely migrate the production system.

My recommendation for your case is to ask them to create an exact clone of the production instance and then use the import/export app to delete the data that’s not needed using Export > Delete through Import. After that the clone will have everything except the private data which then you can ask them to create a complete dump of the database.

Would this suggestion work? I hope it helps.

Thanks!

3

Not tried this before, but you can do a partial dump including only metadata tables after an initial schema only dump, using pg_dump with --table for specific DHIS2 metadata tables. something like;

sudo -u dhis pg_dump --data-only --table=dataelement \ --table=dataset \ --table=indicator \ --table=organisationunit \ --table=categoryoptioncombo \ --table=categorycombo \ --table=program \ --table=trackedentitytype \ --table=validationrule \ --table=userinfo \ --table=userrole \ dhis2 -f dhis2_metadata.sql dhis2
However, you have to know the DHIS2 version-specific table structures in order to identify the which tables to specify

1 Like
4

Hello @Gassim and @paleu256.

Thank you for your answers.
We manage to backup the database with no data.
Our first atempt was to do as Al-Gassim says: exporting and importing metadata. This had one main problem. As the server is in an old version (2.36.13.2) some of the imports didn´t go right (like the options when they are not ordered by default order).
Then, as I said in my first message, we asked them to backup the database empty. But this emptied both the user tables and the configuration tables. Thus we could not upgrade the server database.
Finally we found a solution similar to that explained by Philip but excluding certain tables instead of including them (we had some advice here from a DHIS2 expert). This is the statement that did the job:

sudo pg_dump -Fp --no-privileges --exclude-table=_ --exclude-table=aggregated* --exclude-table=analytics* --exclude-table=completeness* --exclude-table=geography_columns --exclude-table=geometry_columns --exclude-table=raster_columns --exclude-table=raster_overviews --exclude-table=spatial_ref_sys --exclude-table-data=datavalue* --exclude-table-data=trackedentitydatavalue* --exclude-table-data=trackedentityattributevalue* --exclude-table-data=programinstance --exclude-table-data=programstageinstance* --dbname=dhis2 -U dhis -f BACKUP_NODATA_FILE.sql*

Hope this helps others.

Best regards.

1 Like
5

Hi @JaimeLopez

Great new! Thanks for the update and sharing the detailed solution.

I thought that JSON export and import are the same. If you used CSV export, I agree it would’ve probably done as you said. What I meant was exporting the JSON using import/export app and then importing the JSON with the delete option to delete the data in the instance.

1467Ă—1162 117 KB

This didn’t work in that version? (This one was one of that that never failed for me so I’m surprised. :grinning_face_with_smiling_eyes: )

6

Hello @Gassim

The server is at 2.36.13.2 version and I found two issues in the importing of the metadata already reported:

  • When the option sets are manually reordered, the order is altered and it no longer begins with 1. The import porcess generates an error with the order sets that have been manually reordered: Metadata Import - ERROR - java.lang.NullPointerException - #4 by brian. As an example these are the options of one of the option sets:
  •     {
      "code": "1",
      "created": "2022-04-13T13:39:19.718",
      "lastUpdated": "2022-12-27T13:22:23.144",
      "name": "1",
      "id": "a9Mn0My90jS",
      "sortOrder": 6,
      "optionSet": {
          "id": "s7FL8wXiZmN"
      },
      "attributeValues": [
      ],
      "translations": [
      ]
  },
  {
      "code": "2",
      "created": "2022-04-13T13:39:23.307",
      "lastUpdated": "2022-12-27T13:22:23.144",
      "name": "2",
      "id": "HyOcwwNZBrJ",
      "sortOrder": 7,
      "optionSet": {
          "id": "s7FL8wXiZmN"
      },
      "attributeValues": [
      ],
      "translations": [
      ]
  },
  {
      "code": "3",
      "created": "2022-04-13T13:39:26.829",
      "lastUpdated": "2022-12-27T13:22:23.145",
      "name": "3",
      "id": "mfHUK7uksJl",
      "sortOrder": 8,
      "optionSet": {
          "id": "s7FL8wXiZmN"
      },
      "attributeValues": [
      ],
      "translations": [
      ]
  },
  {
      "code": "4",
      "created": "2022-04-13T13:39:30.430",
      "lastUpdated": "2022-12-27T13:22:23.145",
      "name": "4",
      "id": "G38ayzGIAAb",
      "sortOrder": 9,
      "optionSet": {
          "id": "s7FL8wXiZmN"
      },
      "attributeValues": [
      ],
      "translations": [
      ]
  },
  {
      "code": "5",
      "created": "2022-04-27T09:37:58.956",
      "lastUpdated": "2022-12-27T13:22:23.145",
      "name": "5",
      "id": "uL0KQsUEeRJ",
      "sortOrder": 10,
      "optionSet": {
          "id": "s7FL8wXiZmN"
      },
      "attributeValues": [
      ],
      "translations": [
      ]
  }
  • When importing the programs i found this unresolved error regarding the notifications. The message was “The program nitifications template cannot be found”: Import program with notifing template and stage with customized form

I found a walkthrough for the first one editing the json file, but it was quite a hard work. In the meantime I asked the other team for the backup without data and finally that was the best solution.

Thank you

1 Like
7

Amazing! Great work … and thanks for the links and explanation.