PostgreSQL: Pierwsze kroki

Jak zacząć z PostgreSQL

👋 Witamy w dokumentacji Stackhero!

Stackhero oferuje gotowe do użycia rozwiązanie PostgreSQL cloud, które zapewnia wiele korzyści, w tym:

  • Nieograniczone połączenia i transfery danych.
  • W zestawie interfejs webowy PgAdmin.
  • Wiele modułów w zestawie, takich jak PostGIS, TimescaleDB i PgVector.
  • Bezproblemowe aktualizacje za pomocą jednego kliknięcia.
  • Optymalna wydajność i solidne zabezpieczenia dzięki prywatnej i dedykowanej VM.

Oszczędzaj czas i upraszczaj sobie życie: wypróbowanie rozwiązania Stackhero PostgreSQL cloud hosting zajmuje tylko 5 minut!

Korzystanie z CLI psql PostgreSQL

Możesz zarządzać PostgreSQL za pomocą oficjalnego CLI psql. Jest dostępne do instalacji na Twoim komputerze, co pozwala na zdalne zarządzanie usługą PostgreSQL. Alternatywnie, możesz uruchomić je przez Docker. Ta metoda jest często preferowana, ponieważ unika instalacji psql bezpośrednio na komputerze i ułatwia przełączanie między wersjami.

Aby uzyskać dostęp do psql za pomocą Dockera, uruchom następujące polecenie (upewnij się, że zamienisz 18 na główny numer wersji swojej usługi PostgreSQL):

docker run -v $(pwd):/mnt -it postgres:18-alpine /bin/bash

Następnie połącz się z usługą PostgreSQL używając:

cd /mnt
psql \
  --host=<XXXXXX>.stackhero-network.com \
  --username=admin \
  --port=<PORT> \
  --dbname=admin

Tworzenie użytkownika i bazy danych w PostgreSQL

Domyślnie tworzony jest użytkownik admin z prawami administracyjnymi. Dobrą praktyką jest tworzenie dedykowanego użytkownika i bazy danych dla każdego projektu, który planujesz hostować.

Korzystanie z interfejsu webowego PgAdmin

