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

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

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

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

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

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

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

Start Geoserver and Weave

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

Info

Gäller ikartan installerad i Ubuntu server

se även motsvarande dokumentation för Nacka https://viamap.atlassian.net/wiki/spaces/NACKA/pages/2991849473/Uppdateringar+av+InternGIS+plattform#Migrering-och-uppgardering-av-PostgreSQL

...

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.
Genom att använda Postgres Upgrade verktyget som mer automatiserat uppgraderar till en ny version

Uppgradering genom ny installation med backup

Global backup sparar hela instansens inställningar. Den är speciellt viktig för att få med användare (rollr) med dess behörighet och lösenord. Global backup bör göras som med kommando rad.

Code Block
sudo -u postgres pg_dumpall --globals-only --port 5432 > globals.sql

Info

Den här dokumentationen är skriven i samband med att lab2 servern skall uppdatera version av PostgreSQL så exempel och beskrivning är utformade för detta

För all SQL använd Dbeaver

Gör backup på befintliga data

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

Code Block

language	sql

--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
*/

Info
Listan med databaser syns bäst i Dbeaver navigations-träd

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)

Alternativ 2, backa upp med kommandon pg_dump och pg_dumpall

Code Block

language	bash

#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 nationaldata -h localhost -U postgres  -f /home/svsv/Downloads/nationaldatanationaldata.sql
pg_dump lookup -h localhost -U postgres  -f /home/svsv/Downloads/lookup.sql
pg_dump plan -h localhost -U postgres  -f /home/svsv/Downloads/plan.sql
pg_dump bal -h localhost -U postgres  -f /home/svsv/Downloads/bal.sql
# och så vidare med alla databaser

Eller alternativ 3 backa upp allt innehåll med pg_dump_all.

Code Block
#Backup med ett kommandeo pg_dumpall -h localhost -U postgres -f /home/svsv/Downloads/allt.sql

Kontrollera instanser av PostgesSQL

I katalogen /etc/postgresql visas alla nuvarande installationer. Under varje instans finns katalogen main och under den flera konfigurationsfiler

Code Block

language	bash

#/etc/postgresql
svsv@lab2:/etc/postgresql$ dir
12  14	15 	9.6
#/etc/postgresql/9.6
svsv@lab2:/etc/postgresql/9.6$ dir
main
#/etc/postgresql/9.6/main
svsv@lab2:/etc/postgresql/9.6/main$ dir
conf.d	     pg_ctl.conf  pg_ident.conf    start.conf
environment  pg_hba.conf  postgresql.conf

Filen postgresql.conf innehåller bl.a. konfiguration för anslutning och anges vilken port dom används. Varje instans av PostgreSQL måste ha en unik port för att vara unik. Default port är 5432 (Det som den aktuella versionen 9.6 har)

Code Block

language	bash

# Utdrag ur filen postgresql.conf
# - Connection Settings -
listen_addresses = '*'
#listen_addresses = 'localhost'		# what IP address(es) to listen on;
					# comma-separated list of addresses;
					# defaults to 'localhost'; use '*' for 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

					# (change requires restart)
port = 5432				# (change requires restart)
max_connections = 200			# (change requires restart)

Avinstallera eventuella gamla instanser

För att avinstallera en instans av PostgreSQL används kommandot apt-get --purge remove postgresql-12. I detta fall instans 12

Code Block
sudo apt-get --purge remove postgresql-12

Installera PostgreSQL och PosGIS

Sensate versionen av PostgreSQL är 16 och PostGIS är 3

Code Block

#installtioen
sudo apt update
#Installera PostgeSQL
sudo apt install postgresql postgresql-contrib
#Installera PostGIS version 3 på PostgreSQL 16
sudo apt install postgresql-16-postgis-3

#stoppa och starta postgres
sudo systemctl stop postgresql.service
sudo systemctl start postgresql.service

Kontrollera att databasen är igång

När det finns flera instanser av PostgreSQL så startas alla med sudo systemctl start postgresql.service. Det går bra att starta enbart en instans med sudo systemctl start postgresql@16-main. Nedan exempel visar status för en given instans

Code Block

#Kontrollera att den gamla instansen fungerar
sudo systemctl status postgresql@16-main
#kontrollera anslutning av databasen. Använd \q för att avsluta psql
sudo -u postgres psql --port 5432
#psql (16.1 (Ubuntu 16.1-1.pgdg22.04+1), server 9.6.24)
#Type "help" for help.

# postgres=#

Code Block

#Kontrollera att den nya instansen fungerar
sudo systemctl status postgresql@16-main
#kontrollera anslutning av databasen. 
# Inne i psgl sätt lösenordeb för postgres. Fråga adminitratör om passord
Använd \q för att avsluta psql
sudo -u postgres psql --port 5433
#psql (16.1 (Ubuntu 16.1-1.pgdg22.04+1), server 9.6.24)
#Type "help" for help.

# postgres=#alter role postgres with password '*******';

Redigera konfigurations-filerna pg_hba.conf och postgresql.conf. Båda filerna finns under /etc/postgresql/16/main respektive den gamla databasen /etc/postgresql/9.6/main. Visual code kan startas i sudo läge med.

