Postgresql-etäyhteys. PostgreSQL-tietokannan yhdistäminen Alitietokannan käyttäjien hallinta

PostgreSQL on avoimen lähdekoodin, eri alustojen objektirelaatiotietokantajärjestelmä. Tässä artikkelissa opit asentamaan PostgreSQL:n Ubuntu Linuxiin, muodostamaan yhteyden siihen ja suorittamaan pari yksinkertaista SQL-kyselyä sekä varmuuskopioiden määrittämistä.

Asenna PostgreSQL 9.2 Ubuntu 12.10:een suorittamalla seuraavat komennot:

sudo apt-add-arkisto ppa:pitti/postgresql
sudo apt-get päivitys
sudo apt-get install postgresql-9.2

Yritetään työskennellä DBMS:n kanssa kuoren kautta:

sudo -u postgres psql

Luodaan testitietokanta ja testikäyttäjä:

CREATE DATABASE test_tietokanta;
LUO KÄYTTÄJÄ test_user salasanalla "qwerty" ;
MYÖNTÄ KAIKKI TIETOKANNASSA test_tietokanta testauskäyttäjälle;

Poistu komentotulkista kirjoittamalla komento \q .

Yritetään nyt työskennellä luodun tietokannan kanssa test_userin puolesta:

psql -h localhost testi_tietokanta testi_user

Luodaan uusi taulukko:

CREATE SEQUENCE user_ids;
LUO TABLE käyttäjät (
id INTEGER PRIMARY KEY DEFAULT NEXTVAL ("user_ids") ,
kirjaudu sisään CHAR(64) ,
salasana CHAR(64));

Huomaa, että toisin kuin joissakin muissa tietokantajärjestelmissä, PostgreSQL:ssä ei ole sarakkeita, joissa on auto_increment-ominaisuus. Sen sijaan Postgres käyttää sekvenssejä. Toistaiseksi riittää, että tiedämme, että nextval-funktiolla voimme saada yksilölliset numerot tietylle sekvenssille:

SELECT NEXTVAL ("käyttäjätunnukset" ) ;

Asettamalla käyttäjätaulukon id-kentän oletusarvoksi NEXTVAL("käyttäjätunnukset"), olemme saavuttaneet saman vaikutuksen kuin auto_increment antaa. Kun lisäämme uusia tietueita taulukkoon, meidän ei tarvitse määrittää tunnusta, koska yksilöllinen tunnus luodaan automaattisesti. Useat taulukot voivat käyttää samaa järjestystä. Näin voimme taata, että joidenkin kenttien arvot näissä taulukoissa eivät mene päällekkäin. Tässä mielessä sekvenssit ovat joustavampia kuin auto_increment.

Täsmälleen sama taulukko voidaan luoda yhdellä komennolla:

LUO TABLE käyttäjät2 (
id SERIAL PRIMARY KEY ,
kirjaudu sisään CHAR(64) ,
salasana CHAR(64));

Tässä tapauksessa id-kentän järjestys luodaan automaattisesti.

Nyt käyttämällä \d-komentoa näet luettelon kaikista käytettävissä olevista taulukoista, ja käyttämällä \d users näet kuvauksen käyttäjätaulukosta. Jos et saa etsimääsi tietoa, kokeile \d+ \d:n sijaan. Voit saada luettelon tietokannoista komennolla \l ja vaihtaa tiettyyn tietokantaan komennolla \c dbname. Näytä komentoapu sanomalla \? .

On tärkeää huomata, että PostgreSQL muuntaa taulukoiden ja sarakkeiden nimet oletuksena pieniksi. Jos et halua tällaista toimintaa, voit käyttää lainausmerkkejä:

CREATE TABLE "otherTable" ("someValue" VARCHAR (64 ) ) ;

Toinen PostgreSQL:n ominaisuus, joka voi aiheuttaa vaikeuksia aloitettaessa työskennellä tämän DBMS:n kanssa, ovat niin sanotut "skeemat". Kaava on jotain taulukoiden nimiavaruutta, kuten tietokannan sisällä oleva hakemisto, jossa on taulukoita.

