/
Databasadministration

Databasadministration

Owned by Sven-Ola Svensson
Last updated: 2024-01-02
7 min read

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

Gäller ikartan installerad i Ubuntu server

se även motsvarande dokumentation för Nacka Uppdateringar av InternGIS plattform | Migrering och uppgardering 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

 

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.

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

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

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

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

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

#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

#/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)

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

sudo apt-get --purge remove postgresql-12

Installera PostgreSQL och PosGIS

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

#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

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

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

# 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

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

#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

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:

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

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

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 :

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

 

Related content