Copy production data to a new database server in azure staging postgres-dbteam-01.db.stg.gitlab.com
- This is change is being done for https://gitlab.com/gitlab-com/infrastructure/issues/4611
- Currently this is possible because azure staging has network access to azure production (note that this is not the case in GCP staging/production)
-
Create a new replication slot on postgres-03.prd.gitlab.com named dbteam_basebackup_2018_07_27
ssh postgres-03.db.prd.gitlab.com
sudo -u gitlab-psql gitlab-psql postgres -c "SELECT * FROM pg_create_physical_replication_slot('dbteam_basebackup_2018_07_27');"
sudo -u gitlab-psql gitlab-psql postgres -c 'SELECT * FROM pg_replication_slots;'
-
Ensure the /data
directory is empty onpostgres-dbteam-01.db.stg.gitlab.com
ssh postgres-dbteam-01.db.stg.gitlab.com
ls /var/opt/gitlab/postgresql/data/
-
Initiate the base backup
ssh postgres-dbteam-01.db.stg.gitlab.com
sudo -u gitlab-psql PGSSLMODE=disable /opt/gitlab/embedded/bin/pg_basebackup -D /var/opt/gitlab/postgresql/data -c fast --slot=dbteam_basebackup_2018_07_27 -X stream -P --host=postgres-03.db.prd.gitlab.com -p 5432 --username=gitlab_repmgr
-
Before bringing up the database, ensure that the following recovery.conf is in place:
ssh postgres-dbteam-01.db.stg.gitlab.com
# /var/opt/gitlab/postgresql/data/recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=postgres-03.prd.gitlab.com port=5432 user=gitlab_repmgr dbname=gitlab_repmgr sslcompression=0'
recovery_target_timeline = 'latest'
primary_slot_name = dbteam_basebackup_2018_07_27
trigger_file = '/var/opt/gitlab/postgresql/data/trigger_file'
-
Start postgresql
ssh postgres-dbteam-01.db.stg.gitlab.com
sudo gitlab-ctl start postgresql
sudo tail -f /var/log/gitlab/postgresql/current
-
Check replication is up to date:
ssh postgres-dbteam-01.db.stg.gitlab.com
sudo -u gitlab-psql gitlab-psql -c 'SELECT pg_last_xact_replay_timestamp();'
should be current
-
Promote the instance to primary:
ssh postgres-dbteam-01.db.stg.gitlab.com
touch /var/opt/gitlab/postgresql/data/trigger_file
-
Check instance became primary
ssh postgres-dbteam-01.db.stg.gitlab.com
sudo -u gitlab-psql gitlab-psql -c 'SELECT pg_is_in_recovery();'
-
Delete replication slot on postgres-03
ssh postgres-03.db.prd.gitlab.com
sudo -u gitlab-psql gitlab-psql postgres -c "SELECT pg_drop_replication_slot('dbteam_basebackup_2018_07_27');"
Edited by Nikolay Samokhvalov