Yadda ake Shigar da Amfani da PostgreSQL akan Ubuntu 18.04


PostgreSQL (Postgres a takaice) buɗaɗɗen tushe ne, mai ƙarfi, ci gaba, babban aiki da kwanciyar hankali tsarin bayanan bayanai. Yana amfani da haɓaka yaren SQL haɗe tare da ɗimbin fasali don amintaccen ajiyar bayanai da sarrafa bayanai.

Yana da inganci, abin dogaro, kuma mai iya daidaitawa don sarrafa manyan, rikitattun ɗimbin bayanai da kafa matakan kasuwanci da kuma mahalli masu jure wa kuskure, tare da tabbatar da ingantaccen amincin bayanai. Postgres kuma yana da fa'ida sosai tare da fasali kamar fihirisa suna zuwa tare da APIs don ku iya haɓaka hanyoyinku don magance ƙalubalen ajiyar bayanan ku.

A cikin wannan labarin, zamuyi bayanin yadda ake shigar da PostgreSQL akan uwar garken Ubuntu 18.04 (kuma yana aiki akan tsoffin sakin Ubuntu) kuma mu koyi wasu mahimman hanyoyin amfani da shi.

Yadda ake Sanya PostgreSQL akan Ubuntu

Da farko, ƙirƙiri fayil /etc/apt/sources.list.d/pgdg.list wanda ke adana tsarin ma'ajiyar, sannan shigo da maɓallin ma'ajin zuwa tsarin ku, sabunta jerin fakitin tsarin ku kuma shigar da kunshin Postgres ta amfani da bin umarni.

$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
$ sudo apt install wget ca-certificates
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
$ sudo apt update
$ sudo apt install postgresql-10 pgadmin4 

Da zarar an shigar da postgres, sabis ɗin bayanan yana farawa ta atomatik kuma zaku iya tabbatarwa ta hanyar buga bin umarni.

$ sudo systemctl status postgresql.service

Yadda ake Amfani da Matsayin PostgreSQL da Databases

A cikin postgres, fayil ɗin daidaitawa /etc/postgresql/10/main/pg_hba.conf yana sarrafa amincin abokin ciniki. Tsohuwar hanyar tantancewa ita ce \peer ga mai gudanar da bayanai, ma'ana yana samun sunan mai amfani da tsarin aiki na abokin ciniki daga tsarin aiki kuma yana bincika idan ya dace da sunan mai amfani da bayanai don ba da damar shiga, don haɗin gida (kamar yadda aka nuna a cikin mai zuwa. screenshot).

A yayin aikin shigarwa, an ƙirƙiri asusun mai amfani da tsarin mai suna postgres ba tare da kalmar sirri ba, wannan kuma shine sunan mai amfani da mai sarrafa bayanai na asali.

$ sudo vim /etc/postgresql/10/main/pg_hba.conf

Bugu da kari, a karkashin postgres ana gudanar da izinin samun damar bayanai ta hanyar ayyuka. Ana iya ɗaukar rawar a matsayin ko dai mai amfani da bayanai, ko ƙungiyar masu amfani da bayanai, dangane da yadda aka saita rawar.

Matsayin tsoho kuma shine postgres. Mahimmanci, ayyukan bayanai a zahiri ba su da alaƙa da masu amfani da tsarin aiki, amma a zahiri ƙila ba za su rabu ba (misali idan ya zo ga tantancewar abokin ciniki).

Mahimmanci, ayyuka na iya mallakar abubuwan bayanai, kuma suna iya ba wa waɗannan abubuwan gata ga wasu ayyuka don sarrafa wanda ke da damar yin amfani da abubuwan. Bugu da kari, yana yiwuwa a ba da izinin zama memba a cikin rawar zuwa wani rawar.

Don saita wasu ayyuka don amfani da rufaffen kalmomin shiga don sarrafa bayanan da aka sanya musu, baya ga tsohowar aikin postgres, kuna buƙatar canza layin zuwa.

Then restart the postgresql service to apply the recent changes.
$ sudo systemctl restart postgresql

Yadda ake Amfani da PostgreSQL akan Ubuntu

Da zarar an saita komai, zaku iya samun damar asusun tsarin postgres tare da umarni mai zuwa, inda alamar -i ta gaya wa sudo don gudanar da harsashi da aka ayyana ta hanyar shigar da bayanan kalmar sirri na mai amfani azaman harsashi mai shiga.

