15 Handige PostgreSQL-opdrachten

Er zijn veel PostgreSQL-tutorials op internet die de basisopdrachten beschrijven. Maar als je dieper in het werk duikt, komen er praktische problemen naar voren waarvoor geavanceerde teams nodig zijn.

Dergelijke opdrachten of fragmenten worden zelden in de documentatie beschreven. Laten we een paar voorbeelden bekijken die nuttig zijn voor zowel ontwikkelaars als databasebeheerders.

Informatie verkrijgen over een database

Databasegrootte

Om de fysieke grootte van de databasebestanden (opslag) te achterhalen, gebruiken we de volgende query:

SELECTEER pg_database_size(huidige_database());

Het resultaat wordt weergegeven als een nummer in de vorm 41809016.

current_database() is een functie die de naam van de huidige database retourneert. In plaats daarvan kunt u de naam in tekst invoeren:

SELECTEER pg_database_size("mijn_database");

Om informatie in voor mensen leesbare vorm te verkrijgen, gebruiken we de functie pg_size_pretty:

SELECTEER pg_size_pretty(pg_database_size(huidige_database()));

Als gevolg hiervan verkrijgen we informatie van het formulier 40 Mb.

Lijst met tabellen

Soms heeft u een lijst met databasetabellen nodig. Hiervoor gebruiken we de volgende query:

SELECTEER tabelnaam UIT informatie_schema.tables WAAR tabelschema NIET IN ("informatie_schema", "pg_catalog");

information_schema is een standaarddatabaseschema dat verzamelingen weergaven bevat, zoals tabellen, velden, enz. Tabelweergaven bevatten informatie over alle tabellen in de database.

De onderstaande query selecteert alle tabellen uit het opgegeven schema van de huidige database:

SELECTEER tabelnaam UIT informatie_schema.tables WAAR tabelschema NIET IN ("informatie_schema", "pg_catalog") EN tabel_schema IN("public", "mijnschema");

De laatste IN-voorwaarde kan worden gebruikt om de naam van een specifiek schema op te geven.

Tafelgrootte

Net als bij het verkrijgen van de grootte van een database, kan de grootte van de gegevens van een tabel worden berekend met behulp van de juiste functie:

SELECTEER pg_relation_size("accounts");

De functie pg_relation_size retourneert de hoeveelheid schijfruimte die een opgegeven laag van een bepaalde tabel of index op schijf in beslag neemt.

Naam van de grootste tafel

Voer de volgende query uit om een ​​lijst met tabellen in de huidige database weer te geven, gesorteerd op tabelgrootte:

SELECTEER relname, relpages VAN pg_class BESTEL OP relpages DESC;

Om informatie over de grootste tabel weer te geven, beperken we de zoekopdracht met LIMIT:

SELECT relname, relpages VAN pg_class BESTEL OP relpages DESC LIMIT 1;

relname - naam van de tabel, index, weergave, etc.
relpages - de grootte van de schijfrepresentatie van deze tabel in aantal pagina's (standaard is één pagina 8 KB).
pg_class is een systeemtabel die informatie bevat over relaties tussen databasetabellen.

Lijst met verbonden gebruikers

Voer de volgende query uit om de naam, het IP-adres en de gebruikte poort van verbonden gebruikers te achterhalen:

SELECT datname,usename,client_addr,client_port FROM pg_stat_activity;

Gebruikersactiviteit

Gebruik de volgende query om de verbindingsactiviteit van een specifieke gebruiker te achterhalen:

SELECTEER datname VAN pg_stat_activity WAAR usename = "devuser";

Werken met gegevens- en tabelvelden

Dubbele rijen verwijderen

Als het zo is dat de tabel geen primaire sleutel heeft, zullen er waarschijnlijk duplicaten tussen de records voorkomen. Als het voor een dergelijke tabel, vooral een grote, noodzakelijk is om beperkingen in te stellen om de integriteit te controleren, verwijder dan de volgende elementen:

• dubbele regels,
• situaties waarin een of meer kolommen worden gedupliceerd (als deze kolommen bedoeld zijn om als primaire sleutel te worden gebruikt).

Laten we een tabel met klantgegevens bekijken, waarin een hele rij (de tweede) wordt gedupliceerd.

