pgsql-Befehle. Postgres-Befehle und -Skripte. Abrufen einer Liste der verfügbaren Datentypen

postgres=# DATENBANK ERSTELLEN test_database; DATENBANK ERSTELLEN postgres=# BENUTZER ERSTELLEN test_user MIT Passwort "qwerty"; CREATE ROLE postgres=# GEWÄHRE ALLE Privilegien ON DATABASE test_database TO test_user; GEWÄHREN

Um die Shell zu verlassen, geben Sie den Befehl \q ein.

Versuchen wir nun, im Auftrag von test_user mit der erstellten Datenbank zu arbeiten:

psql -h localhost test_datenbank test_benutzer

Lassen Sie uns eine neue Tabelle erstellen:

Test_database=> SEQUENCE ERSTELLEN user_ids; CREATE SEQUENCE test_database=> CREATE TABLE Benutzer (id INTEGER PRIMARY KEY DEFAULT NEXTVAL("user_ids"), Login CHAR(64), Passwort CHAR(64)); HINWEIS: CREATE TABLE / PRIMARY KEY ERSTELLT den impliziten INDEX „users_pkey“ FOR TABLE „users“ CREATE TABLE

Löschen eines Benutzers

Um einen Benutzer zu löschen, müssen Sie seine Rechte auf einen anderen übertragen und dann erst löschen

EIGENTUM VON doomed_role ZU Nachfolger_Rolle neu zuweisen; DROP IM BESITZ VON doomed_role;

- Wiederholen Sie die vorherigen Befehle für jede Basis im Cluster

DROP ROLE doomed_role;

Backup-Skript für Postgres-Tabellen.

#!/bin/bash DBNAMES="web"; USER="postgres"; DB_NAME="web"; NEW_OWNER="user_remote"; DATE_Y=`/bin/date "+%y"` DATE_M=`/bin/date "+%m"` DATE_D=`/bin/date "+%d"` SERVICE="pgdump" BACKUP_DIR="/var/ backup_db/20$(DATE_Y)/$(DATE_M)/$(DATE_D)" mkdir -p $BACKUP_DIR; für tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = "public";" $(DB_NAME)` \ `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = "public";" $(DB_NAME)` \ `psql -qAt -c "select table_name from information_schema.views where table_schema = "public";" $(DB_NAME)` ; do echo "Tabelle $tbl aus Datenbank $(DB_NAME) in Datei tables3/$tbl.backup exportieren" #pg_dump --format p --verbose --table public.$tbl $(DB_NAME) > $BACKUP_DIR/$tbl pg_dump - -format p --verbose --table public.$tbl $(DB_NAME) | gzip > $BACKUP_DIR/$tbl #pg_dump -a -d -t public.$tbl $(DB_NAME) > tables3/$tbl.sql fertig ##################BACKUP POSTGRES FUNKTIONEN # Dump die Datenbank ohne Datum, um daraus weitere Funktionen zu extrahieren pg_dump -Fc -s -f $BACKUP_DIR/db_dump $(DB_NAME) /bin/sleep 4; # Listenfunktion erstellen pg_restore -l $BACKUP_DIR/db_dump | grep FUNCTION > $BACKUP_DIR/function_list ##Funktionen wiederherstellen ######################### #pg_restore -h localhost -U Benutzername -d Basisname - L Funktionsliste db_dump #########################

Backup-Skript für Postgres-Tabellen. in Perl geschrieben

Führen Sie unter user'a - postgres aus. Wenn in Crons, dann auch unter dem Benutzer postgresql.

#!/usr/bin/env perl use strict; Warnungen verwenden; mein $database_name = "book_library"; meine $abfrage =<<"EOT"; SELECT n.nspname as table_schema, c.relname as table_name FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ("r","") AND n.nspname NOT IN ("pg_catalog", "information_schema") AND n.nspname NOT LIKE "^pg_%" ; EOT $query =~ s/\n\s*/ /g; my @results = `echo "$query" | psql -At $database_name`; foreach (@results) { chomp; my ($schema, $table) = split /\|/, $_; next unless ($schema && $table); my $cmd = "pg_dump -U postgres -Fp -t $schema.$table -f $schema.$table.dump $database_name"; system($cmd); } #If you wanted to restore only a single function: ##pg_restore -U $username --dbname=$dbname --function=$functionname(args) #If you wanted to restore only a single table: ##pg_restore -U $username --dbname=$dbname --table=$tablename

Wörterbücher für die Volltextsuche in postgresql

Datenbanksicherung von einem Remote-Rechner

PGPASSWORD="PASSWORD" pg_dump -h $HOSTNAME -U databaseuser -Fc --verbose "database.itc-life.ru" | gzip > Datenbankname.gz

Tabellen sichern und wiederherstellen

BEIM PostgreSQL Es gibt zwei Dienstprogramme für Sicherung pg_dump und pg_dumpall . pg_dump wird verwendet, um eine einzelne Datenbank zu sichern, pg_dumpall wird verwendet, um alle Datenbanken und den Server als Ganzes zu sichern (muss unter postgresql-Superuser ausgeführt werden).

Erstellen einer Datenbanksicherung mydb, komprimiert

Pg_dump -h localhost -p 5432 -U irgendein Benutzer -F c -b -v -f mydb.backup mydb

