Document database migration steps for migrating old QA to servicenet-qa.benetech.org

[johnhbenetech]

As we continue to experience issues running the application on the new host, we would like to ensure we are taking the appropriate steps to migrate data between environments.

We have already attempted to increase memory/CPU for both the app containers as well as the RDS instance - but are still running into performance issues.

We would like to try a fresh database migration but need the following

• Is it enough to use the default postgres dump/restore commands - or would it be better to use something from the liquibase CLI

• In either case can you provide the exact commands for both the dump and restore - including all parameters such as: export schema? export owner? format of export? etc.

• Provide any information about which database user needs to run the command (if elevation is required for example)

[johnhbenetech]

@rkeiii will be working on this from the benetech side

[johnhbenetech]

As we are trying to do this on an RDS instance - it would be best if you could test your process on an RDS to ensure it will work as intended (as there may be RDS specific requirements for privilages that arent applicable in a local env)

we previously tried using these commands:

pg_dump --host RDSHOSTNAME --no-owner -U USERNAME -W -F custom ServiceNet > ServiceNet_custom.dump

pg_restore --username=USERNAME --host RDSHOSTNAME --no-owner -p 5432 --role=servicenet_staging -d servicenet_staging ServiceNet_custom.dump

the above required superuser privilages on RDS due to the need to install postgres extensions (i believe)

After the above, the data was populated (with a few errors) but there were performance issues that we worry are related to improper migration relating to views,indicies,etc

[kkaczmarczyk]

@jkondrat Feel free to take it on once finished with Service Discovery

[jkondrat]

@johnhbenetech @kkaczmarczyk I was able to completely copy the ServiceNet database with the following commands:

pg_dump --host RDSHOSTNAME --port 5432 --username "USERNAME" --format custom --blobs --verbose --file "ServiceNet.backup" "ServiceNet"

createdb --host RDSHOSTNAME --username "USERNAME" -W "ServiceNetCopy"

pg_restore --host RDSHOSTNAME --port 5432 --username "USERNAME" --dbname "ServiceNetCopy" --verbose "ServiceNet.backup"

[rkeiii]

Hi @johnhbenetech and @jkondrat,

I tried that dump command using my account and it failed. Here's the output:

rkeiii@ate:~$ pg_dump --host REDACTED --port 5432 --username REDACTED --format custom --blobs --verbose --file "ServiceNet.backup" ServiceNet
Password: 
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: [archiver (db)] query failed: ERROR:  permission denied for relation databasechangeloglock
pg_dump: [archiver (db)] query was: LOCK TABLE public.databasechangeloglock IN ACCESS SHARE MODE
rkeiii@ate:~$ 

Am I doing something wrong?

[jkondrat]

@rkeiii It looks that your account is missing SELECT permissions for the table databasechangeloglock

[johnhbenetech]

@jkondrat which account did you run your test under? Did you have to add that permission manually? Are there any other permissions you needed to successfully run the test?

[jkondrat]

@johnhbenetech I ran the commands under the account "johnhbenetech" on staging and didn't have to add any permissions manually

[kkaczmarczyk]

@johnhbenetech are there any further actions needed in that ticket or could we close it?

[johnhbenetech]

@kkaczmarczyk can you move the relevant details to a wiki page and then we can close

[kkaczmarczyk]

@johnhbenetech Sure. Wiki here or the data repo?

[johnhbenetech]

Here is fine

[kkaczmarczyk]

I didn't link the Wiki page here for some reason. It was created long time ago: https://github.com/benetech/ServiceNet/wiki/DB-migration-steps