DEV Community

Andrzej Klusiewicz
Andrzej Klusiewicz

Posted on

Bazy danych w PostgreSQL - tworzenie, zarzadzanie i dobre praktyki

#postgres #database #sysadmin #sql

Jeden klaster PostgreSQL moze obslugiwac wiele baz danych - warto wiedziec jak je poprawnie zakladac, kodowac i separowac od siebie zanim narobi sie balaganu na produkcji.

Z tego artykułu dowiesz się:

  • czym są i do czego służą bazy danych w PostgreSQL,- jak sprawdzić dostępne bazy danych za pomocą SQL- jak sprawdzić wielkość baz danych za pomocą SQL- jak sprawdzić przestrzeń tabel w której mieści się baza danych za pomocą SQL,- jak sprawdzić kto jest właścicielem bazy danych za pomocą SQL,- jak w prosty sposób tworzyć bazy danych,- jak tworzyć bazy danych jako kopie innych baz,- jak tworzyć bazy danych w wybranych przestrzeniach tabel,- jak tworzyć bazy danych tak by należały do wybranego użytkownika,- jak przemieszczać bazy danych pomiędzy przestrzeniami tabel i jakie są tego konsekwencje,- jak zmieniać właściciela bazy danych,- jak zmieniać i sprawdzać parametry ustawione dla konkretnej bazy danych,- jak zmieniać nazwę bazy danych,- jak kasować bazę danych.

Bazy danych są strukturą logiczną, choć wiążą się ze strukturą fizyczną. Każda baza danych ma swój osobny katalog na serwerze. - Dane pomiędzy poszczególnymi bazami danych są od siebie odseparowane fizycznie i logicznie. Niestety nie są na tyle niezależne jak np. w Oracle czy SQL Server, by można było osobno robić kopie zapasowe i osobno przywracać bazy danych. Co prawda można wykonać eksport danych do pliku tekstowego za pomocą pg_dump, ale nie jest to kopia zapasowa w pełnym tego słowa znaczeniu i nie pozwala np. przywrócić bazy do wybranego punktu w czasie. W przypadku eksportu danych- możliwość późniejszego odtworzenia bazy ogranicza się do punktu w czasie, z którego pochodzi eksport. Należy mieć to na uwadze przy projektowaniu - struktur bazodanowych. Jeśli potrzebujemy kilku baz dla kilku niezależnych od siebie systemów, to lepiej zamiast osobnych baz stworzyć osobne klastry. Dzięki temu będziemy mogli włączać i wyłączać poszczególne systemy niezależnie, osobno backupować i w razie potrzeby odtwarzać.

Wszystkie bazy danych współdzielą obszar pamięci dostępny dla całego klastra PostgreSQL, a zdefiniowany przez parametr "shared_buffers". - To jest kolejny powód do separacji danych w osobnych klastrach. Jeśli w jednym klastrze będą dane kilku systemów, to każdy z nich będzie opóźniał działanie pozostałych.

Sprawdzanie dostępnych baz danych

Jakie - bazy danych mamy w klastrze, możemy sprawdzić, wykonując:

select oid,datname from pg_database;
Enter fullscreen mode Exit fullscreen mode

Sprawdzanie dostępnych baz danych: Bazy danych w PostgreSQL

Jak widać, mamy w - tym - przypadku 5 baz danych. Bazy danych example i test_config zostały stworzone przy okazji jakiegoś przykładu, nie ma ich domyślnie. Baza "postgres" jest tworzona przez skrypt "initdb" w momencie inicjalizacji klastra. Bazy "template0" i "template1" to bazy wzorcowe. Każda nowa baza danych jest tworzona jako kopia bazy "template1". Oznacza to, że jeśli dodamy jakieś obiekty czy ustawienia dla tej bazy, będzie to duplikowane do każdej nowo tworzonej bazy. Baza "template0" - również jest bazą wzorcową, jednak nie powinniśmy jej modyfikować. Ma ona pozostać “dziewicza” -jak określono w dokumentacji . Służyć będzie do tworzenia nowych pustych baz danych w przypadku, gdy zmodyfikujemy "template1", a chcielibyśmy nową czystą bazę.

Przy okazji należy porównać - oid’y poszczególnych baz z nazwami katalogów w podkatalogu “base” (to tu są pliki danych naszych baz) - PGDATA:

Sprawdzanie dostępnych baz danych: Bazy danych w PostgreSQL (2)

