37. PostgreSQL-scheman

Produktionsdatabaser har oundvikligen ett stort antal tabeller och vyer, och att hantera dem alla i ett schema kan snabbt bli obehagligt. Lyckligtvis innehåller PostgreSQL konceptet ”_Schema”.

Scheman är som mappar och kan innehålla tabeller, vyer, funktioner, sekvenser och andra relationer. Varje databas börjar med ett schema, det ”offentliga” schemat.

_images/schemas.jpg

I det schemat skapar standardinstallationen av PostGIS metadatarelationerna geometry_columns, geography_columns och spatial_ref_sys, samt alla typer och funktioner som används av PostGIS. Så användare av PostGIS behöver alltid tillgång till det offentliga schemat.

I det publika schemat kan du också se alla de tabeller som vi har skapat hittills i workshopen.

37.1. Varför använda Schemas?

Det finns två mycket goda skäl till att använda scheman:

  • Data som hanteras i ett schema är lättare att använda bulkåtgärder på.

    • Det är lättare att säkerhetskopiera data som finns i ett separat schema: så flyktiga data kan ha ett annat säkerhetskopieringsschema än icke-flyktiga data.

    • Det är lättare att återställa data som finns i ett separat schema: så applikationsorienterade scheman kan återställas separat och säkerhetskopieras för tidsresor och återställning.

    • Det är lättare att hantera programskillnader när programdata finns i ett schema: så en ny version av programvaran kan arbeta med en tabellstruktur i ett nytt schema, och övergången innebär en enkel ändring av schemanamnet.

  • Användare kan begränsas i sitt arbete till enstaka scheman för att möjliggöra isolering av analys- och testtabeller från produktionstabeller.

Så för produktionsändamål förbättras hanteringen om du håller dina applikationsdata åtskilda i scheman; och för användarändamål förhindrar du att dina användare trampar på varandra genom att hålla dem i separata scheman.

37.2. Skapa ett dataschema

Låt oss skapa ett nytt schema och flytta en tabell till det. Skapa först ett nytt schema i databasen:

CREATE SCHEMA census;

Därefter flyttar vi tabellen nyc_census_blocks till schemat census:

ALTER TABLE nyc_census_blocks SET SCHEMA census;

Om du använder kommandoradsprogrammet psql kommer du att märka att nyc_census_blocks har försvunnit från din tabellista nu! Om du använder PgAdmin kan du behöva uppdatera din vy för att se det nya schemat och tabellen i det.

Du kan komma åt tabeller i scheman på två sätt:

  • genom att referera till dem med hjälp av schema.table-notation

  • genom att lägga till schemat i din search_path

Explicit hänvisning är enkelt, men det blir tröttsamt att skriva efter ett tag:

SELECT * FROM census.nyc_census_blocks LIMIT 1;

Manipulering av search_path är ett bra sätt att ge åtkomst till tabeller i flera scheman utan en massa extra typning.

Du kan ställa in search_path vid körning med kommandot SET:

SET search_path = census, public;

Detta säkerställer att alla referenser till relationer och funktioner söks i både census och public schemat. Kom ihåg att alla PostGIS-funktioner och -typer finns i public så vi vill inte ta bort dem från sökvägen.

Att ställa in sökvägen varje gång du ansluter kan också bli tröttsamt, men lyckligtvis är det möjligt att permanent ställa in sökvägen för en användare:

ALTER USER postgres SET search_path = census, public;

Nu kommer postgres-användaren alltid att ha schemat census i sin sökväg.

37.3. Skapa ett användarschema

Användare gillar att skapa tabeller, och PostGIS-användare gör det särskilt: analysoperationer med SQL kräver temporära tabeller för visualisering eller tillfälliga resultat, så spatial SQL tenderar att kräva att användare har CREATE-rättigheter mer än vanliga databasarbetsbelastningar.

Som standard får varje roll i Oracle ett personligt schema. Detta är en trevlig praxis att använda för PostgreSQL-användare också, och det är lätt att replikera med PostgreSQL-roller, scheman och sökvägar.

Skapa en ny användare med behörighet att skapa tabeller (se PostgreSQL säkerhet för information om rollen postgis_writer) och skapa sedan ett schema med den användaren som behörig:

CREATE USER myuser WITH ROLE postgis_writer;
CREATE SCHEMA myuser AUTHORIZATION myuser;

Om du loggar in som den användaren kommer du att upptäcka att standard search_path för PostgreSQL faktiskt är detta:

show search_path;

  search_path
----------------
 "$user",public

The first schema on the search path is the user’s named schema! So now the following conditions exist:

  • Användaren finns, med möjlighet att skapa spatiala tabeller.

  • Användarens namngivna schema finns och användaren äger det.

  • Användarens sökväg har användarens schema först, så nya tabeller skapas automatiskt där och frågor söker automatiskt där först.

Det är allt som behövs, användarens standardarbetsområde är nu snyggt separerat från alla tabeller i andra scheman.