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. #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. #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

   -- 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. 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

Det finns två vägar att uppgradera PostgeSQL

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

Anslut mot databasen med Dbeaver. Kontrollera versionen av Postgres samt vilka databaser den innehåller

--Visa versionen av Postgres

select version()
/* Resulatat
PostgreSQL 9.6.24 on x86_64-pc-linux-gnu (Ubuntu 9.6.24-4.pgdg20.04+1), 
compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
*/

-- Lista databser
SELECT datname FROM pg_database;
/* Resulatat
postgres
template1
template0
lookup
nationaldata
plan
globalmap
bal
sfm
scb
document
skff
geonetwork
sk
giteadb
sive_fel
sive
weave_old
weave
*/

Börja göra en Global backup genom att högerklicka databasen postgres och välja tools>Global Backup. Skapa gärna en ny mapp t.ex. lab2_postgres_full_backup eftersom det är många backupers som ska tas.

Fortsätt sedan att ta en backup tools>Backup för varje databas. Se till att kryssa i alla scheman i databasen. (Databaser som uppenbarligen är temporär eller en äldre kopia skippas)

#Alternativet är att göra backupen med kommandon.
#Global backup:
pg_dumpall -h localhost -U postgres -g -f /home/svsv/Downloads/global.sql
# databas backup
pg_dump bal -h localhost -U postgres  -f /home/svsv/Downloads/bal.sql

I katalogen /etc/postgresql visas alla nuvarande installationer

svsv@lab2:/etc/postgresql$ dir -all
total 40
drwxr-xr-x   8 postgres postgres  4096 Dec 19 13:10 .
drwxr-xr-x 182 root     root     12288 Dec 20 06:18 ..
drwxr-xr-x   3 postgres postgres  4096 Mar 15  2019 11
drwxr-xr-x   3 postgres postgres  4096 Apr  3  2020 12
drwxr-xr-x   3 postgres postgres  4096 Nov 22  2021 14
drwxr-xr-x   3 postgres postgres  4096 May 12  2023 15
drwxr-xr-x   3 postgres postgres  4096 Sep 28  2017 9.6