Met de volgende zoekopdracht kunt u alle duplicaten verwijderen:

VERWIJDEREN VAN klanten WAAR ctid NIET IN (SELECTEER max(ctid) VAN klanten GROEPEREN OP klanten.*);

Het ctid-veld, uniek voor elk record, is standaard verborgen, maar is aanwezig in elke tabel.

Het laatste verzoek is arbeidsintensief, dus wees voorzichtig bij het uitvoeren ervan voor een productieproject.

Beschouw nu het geval waarin veldwaarden worden herhaald.

Als het toegestaan ​​is om duplicaten te verwijderen zonder alle gegevens op te slaan, zullen wij het volgende verzoek uitvoeren:

VERWIJDER VAN klanten WAAR ctid NIET IN IS (SELECTEER max(ctid) VAN klanten GROEPEREN OP klant_id);

Als de gegevens belangrijk zijn, moet u eerst records met duplicaten vinden:

SELECT * VAN klanten WAAR ctid NIET IN IS (SELECT max(ctid) VAN klanten GROEPEREN OP klant_id);

Voordat ze worden verwijderd, kunnen dergelijke records naar een tijdelijke tabel worden verplaatst of kan de klant_id-waarde daarin worden vervangen door een andere.

De algemene vorm van een verzoek tot verwijdering van de hierboven beschreven records is als volgt:

VERWIJDER UIT tabelnaam WAAR ctid NIET IN (SELECTEER max(ctid) UIT tabelnaam GROEPEREN OP kolom1, );

Veldtype veilig wijzigen

Er kan een vraag zijn over het opnemen van een dergelijke taak in deze lijst. In PostgreSQL is het immers heel eenvoudig om het type van een veld te wijzigen met behulp van het ALTER-commando. Laten we als voorbeeld nogmaals naar de klantentabel kijken.

Het veld klant_id gebruikt het gegevenstype Varchar-tekenreeks. Dit is een fout omdat dit veld klant-ID's zou moeten opslaan, die in geheel getalformaat zijn. Het gebruik van varchar is niet gerechtvaardigd. Laten we proberen dit misverstand te corrigeren met behulp van het ALTER-commando:

ALTER TABLE klanten ALTER KOLOM klant_id TYPE geheel getal;

Maar als gevolg van de uitvoering krijgen we een foutmelding:

FOUT: kolom “klant_id” kan niet automatisch worden omgezet naar het type geheel getal
SQL-status: 42804
Tip: Geef een USING-expressie op om de conversie uit te voeren.

Dit betekent dat u het type van een veld niet zomaar kunt wijzigen als er gegevens in de tabel staan. Omdat het varchar-type is gebruikt, kan het DBMS niet bepalen of de waarde een geheel getal is. Hoewel de gegevens precies overeenkomen met dit type. Om dit punt te verduidelijken, stelt het foutbericht voor om de USING-expressie te gebruiken om onze gegevens correct naar een geheel getal te converteren:

ALTER TABLE klanten ALTER KOLOM klant_id TYPE geheel getal USING (klant_id::geheel getal);

Als gevolg hiervan verliep alles zonder fouten:

Houd er rekening mee dat bij het gebruik van USING het naast een specifieke expressie mogelijk is om functies, andere velden en operators te gebruiken.

Laten we bijvoorbeeld het veld customer_id terug converteren naar varchar , maar met een conversie van de gegevensindeling:

ALTER TABLE klanten ALTER KOLOM klant_id TYPE varchar USING (klant_id || "-" || voornaam);

Als gevolg hiervan zal de tabel er als volgt uitzien:

Het vinden van "verloren" waarden

Wees voorzichtig bij het gebruik van reeksen als primaire sleutel: tijdens de toewijzing worden sommige elementen van de reeks per ongeluk overgeslagen en als gevolg van het werken met de tabel worden sommige records verwijderd. Dergelijke waarden kunnen opnieuw worden gebruikt, maar zijn moeilijk te vinden in grote tabellen.

Laten we twee zoekopties bekijken.

Eerste manier
Laten we de volgende query uitvoeren om het begin van het interval met de "verloren" waarde te vinden:

SELECTEER klant_id + 1 VAN klanten mo WAAR NIET BESTAAT (SELECTEER NULL VAN klanten mi WAAR mi.klant_id = ma.klant_id + 1) BESTEL OP klant_id;

Als resultaat krijgen we de volgende waarden: 5, 9 en 11.

Als u niet alleen het eerste exemplaar, maar ook alle ontbrekende waarden wilt vinden, gebruiken we de volgende (bronintensieve!) query:

MET seq_max AS (SELECT max(klant_id) VAN klanten), seq_min AS (SELECT min(klant_id) VAN klanten) SELECT * FROM genereren_series((SELECT min VAN seq_min),(SELECT max VAN seq_max)) BEHALVE SELECT klant_id VAN klanten;

Als resultaat zien we het volgende resultaat: 5, 9 en 6.

Tweede manier
We krijgen de naam van de reeks die is gekoppeld aan klant_id:

SELECTEER pg_get_serial_sequence("klanten", "klant_id");

En we vinden alle ontbrekende identificatiegegevens:

MET sequence_info AS (SELECT start_waarde, laatste_waarde VAN "SchemaNaam". "SequenceName") SELECTEER genere_series ((sequence_info.start_value), (sequence_info.last_value)) VAN sequence_info BEHALVE SELECT klant_id VAN klanten;

Het aantal rijen in een tabel tellen

Het aantal rijen wordt berekend door de standaard telfunctie, maar kan worden gebruikt met aanvullende voorwaarden.

Totaal aantal rijen in de tabel:

SELECT aantal(*) FROM tabel;

Aantal rijen, op voorwaarde dat het opgegeven veld geen NULL bevat:

SELECT aantal(col_name) FROM tabel;

Aantal unieke rijen voor het opgegeven veld:

SELECT aantal(verschillende kolomnaam) FROM tabel;

Transacties gebruiken

Een transactie combineert een reeks acties in één handeling. Het bijzondere is dat als er een fout optreedt bij het uitvoeren van een transactie, geen van de resultaten van de acties in de database wordt opgeslagen.

Laten we de transactie starten met behulp van het BEGIN-commando.

Om alle bewerkingen na BEGIN terug te draaien, gebruikt u de opdracht ROLLBACK.

En om het toe te passen: het COMMIT-commando.

Bekijk en beëindig actieve query's

Om informatie over aanvragen te verkrijgen, voert u de volgende opdracht uit:

SELECT pid, age(query_start, clock_timestamp()), usename, query FROM pg_stat_activity WHERE query != " " EN zoekopdracht NIET leuk "%pg_stat_activity%" BESTEL OP query_start desc;

Om een ​​specifiek verzoek te stoppen, voert u het volgende commando uit, waarbij u het proces-ID (pid) aangeeft:

SELECTEER pg_cancel_backend(procpid);

Voer het volgende uit om het verzoek te stoppen:

SELECTEER pg_terminate_backend(procpid);

Werken met configuratie

De locatie van een clusterinstantie zoeken en wijzigen

Er is een situatie mogelijk wanneer meerdere PostgreSQL-instanties op één besturingssysteem zijn geconfigureerd, die op verschillende poorten ‘zitten’. In dit geval is het vinden van een manier om elke instantie fysiek te plaatsen een nogal zenuwslopende taak. Om deze informatie te verkrijgen, voert u de volgende query uit voor elke database in het betreffende cluster:

TOON gegevensmap;

Laten we de locatie naar een andere wijzigen met behulp van de opdracht:

SET data_directory naar nieuw_directory_pad;

Maar voordat de wijzigingen van kracht worden, is opnieuw opstarten vereist.

Een lijst met beschikbare gegevenstypen ophalen

We krijgen een lijst met beschikbare gegevenstypen met behulp van de opdracht:

SELECT typnaam, typlen uit pg_type waarbij typtype="b";

typnaam - naam van het gegevenstype.
typelen - grootte van het gegevenstype.

DBMS-instellingen wijzigen zonder opnieuw op te starten

PostgreSQL-instellingen bevinden zich in speciale bestanden zoals postgresql.conf en pg_hba.conf. Na het wijzigen van deze bestanden moet het DBMS de instellingen opnieuw ontvangen. Om dit te doen, wordt de databaseserver opnieuw opgestart. Het is duidelijk dat je dit moet doen, maar op de productieversie van het project, die door duizenden gebruikers wordt gebruikt, is dit zeer ongewenst. Daarom heeft PostgreSQL een functie waarmee u wijzigingen kunt toepassen zonder de server opnieuw op te starten:

SELECTEER pg_reload_conf();

Maar helaas is dit niet van toepassing op alle parameters. In sommige gevallen is opnieuw opstarten vereist voordat de instellingen worden toegepast.

We hebben gekeken naar opdrachten die het werk van ontwikkelaars en databasebeheerders die PostgreSQL gebruiken, zullen helpen vereenvoudigen. Maar dit zijn niet alle mogelijke methoden. Als je interessante taken bent tegengekomen, schrijf er dan over in de reacties. Laten we nuttige ervaringen delen!

In dit artikel laat ik je de 15 handigste commando's zien voor beheren postgreSQL.

1. Hoe verander ik het root-wachtwoord in PostgreSQL?

$ /usr/local/pgsql/bin/psql postgres postgres Wachtwoord: (oudwachtwoord) # ALTER GEBRUIKER postgres MET WACHTWOORD 'tmppassword'; $ /usr/local/pgsql/bin/psql postgres postgres Wachtwoord: (tmpwachtwoord)

Het wijzigen van het wachtwoord voor een gewone gebruiker gebeurt op dezelfde manier. De rootgebruiker kan het wachtwoord voor elke gebruiker wijzigen.

# ALTER GEBRUIKER gebruikersnaam MET WACHTWOORD 'tmppassword';

2. Hoe installeer ik PostgreSQL in autostart?

$ 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. Controleer de serverstatus

$ /etc/init.d/postgresql-status Wachtwoord: pg_ctl: server draait (PID: 6171) /usr/local/pgsql/bin/postgres “-D” “/usr/local/pgsql/data” [ Opmerking: Dit bericht geeft aan dat de server actief is en normaal werkt] $ /etc/init.d/postgresql-status Wachtwoord: pg_ctl: geen server actief [ Opmerking: Dit bericht geeft aan dat de server niet actief is]

4. Hoe PostgreSQL starten, stoppen en opnieuw opstarten?

# service postgresql stop PostgreSQL stoppen: server gestopt ok # service postgresql starten PostgreSQL starten: ok # service postgresql opnieuw opstarten Herstarten van PostgreSQL: server gestopt ok

5. Hoe kan ik zien welke versie van PostgreSQL actief is?

$ /usr/local/pgsql/bin/psql-test Welkom bij psql 8.3.7, de interactieve PostgreSQL-terminal. Type: \copyright voor distributievoorwaarden \h voor hulp bij SQL-opdrachten \? voor hulp bij psql-opdrachten \g of sluit af met een puntkomma om de query uit te voeren \q om test=# af te sluiten selecteer versie(); versie ————————————————————————————————— PostgreSQL 8.3.7 op i686-pc-linux-gnu, samengesteld door GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42) (1 rij) test=#

5. Hoe maak ik een gebruiker aan in PostgreSQL?

Hiervoor zijn twee methoden..

Methode 1: We maken een gebruiker aan met behulp van de PSQL-shell met behulp van de opdracht CREATE USER.

# CREËER GEBRUIKER ramesh MET wachtwoord 'tmppassword'; CREËER ROL

Methode2: We maken een gebruiker aan met behulp van de shell-opdracht createuser.

$ /usr/local/pgsql/bin/createuser sathiya Zal de nieuwe rol een superuser zijn? (y/n) n Mag de nieuwe rol databases maken? (j/n) n Zal de nieuwe rol meer nieuwe rollen mogen creëren? (y/n) n ROL MAKEN

6. Hoe maak ik een database in PostgreSQL?

Hiervoor zijn 2 methoden.

Methode1: We maken een database via een PSQL-shell met behulp van de opdracht CREATE DATABASE.

# MAAK DATABASE mydb MET EIGENAAR ramesh; DATABANK MAKEN

Methode2: We gebruiken het commando createb.

$ /usr/local/pgsql/bin/createdb mydb -O ramesh DATABANK MAKEN

7. Krijgen we een lijst met alle databases in Postgresql?

# \l Lijst met databases Naam | Eigenaar | Codering ———-+———-+———- back-up | postgres | UTF8 mijndb | ramesh | UTF8 postgres | postgres | UTF8-sjabloon0 | postgres | UTF8-sjabloon1 | postgres | UTF8

8. Hoe verwijder ik een database in PostgreSQL?

# \l Lijst met databases Naam | Eigenaar | Codering ———-+———-+———- back-up | postgres | UTF8 mijndb | ramesh | UTF8 postgres | postgres | UTF8-sjabloon0 | postgres | UTF8-sjabloon1 | postgres | UTF8# DROP DATABASE mydb; DATABANK DROP

9. Gebruik de ingebouwde help voor opdrachten

Team\? zal een hulplijn weergeven voor de PSQL-opdracht. \h CREATE toont hulp voor alle opdrachten die beginnen met CREATE.

# \? # \h MAKEN # \h INDEX MAKEN

10. Hoe krijg ik een lijst met alle tabellen in een bepaalde database in Postgresql?

# \D

Bij een lege database krijgt u de melding “Geen relaties gevonden.”

11. Hoe kan ik de uitvoeringstijd van een verzoek achterhalen?

# \timing - na het uitvoeren van dit commando zal bij elk volgend verzoek de uitvoeringstijd worden weergegeven.

# \timing De timing staat aan. # SELECTEER * uit pg_catalog.pg_attribute; Tijd: 9,583 ms

12. Hoe maak ik een back-up van databases en tabellen in PostgreSQL en herstel ik deze?

Deze vraag is vrij groot en ik zal deze later in een apart artikel publiceren.

13. Hoe kan ik de lijst met beschikbare functies in PostgreSQL bekijken?

Om een ​​lijst met beschikbare functies te krijgen, zegt u \df+

# \df # \df+

14. Hoe bewerk ik een PostgreSQL-query in de editor?

# \ e

\e opent een editor waarin u de query kunt bewerken en opslaan.

15. Waar kan ik het postgreSQL-geschiedenisbestand vinden?

Net als bij het bestand ~/.bash_history slaat postgreSQL alle SQL-opdrachten op in het bestand ~/.psql_history.

$ cat ~/.psql_geschiedenis verander gebruiker postgres met wachtwoord 'tmppassword'; \h wijzig gebruiker geselecteerde versie(); maak gebruiker ramesh aan met wachtwoord ‘tmppassword’; \timing select * uit pg_catalog.pg_attribute;

Hallo allemaal, vandaag wil ik een korte herinnering maken over de belangrijkste PostgreSQL-opdrachten. Je kunt zowel interactief als vanaf de opdrachtregel met PosgreSQL werken. Het programma is psql. Ik ben er zeker van dat deze lijst zeer nuttig voor u zal zijn en u tijd zal besparen bij het doorzoeken van verschillende bronnen. Laat me je eraan herinneren dat dit een open-sourceproject is, gebaseerd op het Postgres DBMS, uitgebracht in 1986. Het wordt ontwikkeld door een wereldwijde groep PGDG-ontwikkelaars, in wezen 5-8 mensen, maar desondanks ontwikkelt het zich zeer intensief. , het introduceren van nieuwe functies en het oplossen van oude bugs en fouten.

Basis PostgreSQL-opdrachten in interactieve modus:

• \connect db_name – maak verbinding met de database met de naam db_name
• \du – lijst met gebruikers
• \dp (of \z) – lijst met tabellen, views, reeksen, toegangsrechten daartoe
• \di – indices
• \ds – reeksen
• \dt – lijst met tabellen
• \dt+ - lijst met alle tabellen met beschrijvingen
• \dt *s* - lijst met alle tabellen die s in de naam bevatten
• \dv – representaties
• \dS – systeemtabellen
• \d+ – tabelbeschrijving
• \o – stuur queryresultaten naar een bestand
• \l – lijst met databases
• \i – lees inkomende gegevens uit een bestand
• \e – opent de huidige inhoud van de verzoekbuffer in de editor (tenzij anders aangegeven in de omgeving van de EDITOR-variabele, wordt vi standaard gebruikt)
• \d “tabelnaam” – beschrijving van de tabel
• \i een opdracht uitvoeren vanuit een extern bestand, bijvoorbeeld \i /mijn/directory/mijn.sql
• \pset – opdracht om opmaakopties te configureren
• \echo – geeft een bericht weer
• \set – Stelt de waarde van een omgevingsvariabele in. Zonder parameters wordt een lijst met huidige variabelen weergegeven (\unset – verwijdert).
• \? – psql-referentie
• \help – SQL-referentie
• \q (of Ctrl+D) – sluit het programma af

