คำสั่ง PostgreSQL การได้รับข้อมูลอ้างอิง การประกอบและติดตั้ง

PostgreSQL เป็น DBMS เชิงวัตถุข้ามแพลตฟอร์มพร้อมระบบเปิด ซอร์สโค้ด- บทความนี้จะแสดงวิธีการติดตั้ง PostgreSQL อูบุนตู ลินุกซ์ให้เชื่อมต่อและทำการจับคู่ คำสั่ง SQL อย่างง่ายรวมถึงวิธีตั้งค่าการสำรองข้อมูล

หากต้องการติดตั้ง PostgreSQL 9.2 บน Ubuntu 12.10 ให้รันคำสั่งต่อไปนี้:

sudo apt-add-repository ppa:pitti/ postgresql
อัปเดต sudo apt-get
sudo apt-get ติดตั้ง postgresql-9.2

มาลองทำงานกับ DBMS ผ่านเชลล์กันดีกว่า:

sudo -u postgres psql

มาสร้างฐานข้อมูลทดสอบและผู้ใช้ทดสอบกัน:

สร้างฐานข้อมูล test_database;
สร้างผู้ใช้ test_user ด้วยรหัสผ่าน "qwerty" ;
ให้สิทธิ์ทั้งหมดบนฐานข้อมูล test_database ให้กับ test_user;

หากต้องการออกจากเชลล์ ให้ป้อนคำสั่ง \q

ตอนนี้เรามาลองทำงานกับฐานข้อมูลที่สร้างขึ้นในนามของ test_user:

psql -h localhost test_database test_user

มาสร้างตารางใหม่กันเถอะ:

สร้างลำดับ user_ids;
สร้างผู้ใช้ตาราง (
id INTEGER คีย์หลักเริ่มต้น NEXTVAL ("user_ids" ) ,
เข้าสู่ระบบ CHAR(64) ,
รหัสผ่าน CHAR(64));

โปรดทราบว่า PostgreSQL ต่างจาก DBMS อื่นๆ ตรงที่ไม่มีคอลัมน์ที่มีคุณสมบัติ auto_increation Postgres ใช้ลำดับแทน บน ในขณะนี้ก็เพียงพอแล้วที่จะรู้ว่าการใช้ฟังก์ชัน nextval เราจะได้ตัวเลขเฉพาะสำหรับลำดับที่กำหนด:

เลือกถัดไป ("user_ids" ) ;

โดยการตั้งค่าฟิลด์ id เป็นค่าเริ่มต้น ตารางผู้ใช้ความหมาย ถัดไป("user_ids")เราได้รับเอฟเฟกต์แบบเดียวกับที่ auto_increation ให้มา เมื่อเพิ่มบันทึกใหม่ลงในตาราง เราไม่จำเป็นต้องระบุรหัส เนื่องจากรหัสเฉพาะจะถูกสร้างขึ้นโดยอัตโนมัติ หลายตารางสามารถใช้ลำดับเดียวกันได้ ด้วยวิธีนี้เราสามารถรับประกันได้ว่าค่าของบางฟิลด์ในตารางเหล่านี้จะไม่ทับซ้อนกัน ในแง่นี้ ลำดับมีความยืดหยุ่นมากกว่าการเพิ่มขึ้นอัตโนมัติ

คุณสามารถสร้างตารางเดียวกันได้โดยใช้เพียงคำสั่งเดียว:

สร้างตาราง users2 (
รหัสหลักซีเรียลคีย์
เข้าสู่ระบบ CHAR(64) ,
รหัสผ่าน CHAR(64));

ในกรณีนี้ ลำดับสำหรับฟิลด์ id จะถูกสร้างขึ้นโดยอัตโนมัติ

ขณะนี้การใช้คำสั่ง \d คุณสามารถดูรายการตารางที่มีอยู่ทั้งหมด และเมื่อใช้ \d ผู้ใช้ คุณสามารถดูคำอธิบายของตารางผู้ใช้ได้ หากคุณไม่ได้รับข้อมูลที่ต้องการ ให้ลอง \d+ แทน \d คุณสามารถรับรายการฐานข้อมูลได้ด้วยคำสั่ง \l และสลับไปยังฐานข้อมูลเฉพาะด้วยคำสั่ง \c dbname หากต้องการแสดงวิธีใช้คำสั่ง ให้พูด \?

-