Erstellen einer Datenbanksicherung mydb, als einfache Textdatei, einschließlich eines Befehls zum Erstellen einer Datenbank

pg_dump -h localhost -p 5432 -U irgendein Benutzer -C -F p -b -v -f mydb.backup mydb

Erstellen einer Datenbanksicherung mydb, in komprimierter Form, mit Tabellen, die im Namen enthalten Zahlungen

Pg_dump -h localhost -p 5432 -U irgendein Benutzer -F c -b -v -t *Zahlungen* -f Zahlungstabellen.backup mydb

Datendump nur einer bestimmten Tabelle. Wenn Sie mehrere Tabellen sichern müssen, werden die Namen dieser Tabellen mit dem Schlüssel aufgelistet -t für jeden Tisch.

pg_dump -a -t Tabellenname -f Dateiname Datenbankname

Erstellen Sie eine komprimierte Sicherung gz

pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c > mydb.gz

Liste der am häufigsten verwendeten Optionen:

H host - Host, falls nicht angegeben, dann verwendet lokaler Host PGHOST.

P-Port - Port, wenn nicht angegeben, dann verwendet 5432 oder Wert aus Umgebungsvariable PGPORT.

U - Benutzer, wenn nicht angegeben, wird der aktuelle Benutzer verwendet, der Wert kann auch in der Umgebungsvariable angegeben werden PGBENUTZER.

A, --data-only - Dump nur Daten, Daten und Schema werden standardmäßig gespeichert.

B - große Objekte (Blogs) in den Dump aufnehmen.

S, --schema-only - Nur Schemas ausgeben.

C, --create - fügt einen Befehl zum Erstellen einer Datenbank hinzu.

C - fügt Befehle zum Löschen (Drop) von Objekten (Tabellen, Ansichten usw.) hinzu.

O - Fügen Sie keine Befehle hinzu, um den Eigentümer eines Objekts festzulegen (Tabellen, Ansichten usw.).

F, --format (c|t|p) — Dump-Ausgabeformat, benutzerdefinierte, Teer, oder Klartext.

T, --table=TABLE - Spezifiziert eine bestimmte Tabelle für den Dump.

V, --verbose - detaillierte Informationen ausgeben.

D, --attribute-inserts - Dump mit dem Befehl EINFÜGUNG mit einer Liste von Eigenschaftsnamen.

Sichern Sie alle Datenbanken mit dem Befehl pg_dumpall.

pg_dumpall > all.sql # Sicherung prüfen grep "^[\]connect" all.sql \connect db1 \connect db2

PostgreSQL hat zwei Dienstprogramme für Wiederherstellung Sicherungsbasen.

psql- Sicherungen wiederherstellen, die in einer Klartextdatei (Klartext) gespeichert sind;
pg_restore— Wiederherstellung komprimierter Backups (tar);

Datenbankwiederherstellung u

#pg_restore -v -e -d Datenbankname Datenbankname.dump

Wiederherstellen der gesamten Sicherung mit Ignorieren von Fehlern

psql -h localhost -U irgendein Benutzer -d Datenbankname -f mydb.sql

Wiederherstellen der gesamten Sicherung, Stoppen beim ersten Fehler

psql -h localhost -U irgendein Benutzer --set ON_ERROR_STOP=on -f mydb.sql

Sich erholen von Teer-archive müssen wir zuerst eine Datenbank mit CREATE DATABASE mydb erstellen; (falls die Option -C beim Erstellen der Sicherung nicht angegeben wurde) und wiederherstellen

pg_restore --dbname=mydb --jobs=4 --verbose mydb.backup

Wiederherstellen einer komprimierten Datenbanksicherung gz

Gunzip mydb.gz psql -U postgres -d mydb -f mydb

Ab Version 9.2 kann nur die Struktur von Tabellen mit der Option --section wiederhergestellt werden

# eine Datenbank erstellen CREATE DATABASE mydb2; # wiederherstellen pg_restore --dbname=mydb2 --section=pre-data --jobs=4 mydb.backup

Tischpflege

VAKUUMANALYSE-Tabelle; REINDEX-DATENBANK dbName; REINDEX-TABELLE tabName;

Übertragen des Datenverzeichnisses

Finden Sie den aktuellen Pfad heraus

# Weg 1 $ su - postgres $ psql psql > SHOW data_directory; # Weg 2 $ ps Axt | grep "postgres -D"

Erstellen Sie ein neues Verzeichnis, weisen Sie einen Benutzer zu und initialisieren Sie

mkdir -p /pathto/postgresql/data chown -R postgres:postgres /pathto/postgresql su - postgres initdb -D /pathto/postgresql/data

Jetzt müssen wir die Datei mit dem gestarteten Dienst korrigieren postgresql

# unter Arch Linux sudo vim /etc/systemd/system/multi-user.target.wants/postgresql.service Environment =PGROOT=/pathto/postgresql/ PIDFile =/pathto/postgresql/data/postmaster.pid

Reinigung des Tisches

Reinigung des Tisches Tabellenname und Reset-Zähler mit ID.

TRUNCATE TABLE Tabellenname RESTART IDENTITY CASCADE ;

KASKADE notfalls benötigt Tabellenname mit einer anderen Tabelle verknüpft.

Entfernen von NULL aus einem Feld