Werken met PostgreSQL vanaf de opdrachtregel:

• -c (of –command) – voer een SQL-opdracht uit zonder naar de interactieve modus te gaan
• -f bestand.sql - voer opdrachten uit bestand bestand.sql uit
• -l (of –lijst) – toont een lijst met beschikbare databases
• -U (of –gebruikersnaam) – specificeer de gebruikersnaam (bijvoorbeeld postgres)
• -W (of –wachtwoord) – wachtwoordprompt
• -d dbname - maak verbinding met de dbname-database
• -h – hostnaam (server)
• -s – stapsgewijze modus, dat wil zeggen dat u alle opdrachten moet bevestigen
• –S – modus met één regel, dat wil zeggen dat als u naar een nieuwe regel gaat, de query wordt uitgevoerd (verwijdert; aan het einde van de SQL-constructie)
• -V – versie van PostgreSQL zonder de interactieve modus te openen

Voorbeelden:

psql -U postgres -d dbname -c “CREATE TABLE my(some_id serial PRIMARY SLEUTEL, some_text text);” - uitvoering van een commando in de dbname-database.

psql -d dbname -H -c “SELECT * FROM my” -o my.html - voer het queryresultaat uit naar een HTML-bestand.

PosgreSQL-hulpprogramma's (programma's):

• createb en dropdb - respectievelijk een database maken en verwijderen
• createuser en dropuser - creatie en gebruiker (respectievelijk)
• pg_ctl – een programma dat is ontworpen om algemene besturingstaken op te lossen (starten, stoppen, parameters instellen, enz.)
• postmaster – PostgreSQL-servermodule voor meerdere gebruikers (debug-niveaus, poorten, gegevensmappen configureren)
• initdb – nieuwe PostgreSQL-clusters maken
• initlocation – een programma voor het maken van mappen voor secundaire databaseopslag
• vacuümdb – fysieke en analytische database-ondersteuning
• pg_dump – archiveren en herstellen van gegevens
• pg_dumpall – maak een back-up van het volledige PostgreSQL-cluster
• pg_restore – databaseherstel vanuit archieven (.tar, .tar.gz)

Voorbeelden van het maken van back-ups:

Het maken van een back-up van de mydb-database, in gecomprimeerde vorm

Pg_dump -h localhost -p 5440 -U een gebruiker -F c -b -v -f mydb.backup mydb

Het maken van een back-up van de mydb-database, in de vorm van een regulier tekstbestand, inclusief de opdracht om de database aan te maken

Pg_dump -h localhost -p 5432 -U een gebruiker -C -F p -b -v -f mydb.backup mydb

Het maken van een back-up van de mydb-database, in gecomprimeerde vorm, met tabellen met betalingen in de naam

Pg_dump -h localhost -p 5432 -U een gebruiker -F c -b -v -t *betalingen* -f payment_tables.backup mydb

Dump gegevens uit slechts één specifieke tabel. Als er van meerdere tabellen een back-up moet worden gemaakt, worden de tabelnamen voor elke tabel vermeld met de schakeloptie -t.

Pg_dump -a -t tabelnaam -f bestandsnaam databasenaam

Een back-up maken met gz-compressie

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

Lijst met meest gebruikte opties:

• -h host - host, als dit niet is opgegeven, wordt localhost of de waarde uit de omgevingsvariabele PGHOST gebruikt.
• -p port - port; indien niet gespecificeerd, wordt 5432 of de waarde van de PGPORT-omgevingsvariabele gebruikt.
• -u - user, indien niet gespecificeerd, wordt de huidige gebruiker gebruikt, de waarde kan ook worden gespecificeerd in de PGUSER-omgevingsvariabele.
• -a, -data-only - alleen gegevens dumpen; gegevens en schema worden standaard opgeslagen.
• -b - neem grote objecten (blogs) op in de dump.
• -s, -schema-only - dump alleen het schema.
• -C, -create - voegt een opdracht toe om een ​​database aan te maken.
• -c - voegt opdrachten toe om objecten (tabellen, weergaven, enz.) te verwijderen (neerzetten).
• -O - ​​​​voeg geen opdrachten toe om de eigenaar van een object in te stellen (tabellen, weergaven, enz.).
• -F, -format (c|t|p) - dump uitvoerformaat, aangepast, tar of platte tekst.
• -t, -table=TABLE - specificeer een specifieke tabel voor de dump.
• -v, -verbose - voer gedetailleerde informatie uit.
• -D, -attribute-inserts - dump met behulp van de opdracht INSERT met een lijst met eigenschapsnamen.