Aby użyć interfejsu webowego PgAdmin, otwórz swoją domenę PostgreSQL z HTTPS (na przykład, https://<XXXXXX>.stackhero-network.com). Zaloguj się jako admin używając hasła, które ustawiłeś w konfiguracji usługi (widoczne w panelu Stackhero).

Tworzenie użytkownika

  1. Przejdź do Servers / PostgreSQL, kliknij prawym przyciskiem na Login/Group Roles i wybierz Create / Login/Group Role:

    Tworzenie użytkownika w PostgreSQL za pomocą PgAdminTworzenie użytkownika w PostgreSQL za pomocą PgAdmin

  2. Ustaw nazwę logowania:

    Definiowanie loginu użytkownikaDefiniowanie loginu użytkownika

  3. Ustaw bezpieczne hasło, aby uniknąć ataków brute force:

    Definiowanie hasła użytkownikaDefiniowanie hasła użytkownika

  4. Na koniec upewnij się, że wybrany jest tylko przywilej "Can login":

    Definiowanie praw użytkownikaDefiniowanie praw użytkownika

Kliknij przycisk "Save", aby utworzyć użytkownika.

Tworzenie bazy danych

  1. Przejdź do Servers / postgresql, kliknij prawym przyciskiem na Databases i wybierz Create / Database...:

    Tworzenie bazy danych za pomocą PgAdminTworzenie bazy danych za pomocą PgAdmin

Dobrą praktyką jest używanie tej samej nazwy dla bazy danych i użytkownika. Na przykład, jeśli Twój projekt nazywa się "superWebsite", rozważ utworzenie użytkownika o nazwie "superWebsite" i bazy danych o nazwie "superWebsite".

  1. Ustaw nazwę bazy danych i wybierz właściciela (użytkownika, którego właśnie utworzyłeś):

    Definiowanie nazwy i właściciela bazy danychDefiniowanie nazwy i właściciela bazy danych

Twoja baza danych została utworzona.

Korzystanie z CLI psql

Tworzenie użytkownika

Aby utworzyć użytkownika w PostgreSQL za pomocą CLI psql, uruchom następujące zapytanie SQL:

CREATE ROLE "myProject" WITH
  LOGIN
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOINHERIT
  NOREPLICATION
  CONNECTION LIMIT -1
  PASSWORD 'secretPassword';

Nie zapomnij zastąpić myProject nazwą swojego projektu i secretPassword bezpiecznym hasłem. Dobrą praktyką jest również używanie nazwy projektu jako loginu i nazwy bazy danych. Jeśli Twój projekt nazywa się "superWebsite", rozważ utworzenie użytkownika o nazwie "superWebsite" i bazy danych o nazwie "superWebsite".

Możesz wygenerować bezpieczne hasło za pomocą tej linii poleceń: openssl rand -base64 24 | tr -d '\n' | cut -c1-32

Tworzenie bazy danych

Aby utworzyć bazę danych w PostgreSQL za pomocą CLI psql, uruchom następujące zapytanie SQL:

CREATE DATABASE "myProject"
  WITH
  OWNER = "myProject"
  ENCODING = 'UTF8'
  CONNECTION LIMIT = -1
  IS_TEMPLATE = false;

Dobrą praktyką jest używanie tej samej nazwy dla bazy danych i użytkownika. Na przykład, jeśli Twój projekt nazywa się "superWebsite", rozważ utworzenie użytkownika o nazwie "superWebsite" i bazy danych o nazwie "superWebsite".

Importowanie danych z komputera do PostgreSQL

Jednym z najprostszych sposobów importowania danych z komputera do instancji PostgreSQL jest użycie CLI PostgreSQL. Na swoim komputerze uruchom następujące polecenie (zamień <DB_NAME> na nazwę swojej bazy danych i data.pgsql na nazwę swojego pliku SQL):

psql \
  --host=<XXXXXX>.stackhero-network.com \
  --username=admin \
  --port=<PORT> \
  --dbname=<DB_NAME> \
  < data.pgsql

Eksportowanie danych z PostgreSQL na komputer

Eksportowanie danych z instancji PostgreSQL na komputer jest równie proste przy użyciu CLI PostgreSQL.

  1. Aby wyeksportować całą bazę danych (zamień <DB_NAME> na nazwę swojej bazy danych):

    pg_dump \
  --host=<XXXXXX>.stackhero-network.com \
  --username=admin \
  --port=<PORT> \
  --dbname=<DB_NAME> \
  > data.pgsql

  2. Aby wyeksportować pojedynczą tabelę (zamień <DB_NAME> na nazwę swojej bazy danych i <TABLE_NAME> na nazwę swojej tabeli):

    pg_dump \
  --host=<XXXXXX>.stackhero-network.com \
  --username=admin \
  --port=<PORT> \
  --dbname=<DB_NAME> \
  --table=<TABLE_NAME> \
  > data.pgsql

Korzystanie z PostgreSQL z Node.js

Aby połączyć PostgreSQL z użyciem Node.js i pakietu pg (znanego również jako node-postgres), możesz rozważyć poniższy przykład. Zwróć uwagę na argument ssl, który jest ważny dla zapewnienia szyfrowania TLS:

const { Client } = require('pg');

(async () => {
  const pg = new Client({
    host: '<XXXXXX>.stackhero-network.com',
    port: <PORT>,
    user: '<USERNAME>',
    password: '<PASSWORD>',
    database: '<DATABASE>',
    ssl: {}
  });

  await pg.connect();

  const result = await pg.query('SELECT 1');

  await pg.end();
})().catch(error => {
  console.error('');
  console.error('🐞 Wystąpił błąd!');
  console.error(error);
  process.exit(1);
});

Kompletny przykład użycia PostgreSQL z Node.js i biblioteką pg (oraz async/await) jest dostępny w tym repozytorium Git: https://github.com/stackhero-io/postgresqlGettingStarted.

Korzystanie z PostgreSQL z Node.js/TypeORM

Aby połączyć się z PostgreSQL z Node.js używając TypeORM, ustaw flagę ssl na true, jak pokazano w poniższym przykładzie:

createConnection({
  type: 'postgres',
  host: '<XXXXXX>.stackhero-network.com',
  port: <PORT>,
  username: '<USERNAME>',
  password: '<PASSWORD>',
  database: '<DATABASE>',
  extra: {
    ssl: true
  }
});

Korzystanie z PostgreSQL z Django

Jeśli nie jest jeszcze zainstalowany, zainstaluj moduł psycopg, który będzie używany do połączenia z PostgreSQL:

pip install psycopg

W tym początkowym kroku będziesz przechowywać hasło bezpośrednio w pliku settings.py. Ta metoda jest tylko do testów, ponieważ nie jest bezpieczna. Później w tej dokumentacji znajdziesz przykład najlepszej praktyki.

Otwórz plik settings.py i dodaj następującą konfigurację:

DATABASES = {
  'default': {
    'ENGINE': 'django.db.backends.postgresql',
    'HOST': '<XXXXXX>.stackhero-network.com',
    'PORT': <PORT>,
    'OPTIONS': {
      'sslmode': 'require',
    },
    'NAME': 'admin',
    'USER': 'admin',
    'PASSWORD': '<ADMIN_PASSWORD>'
  }
}

Uwaga: ten przykład nie jest zalecany do produkcji i jest przeznaczony tylko do celów testowych!

Gdy Twoje połączenie działa, możesz przyjąć bardziej bezpieczną metodę przechowywania poświadczeń. Poniższy przykład używa django-environ i przechowuje poświadczenia w pliku .env.

  1. Zainstaluj django-environ:

    pip install django-environ

  2. Otwórz plik settings.py i zmodyfikuj go w następujący sposób:

    import environ
env = environ.Env()
environ.Env.read_env()

DATABASES = {
  'default': {
    'ENGINE': 'django.db.backends.postgresql',
    'HOST': env('STACKHERO_POSTGRESQL_HOST'),
    'PORT': <PORT>,
    'OPTIONS': {
      'sslmode': 'require',
    },
    'NAME': 'admin',
    'USER': 'admin',
    'PASSWORD': env('STACKHERO_POSTGRESQL_ADMIN_PASSWORD')
  }
}

  3. Otwórz lub utwórz plik .env w tym samym katalogu co settings.py i dodaj:

    STACKHERO_POSTGRESQL_HOST=<XXXXXX>.stackhero-network.com
STACKHERO_POSTGRESQL_ADMIN_PASSWORD=<ADMIN_PASSWORD>

  4. Na koniec dodaj .env do swojego pliku .gitignore, aby upewnić się, że Twoje poświadczenia nie są przechowywane w repozytorium Git:

echo ".env" >> .gitignore

Aktywacja rozszerzenia PostGIS PostgreSQL

Rozszerzenie PostGIS jest dołączone do naszej usługi PostgreSQL. Musisz je aktywować w każdej bazie danych, w której planujesz je używać.

Aby aktywować rozszerzenie, połącz się z bazą danych i wykonaj to zapytanie:

CREATE EXTENSION postgis;

Możesz sprawdzić, czy PostGIS działa, sprawdzając jego wersję:

SELECT PostGIS_Full_Version();

Alternatywnie, pobierz listę wszystkich zainstalowanych rozszerzeń PostGIS:

SELECT * FROM pg_extension WHERE extname LIKE 'postgis%';

Jeśli to konieczne, możesz dodać inne rozszerzenia. Jednak zdecydowanie zaleca się, aby nie aktywować rozszerzeń, których nie będziesz używać:

-- Włącz PostGIS
CREATE EXTENSION postgis;

-- Włącz wsparcie dla rastrów
CREATE EXTENSION postgis_raster;

-- Włącz Topologię
CREATE EXTENSION postgis_topology;

-- Fuzzy matching potrzebny dla Tiger
CREATE EXTENSION fuzzystrmatch;

-- Standardyzator oparty na regułach
CREATE EXTENSION address_standardizer;

-- Przykładowy zestaw danych reguł
CREATE EXTENSION address_standardizer_data_us;

-- Włącz US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder;

Uwaga: nie aktywuj PostGIS na bazie danych postgres!

Dezaktywacja rozszerzenia PostGIS PostgreSQL

Aby usunąć PostGIS z bazy danych, połącz się z odpowiednią bazą danych i wykonaj następujące zapytanie:

DROP EXTENSION postgis;

Aktywacja rozszerzenia PgVector

Aktywacja rozszerzenia PgVector na Stackhero jest prosta. Uruchom następujące zapytanie:

CREATE EXTENSION vector;

Aktywacja rozszerzenia TimescaleDB

Aktywacja rozszerzenia TimescaleDB na Stackhero jest prosta. Uruchom następujące zapytanie:

CREATE EXTENSION timescaledb;

Śledzenie statystyk planowania i wykonania SQL (pg_stat_statements)

Aby włączyć wsparcie dla pg_stat_statements, najpierw włącz je w konfiguracji PostgreSQL za pośrednictwem panelu Stackhero. Następnie włącz rozszerzenie dla bieżącej bazy danych, uruchamiając:

CREATE EXTENSION pg_stat_statements;

Więcej informacji znajdziesz w oficjalnej dokumentacji PostgreSQL.

Konfiguracja PostgreSQL z Skyvia

Korzystanie z Skyvia z Stackhero dla PostgreSQL jest proste. Zapewnij bezpieczne połączenie, konfigurując następujące ustawienia:

  1. Ustaw Protocol na SSL
  2. Ustaw Port na swój port PostgreSQL
  3. Wymuś szyfrowanie, ustawiając SSL Mode na Require
  4. Wybierz SSL TLS Protocol o wartości 1.2 lub wyższej

Więcej szczegółów na temat konfiguracji Skyvia znajdziesz w oficjalnej dokumentacji Skyvia.

Przykład konfiguracji Skyvia z Stackhero dla PostgreSQLPrzykład konfiguracji Skyvia z Stackhero dla PostgreSQL

Other articles for PostgreSQL