สิ่งสำคัญที่ควรทราบคือ PostgreSQL จะแปลงชื่อตารางและคอลัมน์เป็นตัวพิมพ์เล็กตามค่าเริ่มต้น หากคุณไม่ต้องการให้มีพฤติกรรมเช่นนี้ คุณสามารถใช้เครื่องหมายคำพูดคู่ได้:

สร้างตาราง "anotherTable" ("someValue" VARCHAR (64 ) ) ;

คุณสมบัติอื่นของ PostgreSQL ที่อาจทำให้เกิดปัญหาเมื่อเริ่มทำงานกับ DBMS นี้คือสิ่งที่เรียกว่า "สคีมา" สคีมาเป็นเหมือนเนมสเปซสำหรับตาราง เช่น ไดเร็กทอรีที่มีตารางอยู่ภายในฐานข้อมูล

การสร้างสคีมา:

สร้างการจอง SCHEMA;

เปลี่ยนไปใช้รูปแบบ:

ตั้งค่า search_path ถึงการจอง;

คุณสามารถดูรายการโครงร่างที่มีอยู่ได้โดยใช้คำสั่ง \dn สคีมาเริ่มต้นมีชื่อว่าสาธารณะ โดยหลักการแล้ว คุณสามารถใช้ PostgreSQL ได้สำเร็จโดยไม่ต้องรู้ว่ามีสคีมาอยู่ แต่เมื่อทำงานกับโค้ดเดิมและในบางกรณี Edge การทราบเกี่ยวกับสคีมาอาจมีประโยชน์มาก

มิฉะนั้นการทำงานกับ PostgreSQL ก็ไม่แตกต่างจากการทำงานกับ DBMS เชิงสัมพันธ์อื่น ๆ มากนัก:
INSERT INTO ผู้ใช้ (เข้าสู่ระบบ, รหัสผ่าน)
ค่านิยม ("afiskon" , "123456" ) ;

เลือก * จากผู้ใช้;

หากคุณพยายามเชื่อมต่อกับ Postgres จากเครื่องอื่น คุณจะล้มเหลว:

psql -h 192.168.0.1 test_database test_user พีเอสแอลแอล:ไม่สามารถ
เชื่อมต่อกับเซิร์ฟเวอร์: การเชื่อมต่อถูกปฏิเสธ
เซิร์ฟเวอร์ทำงานบนโฮสต์ "192.168.0.1" และยอมรับหรือไม่

การเชื่อมต่อ TCP/IP บนพอร์ต 5432?

หากต้องการแก้ไขปัญหานี้ ให้เพิ่มบรรทัด:

Listen_addresses = "localhost,192.168.0.1"

... ไปยังไฟล์ /etc/postgresql/9.2/main/postgresql.conf เช่นกัน ในบทความนี้ฉันจะแสดง 15 รายการมากที่สุดคำสั่งที่เป็นประโยชน์ สำหรับการจัดการ.

postgreSQL

$ 1. จะเปลี่ยนรหัสผ่านรูทใน PostgreSQL ได้อย่างไร?/usr/local/pgsql/bin/psql postgres postgres รหัสผ่าน: (รหัสผ่านเก่า) # $ แก้ไขผู้ใช้ postgres ด้วยรหัสผ่าน 'tmppassword';/usr/local/pgsql/bin/psql postgres postgres

รหัสผ่าน: (tmppassword) การเปลี่ยนรหัสผ่านสำหรับผู้ใช้ทั่วไปเกิดขึ้นในลักษณะเดียวกันผู้ใช้รูท

# สามารถเปลี่ยนรหัสผ่านสำหรับผู้ใช้คนใดก็ได้

เปลี่ยนชื่อผู้ใช้ด้วยรหัสผ่าน 'tmppassword';

$ 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 / ฯลฯ/rc.d/init.d/postgresql

3. ตรวจสอบสถานะเซิร์ฟเวอร์

$ /etc/init.d/postgresql สถานะรหัสผ่าน: pg_ctl: เซิร์ฟเวอร์กำลังทำงาน (PID: 6171) /usr/local/pgsql/bin/postgres “-D” “/usr/local/pgsql/data” [ ความคิดเห็น: ข้อความนี้ระบุว่าเซิร์ฟเวอร์กำลังทำงานและทำงานได้ตามปกติ] $ /etc/init.d/postgresql สถานะรหัสผ่าน: pg_ctl: ไม่มีเซิร์ฟเวอร์ทำงาน [ ความคิดเห็น: ข้อความนี้ระบุว่าเซิร์ฟเวอร์ไม่ทำงาน]