Code Block
sudo code /etc/postgresql --user-data-dir="~/.vscode-root"

pg_hba.conf innehåller information om hur databasen för anslutas. I princip så ska den nya instansen se ut som den gamla. Nedan visar hur den ska se ut

Code Block

# Database administrative login by Unix domain socket
local   all             postgres                                peer
host    all             all             192.168.0.150/24        trust
host    all             all             192.168.0.18/24         trust

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

För postgresql.conf så ändra vi portnummer för båda gamla och ny instsansen. Vi sätter nya instansen till port = 5432 och den gamla till port=5433. Nedan visar ett utdrag ur filen

Code Block

language	bash

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'
#listen_addresses = 'localhost'		# what IP address(es) to listen on;
					# comma-separated list of addresses;
					# defaults to 'localhost'; use '*' for all
					# (change requires restart)
port = 5433				# (change requires restart)
max_connections = 200			# (change requires restart)
#superuser_reserved_connections = 3	# (change requires restart)

Starta om databaserna

Code Block
#stoppa och start postgres sudo systemctl stop postgresql.service sudo systemctl start postgresql.service

Skapa och återställ databaser med dess innehåll

Nu kan Dbeaver även används. Justera anslutningarna så det är åtkomst till den nya instansen.

Återställ globala den globala backupen

Det är enklast att kopiera av inställningarna från backupfilen och kör SQL script enligt nedan

Code Block

language	sql

SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
-- ROLES
CREATE ROLE gitea;
ALTER ROLE gitea WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE ikartanrw;
ALTER ROLE ikartanrw WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;

ALTER SCHEMA public OWNER TO postgres;
REVOKE USAGE ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO PUBLIC;
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

Återställ övriga databaser

Varje databas läggs tillbaka från sin backup som togs innan installationen eller en direkt från produktionsmiljön där nattliga backuper sparas i Lab2.

För varje ny databas som ska återställas så skapas den först:

Code Block

language	sql

-- create database bal; /* Görs bäst genom Dbeaver (högerklicka på databaser) */
-- \c bal; /* Görs bäst i Dbeaver (anslutning för script. Högst upp)
CREATE EXTENSION postgis; /* för de databaser som är spatiala */
CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; /* för de databaser som är använde UUId */

Om databasen inte finns som en daglig backup så laddas den med Dbeaver. Högerklicka på databsen (t.ex. bal) och välj tool > restore. Välj rätt backup-fil och kör

De databaser som har daglig backup så kan pg_restore användas från lab2 servern

Code Block

language	bash

pg_restore -h localhost -U postgres --port 5432 -W --dbname skff  /opt/backups/database/2023-12-29_01-00-32-skff.backup

Trimma databaser för prestanda

Code Block

language	bash

svsv@lab2:/var$ vacuumdb -a  -h localhost -U postgres --port 5432 -W 
Password: 
vacuumdb: vacuuming database "bal"
vacuumdb: vacuuming database "document"
vacuumdb: vacuuming database "geonetwork"
vacuumdb: vacuuming database "giteadb"
vacuumdb: vacuuming database "globalmap"
vacuumdb: vacuuming database "lookup"
vacuumdb: vacuuming database "nationaldata"
vacuumdb: vacuuming database "plan"
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "scb"
vacuumdb: vacuuming database "sfm"
vacuumdb: vacuuming database "sive"
vacuumdb: vacuuming database "skff"
vacuumdb: vacuuming database "template1"
vacuumdb: vacuuming database "weave"
svsv@lab2:/var$

Nu är det dags att testa klienterna Geoserver och Weave

Under testen upptäcks att att användren ikartarw inte har fått sitt ursprungliga lösenord. Detta beror på att den globala backupen inte tagit med lösenord. Åtgärden är :

Code Block
alter role ikartanrw with password '*******';

Version	Old Version 7	New Version Current
Changes made by	Sven-Ola Svensson	Sven-Ola Svensson
Saved on	2023-12-28	2024-01-02

Versions Compared

Key

Copy database from production to test

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

Uppgradering av PostgreSQL

Uppgradering genom ny installation med backup

Gör backup på befintliga data

Kontrollera instanser av PostgesSQL

Avinstallera eventuella gamla instanser

Installera PostgreSQL och PosGIS

Kontrollera att databasen är igång

Starta om databaserna

Skapa och återställ databaser med dess innehåll

Återställ globala den globala backupen

Återställ övriga databaser

Nu är det dags att testa klienterna Geoserver och Weave

Content Comparison

Versions Compared

Key

Copy database from production to test

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

Uppgradering av PostgreSQL

Uppgradering genom ny installation med backup

Gör backup på befintliga data

Kontrollera instanser av PostgesSQL

Avinstallera eventuella gamla instanser

Installera PostgreSQL och PosGIS

Kontrollera att databasen är igång

Starta om databaserna

Skapa och återställ databaser med dess innehåll

Återställ globala den globala backupen

Återställ övriga databaser

Nu är det dags att testa klienterna Geoserver och Weave