Kaavan luominen:

LUO SCHEMA-varaukset;

Vaihda malliin:

SET hakupolku varauksiin;

Voit tarkastella olemassa olevien mallien luetteloa komennolla \dn. Oletusskeema on julkinen. Periaatteessa voit käyttää PostgreSQL:ää menestyksekkäästi tietämättä skeemojen olemassaolosta. Mutta kun työskentelet vanhan koodin kanssa ja joissakin reunatapauksissa, skeemojen tunteminen voi olla erittäin hyödyllistä.

Muuten PostgreSQL:n kanssa työskentely ei eroa paljon minkään muun relaatiotietokantajärjestelmän kanssa työskentelystä:

INSERT INTO käyttäjiin (kirjautumistunnus, salasana)
ARVOT ("afiskon" , "123456" );
SELECT * FROM käyttäjiltä;

Jos yrität nyt muodostaa yhteyden Postgresiin toiselta koneelta, epäonnistut:

psql -h 192.168.0.1 testitietokanta testikäyttäjä

Psql: ei saatu yhteyttä palvelimeen: Yhteys evätty
Onko palvelin käynnissä isännässä "192.168.0.1" ja hyväksyykö se
TCP/IP-yhteydet portissa 5432?

Korjaa tämä lisäämällä rivi:

listen_addresses = "paikallinen isäntä,192.168.0.1"

...myös /etc/postgresql/9.2/main/postgresql.conf-tiedostoon.

0

Olen järkyttynyt. Meillä on postgres 9.2 -tietokanta, jota olemme käyttäneet jo jonkin aikaa. Nyt meidän on voitava muodostaa yhteys siihen verkkomme ulkopuolelta.

Joten määritämme ulkoisen IP-osoitteen ja testaamme, että voimme muodostaa yhteyden porttiin 5432 etäisännästä Telnetin kautta. Tämä siis todistaa, että verkkoyhteys toimii, palomuurit jne. ovat kaikki kunnossa.

Kun yritän muodostaa yhteyden:
PSQL -h db.host.com -d dbname -p 5432 -U käyttäjä

Tulen takaisin Psql: Palvelin sulki yhteyden odottamatta.

Tarkistin sen
listen_addresses = "*" on asetettu tiedostoon postgresql.conf

Ja tiedostossa pg_hba.conf on rivi, joka lukee (vain testaustarkoituksiin)
isännöi kaikki 0.0.0.0/0 md5

Ja latasin tietokannan uudelleen testatakseni sen valittuja muutoksia.
Joten tämän pitäisi sallia yhteydet mistä tahansa lähteestä.

Tietokantapalvelimen psql-versio on 9.2.14 ja asiakaskoneessa 9.2.13.

Onko ehdotuksia tai ideoita?

• 1 vastaus

Lajittelu:

Toiminta

1

Haluaisin kokeilla muutamia asioita saadakseni lisätietoja:

Postgres tail kirjaudu sisään palvelimeen nähdäksesi mitä siellä tapahtuu, kun yhteys on muodostettu.

Suorita psql --version varmistaaksesi, että se vastaa enemmän tai vähemmän palvelinversiota. (Näen, olet jo tehnyt tämän, mutta jätän sen tähän jälkipolville.)

Suorita strace psql... nähdäksesi kuinka pitkälle se menee epäonnistumiseen.

Tämä kuulostaa edelleen verkko-ongelmalta. Mitä telnet-komentoa käytät? "Ulkoinen IP" kuulostaa samalta kuin AWS:ssä. VPC:n etäkäytön määrittäminen kestää useita vaiheita. Onko teillä muita ulkopuolisia palveluita käynnissä?

Voit myös tehdä vianmäärityksen sammuttamalla Postgres-palvelimen ja käyttämällä nc:tä kuunnellaksesi 5432:ta. Telnet ja katso, voitko lähettää tietoja edestakaisin.

1

Paul, kiitos ehdotuksista. Tein venytyksen ja tarkistin versiot. Ja olit oikeassa, yritin muodostaa yhteyden AWS:stä. Kävi ilmi, että se oli taso, jota käytin kuormituksen tasapainottimessa salliakseen pääsyn vain AWS:n julkaisemien IP-osoitteiden IP-2-alueisiin. Kun otin irulen pois päältä, sain yhteyden heti. Joten tämän täytyy olla surrealistista logiikkaa, koska tarkistin EC2-instanssini julkisen IP-osoitteen ja se todellakin oli listattu iruleen CIDR-ilmoituksen kautta. -

Järjestelmänhallinta

Tämä viesti on lyhyt ohje aloittelijoille, niille, jotka asensivat PostgreSQL:n ensimmäistä kertaa. Tässä on kaikki tiedot, joita tarvitset PostgreSQL:n käytön aloittamiseen.

Yhdistetään DBMS:ään

Ensimmäinen asia on päästä PostgreSQL:ään, pääsy pääkäyttäjänä.
Todennusasetukset ovat pg_hba.conf-tiedostossa.

1. paikalliset kaikki postgres-verkot

Tämä rivi osoittaa, että postgres-käyttäjä voi muodostaa yhteyden mihin tahansa paikalliseen PostgreSQL-tietokantaan pistorasian kautta. Salasanaa ei tarvitse antaa, käyttöjärjestelmä lähettää käyttäjätunnuksen, ja sitä käytetään todentamiseen.
Yhdistetään:

1. $ sudo -u postgres psql postgres postgres

Jotta voit muodostaa yhteyden verkon kautta, sinun on lisättävä rivi tiedostoon pg_hdba.conf:

1. # TYYPPI TIETOKANTA KÄYTTÄJÄOSOITE MENETELMÄ
2. hostssl kaikki kaikki 0.0.0.0/0 md5

Todennus tapa md5 tarkoittaa, että sinun on syötettävä salasana muodostaaksesi yhteyden. Tämä ei ole kovin kätevää, jos käytät usein psql-konsolia. Jos haluat automatisoida jotain, huono uutinen on, että psql ei hyväksy salasanaa argumenttina. On kaksi tapaa ratkaista nämä ongelmat: aseta sopiva ympäristömuuttuja ja tallenna salasana erityiseen .pgpass-tiedostoon.

PGPASSWORD-ympäristömuuttujan asettaminen

Sanon heti, että on parempi olla käyttämättä tätä menetelmää, koska jotkut käyttöjärjestelmät sallivat tavallisten käyttäjien tarkastella ympäristömuuttujia ps:n avulla. Mutta jos haluat, sinun on kirjoitettava terminaaliin:

1. vie PGPASSWORD=mypasswd

Muuttuja on käytettävissä nykyisessä istunnossa. Jos sinun on asetettava muuttuja kaikille istunnoille, sinun on lisättävä esimerkin rivi .bashrc- tai .bash_profile-tiedostoon

Salasanan tallentaminen .pgpass-tiedostoon

Jos puhumme Linuxista, tiedoston tulisi sijaita $HOMEssa (/home/username). Vain omistajalla (0600) on oltava kirjoitus- ja lukuoikeudet. Sinun on kirjoitettava tiedostoon seuraavat rivit:

1. isäntänimi:portti:tietokanta:käyttäjänimi:salasana

Voit kirjoittaa "*" neljään ensimmäiseen kenttään, mikä tarkoittaa, ettei suodatusta (koko valinta).

Aputietojen saaminen

\? - näyttää kaikki saatavilla olevat komennot niiden lyhyen kuvauksen kanssa,
\h - näyttää luettelon kaikista käytettävissä olevista kyselyistä,
\h CREATE - tarjoaa apua tiettyyn pyyntöön.

DBMS-käyttäjien hallinta

Kuinka saada luettelo PostgreSQL-käyttäjistä? Tai voit tehdä kyselyn pg_user-taulukosta.