4. จะเริ่ม, หยุด, รีสตาร์ท PostgreSQL ได้อย่างไร?

# บริการ postgresql หยุดการหยุด PostgreSQL: เซิร์ฟเวอร์หยุดทำงานตกลง # บริการ postgresql เริ่มต้นการเริ่มต้น PostgreSQL: ตกลง # บริการ postgresql รีสตาร์ทการรีสตาร์ท PostgreSQL: เซิร์ฟเวอร์หยุดทำงานแล้ว

5. ฉันจะดูได้อย่างไรว่า PostgreSQL เวอร์ชันใดกำลังทำงานอยู่

$ /usr/local/pgsql/bin/psql ทดสอบยินดีต้อนรับสู่ psql 8.3.7 เทอร์มินัลแบบโต้ตอบ PostgreSQL ประเภท: \copyright สำหรับเงื่อนไขการแจกจ่าย \h เพื่อขอความช่วยเหลือเกี่ยวกับคำสั่ง SQL \? เพื่อขอความช่วยเหลือเกี่ยวกับคำสั่ง psql \g หรือยุติด้วยเครื่องหมายอัฒภาคเพื่อดำเนินการค้นหา \q เพื่อออกจากการทดสอบ=# เลือกเวอร์ชัน();เวอร์ชัน —————————————————————————————————— PostgreSQL 8.3.7 บน i686-pc-linux-gnu เรียบเรียงโดย GCC gcc (GCC) 4.1.2 20071124 ( หมวกแดง 4.1.2-42) (1 แถว) ทดสอบ=#

5. จะสร้างผู้ใช้ใน PostgreSQL ได้อย่างไร?

มีสองวิธีสำหรับสิ่งนี้ ..

วิธีที่ 1:เราสร้างผู้ใช้โดยใช้เชลล์ PSQL โดยใช้คำสั่ง CREATE USER

# สร้างผู้ใช้ด้วยรหัสผ่าน 'tmppassword';สร้างบทบาท

วิธี2: เราสร้างผู้ใช้โดยใช้คำสั่งเชลล์ createuser

$ /usr/local/pgsql/bin/createuser sathiyaบทบาทใหม่จะเป็น superuser หรือไม่? (y/n) n บทบาทใหม่จะได้รับอนุญาตให้สร้างฐานข้อมูลหรือไม่? (y/n) n บทบาทใหม่จะได้รับอนุญาตให้สร้างบทบาทใหม่เพิ่มเติมหรือไม่? (y/n) n สร้างบทบาท

6. จะสร้างฐานข้อมูลใน PostgreSQL ได้อย่างไร?

มี 2 ​​วิธีสำหรับสิ่งนี้

วิธี1: เราสร้างฐานข้อมูลผ่านเชลล์ PSQL โดยใช้คำสั่ง CREATE DATABASE

# สร้างฐานข้อมูล mydb ด้วย ramesh เจ้าของ;สร้างฐานข้อมูล

วิธี2: เราใช้คำสั่ง createb

$ /usr/local/pgsql/bin/createdb mydb -O rameshสร้างฐานข้อมูล

7. เราได้รายชื่อฐานข้อมูลทั้งหมดใน Postgresql หรือไม่?

# \ลรายชื่อฐานข้อมูล ชื่อ | เจ้าของ | การเข้ารหัส ———-+———-+———- สำรอง | โพสต์เกรส | UTF8 mydb | ราเมช | UTF8 postgres | โพสต์เกรส | เทมเพลต UTF80 | โพสต์เกรส | เทมเพลต UTF81 | โพสต์เกรส | UTF8

8. จะลบฐานข้อมูลใน PostgreSQL ได้อย่างไร?

# \ลรายชื่อฐานข้อมูล ชื่อ | เจ้าของ | การเข้ารหัส ———-+———-+———- สำรอง | โพสต์เกรส | UTF8 mydb | ราเมช | UTF8 postgres | โพสต์เกรส | เทมเพลต UTF80 | โพสต์เกรส | เทมเพลต UTF81 | โพสต์เกรส | UTF8# วางฐานข้อมูล mydb;วางฐานข้อมูล

9. ใช้วิธีใช้ในตัวสำหรับคำสั่ง

ทีม \? จะแสดงบรรทัดช่วยเหลือสำหรับคำสั่ง PSQL \h CREATE จะแสดงความช่วยเหลือสำหรับคำสั่งทั้งหมดที่ขึ้นต้นด้วย CREATE

