Start z PostgreSQL na Linux Debian część 2 – praca z psql, kopia zapasowa bazy


Psql – klient serwera PostgreSQL

Psql to tekstowe narzędzie dostarczone wraz z serwerem. Znane mi są jeszcze dwa inne graficzne rozwiązania – phpPgAdmin oraz pgAdmin – jak to piszę w wersji 4.  Pierwsze mi odpadło bo się okazało, że nie ma wsparcia dla najnowszej wersji serwera –  niby znalazłem jakiś kruczek, ale nie zadziałał, więc daruję sobie przytaczanie. Drugie zainstalowałem, odpaliłem na chwilę, połączyłem się z serwerem… po czym zacząłem pracę z bazą używając jednak psql. Myślę, że szczególnie na początku warto jednak poznać pracę u podstaw 😉 .

W psql można pisać używając języka zapytań SQL – zawsze pamiętaj o użyciu średnika na końcu, tj. można naciskać wiele razy enter ale zapytanie kończy się średnikiem. No i można pisać w psql  jego własne polecenia, poprzedzając je ukośnikiem, bez konieczności wstawiania średnika na końcu.  

Podstawowe komendy psql:

  • \? – najbardziej podstawowa, wyświetlająca dostępne polecenia dla psql 😉
  • \h – druga najbardziej podstawowa, wyświetlająca instrukcje zapytań języka SQL
  • \l+ – podgląd dostępnych baz danych wraz z ich rozmiarem
  • \du – podgląd użytkowników
  • \d – podgląd tabel / relacji danej bazy, z którą aktualnie występuje połączenie
  • \d nazwa_tabeli – wyświetla szczegóły danej tabeli – kolumn,  typów danych, indeksów itp.
  • \c nazwa_bazy próba połączenia z bazą
  • \conninfo – wyświetla informację o aktualnym połączeniu z bazą
  • \q – wyjście

Podstawowe operacje wewnątrz psql

Założenie hasła dla serwerowego użytkownika postgres.

ALTER USER postgres WITH PASSWORD 'some password';

Z poziomu użytkownika postgres, założenie nowego konta użytkownika z wybranymi uprawnieniami, w tym z czasowym ograniczeniem walidacji hasła.

CREATE USER adam WITH PASSWORD 'some password' CREATEDB CREATEROLE VALID UNTIL '2018-05-01';

Można by tu dodać, że CREATE USER jest aliasem dla CREATE ROLE, które ma szerszy kontekst, gdyż może określać również grupę użytkowników. Kiedy zatwierdzisz powyższe zapytanie otrzymasz komunikat CREATE ROLE, a nie CREATE USER. Jest tylko jedna różnica pomiędzy nimi: używając CREATE USER, rola domyślnie będzie zawierała opcję LOGIN – uprawnienia do logowania, a używając CREATE ROLE, rola domyślnie będzie ustawiona na NOLOGIN.

Przykładowe usunięcie użytkownika.

DROP USER adam;

Założenie nowego użytkownika na serwerze typu superuser.

CREATE USER adam WITH PASSWORD 'some password' SUPERUSER;

Założenie nowej bazy dla użytkownika adam.

CREATE DATABASE adam_db OWNER adam;

Wyjście z psql  i ponowne zalogowanie się na nowego użytkownika, do jego nowej bazy.

\q
psql -U adam -d adam_db

Można teraz tworzyć tabele, relacje, ćwiczyć SQL,  lub zacząć testować połączenie z poziomu Javy i JDBC.

Utworzenie przykładowej tabeli.

CREATE TABLE klient (
  klient_id INTEGER PRIMARY KEY,
  imie VARCHAR(20) NOT NULL,
  nazwisko VARCHAR(30) NOT NULL
  );

Przykładowe usunięcie bazy.

DROP DATABASE adam_db;

Kopia zapasowa pojedynczej bazy w trzech krokach

Kopie zapasowe dzielą się na tak zwane gorące i zimne. Zimne kopie charakteryzują się fizycznym skopiowaniem niezbędnych plików z takim zaleceniem, że serwer baz danych powinien być w trakcie kopiowania wyłączony. Gorące natomiast są wykonywane przez serwer podczas jego działania i za pomocą jego narzędzi. Stworzenie kopii zapasowej przez serwer PostgreSQL polega na utworzeniu skryptu SQL, który wrzuci dane do bazy, nie tworząc jej jednak. Pamiętać więc trzeba, że generalnie należy utworzyć nową pustą bazę, a następnie odtworzyć w niej dane.

1. Kopia zapasowa pojedynczej bazy z poziomu konsoli systemowej.

pg_dump -U adam -Fc adam_db > adam_backup.dump

2.  Stworzenie nowej bazy z poziomu psql

CREATE DATABASE adam_db2 OWNER adam;

3. Wczytanie danych do nowej pustej bazy (ważne żeby była pusta) z konsoli.

pg_restore -U adam -d adam_db2 adam_backup.dump

Powyżej znajduje się tylko prosty przykład wykonania kopii pojedynczej bazy, temat jak można się domyślić jest nieco szerszy. Z resztą dotyczy to całego postu, dość powiedzieć, że dokumentacja do PostgreSql, którą zaciągnąłem w pdf ma w sumie 3291 stron 😯 .

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *