How to replicate postgres database for staging environement

[Merve Nur Bas]

Hi guys, 

so obviously there are a lot of documentation and community posts about Jira staging environments and also PostgreSQL databases. 

However I find it really confusing, as every documentation says something different about how to replicate the database on a different windows server. 

Now I do know that I have to use the pg_dump + psql options. However I do not understand how permissions and privileges are influenced by that. What do I need to keep in mind when replicating the database by using pg_dump and psql so I do not get any errors regarding duplicate keys or roles (or other errors)?

I really hope you guys can help me here. 

My best

Merve

[Ismael Jimoh · Accepted solution]

Hi @Merve Nur Bas 

When you duplicate your database, you should have a totally separate database in this case hence permission on this database must be manually added by you. 
Here is how I will do it.

1. Take the database dump.
2. Setup a new empty database (different name from your production one)
3. Install and connect Jira to the new database to ensure that you configuration all works.
4. Make sure you have a new home directory for this Jira of it is on the same server.
5. Stop Jira 
6. Drop the database and recreate an empty database with the same name.
7. Restore the database dump
8. Disable all email and notifications from the start up parameters because you do not want this instance pulling emails meant for the production 
9. Start Jira.

These steps should get your Jira working just fine.

Remember if on the same server, point Jira to a different database and home directory

Hope this helps

[Merve Nur Bas]

Hi @Ismael Jimoh

many thanks for the quick response. The new database will be on a new server (along with the new Jira environment). I will follow your instructions and see if I will be able to manage it without errors. Thanks for that. 

Two more questions if that is okay! I guess these are trivial things but as a newbie in Postgres I want to make sure. 

• As I understand the user who dumps and restores the database must be the same right? 
• Which syntax for dump and restore do you prefer as there are a lot of different opinions using options (like --no owner etc.)

Does these questions make sense? Hope they do :)

Thanks again. Have a nice weekend. 

My best. 

Merve

[Ismael Jimoh]

Hi @Merve Nur Bas 

I normally use the following command for dump and restore but mostly on linux.

pg_dump -U <user_name> <database_name> > postgres-<product_name>-$(date +"%Y%m%d").dump

I do not think the username is important because you just need a user who has access to the database you are restoring to but in most cases where you have the different users, I used the same username but a different password. (Also, this depends on what permission the user has on the database you are restoring to)

The restore is as follows:

psql -U <user_name> <database_name> < <generated_backup>.dump

I hope this helps.

[Merve Nur Bas]

Thanks a lot, @Ismael Jimoh

Have a great day!

My best