# \? # \h สร้าง # \h สร้างดัชนี

10. จะรับรายการตารางทั้งหมดในฐานข้อมูลที่กำหนดใน Postgresql ได้อย่างไร

# \d

สำหรับฐานข้อมูลว่าง คุณจะได้รับข้อความ “ไม่พบความสัมพันธ์”

11. ฉันจะทราบเวลาดำเนินการตามคำขอได้อย่างไร?

# \timing - หลังจากดำเนินการคำสั่งนี้ แต่ละคำขอที่ตามมาจะแสดงเวลาดำเนินการ

# \เวลาเวลาเปิดอยู่ - SELECT * จาก pg_catalog.pg_attribute ;เวลา: 9.583 มิลลิวินาที

12. จะสำรองและกู้คืนฐานข้อมูลและตารางใน PostgreSQL ได้อย่างไร

คำถามนี้ค่อนข้างใหญ่ และฉันจะเผยแพร่ในบทความแยกต่างหากในภายหลัง

13. จะดูรายการฟังก์ชั่นที่มีอยู่ใน PostgreSQL ได้อย่างไร?

เพื่อรับรายชื่อ ฟังก์ชั่นที่มีอยู่ให้พูดว่า \df+

# \df # \df+

14. จะแก้ไขแบบสอบถาม PostgreSQL ในตัวแก้ไขได้อย่างไร

# \e

\e จะเปิดตัวแก้ไขซึ่งคุณสามารถแก้ไขแบบสอบถามและบันทึกได้

15. ฉันจะหาไฟล์ประวัติ postgreSQL ได้ที่ไหน?

เช่นเดียวกับไฟล์ ~/.bash_history postgreSQL จะจัดเก็บทุกอย่าง คำสั่ง sqlในไฟล์ ~/.psql_history

$ แมว ~/.psql_historyแก้ไข postgres ผู้ใช้ด้วยรหัสผ่าน 'tmppassword'; \h แก้ไขผู้ใช้เลือกเวอร์ชัน (); สร้าง ramesh ผู้ใช้ด้วยรหัสผ่าน 'tmppassword'; \timing เลือก * จาก pg_catalog.pg_attribute;

การบริหารระบบ

โพสต์นี้คือ คำแนะนำสั้น ๆสำหรับผู้เริ่มต้น สำหรับผู้ที่ติดตั้ง PostgreSQL เป็นครั้งแรก ทุกอย่างอยู่ที่นี่ ข้อมูลที่จำเป็นเพื่อเริ่มต้นใช้งาน PostgreSQL

การเชื่อมต่อกับ DBMS

สิ่งแรกที่ต้องทำคือเข้าถึง PostgreSQL เข้าถึงในฐานะผู้ใช้ระดับสูง
การตั้งค่าการตรวจสอบสิทธิ์จะอยู่ในไฟล์ pg_hba.conf

1. ในพื้นที่เพื่อน postgres ทั้งหมด

บรรทัดนี้บอกว่าผู้ใช้ postgres สามารถเชื่อมต่อกับฐานข้อมูลท้องถิ่นใดก็ได้ PostgreSQL DBMSผ่านซ็อกเก็ต ไม่จำเป็นต้องป้อนรหัสผ่าน ระบบปฏิบัติการจะส่งชื่อผู้ใช้และจะใช้สำหรับการตรวจสอบสิทธิ์
มาเชื่อมต่อกัน:

1. $ sudo -u postgres psql postgres postgres

หากต้องการเชื่อมต่อผ่านเครือข่าย คุณต้องเพิ่มบรรทัดใน pg_hdba.conf:

1. # ประเภทวิธีระบุที่อยู่ผู้ใช้ฐานข้อมูล
2. hostssl ทั้งหมด 0.0.0.0/0 md5

วิธีการรับรองความถูกต้อง md5หมายความว่าคุณจะต้องป้อนรหัสผ่านเพื่อเชื่อมต่อ สิ่งนี้ไม่สะดวกหากคุณใช้คอนโซล psql บ่อยครั้ง หากคุณต้องการดำเนินการบางอย่างโดยอัตโนมัติ ข่าวร้ายก็คือ psql ไม่ยอมรับรหัสผ่านเป็นอาร์กิวเมนต์ มีสองวิธีในการแก้ปัญหาเหล่านี้: การตั้งค่าตัวแปรสภาพแวดล้อมที่เหมาะสม และการจัดเก็บรหัสผ่านในไฟล์ .pgpass พิเศษ

