0

We are facing well-known pg_dumps effeciency problems in terms of velocity. We currently have a Azure hosted PostgreSQL, which holds our resources that are being created/updated by SmileCDR. Somehow after three months it is getting larger due to saving FHIR objects. Now, we want to have brand new environment; in that case persistent data in PostgreSQL has to be ripped out and new database has to be initiated with old data set.

Please be advised.

  1. pg_dump consumes relative much more time, almost a day. How can we speed up backup-restore process?
  2. What kind of alternatives that we could use and apply whereas pg_dump to achieve the goal?

Important notes;

  • Flyway utilized by SmileCDR to make versioning in PostgreSQL.
  • Everything has to be copied from old one to new one.
  • PostgreSQL version is 11, 2vCores, 100GB storage.
  • FHIR objects are being kept in PostgreSQL.
  • Some kind of suggestions like multiple jobs, without compress, directory format have been practiced but it didn't affect significantly.
nurisezgin
  • 1,530
  • 12
  • 19

2 Answers2

0

Since you put yourself in the cage of database hosting, you have no alternative to pg_dump. They make it deliberately hard for you to get your data out.

The best you can do is a directory format dump with many processes; that will read data in parallel wherever possible:

pg_dump -F d -j 6 -f backupdir -h ... -p ... -U ... dbname

In this example, I specified 6 processes to run in parallel. This will speed up processing unless you have only one large table and all the others are quite small.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Hi Laurenz, thanks for your answer. We didn't get significant improvement in terms of creating backup with that approach. We are looking for Azure provided utilities in backupping. Thanks – nurisezgin Mar 04 '21 at 08:34
  • Sure. Note, however, that asking for recommendations for utilities is off-topic. – Laurenz Albe Mar 04 '21 at 09:35
0

Alternatively, you may use smileutil with the synchronize-fhir-servers command, bulk export API on the system level, subscription mechanism. Just a warning that these options may be too slow to migrate the 100Gb database. As you mentioned, if you can replicate the Azure VM that may be the fastest.

Shamil
  • 910
  • 4
  • 17