Maak een back-up van alle databases met de opdracht pg_dumpall.

Pg_dumpall > all.sql

Tabellen herstellen vanuit back-ups:

psql - back-ups herstellen die zijn opgeslagen in een tekstbestand;
pg_restore - gecomprimeerde back-ups herstellen (tar);

Een volledige back-up herstellen zonder fouten te negeren

Psql -h localhost -U iemandgebruiker -d dbnaam -f mijndb.sql

Een volledige back-up herstellen en stoppen bij de eerste fout

Psql -h localhost -U een gebruiker -set ON_ERROR_STOP=on -f mydb.sql

Om te herstellen vanuit een tar-archief, moeten we eerst een database maken met behulp van CREATE DATABASE mydb; (als de optie -C niet was opgegeven bij het maken van de back-up) en herstel

Pg_restore -dbname=mijndb -jobs=4 -verbose mijndb.backup

Een met gz gecomprimeerde databaseback-up herstellen

psql -U postgres -d mijndb -f mijndb

Ik denk dat de postgresql-database nu begrijpelijker voor je zal zijn. Ik hoop dat deze lijst met PostgreSQL-opdrachten nuttig voor je was.

Laatste update: 17-03-2018

Om tabellen te maken, gebruikt u de opdracht CREATE TABLE, gevolgd door de tabelnaam. U kunt met deze opdracht ook een aantal operators gebruiken die tabelkolommen en hun attributen definiëren. De algemene syntaxis voor het maken van een tabel is als volgt:

MAAK TABEL tabelnaam (kolomnaam1 datatype kolomattributen1, kolomnaam2 datatype kolomattributen2, ..................................... . ......... kolom_naamN data_type kolom_attributenN, tabel_attributen);

Na de tabelnaam wordt tussen haakjes de specificatie voor alle kolommen vermeld. Bovendien moet u voor elke kolom de naam en het type gegevens opgeven dat deze zal vertegenwoordigen. Het datatype bepaalt welke gegevens (getallen, strings, etc.) een kolom kan bevatten.

Laten we bijvoorbeeld een tabel in de database maken met behulp van pgAdmin. Om dit te doen, selecteert u eerst de doeldatabase in pgAdmin, klikt u er met de rechtermuisknop op en selecteert u Query Tool... in het contextmenu:

Hierna wordt een veld geopend voor het invoeren van SQL-code. Bovendien wordt de tabel specifiek gemaakt voor de database waarvoor we dit SQL-invoerveld openen.

CREATE TABLE-klanten (Id SERIËLE PRIMAIRE SLEUTEL, Voornaam KARAKTER VARYING(30), Achternaam KARAKTER VARYING(30), E-mail KARAKTER VARYING(30), Leeftijd INTEGER);

In dit geval zijn er vijf kolommen gedefinieerd in de tabel Klanten: Id, Voornaam, Achternaam, Leeftijd, E-mailadres. De eerste kolom, Id, vertegenwoordigt de client-ID, deze dient als primaire sleutel en is daarom van het type SERIAL. In feite zal deze kolom de numerieke waarde 1, 2, 3, enz. opslaan, die voor elke nieuwe rij automatisch met één wordt verhoogd.

De volgende drie kolommen vertegenwoordigen de voornaam, achternaam en het e-mailadres van de klant en zijn van het type CHARACTER VARYING(30) , wat betekent dat ze een reeks van niet meer dan 30 tekens vormen.

De laatste kolom, Leeftijd, vertegenwoordigt de leeftijd van de gebruiker en is van het type INTEGER, wat betekent dat er getallen in worden opgeslagen.

En na het uitvoeren van deze opdracht wordt de klantentabel toegevoegd aan de geselecteerde database.