การตั้งค่าตัวแปรสภาพแวดล้อม PGPASSWORD

ฉันจะบอกทันทีว่าอย่าใช้วิธีนี้ดีกว่าเพราะบางคน ระบบปฏิบัติการอนุญาตให้คุณดู ผู้ใช้ทั่วไป ตัวแปรสภาพแวดล้อมผ่านทางปล. แต่ถ้าคุณต้องการคุณต้องเขียนลงในเทอร์มินัล:

1. ส่งออก PGPASSWORD=mypasswd

ตัวแปรจะพร้อมใช้งานในเซสชันปัจจุบัน หากคุณต้องการตั้งค่าตัวแปรสำหรับทุกเซสชัน คุณจะต้องเพิ่มบรรทัดจากตัวอย่างลงในไฟล์ .bashrc หรือ .bash_profile

การจัดเก็บรหัสผ่านในไฟล์ .pgpass

หากเรากำลังพูดถึง Linux ไฟล์นั้นควรจะอยู่ใน $HOME (/home/username) เฉพาะเจ้าของ (0600) เท่านั้นที่ต้องมีสิทธิ์ในการเขียนและอ่าน คุณต้องเขียนบรรทัดเช่นนี้ลงในไฟล์:

1. ชื่อโฮสต์: พอร์ต: ฐานข้อมูล: ชื่อผู้ใช้: รหัสผ่าน

คุณสามารถเขียน "*" ในสี่ช่องแรกได้ ซึ่งจะหมายถึงไม่มีการกรอง (ตัวเลือกทั้งหมด)

การรับข้อมูลความช่วยเหลือ

- - จะยอมทิ้งทุกสิ่ง คำสั่งที่ใช้ได้พร้อมด้วยคำอธิบายสั้นๆ
\h - จะแสดงรายการคำค้นหาที่มีอยู่ทั้งหมด
\h CREATE - จะให้ความช่วยเหลือสำหรับคำขอเฉพาะ

การจัดการผู้ใช้ DBMS

จะรับรายชื่อผู้ใช้ PostgreSQL ได้อย่างไร

1. หรือคุณสามารถสืบค้นตาราง pg_user

SELECT * จาก pg_user ;

การสร้างผู้ใช้ PostgreSQL ใหม่ จากเปลือก

1. psql สามารถทำได้โดยใช้คำสั่ง CREATE

สร้างชื่อผู้ใช้ด้วยรหัสผ่าน "รหัสผ่าน" ;

1. หรือคุณสามารถใช้เทอร์มินัล

createuser -S -D -R -P ชื่อผู้ใช้

คุณจะได้รับแจ้งให้ป้อนรหัสผ่าน

1. การเปลี่ยนรหัสผ่านผู้ใช้

เปลี่ยนชื่อผู้ใช้ด้วยรหัสผ่าน "รหัสผ่าน" ;

การเปลี่ยนบทบาทของผู้ใช้

1. เมื่อต้องการให้สิทธิ์ผู้ใช้ในการสร้างฐานข้อมูล ให้เรียกใช้แบบสอบถามต่อไปนี้:

เปลี่ยนชื่อผู้ใช้บทบาทด้วย CREATEDB ;

การจัดการฐานข้อมูล

1. การแสดงรายการฐานข้อมูลในเทอร์มินัล psql: เหมือนกันจากเทอร์มินัล Linux:

psql -l

1. การสร้างฐานข้อมูลจาก psql (PostgreSQL Terminal)

สร้างฐานข้อมูล dbname เจ้าของ dbadmin ; การสร้างฐานใหม่

1. ข้อมูลโดยใช้เทอร์มินัล:

createb -O ชื่อผู้ใช้ dbname;

หากผู้ใช้เป็นเจ้าของฐานข้อมูลแสดงว่าเขามีสิทธิ์ทั้งหมด แต่ถ้าคุณต้องการให้สิทธิ์การเข้าถึงแก่ผู้ใช้รายอื่น คุณสามารถทำได้โดยใช้คำสั่ง GRANT แบบสอบถามด้านล่างจะอนุญาตให้ผู้ใช้สามารถเชื่อมต่อกับฐานข้อมูล แต่อย่าลืมเกี่ยวกับ ไฟล์การกำหนดค่า pg_hba.conf จะต้องมีสิทธิ์ในการเชื่อมต่อที่เหมาะสมด้วย

1. ให้สิทธิ์เชื่อมต่อกับฐานข้อมูล dbname ไปยัง dbadmin ;