1. SELECT * FROM pg_user ;

Uuden PostgreSQL-käyttäjän luominen

Psql-kuoressa tämä voidaan tehdä CREATE-komennolla.

1. LUO KÄYTTÄJÄN käyttäjätunnus salasanalla "salasana" ;

Tai voit käyttää terminaalia.

1. Createuser -S -D -R -P käyttäjätunnus

Sinua pyydetään antamaan salasana.

Käyttäjän salasanan vaihtaminen

1. MUUTA KÄYTTÄJÄN käyttäjätunnus SALASANALLA "salasana" ;

Käyttäjäroolien vaihtaminen

Jos haluat antaa käyttäjälle luvan luoda tietokantoja, suorita seuraava kysely:

1. ALTER ROLE käyttäjätunnus WITH CREATEDB ;

Tietokannanhallinta

Tietokantaluettelon näyttäminen psql-päätteessä: Sama Linux-päätteestä:

1. psql -l

Tietokannan luominen psql:stä (PostgreSQL-pääte)

1. LUO TIETOKANTA tietokantanimi OMISTAJA dbadmin ;

Uuden tietokannan luominen terminaalilla:

1. Createdb -O käyttäjätunnus dbname;

Tietokannan käyttöoikeuksien määrittäminen

Jos käyttäjä on tietokannan omistaja, hänellä on kaikki oikeudet. Mutta jos haluat antaa käyttöoikeuden toiselle käyttäjälle, voit tehdä tämän käyttämällä GRANT-komentoa. Alla olevan kyselyn avulla käyttäjä voi muodostaa yhteyden tietokantaan. Älä kuitenkaan unohda pg_hba.conf-määritystiedostoa, sillä sillä on myös oltava asianmukaiset yhteysoikeudet.

1. MYÖNTÄ YHTEYS TIETOKANTAAN dbname DBadminille;

Huomio : Jos työskentelet Windows-käyttöjärjestelmässä etkä ole vielä asentanut PostgreSQL:tä ja Debit Plus V12:ta, voit ladata Debit Plus V12 -kokoonpanon lataussivulta esiasennetun PostgreSQL-tietokannan ja yhdistetyn tietokannan kanssa (peruskokoonpanolla Ukrainaa varten) . Muussa tapauksessa sinun on ensin asennettava PostgreSQL DBMS ja Debit Plus V12 -ohjelmistopaketti, minkä jälkeen voit määrittää yhteyden ja tuoda PostgreSQL-tietokannan näiden ohjeiden mukaisesti.

Jos haluat yhdistää PostgreSQL-tietokannan, sinun on suoritettava seuraavat vaiheet:

Käynnistä "Debit Plus V12" ja lisää uusi kanta (kontekstivalikko "Lisää uusi").

Jätä valintamerkki "Lisää olemassa oleva luetteloon" -ruutuun.

"DBMS" - POSTGRE.

"Tietokantapalvelin" - localhost.

"Tietokannan nimi" - määritä tietokannan nimi PostgreSQL DBMS:stä pienillä latinalaisilla kirjaimilla (pienillä kirjaimilla).

Huomautus: Yleensä käytetään yhtä pohjaa. Erikoistarkoituksiin tietokanta voidaan jakaa useisiin, sitten sinun on valittava "Käytä useita tietokantoja" -kohdan vieressä oleva ruutu, napsauta "Seuraava" -painiketta ja ilmoitettava "tunnisteiden" vastaavuus fyysisiin tietokantoihin. Tämä voi olla tarpeen esimerkiksi, jos useiden yritysten tietokannat viittaavat samoihin hakemistoihin (vastapuolet, tuotteet jne.). Tätä tapausta ei käsitellä enempää.

"Asetushakemisto" - määritä polku PostgreSQL:n tietokanta-asetuksiin (drive\DebetPlusV12\base\pgdpbase).

Tallenna muutokset napsauttamalla "Valmis"-painiketta.