Jak widać, - każda baza ma osobny katalog i jego nazwa pokrywa się z wartością oid dla każdej bazy. To powoduje, - że możemy między innymi umieścić wybraną bazę na innym dysku. Nie sprawia to jednak, że można te katalogi osobno backupować, ponieważ pliki w nich zawarte są powiązane z innymi plikami w PGDATA.

Sprawdzanie wielkości baz danych

Możemy sprawdzić wielkość poszczególnych baz danych za pomocą funkcji: "pg_database_size" oraz - outputu "pg_size_pretty":

select datname,pg_database_size(datname), pg_size_pretty(pg_database_size(datname))
from pg_database;
Enter fullscreen mode Exit fullscreen mode

Sprawdzanie wielkości baz danych: Bazy danych w PostgreSQL

Sprawdzanie przestrzeni tabel, w której mieści się baza danych

Przestrzeniami tabel jeszcze się nie zajmowaliśmy. Na potrzeby tego rozdziału należy przyjąć, że przestrzeń tabel to - katalog, w którym mogą się znajdować pliki danych.

Jeśli chcielibyśmy sprawdzić, w jakim tablespace leżą poszczególne bazy, potrzebujemy wykorzystać dodatkowo słownik "pg_tablespace":

select datname,spcname from pg_database d join pg_tablespace t on t.oid=d.dattablespace;
Enter fullscreen mode Exit fullscreen mode

Sprawdzanie przestrzeni tabel, w której mieści się baza danych: Bazy danych w PostgreSQL

Sprawdzanie, kto jest właścicielem bazy danych

Korzystając z funkcji "pg_get_userbyid" znajdującej się w schemacie "pg_catalog", jesteśmy - w stanie zweryfikować, kto jest właścicielem bazy danych:

SELECT d.datname, pg_catalog.pg_get_userbyid(d.datdba)
FROM pg_catalog.pg_database d;
Enter fullscreen mode Exit fullscreen mode

Sprawdzanie, kto jest właścicielem bazy danych: Bazy danych w PostgreSQL

Sprawdzanie parametrów ustawionych indywidualnie dla bazy

Niektóre z parametrów (np. random_page_cost, seq_page_cost) można ustawić na poziomie bazy danych. Bazy, które mają tak ustawione parametry, będą widoczne w słowniku "pg_db_role_setting". Co ważne - :nie figurują tam wszystkie bazy danych, a jedynie te, które mają - parametry ustawione indywidualnie

select * from pg_db_role_setting;
Enter fullscreen mode Exit fullscreen mode

Sprawdzanie parametrów ustawionych indywidualnie dla bazy: Bazy danych w PostgreSQL

Zamiast nazwy bazy danych mamy jej oid. Potrzebujemy więc - zapytania, by zwracało nam nazwy baz:

select datname,setconfig
from pg_db_role_setting s
join pg_database d on s.setdatabase=d.oid;
Enter fullscreen mode Exit fullscreen mode

Sprawdzanie parametrów ustawionych indywidualnie dla bazy: Bazy danych w PostgreSQL (2)

Tworzenie - bazy danych

Ogólna konstrukcja polecenia tworzenia bazy danych jest następująca:

CREATE DATABASE NAZWA_BAZY

WITH OWNER = NAZWA UŻYTKOWNIKA

TEMPLATE = BAZA_ROBIĄCA_ZA_SZABLON

ENCODING = KODOWANIE

TABLESPACE = NAZWA_PRZESTRZENI

CONNECTION LIMIT = X

Najprostszy sposób stworzenia bazy:

create database druga;
Enter fullscreen mode Exit fullscreen mode

W ramach takiej bazy utworzy się jeden schemat o nazwie public oraz ewentualnie inne obiekty, jeśli dodamy je do bazy "template1". Możemy też utworzyć bazę danych na podstawie innej bazy:

create database trzecia with template=test_config;
Enter fullscreen mode Exit fullscreen mode

Stworzona w ten sposób kopia będzie zawierała kopię obiektów z bazy wzorcowej.Jest to - ciekawy sposób na produkowanie baz developerskich czy testowych. Jedna uwaga - nie możemy w ten sposób stworzyć kopii bazy, do której została podłączona jakakolwiek sesja.

Możemy również umieścić bazę danych w określonej przestrzeni tabel (co za tym idzie i lokalizacji) w następujący sposób:

create database czwarta tablespace=pg_default;
Enter fullscreen mode Exit fullscreen mode

Dane tak stworzonej bazy będą umieszczone w katalogu związanym z daną przestrzenią tabel.

Domyślnie właścicielem bazy danych będzie użytkownik ją tworzący. Aby stworzyć bazę, której właścicielem będzie określony użytkownik, trzeba wykonać - polecenie:

create database piata owner=mapet;
Enter fullscreen mode Exit fullscreen mode

Użytkownik, który jest właścicielem bazy danych, będzie mógł zrobić z nią wszystko - w tym nawet - skasować, jeśli w danej bazie istnieją obiekty należące do innego użytkownika. Może też kasować i zmieniać obiekty wszystkich innych użytkowników - w tym obiekty stworzone przez użytkownika postgres - czyli administratora klastra.

Zmiana właściciela bazy danych

Tym razem stworzymy nową bazę danych z domyślnym właścicielem "postgres" .Z jego poziomu utworzymy - bazę danych.To będzie - jej właściciel. Następnie zmienimy właściciela:

create database nowa;
select datname,pg_catalog.pg_get_userbyid(d.datdba) from pg_database d;
Enter fullscreen mode Exit fullscreen mode

Zmiana właściciela bazy danych: Bazy danych w PostgreSQL

Widzimy wszystkie stworzone dotychczas bazy - w tym np. bazę “piata” należąca do użytkownika mapet. Nasza nowo stworzona baza “nowa” - należy do użytkownika postgres. Zmieniamy właściciela na użytkownika mapet:

alter database nowa owner to mapet;
Enter fullscreen mode Exit fullscreen mode

Sprawdzamy właściciela dla tej bazy:

select datname,pg_catalog.pg_get_userbyid(d.datdba) from pg_database d
where datname='nowa';
Enter fullscreen mode Exit fullscreen mode

Zmiana właściciela bazy danych: Bazy danych w PostgreSQL (2)

Właścicielem bazy “nowa” jest już użytkownik “mapet”.

Konfiguracja indywidualnych parametrów bazy danych

Niektóre parametry można ustawiać na poziomie bazy danych. Aby zmienić parametry, wykonujemy poniższe zapytanie:

alter database nowa set random_page_cost=1;
Enter fullscreen mode Exit fullscreen mode

Został użyty parametr: "random_page_cost" , ale może to być dowolny parametr konfigurowalny na poziomie bazy danych. Sprawdźmy jeszcze, czy to działa:

select datname,setconfig from
pg_db_role_setting s join pg_database d on s.setdatabase=d.oid;
Enter fullscreen mode Exit fullscreen mode

Konfiguracja indywidualnych parametrów bazy danych: Bazy danych w PostgreSQL

Przenoszenie bazy danych pomiędzy przestrzeniami tabel

Bazy danych przy tworzeniu będą lądowały w domyślnej przestrzeni tabel tj. "pg_default", czyli w podkatalogu "base" naszej PGDATA. Przestrzeń docelową możemy wskazać, tworząc bazę danych, ale jeśli taka już istnieje, możemy ją też przenieść. Przeniesienie bazy danych pomiędzy przestrzeniami tabel będzie się wiązało z fizycznym przeniesieniem jej plików pomiędzy katalogami. W związku z tym, by zapewnić spójność danych, baza w trakcie przenoszenia pomiędzy przestrzeniami tabel będzie niedostępna. Aby przenieść bazę do innej przestrzeni tabel, - wykonujemy komendę wg schematu:

alter database do_przeniesienia set tablespace dane1;
Enter fullscreen mode Exit fullscreen mode

Zmiana nazwy bazy danych

Zmienimy - nazwę bazy danych. W swoim klastrze mamy bazę o nazwie "example":

Zmiana nazwy bazy danych: Bazy danych w PostgreSQL

Zmieniamy - nazwę z “example” na “samples”:

alter database example rename to samples;
Enter fullscreen mode Exit fullscreen mode

Nazwa została zmieniona:

Zmiana nazwy bazy danych: Bazy danych w PostgreSQL (2)

Kasowanie bazy danych

Jeśli chcemy skasować bazę danych, możemy wywołać:

drop database samples;
Enter fullscreen mode Exit fullscreen mode

Wykonując tę operację, należy podpiąć się do innej bazy niż ta kasowana. - Nie uda się też skasować bazy, jeśli jakiś użytkownik będzie do niej podpięty. Trzeba będzie ubić jego sesję. Każdą bazę niezależnie od tego, kto jest jej właścicielem, może skasować superużytkownik, np. postgres. Kasowanie bazy danych powoduje skasowanie wszystkich obiektów w niej zawartych.

Baner szkolenia Administracja, replikacja i tuning baz danych PostgreSQL w JSystems, terminy gwarantowane

Szkolenie Administracja, replikacja i tuning baz danych PostgreSQL →

To szkolenie może być dofinansowane dla Ciebie z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5

Powiązane artykuły

Top comments (0)