Copy database from production to test

This page describes how to update test.ikartan.se with data from ikartan.se, using Dbeaver installed in your local computer. You need to have administrators permission on test.ikartan.se , the Seco VPN client running on your local computer and a connection ready in Dbeaver

1. Get the latest backup from lab2.ikartab.se (target of the daily auto backup) to local computer

   1. Code Block
      language bash
      #replace <username> with your usernamne ssh -p 13738 <username>@lab2.ikartan.se cd /opt/backups/database ls exit #Now, you can select the backup to use #copy the file scp -P 13738 <username>@lab2.ikartan.se:/opt/backups/database/2023-10-10_01-00-05-sive.backup C:\opt\Ikartan

2. Start Dbeaver and read in the backup file into a database.

   1. Start Dbeaver

   2. Connect to test.ikartan.se

   3. Right click on “Databases” and select new database

   4. Enter a name e.g. siv_new (must be a name that not exist)

   5. Right click on the new database e.g. siv_new and select tools/restore

   6. Browse for the backup e.g. C:\opt\Ikartan\2023-10-10_01-00-05-sive.backup

   7. Press Start button and the restore begins

3. Log in to test.ikartan.se and stop Weave and Geoserver to release all connection to the present database we will replace

   1. Code Block
      #Seco clieent must be running ssh -i C:/Users/sven-/.ssh/id_ed25519 b30502@10.202.8.12 Enter passphrase for key 'C:/Users/sven-/.ssh/id_ed25519':<your locla password for the ssh file> b30502@viamap-ikartan-test02:~$ #stop weave service sudo systemctl stop weave.service #stop Geoserver sudo systemctl stop geoserver.service

4. Use Dbeaver to rename databases

   Code Block
   language sql
   -- Problem of database that is still connected! Try this-. SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity

   1. Right click on the present database e.g. sive and select rename

   2. Enter another name e.g. sive_23-10-01

   3. Click Persist when the SQL command is shown

   4. Now right click on the newly restored database e.g. siv_new and select rename

   5. Rename this database to the same name as as the previous database

5. Start Geoserver and Weave

   1. Code Block
      language bash
      sudo systemctl start geoserver.service sudo systemctl start weave.service

Create a connection with Dbeaver to test.ikartan.se using the Seco VPN client running

Uppgradering av PostgreSQL

...

1. Genom en ny installation där alla databaser från den gamla installationen backas upp och sen läses in i den nya. Passar bäst om uppgraderingen sker över många versioner.

2. Genom att använda Postgres Upgrade verktyget som mer automatiserat uppgraderar till en ny version

Uppgradering genom ny installation med backup

Gör backup på befintliga data

...