Tabellen verwijderen

Om tabellen te verwijderen, gebruikt u de opdracht DROP TABLE, die de volgende syntaxis heeft:

DROP TABLE tabel1 [, tabel2, ...];

Bijvoorbeeld het verwijderen van de klantentabel.

Een alternatief voor het werken met de database via de console is de meer intuïtieve omgeving van elke GUI-client. Bijvoorbeeld, pgAdmin. Het installeren is heel eenvoudig, we voeren het uit met superuser-rechten:

sudo apt-get install pgadmin3

Nu kun je rennen pgAdmin via grafische interface. We selecteren de lokale databaseserver uit de lijst (deze heeft de standaardpoort 5432) en voegen de database toe die we al hebben gemaakt met de parameters die we hebben opgegeven.

pgAdmin

In deze lokale server naast het knooppunt Databases je kunt ook een knooppunt vinden met de naam Inlogrollen- alle beschikbare rollen.

Laten we uit alle beschikbare databases degene selecteren die we hebben gemaakt met . De database bevat veel verschillende soorten objecten. Onder hen moet speciale aandacht worden besteed aan tabellen en reeksen ( sequenties).

Meestal bevat een gewone tabel een primaire sleutel in de vorm van een positieve numerieke waarde. Voor elke rij moet deze waarde uniek zijn. Om deze niet elke keer direct in de aanvraag op te geven, kunt u de reeks als standaardwaarde instellen.

Laten we eerst een reeks maken. Kiezen Reeksen - Nieuwe reeks. Voer op het eerste tabblad een naam in en specificeer als Eigenaar de rol die we hebben gecreëerd. Deze reeks is niet beschikbaar voor andere rollen. Op het tweede tabblad in de velden Verhogen En Begin voer één voor één in (tenzij u iets anders nodig heeft). Op het laatste tabblad van het dialoogvenster ziet u de resulterende SQL-query die naar de database wordt uitgevoerd.

Zodra de reeks is gemaakt, gaan we beginnen met het maken van de tabel. We vermelden ook de naam en de eigenaar. Op het vierde tabblad Kolommen Allereerst voegen we de primaire sleutel toe. Knop Toevoegen Geef in het venster dat verschijnt de naam van de kolom op, bijvoorbeeld Identiteitskaart. Voor gegevenstype selecteren we grootint. Op het tweede tabblad Definitie in het veld Standaardwaarde Wij geven onze volgorde aan. Het veld moet een waarde bevatten zoals nextval("message_id_seq"::regclass). Dat wil zeggen dat elke keer dat er een nieuwe rij wordt toegevoegd, de volgende waarde uit de reeks wordt gehaald. Indien nodig andere kolommen toegevoegd. Tenslotte op het tabblad Beperkingen voeg een beperking toe aan de primaire sleutel ( Primaire sleutel). Op het laatste tabblad kunnen we de resulterende SQL-code bewonderen die pgAdmin heeft gegenereerd. Nadat u op OK hebt geklikt, wordt de tabel aangemaakt.

In plaats van bigint als primaire sleutel, kunt u deze opgeven als kolomtype grootserieel. Dit type wordt automatisch verhoogd naarmate elke nieuwe rij wordt toegevoegd, dus het is niet nodig om er een reeks voor te maken. Dat wil zeggen dat in het eenvoudigste geval, als u geen speciale regels heeft voor het genereren van record-ID's, we u kunnen aanbevelen bigserial te gebruiken.

Laten we eens kijken naar de inhoud van onze tabel. Om dit te doen, klikt u er met de rechtermuisknop op en selecteert u het in het contextmenu Gegevens bekijken - Bekijk 100 rijen.

In hetzelfde venster kunt u snel de waarde van elke tabelcel bewerken. Als uw tabel meer dan 100 records bevat, wijzigt u het aantal records dat wordt weergegeven in de vervolgkeuzelijst bovenaan het venster. U kunt 100, 500, 1000 of alle vermeldingen weergeven. Maar dit is niets meer dan een manier om snel de inhoud van de tabel te bekijken en je moet er niet aan wennen. Als uw tabel tienduizenden records bevat, raad ik u af om alle records in één keer weer te geven. In dit geval is het beter om een ​​query te schrijven met behulp van parameters beperken En gecompenseerd.