$ sudo -i -u postgres 
$ psql		#to launch the postgres shell program  
postgres=#

Don samun damar harsashin postgres kai tsaye, ba tare da fara shiga asusun mai amfani na postgres ba, gudanar da umarni mai zuwa.

$ sudo -i -u postgres psql

Kuna iya barin/fita daga postgres ta hanyar buga umarni mai zuwa.

postgres=# \q

Ƙirƙiri sabon rawar mai amfani ta amfani da umarni mai zuwa.

postgres=# CREATE ROLE tecmint;

Don ƙirƙirar matsayi tare da sifa ta LOGIN, yi amfani da umarni mai zuwa (ana iya ɗaukar matsayi tare da sifa ta LOGIN iri ɗaya da masu amfani da bayanai).

postgres=#CREATE ROLE tecmint LOGIN;
OR
postgres=#CREATE USER name;	#assumes login function by default

Hakanan za'a iya ƙirƙira wata rawa tare da kalmar sirri, wannan yana da amfani idan kun saita hanyar tantance abokin ciniki don tambayar masu amfani da su samar da rufaffen kalmar sirri lokacin haɗi zuwa bayanan bayanai.

postgres=#CREATE ROLE tecmint PASSWORD 'passwd_here'

Don lissafin ayyukan mai amfani da ke akwai, yi amfani da kowane ɗayan waɗannan umarni.

postgres=# \du 				#shows actual users
OR
postgres=# SELECT rolname FROM pg_roles;

Don sauke kowane rawar mai amfani da ke akwai amfani da umarnin DROP ROLE kamar yadda aka nuna.

postgres=# DROP ROLE tecmint;

Da zarar kun ƙirƙiri wata rawa tare da takamaiman suna (misali tecmint mai amfani), zaku iya ƙirƙirar bayanai (mai suna iri ɗaya da rawar) wanda wannan rawar za ta sarrafa ta kamar yadda aka nuna.

postgres=# CREATE DATABASE tecmint;

Yanzu don sarrafa tecmint database, shiga harsashi postgres azaman tecmint, samar da kalmar wucewa kamar haka.

$ sudo -i -u tecmint psql

Ƙirƙirar teburi yana da sauƙi, za mu ƙirƙiri teburin gwaji da ake kira marubuta, wanda ke adana bayanai game da mawallafin TecMint.com, kamar yadda aka nuna.

tecmint=>CREATE TABLE authors (
    code      char(5) NOT NULL,
    name    varchar(40) NOT NULL,
    city varchar(40) NOT NULL
    joined_on date NOT NULL,	
    PRIMARY KEY (code)
);

Bayan ƙirƙirar tebur, gwada cika shi da wasu bayanai, kamar haka.

tecmint=> INSERT INTO authors VALUES(1,'Ravi Saive','Mumbai','2012-08-15');

Don duba bayanan da aka adana a cikin tebur, kuna iya gudanar da umarnin SELECT.

tecmint=> SELECT * FROM authors;

Kuna iya jera duk allunan da ke cikin bayanan bayanai na yanzu tare da umarni mai zuwa.

tecmint=>\dt

Don share tebur a cikin bayanai na yanzu, yi amfani da umarnin DROP.

tecmint=> DROP TABLE authors;

Don jera duk bayanan bayanai, yi amfani da kowane umarni masu zuwa.

tecmint=>SELECT datname FROM pg_database;
OR
tecmint=>\list	#shows a detailed description 
OR
tecmint=>\l

Idan kana son share bayanan bayanai, yi amfani da umarnin DROP, misali.

tecmint=>DROP DATABASE tecmint;

Hakanan zaka iya canzawa daga wannan bayanan zuwa wani cikin sauƙi ta amfani da umarni mai zuwa.

tecmint=>\connect database_name

Don ƙarin bayani, koma zuwa Takardun PostgreSQL 10.4.

Shi ke nan a yanzu! A cikin wannan labarin, mun bayyana yadda ake shigarwa da amfani da tsarin sarrafa bayanai na PostgreSQL akan Ubuntu 18.04. Kuna iya aiko mana da tambayoyinku ko tunaninku a cikin sharhi.