ALTER TABLE Filme ALTER COLUMN Jahr DROP NOT NULL ;

Pgbouncer ausführen

su -s /bin/sh - postgres -c "/usr/sbin/pgbouncer -d --verbose /etc/pgbouncer/pgbouncer.ini"

Trennen Sie Benutzer von der Datenbank

WÄHLEN Sie pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = "mydb";`

Hallo zusammen, heute möchte ich eine kleine Erinnerung an die wichtigsten PostgreSQL-Befehle machen. Sie können mit PostgreSQL sowohl interaktiv als auch von der Befehlszeile aus arbeiten. Das Programm ist psql. Ich bin sicher, dass diese Liste für Sie sehr nützlich sein wird und Ihnen Zeit beim Durchsuchen verschiedener Ressourcen ersparen wird. Ich möchte Sie daran erinnern, dass dies ein Open-Source-Projekt ist, das auf dem Postgres-DBMS basiert und 1986 veröffentlicht wurde. Es wird von der globalen Entwicklungsgruppe PGDG entwickelt, im Wesentlichen besteht es aus 5-8 Personen, aber trotzdem entwickelt es sich sehr intensiv, um alle neuen Funktionen einzuführen und alte Bugs und Fehler zu beheben.

Grundlegende PostgreSQL-Befehle im interaktiven Modus:

\connect db_name - verbindet sich mit einer Datenbank namens db_name
\du - Liste der Benutzer
\dp (oder \z) - Liste der Tabellen, Ansichten, Sequenzen, Zugriffsrechte darauf
\di - Indizes
\ds - Sequenzen
\dt - Liste der Tabellen
\dt+ - Liste aller Tabellen mit Beschreibung
\dt *s* - Liste aller Tabellen, die s im Namen enthalten
\dv - Ansichten
\dS - Systemtabellen
\d+ - Tabellenbeschreibung
\o - Abfrageergebnisse an eine Datei senden
\l - Liste der Datenbanken
\i - Eingehende Daten aus einer Datei lesen
\e - öffnet den aktuellen Inhalt des Abfragepuffers im Editor (wenn nicht anders in der EDITOR-Variablenumgebung angegeben, wird er standardmäßig von vi verwendet)
\d „table_name“ – Tabellenbeschreibung
\i Führe einen Befehl aus einer externen Datei aus, z.B. \i /my/directory/my.sql
\pset - Befehl zum Setzen von Formatierungsoptionen
\echo - zeigt eine Nachricht an
\set - Legt den Wert einer Umgebungsvariablen fest. Ohne Parameter wird eine Liste der aktuellen Variablen angezeigt (\unset - deletes).
\? - psql-Referenz
\help - SQL-Referenz
\q (oder Strg+D) - Programm beenden

Arbeiten mit PostgreSQL über die Befehlszeile:

-c (oder --command) – SQL-Befehl ausführen, ohne in den interaktiven Modus zu wechseln
-f file.sql - führt Befehle aus der Datei file.sql aus
-l (oder --list) listet verfügbare Datenbanken auf
-U (oder --username) – Geben Sie den Benutzernamen an (z. B. postgres)
-W (oder --password) - Passwortabfrage
-d dbname - Verbindung zur dbname-Datenbank herstellen
-h - Hostname (Server)
-s - Schritt-für-Schritt-Modus, das heißt, Sie müssen alle Befehle bestätigen
-S - einzeiliger Modus, d. h. ein Zeilenumbruch führt die Abfrage aus (beseitigt; am Ende des SQL-Konstrukts)
-V - PostgreSQL-Version ohne Wechsel in den interaktiven Modus

Beispiele:

psql -U postgres -d dbname -c „CREATE TABLE my(some_id serial PRIMARY KEY, some_text text);“ - Ausführen eines Befehls in der dbname-Datenbank.

psql -d dbname -H -c "SELECT * FROM my" -o my.html - Abfrageergebnis in HTML-Datei ausgeben.

PostgreSQL-Dienstprogramme (Programme):

createdb und dropdb - Erstellen bzw. Löschen einer Datenbank
createuser und dropuser - create und user (jeweils)
pg_ctl ist ein Programm zur Lösung allgemeiner Steuerungsaufgaben (Starten, Stoppen, Parametrieren etc.)
postmaster - PostgreSQL Mehrbenutzer-Servermodul (Konfiguration von Debug-Levels, Ports, Datenverzeichnissen)
initdb - Erstellen neuer PostgreSQL-Cluster
initlocation - ein Programm zum Erstellen von Verzeichnissen für die sekundäre Speicherung von Datenbanken
vakuumdb - physische und analytische Wartung der Datenbank
pg_dump - Archivieren und Wiederherstellen von Daten
pg_dumpall - Sichert einen gesamten PostgreSQL-Cluster
pg_restore - Datenbank aus Archiven wiederherstellen (.tar, .tar.gz)

Backup-Beispiele:

Erstellen einer Sicherungskopie der mydb-Datenbank in komprimierter Form

Pg_dump -h localhost -p 5440 -U irgendein Benutzer -F c -b -v -f mydb.backup mydb

Erstellen einer Sicherungskopie der mydb-Datenbank als reine Textdatei, einschließlich eines Befehls zum Erstellen einer Datenbank

pg_dump -h localhost -p 5432 -U irgendein Benutzer -C -F p -b -v -f mydb.backup mydb

Erstellen einer Sicherungskopie der mydb-Datenbank in komprimierter Form mit Tabellen, die Zahlungen im Namen enthalten

Pg_dump -h localhost -p 5432 -U irgendein Benutzer -F c -b -v -t *Zahlungen* -f Zahlungstabellen.backup mydb

Datendump nur einer bestimmten Tabelle. Wenn mehr als eine Tabelle gesichert werden muss, werden die Tabellennamen mit der Option -t für jede Tabelle aufgelistet.

pg_dump -a -t Tabellenname -f Dateiname Datenbankname

Erstellen Sie ein Backup mit gz-Komprimierung

Pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c > mydb.gz

Liste der am häufigsten verwendeten Optionen:

-h host - Host, falls nicht angegeben, wird localhost oder der Wert aus der Umgebungsvariable PGHOST verwendet.
-p port - Port, falls nicht angegeben, wird 5432 oder der Wert aus der Umgebungsvariable PGPORT verwendet.
-u - Benutzer, wenn nicht angegeben, wird der aktuelle Benutzer verwendet, der Wert kann auch in der Umgebungsvariable PGUSER angegeben werden.
-a, -data-only – Nur Daten ausgeben, Daten und Schema standardmäßig speichern.
-b - schließt große Objekte (Blogs) in den Dump ein.
-s, -schema-only – Nur Schemas ausgeben.
-C, -create - fügt einen Befehl zum Erstellen einer Datenbank hinzu.
-c - fügt Befehle zum Löschen (Drop) von Objekten (Tabellen, Ansichten usw.) hinzu.
-O Keine Befehle hinzufügen, um den Eigentümer eines Objekts festzulegen (Tabellen, Ansichten usw.).
-F, -format (c|t|p) – Dump-Ausgabeformat, benutzerdefiniert, tar oder einfacher Text.
-t, -table=TABELLE – Geben Sie eine bestimmte Tabelle zum Sichern an.
-v, -verbose - ausführliche Ausgabe.
-D, -attribute-inserts Dump mit dem INSERT-Befehl mit einer Liste von Eigenschaftsnamen.

Sichern Sie alle Datenbanken mit dem Befehl pg_dumpall.

pg_dumpall > all.sql

Tabellen aus Backups wiederherstellen (Backups):

psql - Sicherungen wiederherstellen, die in einer Nur-Text-Datei (einfacher Text) gespeichert sind;
pg_restore - komprimierte Backups wiederherstellen (tar);

Wiederherstellen der gesamten Sicherung mit Ignorieren von Fehlern

psql -h localhost -U irgendein Benutzer -d Datenbankname -f mydb.sql

Wiederherstellen der gesamten Sicherung, Stoppen beim ersten Fehler

psql -h localhost -U irgendein Benutzer -set ON_ERROR_STOP=on -f mydb.sql

Um von einem Tarball wiederherzustellen, müssen wir zuerst eine Datenbank mit CREATE DATABASE mydb erstellen; (falls die Option -C beim Erstellen der Sicherung nicht angegeben wurde) und wiederherstellen

pg_restore -dbname=mydb -jobs=4 -verbose mydb.backup

Wiederherstellen einer mit gz komprimierten Datenbanksicherung

psql -U postgres -d mydb -f mydb

Ich denke, die postgresql-Datenbank wird jetzt für Sie verständlicher sein. Ich hoffe, diese Liste von PostgreSQL-Befehlen war hilfreich für Sie.

In diesem Artikel zeige ich die 15 nützlichsten Befehle zur Verwaltung postgreSQL.

1. Wie ändere ich das Root-Passwort in PostgreSQL?

$ /usr/local/pgsql/bin/psql postgres postgres Passwort: (altes Passwort) # ALTER USER postgres WITH PASSWORD ‘tmppassword’; $ /usr/local/pgsql/bin/psql postgres postgres Passwort: (tmppasswort)

Das Ändern des Passworts für einen normalen Benutzer funktioniert auf die gleiche Weise. Der Root-Benutzer kann das Passwort für jeden Benutzer ändern.

# ALTER USER username WITH PASSWORD ‘tmppassword’;

2. Wie setze ich PostgreSQL auf Autorun?

$ su - root # tar xvfz postgresql-8.3.7.tar.gz # cd postgresql-8.3.7 # cp contrib/start-scripts/linux /etc/rc.d/init.d/postgresql # chmod a+x / etc/rc.d/init.d/postgresql

3. Überprüfen Sie den Status des Servers

$ /etc/init.d/postgresql-Status Passwort: pg_ctl: Server läuft (PID: 6171) /usr/local/pgsql/bin/postgres "-D" "/usr/local/pgsql/data" [ Kommentar: Diese Meldung weist darauf hin, dass der Server betriebsbereit ist und ordnungsgemäß ausgeführt wird] $ /etc/init.d/postgresql-Status Passwort: pg_ctl: kein Server läuft [ Kommentar: Diese Meldung zeigt an, dass der Server nicht läuft]

4. Wie starte, stoppe und starte ich PostgreSQL neu?

# service postgresql stoppen Stoppen von PostgreSQL: Server gestoppt ok # Dienst postgresql starten PostgreSQL starten: ok # Dienst postgresql Neustart Neustart von PostgreSQL: Server gestoppt ok

5. Wie kann ich sehen, welche Version von PostgreSQL läuft?

$ /usr/local/pgsql/bin/psql test Willkommen bei psql 8.3.7, dem interaktiven PostgreSQL-Terminal. Geben Sie Folgendes ein: \copyright für Vertriebsbedingungen \h für Hilfe zu SQL-Befehlen \? für Hilfe zu psql-Befehlen \g oder mit Semikolon beenden, um Abfrage auszuführen \q zum Beenden von Test=# Version auswählen(); Version ————————————————————————————————- PostgreSQL 8.3.7 auf i686-pc-linux-gnu, kompiliert von GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42) (1 Zeile) test=#

5. Wie erstelle ich einen Benutzer in PostgreSQL?

Dafür gibt es zwei Methoden..

Methode 1: Wir erstellen einen Benutzer in der PSQL-Shell mit dem Befehl CREATE USER.

# CREATE USER ramesh WITH password 'tmppassword'; ROLLE ERSTELLEN

Methode2: Wir erstellen einen Benutzer über den Shell-Befehl createuser.

$ /usr/local/pgsql/bin/createuser sathiya Soll die neue Rolle ein Superuser sein? (j/n) Soll die neue Rolle Datenbanken erstellen dürfen? (j/n) Soll die neue Rolle weitere neue Rollen erstellen dürfen? (j/n) n ROLLE ERSTELLEN

6. Wie erstelle ich eine Datenbank in PostgreSQL?

Dafür gibt es 2 Methoden.

Methode1: Erstellen Sie eine Datenbank mithilfe der PSQL-Shell mit dem Befehl CREATE DATABASE.

# DATENBANK mydb MIT BESITZER ERSTELLEN ramesh; DATENBANK ERSTELLEN

Methode2: Wir verwenden den createdb-Befehl.

$ /usr/local/pgsql/bin/createdb mydb -O ramesh DATENBANK ERSTELLEN

7. Eine Liste aller Datenbanken in Postgresql erhalten?

8. Wie lösche ich eine Datenbank in PostgreSQL?

9. Verwenden der integrierten Hilfe zu Befehlen

Team \? zeigt eine Hilfezeile für den Befehl psql an. \h CREATE zeigt eine Hilfe für alle Befehle, die mit CREATE beginnen.

# \? # \h ERSTELLEN # \h INDEX ERSTELLEN

10. Wie bekomme ich eine Liste aller Tabellen in einer Datenbank in Postgresql?

# \d

Bei einer leeren Datenbank erhalten Sie die Meldung „Keine Relationen gefunden“.

11. Wie kann ich die Ausführungszeit einer Anfrage erfahren?

# \timing - Nach Ausführung dieses Befehls zeigt jede nachfolgende Anfrage die Ausführungszeit an.

# \zeitliche Koordinierung Die Zeitmessung ist eingeschaltet. # SELECT * from pg_catalog.pg_attribute ; Zeit: 9,583 ms

12. Wie werden Datenbanken und Tabellen in PostgreSQL gesichert und wiederhergestellt?

Diese Frage ist ziemlich groß und ich werde sie später in einem separaten Artikel veröffentlichen.

13. Wie listet man verfügbare Funktionen in PostgreSQL auf?

Um eine Liste der verfügbaren Funktionen anzuzeigen, sagen Sie \df+

# \df # \df+

14. Wie bearbeite ich eine PostgreSQL-Abfrage im Editor?

# \e

\e öffnet einen Editor, in dem Sie die Abfrage bearbeiten und speichern können.

15. Wo finde ich die postgreSQL-Verlaufsdatei?

Wie die Datei ~/.bash_history speichert postgreSQL alle SQL-Befehle in der Datei ~/.psql_history.

$ Katze ~/.psql_history Benutzer postgres mit Passwort 'tmppassword' ändern; \h Benutzerauswahlversion ändern (); Benutzer Ramesh mit Passwort 'tmppassword' erstellen; \timing select * from pg_catalog.pg_attribute;

Letzte Aktualisierung: 17.03.2018

Tabellen werden mit dem Befehl CREATE TABLE gefolgt vom Tabellennamen erstellt. Sie können mit diesem Befehl auch eine Reihe von Operatoren verwenden, die Tabellenspalten und ihre Attribute definieren. Die allgemeine Syntax zum Erstellen einer Tabelle lautet wie folgt:

CREATE TABLE Tabellenname (Spaltenname1 Datentyp Spaltenattribute1, Spaltenname2 Datentyp Spalte2 Attribute, ..................................... .... .......... SpaltennameN Datentyp SpaltenattributeN, Tabellenattribute);

Hinter dem Tabellennamen steht in Klammern die Angabe für alle Spalten. Darüber hinaus müssen Sie für jede Spalte den Namen und den Datentyp angeben, die sie darstellen soll. Der Datentyp bestimmt, welche Daten (Zahlen, Zeichenfolgen usw.) die Spalte enthalten kann.

Lassen Sie uns beispielsweise mit pgAdmin eine Tabelle in der Datenbank erstellen. Wählen Sie dazu zunächst die Zieldatenbank in pgAdmin aus, klicken Sie mit der rechten Maustaste darauf und wählen Sie im Kontextmenü den Punkt Query Tool...:

Danach öffnet sich ein Feld zur Eingabe des Codes in SQL. Außerdem wird die Tabelle speziell für die Datenbank erstellt, für die wir dieses Feld für die Eingabe von SQL öffnen werden.

CREATE TABLE Kunden (Id SERIAL PRIMARY KEY, FirstName CHARACTER VARYING(30), LastName CHARACTER VARYING(30), Email CHARACTER VARYING(30), Age INTEGER);

In diesem Fall sind fünf Spalten in der Customers-Tabelle definiert: Id, FirstName, LastName, Age, Email. Die erste Spalte Id stellt die Kunden-ID dar, sie dient als Primärschlüssel und ist daher vom Typ SERIAL . Tatsächlich speichert diese Spalte den numerischen Wert 1, 2, 3 usw., der automatisch für jede neue Zeile um eins erhöht wird.

Die nächsten drei Spalten stellen den Vornamen, den Nachnamen und die E-Mail-Adresse des Kunden dar und sind vom Typ CHARACTER VARYING(30) , was bedeutet, dass sie eine Zeichenfolge darstellen, die nicht länger als 30 Zeichen ist.

Die letzte Spalte - Age stellt das Alter des Benutzers dar und ist vom Typ INTEGER , d. h. sie speichert Zahlen.

Und nach Ausführung dieses Befehls wird die Kundentabelle zur ausgewählten Datenbank hinzugefügt.

Tabellen löschen

Verwenden Sie zum Löschen von Tabellen den Befehl DROP TABLE mit der folgenden Syntax:

DROP TABLE Tabelle1 [, Tabelle2, ...];

Beispielsweise das Löschen der Kundentabelle.

15 nützliche PostgreSQL-Befehle

Es gibt viele PostgreSQL-Handbücher im Netz, die die grundlegenden Befehle beschreiben. Aber wenn man tiefer in die Arbeit eintaucht, gibt es solche praktischen Probleme, die fortgeschrittene Befehle erfordern.

Solche Befehle oder Schnipsel werden selten dokumentiert. Sehen wir uns einige Beispiele an, die sowohl für Entwickler als auch für Datenbankadministratoren nützlich sind.

Abrufen von Informationen über die Datenbank

Datenbankgröße

Um die physische Größe der Dateien (Speicher) der Datenbank zu erhalten, verwenden Sie die folgende Abfrage:

SELECT pg_database_size(aktuelle_Datenbank());

Das Ergebnis wird als Zahl wie 41809016 dargestellt.

current_database() ist eine Funktion, die den Namen der aktuellen Datenbank zurückgibt. Stattdessen können Sie einen Namen als Text eingeben:

SELECT pg_database_size("meine_datenbank");

Um Informationen in menschenlesbarer Form zu erhalten, verwenden wir die Funktion pg_size_pretty:

SELECT pg_size_pretty(pg_database_size(current_database()));

Als Ergebnis erhalten wir Informationen wie 40 Mb .

Liste der Tabellen

Manchmal benötigen Sie eine Liste von Datenbanktabellen. Dazu verwenden wir die folgende Abfrage:

SELECT table_name FROM information_schema.tables WHERE table_schema NOT IN ("information_schema","pg_catalog");

information_schema – ein Standard-Datenbankschema, das Sammlungen von Ansichten (Ansichten) wie Tabellen, Felder usw. enthält. Tabellenansichten enthalten Informationen über alle Datenbanktabellen.

Die unten beschriebene Abfrage wählt alle Tabellen aus dem angegebenen Schema der aktuellen Datenbank aus:

SELECT table_name FROM information_schema.tables WHERE table_schema NOT IN ("information_schema", "pg_catalog") AND table_schema IN("public", "myschema");

In der letzten IN-Klausel können Sie den Namen eines bestimmten Schemas angeben.

Tischgröße

Analog zum Ermitteln der Größe einer Datenbank kann die Größe der Daten einer Tabelle mit der entsprechenden Funktion berechnet werden:

SELECT pg_relation_size("Konten");

Die Funktion pg_relation_size gibt die Größe zurück, die die angegebene Schicht der angegebenen Tabelle oder des angegebenen Index auf der Festplatte einnimmt.

Der Name der größten Tabelle

Führen Sie die folgende Abfrage aus, um eine nach Tabellengröße sortierte Liste der Tabellen in der aktuellen Datenbank anzuzeigen:

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;

Um Informationen über die größte Tabelle anzuzeigen, schränken wir die Abfrage mit LIMIT ein:

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC LIMIT 1;

relname ist der Name einer Tabelle, eines Indexes, einer Ansicht usw.
relpages - die Größe der Darstellung dieser Tabelle auf der Festplatte in Seiten (standardmäßig ist eine Seite 8 KB groß).
pg_class ist eine Systemtabelle, die Informationen über Datenbanktabellenbeziehungen enthält.

Liste der verbundenen Benutzer

Um den Namen, die IP und den verwendeten Port der verbundenen Benutzer herauszufinden, führen Sie die folgende Abfrage aus:

WÄHLEN Sie Datenname, Benutzername, Client-Adresse, Client-Port FROM pg_stat_activity;

Benutzeraktivität

Um die Verbindungsaktivität eines bestimmten Benutzers herauszufinden, verwenden Sie die folgende Abfrage:

SELECT dataname FROM pg_stat_activity WHERE usename = "devuser";

Arbeiten mit Daten- und Tabellenfeldern

Doppelte Zeilen entfernen

Wenn es so kommt, dass die Tabelle keinen Primärschlüssel (Primärschlüssel) hat, dann wird es sicher Duplikate unter den Datensätzen geben. Wenn für eine solche Tabelle, insbesondere für eine große, Einschränkungen (Constraint) festgelegt werden müssen, um die Integrität zu überprüfen, entfernen Sie die folgenden Elemente:

doppelte Zeilen
Situationen, in denen eine oder mehrere Spalten dupliziert werden (wenn diese Spalten als Primärschlüssel verwendet werden sollen).

Stellen Sie sich eine Tabelle mit Kundendaten vor, bei der die gesamte Zeile dupliziert wird (die zweite in einer Zeile).

Die folgende Abfrage hilft dabei, alle Duplikate zu entfernen:

DELETE FROM customers WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customers.*);

Das ctid-Feld, das für jeden Datensatz eindeutig ist, ist standardmäßig ausgeblendet, aber es ist in jeder Tabelle vorhanden.

Die letzte Anfrage ist ressourcenintensiv, seien Sie also vorsichtig, wenn Sie sie in einem funktionierenden Projekt ausführen.

Betrachten Sie nun den Fall, wenn Feldwerte wiederholt werden.

Wenn es akzeptabel ist, Duplikate zu löschen, ohne alle Daten zu speichern, führen Sie die folgende Abfrage aus:

DELETE FROM customers WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customer_id);

Wenn die Daten wichtig sind, müssen Sie zuerst Datensätze mit Duplikaten finden:

SELECT * FROM Kunden WHERE ctid NOT IN (SELECT max(ctid) FROM Kunden GROUP BY Kunden-ID);

Bevor Sie solche Datensätze löschen, können Sie sie in eine temporäre Tabelle verschieben oder den darin enthaltenen Wert customer_id durch einen anderen ersetzen.

Die allgemeine Form eines Antrags auf Löschung der oben beschriebenen Aufzeichnungen lautet wie folgt:

DELETE FROM table_name WHERE ctid NOT IN (SELECT max(ctid) FROM table_name GROUP BY Spalte1, );

Änderung des sicheren Feldtyps

Möglicherweise stellt sich die Frage, ob eine solche Aufgabe in diese Liste aufgenommen werden soll. Tatsächlich ist es in PostgreSQL sehr einfach, den Typ eines Felds mit dem ALTER-Befehl zu ändern. Betrachten wir als Beispiel noch einmal die Kundentabelle.

Das Feld customer_id verwendet den Datentyp varchar string. Dies ist ein Fehler, da dieses Feld Kunden-IDs speichern soll, die im Integer-Format vorliegen. Die Verwendung von varchar ist nicht gerechtfertigt. Versuchen wir, dieses Missverständnis mit dem ALTER-Befehl zu beheben:

ALTER TABLE Kunden ALTER COLUMN Kunden-ID TYPE Ganzzahl;

Aber als Ergebnis der Ausführung erhalten wir einen Fehler:

FEHLER: Spalte „customer_id“ kann nicht automatisch in den Typ Integer umgewandelt werden
SQL-Status: 42804
Hinweis: Geben Sie einen USING-Ausdruck an, um die Konvertierung durchzuführen.

Das bedeutet, dass Sie den Feldtyp nicht einfach nehmen und ändern können, wenn Daten in der Tabelle vorhanden sind. Da der Typ varchar verwendet wurde, kann das DBMS nicht feststellen, ob der Wert eine ganze Zahl ist. Obwohl die Daten diesem Typ entsprechen. Um diesen Punkt zu verdeutlichen, schlägt die Fehlermeldung vor, die USING-Klausel zu verwenden, um unsere Daten korrekt in Integer umzuwandeln:

ALTER TABLE Kunden ALTER COLUMN Kunden-ID TYPE Ganzzahl USING (Kunden-ID::Ganzzahl);

Als Ergebnis verlief alles ohne Fehler:

Bitte beachten Sie, dass bei der Verwendung von USING neben einem bestimmten Ausdruck auch Funktionen, andere Felder und Operatoren verwendet werden können.

Konvertieren wir beispielsweise das Feld customer_id zurück in varchar , jedoch mit Datenformatkonvertierung:

ALTER TABLE Kunden ALTER COLUMN Kunden-ID TYPE Varchar USING (Kunden-ID || "-" || Vorname);

Als Ergebnis sieht die Tabelle wie folgt aus:

Suche nach "verlorenen" Werten

Vorsicht bei der Verwendung von Sequenzen als Primärschlüssel: Beim Zuweisen werden einige Elemente der Sequenz versehentlich übersprungen, durch das Arbeiten mit der Tabelle werden einige Datensätze gelöscht. Solche Werte können wieder verwendet werden, sind aber in großen Tabellen schwer zu finden.

Betrachten Sie zwei Suchoptionen.

Erster Weg
Lassen Sie uns die folgende Abfrage ausführen, um den Beginn des Intervalls mit dem "verlorenen" Wert zu finden:

SELECT kunden_id + 1 FROM Kunden mo WHERE NOT EXISTS (SELECT NULL FROM kunden mi WHERE mi.customer_id = mo.customer_id + 1) ORDER BY kunden_id;

Als Ergebnis erhalten wir die Werte: 5 , 9 und 11 .

Wenn Sie nicht nur das erste Vorkommen, sondern alle fehlenden Werte finden müssen, verwenden Sie die folgende (ressourcenintensive!) Abfrage:

WITH seq_max AS (SELECT max(customer_id) FROM Kunden), seq_min AS (SELECT min(customer_id) FROM Kunden) SELECT * FROM generate_series((SELECT min FROM seq_min),(SELECT max FROM seq_max)) EXCEPT SELECT customer_id FROM Kunden;

Als Ergebnis sehen wir folgendes Ergebnis: 5 , 9 und 6 .

Zweiter Weg
Rufen Sie den Namen der Sequenz ab, die mit customer_id verknüpft ist:

SELECT pg_get_serial_sequence("Kunden", "Kunden-ID");

Und finden Sie alle fehlenden Bezeichner:

WITH sequence_info AS (SELECT start_value, last_value FROM "SchemaName", "SequenceName") SELECT generate_series ((sequence_info.start_value), (sequence_info.last_value)) FROM sequence_info EXCEPT SELECT customer_id FROM customers;

Die Anzahl der Zeilen in einer Tabelle zählen

Die Anzahl der Zeilen wird von der Standardzählfunktion berechnet, kann aber mit zusätzlichen Bedingungen verwendet werden.

Gesamtzahl der Zeilen in der Tabelle:

SELECT count(*) FROM-Tabelle;

Anzahl der Zeilen, sofern das angegebene Feld nicht NULL enthält:

SELECT count(col_name) AUS Tabelle;

Anzahl eindeutiger Zeilen für das angegebene Feld:

SELECT count(distinct col_name) FROM table;

Transaktionen verwenden

Eine Transaktion kombiniert eine Abfolge von Aktionen zu einer einzigen Operation. Seine Besonderheit besteht darin, dass im Falle eines Fehlers bei der Ausführung der Transaktion keines der Ergebnisse der Aktionen in der Datenbank gespeichert wird.

Lassen Sie uns eine Transaktion mit dem BEGIN-Befehl starten.

Verwenden Sie den Befehl ROLLBACK, um alle Vorgänge nach BEGIN rückgängig zu machen.

Und zum Anwenden - der COMMIT-Befehl.

Anzeigen und Beenden von ausführbaren Abfragen

Führen Sie den folgenden Befehl aus, um Informationen zu Anforderungen abzurufen:

SELECT pid, age(query_start, clock_timestamp()), usename, query FROM pg_stat_activity WHERE query != " " AND query NOT ILIKE "%pg_stat_activity%" ORDER BY query_start desc;

Um eine bestimmte Anfrage zu stoppen, führen Sie den folgenden Befehl aus und geben Sie dabei die Prozess-ID (PID) an:

SELECT pg_cancel_backend(procpid);

Um die Anfrage zu beenden, führen Sie Folgendes aus:

SELECT pg_terminate_backend(procpid);

Arbeiten mit der Konfiguration

Suchen und Ändern des Speicherorts einer Clusterinstanz

Eine Situation ist möglich, wenn mehrere PostgreSQL-Instanzen auf demselben Betriebssystem konfiguriert sind, die auf verschiedenen Ports „sitzen“. In diesem Fall ist es eine ziemlich nervenaufreibende Aufgabe, einen Pfad zum physischen Standort jeder Instanz zu finden. Um diese Informationen zu erhalten, führen Sie die folgende Abfrage für eine beliebige Datenbank des gewünschten Clusters aus:

SHOW data_directory;

Ändern Sie den Speicherort mit dem Befehl zu einem anderen:

SET data_directory auf new_directory_path;

Es ist jedoch ein Neustart erforderlich, damit die Änderungen wirksam werden.

Abrufen einer Liste der verfügbaren Datentypen

Rufen Sie mit dem Befehl eine Liste der verfügbaren Datentypen ab:

SELECT typname, typlen from pg_type where typtype="b";

typname ist der Name des Datentyps.
typlen - die Größe des Datentyps.

Ändern der DBMS-Einstellungen ohne Neustart

PostgreSQL-Einstellungen befinden sich in speziellen Dateien wie postgresql.conf und pg_hba.conf . Nachdem Sie diese Dateien geändert haben, benötigen Sie das DBMS, um die Einstellungen erneut zu erhalten. Dazu wird der Datenbankserver neu gestartet. Es ist klar, dass Sie dies tun müssen, aber in der Produktionsversion des Projekts, die von Tausenden von Benutzern verwendet wird, ist dies sehr unerwünscht. Daher hat PostgreSQL eine Funktion, mit der Sie Änderungen übernehmen können, ohne den Server neu zu starten:

WÄHLEN Sie pg_reload_conf();

Aber leider trifft es nicht auf alle Parameter zu. In einigen Fällen ist ein Neustart erforderlich, um die Einstellungen zu übernehmen.

Wir haben uns Befehle angesehen, die Entwicklern und DBAs die Arbeit mit PostgreSQL erleichtern. Dies sind jedoch nicht alle möglichen Methoden. Wenn Sie auf interessante Aufgaben stoßen, schreiben Sie in den Kommentaren darüber. Lassen Sie uns unsere nützliche Erfahrung teilen!