nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Accepted answers

Ismael Jimoh

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.

Take the database dump.
Setup a new empty database (different name from your production one)
Install and connect Jira to the new database to ensure that you configuration all works.
Make sure you have a new home directory for this Jira of it is on the same server.
Stop Jira
Drop the database and recreate an empty database with the same name.
Restore the database dump
Disable all email and notifications from the start up parameters because you do not want this instance pulling emails meant for the production
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

All comments

Ismael Jimoh

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.

Take the database dump.
Setup a new empty database (different name from your production one)
Install and connect Jira to the new database to ensure that you configuration all works.
Make sure you have a new home directory for this Jira of it is on the same server.
Stop Jira
Drop the database and recreate an empty database with the same name.
Restore the database dump
Disable all email and notifications from the start up parameters because you do not want this instance pulling emails meant for the production
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.

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

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:

.dumppsql -U <user_name> <database_name> < <generated_backup>

I hope this helps.

Merve Nur Bas

Thanks a lot, @Ismael Jimoh

Have a great day!

My best

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

jira-software

jira-software-server

database

postgres