Suorita pgAdmin (PostgreSQL DBMS-järjestelmänvalvoja), lisää palvelin (Tiedosto/Lisää palvelin...) ja lisää uusi tietokanta ("Uusi tietokanta..." -valikko).

Kirjoita palvelimen nimi, localhost Isäntä-kenttään, täytä loput kentät haluamallasi tavalla.

Syötä tietokannan nimi "Nimi"-kenttään (sama kuin "Palvelin"-kenttään "Debit Plus V12" -tietokantaan yhdistämisen asetuksissa).

Jos haluat tuoda tietokannan arkistosta, käytä "Palauta..." -valikkoa

Määritä polku tietokanta-arkistoon dpbase.bakup (asema\DebetPlusV12\base\pgdpbase).

Voit lisätä käyttäjän dpadmin, joka on Debit Plus V12:n järjestelmänvalvoja oletuksena (ilman salasanaa).

Anna kaikki oikeudet tälle käyttäjälle.

Huomautus : Et voi lisätä dpadmin-käyttäjää, vaan tietokantaa aktivoiessasi sinun on syötettävä postgres-järjestelmänvalvojan nimi.

Päivitä tietokantaluettelo (valikko "Päivitä").

Huomautus : Jos et halua antaa käyttäjälle "Superuser"-oikeuksia, anna oikeudet "Taulukoille" ja "Näkymille". Voit tehdä tämän valitsemalla vastaavan objektin ja valitsemalla "Grant Wizard" -kontekstivalikosta kohdan.

Napsauta "Valinta"-välilehdellä "Tarkista kaikki" -painiketta ja "Etuoikeudet"-välilehdellä "Kaikki" ja napsauta "Lisää/Muuta" -painiketta. Tallenna muutokset.

Aktivoi tietokantayhteysasetus Debit Plus V12 -ohjelmistossa (kontekstivalikko "Tee aktiiviseksi").

Järjestelmä pyytää salasanaa muodostaakseen yhteyden tietokantaan. Napsauta Kyllä.

Huomio! Jos tällaista viestiä ei tule näkyviin, aloita tietokantarakenteiden päivittäminen valitsemalla valikosta "Työkalut" / "Päivitä tietokantarakenteita".

Tämän seurauksena seuraava ikkuna tulee näkyviin:

Jätä kaikki valintaruudut oletusarvoiksi (reindex-sarakkeessa on oltava valintamerkit kaikissa tietokantataulukoissa).

Kun muutos on valmis, voit aloittaa työskentelyn.

Käynnistä "Debit Plus V12" ja lisää uusi kanta (kontekstivalikko "Lisää uusi").

Jätä valintamerkki Lisää olemassa oleva luetteloon -kenttään:

Syötä ensimmäiseen kenttään mielivaltainen tietokannan nimi (nimi voi olla erilainen jokaisessa toimivassa tietokoneessa).

"DBMS" - POSTGRE.

"Tietokantapalvelin" - palvelimen nimi tai IP-osoite.

"Tietokannan palvelimen portti" - määritä tietokantapalvelimen portti, oletusarvo on 5432.

"Tietokannan nimi" - kirjoita tietokannan nimi pienillä latinalaisilla kirjaimilla.

"Tietokannan skeeman nimi" - dpbase.

Valitse vain "Käytä Debit Plus -valtakirjaa" -ruutu.

"Asetushakemisto" - määritä polku PostgreSQL:n tietokanta-asetuksiin (verkkopolku\DebetPlusV12\base\pgdpbase).

Tallenna muutokset napsauttamalla "Valmis"-painiketta ja aktivoi tietokanta.

Aseta käynnistysasetukset (päävalikko "Asetukset" / "Käynnistysasetukset")

Syötä näkyviin tulevassa ikkunassa "Salasana asetusten muuttamiseen" -kenttään salasana 150301 ja napsauta "Hyväksy" -painiketta.

Määritä palvelimen JDebet-kansion polku ja napsauta Tallenna. Aktivoi tietokanta ja voit aloittaa työskentelyn.