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
#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.
Start Dbeaver
Connect to test.ikartan.se
Right click on “Databases” and select new database
Enter a name e.g. siv_new (must be a name that not exist)
Right click on the new database e.g. siv_new and select tools/restore
Browse for the backup e.g. C:\opt\Ikartan\2023-10-10_01-00-05-sive.backup
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
#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
-- Problem of database that is still connected! Try this-. SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity
Right click on the present database e.g. sive and select rename
Enter another name e.g. sive_23-10-01
Click Persist when the SQL command is shown
Now right click on the newly restored database e.g. siv_new and select rename
Rename this database to the same name as as the previous database
Start Geoserver and Weave
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 https://viamap.atlassian.net/wiki/spaces/NACKA/pages/2991849473/Uppdateringar+av+InternGIS+plattform#Migrering-och-uppgardering-av-PostgreSQL
Det finns två vägar att uppgradera PostgeSQL
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
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
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)
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 ej aktuella 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 sudo apt install postgresql postgresql-contrib sudo apt install postgresql-16-postgis-3 #stoppa och start 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;