Pierwsze logowanie

Zaraz po instalacji postgresql pozwala się zalogować jedynie z lokalnej maszyny z konta użytkownika postgres:

sudo -u postgres psql

Po zalogowaniu możemy nadać hasło dla użytkownika postgres z użyciem polecenia:

ALTER USER postgres WITH PASSWORD 'pass';

Nowego użytkownika towrzymy wydając polecenie:

CREATE USER usr with CREATEDB CREATEROLE;
ALTER USER usr with PASSWORD 'pass';

Listę użytkowniku możemy uzyskać za pomocą \du

Następnie tworzymy bazę dla nowego użytkownika:

CREATE DATABASE usr OWNER usr;

Listę baz wyświetlimy z użyciem \l

Dodanie wszystkich praw dla danej bazy użytkownikowi:

GRANT ALL PRIVILEGES ON DATABASE "usr" to usr;

Dostęp z innych hostów

Domyślnie PostgreSQL nasłuchuje na adresie 127.0.0.1. Możemy to zmienić edytując plik /etc/postgresql/15/main/postgresql.conf a w nim wiersz listen_addresses.

Dodatkowo w pliku /etc/postgresql/15/main/pg_hba.conf należy dodać wiersz wskazujący z jakich adresów baza danych ma przyjmować połączenia i jaką metodę autoryzacji ma przyjąć, przykadowy wiersz poniżej:

host    all             all             192.168.8.0/24        scram-sha-256

Przydatne polecenia

Logowanie do PostgreSQL przy użyciu psql:

psql -U user -W -d baza -h host -p port

Lista tabel \d

Opis bazy \d bazwa bazy

Dane wyjściowe prezentowane w postaci HTML, ponowne użycie przełącza znowu na zwykły tekst: \H

Tworzenie tabeli

CREATE TABLE accounts (
    user_id serial PRIMARY KEY,
    username VARCHAR ( 50 ) UNIQUE NOT NULL,
    password VARCHAR ( 50 ) NOT NULL,
    email VARCHAR ( 255 ) UNIQUE NOT NULL,
    user_number INT NOT NULL,
    created_on TIMESTAMP NOT NULL,
    last_login TIMESTAMP 
);

Dodawanie danych - jeden wiersz

INSERT INTO accounts (username, password, email, user_number, created_on)
VALUES ('x1', 'pass', 'x1@example.tmp', 123, '2023-01-01');

Dodawanie danych - wiele wierszy

INSERT INTO accounts (username, password, email, user_number, created_on)
VALUES
('x1', 'pass', 'x1example.tmp', 123, '2023-01-01'),
('x2', 'pass', 'x2example.tmp', 124, '2023-01-01'),
('x3', 'pass', 'x3example.tmp', 125, '2023-01-01');

Zmiana typu danych w kolumnie z INT na VARCHAR(15):

ALTER TABLE phonebook ALTER COLUMN telefon TYPE VARCHAR(15);

Backup konkretnej bazy danych z serwera PostgreSQL:

pg_dump -h 192.168.xxx.yyy --user=USER -p 5432 BAZADANYCH > BAZADANYCH.bak

Backup wszystkich baz danych z serwera:

pg_dumpall -h 192.168.xxx.yyy --user=USER -p 5432 > postgresql_all_databases.bak

Jeżeli nasze bazy są duże możemy dokonać zrzutów z kompresją w locie:

pg_dump -h 192.168.xxx.yyy --user=USER -p 5432 BAZADANYCH | bzip2 > BAZADANYCH.bak.bzip2
pg_dumpall -h 192.168.xxx.yyy --user=USER -p 5432 | bzip2 > postgresql_all_databases.bak.bzip2

Przywracanie konkretnej bazy danych:

psql -U user -h 127.0.0.1 -W BAZADANYCH < BAZDANYCH.bak

Przywracanie wszystkich baz danych (postgres na końcu jest tu nazwą klastra)

psql -f postgresql_all_databases.bak postgres

Jeżeli mamy skompresowane wersje plików to możemy zdekompresować je w locie:

bzip2 -dc BAZDANYCH.bak.bzip2 | psql BAZADANYCH

Łączenie wyników z dwóch baz:

SELECT weather.city, weather.temp_lo, weather.temp_hi,
       weather.prcp, weather.date, cities.location
    FROM weather JOIN cities ON weather.city = cities.name;

Logowanie jako administrator PostgreSQL oraz zakładanie baz i użytkowników

Logowanie z mocą superużytkownika PostgreSQL: sudo -u postgres psql postgres

Tworzenie nowej bazy i użytkownika z prawami do tej bazy:

create database mydb;
create user myuser with encrypted password 'mypass';
grant all privileges on database mydb to myuser;