15 คำสั่ง PostgreSQL ที่มีประโยชน์

มีบทช่วยสอน PostgreSQL มากมายบนเว็บที่อธิบายคำสั่งพื้นฐาน แต่เมื่อเจาะลึกลงไปในงาน ปัญหาเชิงปฏิบัติก็เกิดขึ้นซึ่งต้องใช้ทีมงานขั้นสูง

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

รับข้อมูลเกี่ยวกับฐานข้อมูล

ขนาดฐานข้อมูล

ในการรับขนาดทางกายภาพของไฟล์ฐานข้อมูล (ที่เก็บข้อมูล) เราใช้แบบสอบถามต่อไปนี้:

เลือก pg_database_size(current_database());

ผลลัพธ์จะแสดงเป็นตัวเลขในแบบฟอร์ม 41809016

current_database() เป็นฟังก์ชันที่ส่งคืนชื่อของฐานข้อมูลปัจจุบัน คุณสามารถป้อนชื่อเป็นข้อความแทนได้:

SELECT pg_database_size("my_database");

เพื่อให้ได้ข้อมูลในรูปแบบที่มนุษย์สามารถอ่านได้ เราใช้ฟังก์ชัน pg_size_pretty:

SELECT pg_size_pretty(pg_database_size(current_database()));

เป็นผลให้เราได้รับข้อมูลในรูปแบบ 40 Mb

รายชื่อตาราง

บางครั้งคุณจำเป็นต้องได้รับรายการตารางฐานข้อมูล เมื่อต้องการทำเช่นนี้ เราใช้แบบสอบถามต่อไปนี้:

เลือก table_name จาก information_schema.tables โดยที่ table_schema ไม่อยู่ใน ("information_schema", "pg_catalog");

information_schema เป็นสคีมาฐานข้อมูลมาตรฐานที่รวบรวมคอลเลกชันของมุมมอง เช่น ตาราง ฟิลด์ ฯลฯ มุมมองตารางประกอบด้วยข้อมูลเกี่ยวกับตารางทั้งหมดในฐานข้อมูล

แบบสอบถามด้านล่างจะเลือกตารางทั้งหมดจากสคีมาที่ระบุของฐานข้อมูลปัจจุบัน:

เลือก table_name จาก information_schema.tables โดยที่ table_schema ไม่ได้อยู่ใน ("information_schema", "pg_catalog") และ table_schema IN("public", "myschema");

เงื่อนไข IN สุดท้ายสามารถใช้เพื่อระบุชื่อของสคีมาเฉพาะ

ขนาดโต๊ะ

เช่นเดียวกับการหาขนาดของฐานข้อมูล ขนาดของข้อมูลในตารางสามารถคำนวณได้โดยใช้ฟังก์ชันที่เหมาะสม:

SELECT pg_relation_size("บัญชี");

ฟังก์ชัน pg_relation_size จะส่งคืนจำนวนเนื้อที่ดิสก์ที่เลเยอร์ที่ระบุของตารางหรือดัชนีที่กำหนดครอบครองบนดิสก์

ชื่อโต๊ะที่ใหญ่ที่สุด

หากต้องการแสดงรายการตารางในฐานข้อมูลปัจจุบัน เรียงตามขนาดตาราง ให้เรียกใช้แบบสอบถามต่อไปนี้:

เลือก relname, relpages จาก pg_class ORDER BY relpages DESC;

เพื่อแสดงข้อมูลเกี่ยวกับตารางที่ใหญ่ที่สุด เราจำกัดการสืบค้นโดยใช้ LIMIT:

SELECT relname, relpages จาก pg_class ORDER BY relpages DESC LIMIT 1;

relname - ชื่อของตาราง ดัชนี มุมมอง ฯลฯ
relpages - ขนาดของการแสดงดิสก์ของตารางนี้เป็นจำนวนหน้า (โดยค่าเริ่มต้น หนึ่งหน้าคือ 8 KB)
pg_class เป็นตารางระบบที่มีข้อมูลเกี่ยวกับความสัมพันธ์ระหว่างตารางฐานข้อมูล

รายชื่อผู้ใช้ที่เชื่อมต่อ

หากต้องการค้นหาชื่อ IP และพอร์ตที่ใช้ของผู้ใช้ที่เชื่อมต่อ ให้เรียกใช้แบบสอบถามต่อไปนี้:

เลือก datname, usename, client_addr, client_port จาก pg_stat_activity;

กิจกรรมของผู้ใช้

หากต้องการค้นหากิจกรรมการเชื่อมต่อของผู้ใช้รายใดรายหนึ่ง ให้ใช้แบบสอบถามต่อไปนี้:

เลือกชื่อข้อมูลจาก pg_stat_activity โดยที่ usename = "devuser";

การทำงานกับเขตข้อมูลและตาราง

การลบแถวที่ซ้ำกัน

หากเกิดขึ้นว่าตารางไม่มีคีย์หลัก ก็อาจมีรายการที่ซ้ำกันในเรคคอร์ด หากสำหรับตารางดังกล่าว โดยเฉพาะอย่างยิ่งตารางขนาดใหญ่ จำเป็นต้องกำหนดข้อจำกัดในการตรวจสอบความสมบูรณ์ จากนั้นจึงลบองค์ประกอบต่อไปนี้:

• เส้นที่ซ้ำกัน
• สถานการณ์ที่มีการทำซ้ำหนึ่งคอลัมน์ขึ้นไป (หากคอลัมน์เหล่านี้มีวัตถุประสงค์เพื่อใช้เป็นคีย์หลัก)

ลองพิจารณาตารางที่มีข้อมูลลูกค้าซึ่งมีการทำซ้ำทั้งแถว (อันที่สอง)

แบบสอบถามต่อไปนี้จะช่วยคุณลบรายการที่ซ้ำกันทั้งหมด:

ลบจากลูกค้าโดยที่ ctid ไม่อยู่ใน (เลือกสูงสุด (ctid) จากลูกค้า GROUP BY ลูกค้า *);

เขตข้อมูล ctid ซึ่งไม่ซ้ำกันสำหรับแต่ละระเบียน จะถูกซ่อนไว้ตามค่าเริ่มต้น แต่จะปรากฏในทุกตาราง

คำขอสุดท้ายต้องใช้ทรัพยากรจำนวนมาก ดังนั้นควรระมัดระวังเมื่อดำเนินการกับโครงการที่ใช้งานจริง

ตอนนี้ให้พิจารณากรณีที่ค่าฟิลด์ถูกทำซ้ำ

หากอนุญาตให้ลบรายการที่ซ้ำกันโดยไม่บันทึกข้อมูลทั้งหมด เราจะดำเนินการตามคำขอต่อไปนี้:

ลบจากลูกค้าโดยที่ ctid ไม่อยู่ใน (เลือกสูงสุด (ctid) จากลูกค้า GROUP BY customer_id);

หากข้อมูลมีความสำคัญ คุณต้องค้นหาบันทึกที่ซ้ำกันก่อน:

SELECT * จากลูกค้าโดยที่ ctid ไม่อยู่ใน (เลือกสูงสุด (ctid) จากลูกค้า GROUP BY customer_id);

ก่อนที่จะลบ บันทึกดังกล่าวสามารถย้ายไปยังตารางชั่วคราวหรือสามารถแทนที่ค่า customer_id ในนั้นด้วยค่าอื่นได้

รูปแบบทั่วไปของการร้องขอให้ลบบันทึกที่อธิบายไว้ข้างต้นมีดังนี้:

ลบจาก table_name โดยที่ ctid ไม่อยู่ใน (เลือกสูงสุด (ctid) จาก table_name จัดกลุ่มตามคอลัมน์ 1, );

การเปลี่ยนประเภทฟิลด์อย่างปลอดภัย

อาจมีคำถามเกี่ยวกับการรวมงานดังกล่าวไว้ในรายการนี้ ท้ายที่สุดแล้ว ใน PostgreSQL การเปลี่ยนประเภทของฟิลด์โดยใช้คำสั่ง ALTER ทำได้ง่ายมาก เรามาดูตารางลูกค้าอีกครั้งเป็นตัวอย่าง

ช่อง customer_id ใช้ประเภทข้อมูลสตริง varchar นี่เป็นข้อผิดพลาดเนื่องจากช่องนี้ควรจัดเก็บรหัสลูกค้าซึ่งอยู่ในรูปแบบจำนวนเต็ม การใช้ varchar นั้นไม่สมเหตุสมผล ลองแก้ไขความเข้าใจผิดนี้โดยใช้คำสั่ง ALTER:

ลูกค้า ALTER TABLE ALTER COLUMN customer_id TYPE จำนวนเต็ม;

แต่จากการดำเนินการเราได้รับข้อผิดพลาด:

ข้อผิดพลาด: ไม่สามารถแปลงคอลัมน์ “customer_id” เพื่อพิมพ์จำนวนเต็มโดยอัตโนมัติได้
สถานะ SQL: 42804
คำแนะนำ: ระบุนิพจน์ USING เพื่อทำการแปลง

ซึ่งหมายความว่าคุณไม่สามารถเปลี่ยนประเภทของเขตข้อมูลได้หากมีข้อมูลในตาราง เนื่องจากมีการใช้ประเภท varchar DBMS จึงไม่สามารถระบุได้ว่าค่านั้นเป็นจำนวนเต็มหรือไม่ แม้ว่าข้อมูลจะสอดคล้องกับประเภทนี้ก็ตาม เพื่อชี้แจงประเด็นนี้ ข้อความแสดงข้อผิดพลาดแนะนำให้ใช้นิพจน์ USING เพื่อแปลงข้อมูลของเราเป็นจำนวนเต็มอย่างถูกต้อง:

ลูกค้า ALTER TABLE ALTER COLUMN customer_id TYPE จำนวนเต็มโดยใช้ (customer_id::integer);

เป็นผลให้ทุกอย่างดำเนินไปโดยไม่มีข้อผิดพลาด:

โปรดทราบว่าเมื่อใช้ USING นอกเหนือจากนิพจน์เฉพาะแล้ว ยังสามารถใช้ฟังก์ชัน ฟิลด์ และตัวดำเนินการอื่นๆ ได้

ตัวอย่างเช่น ลองแปลงช่อง customer_id กลับเป็น varchar แต่ด้วยการแปลงรูปแบบข้อมูล:

ลูกค้าเปลี่ยนตาราง ALTER COLUMN customer_id TYPE varchar USING (customer_id || "-" || first_name);

ดังนั้นตารางจะมีลักษณะดังนี้:

การค้นหาคุณค่าที่ "สูญหาย"

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

ลองพิจารณาสองตัวเลือกการค้นหา

วิธีแรก
ลองเรียกใช้แบบสอบถามต่อไปนี้เพื่อค้นหาจุดเริ่มต้นของช่วงเวลาที่มีค่า "สูญหาย":

SELECT customer_id + 1 จากลูกค้า mo ในกรณีที่ไม่มีอยู่ (SELECT NULL จากลูกค้า mi WHERE mi.customer_id = mo.customer_id + 1) สั่งซื้อโดย customer_id;

เป็นผลให้เราได้รับค่าต่อไปนี้: 5, 9 และ 11

หากคุณต้องการค้นหาไม่เพียงแต่การเกิดขึ้นครั้งแรก แต่ยังรวมถึงค่าที่หายไปทั้งหมด เราจะใช้แบบสอบถามต่อไปนี้ (ต้องใช้ทรัพยากรมาก!)

ด้วย seq_max AS (SELECT max(customer_id) จากลูกค้า), seq_min AS (SELECT min(customer_id) จากลูกค้า) SELECT * FROM Generate_series((SELECT min FROM seq_min),(SELECT max FROM seq_max)) ยกเว้น SELECT customer_id FROM ลูกค้า;

ด้วยเหตุนี้เราจึงเห็นผลลัพธ์ดังต่อไปนี้: 5, 9 และ 6

วิธีที่สอง
เราได้รับชื่อของลำดับที่เกี่ยวข้องกับ customer_id:

SELECT pg_get_serial_sequence("ลูกค้า", "customer_id");

และเราพบตัวระบุที่หายไปทั้งหมด:

ด้วย sequence_info AS (SELECT start_value, Last_value จาก "SchemaName"."SequenceName") SELECT Generate_series ((sequence_info.start_value), (sequence_info.last_value)) จาก sequence_info ยกเว้น SELECT customer_id จากลูกค้า;

การนับจำนวนแถวในตาราง

จำนวนแถวคำนวณโดยฟังก์ชันการนับมาตรฐาน แต่สามารถใช้กับเงื่อนไขเพิ่มเติมได้

จำนวนแถวทั้งหมดในตาราง:

SELECT count(*) จากตาราง;

จำนวนแถวที่มีเงื่อนไขว่าฟิลด์ที่ระบุไม่มีค่า NULL:

เลือกนับ (col_name) จากตาราง;

จำนวนแถวที่ไม่ซ้ำกันสำหรับฟิลด์ที่ระบุ:

เลือกนับ (col_name ที่แตกต่าง) จากตาราง;

การใช้ธุรกรรม

ธุรกรรมจะรวมลำดับของการดำเนินการไว้ในการดำเนินการเดียว ลักษณะเฉพาะของมันคือหากมีข้อผิดพลาดในการทำธุรกรรม ผลลัพธ์ของการกระทำจะไม่ถูกบันทึกลงในฐานข้อมูล

มาเริ่มการทำธุรกรรมโดยใช้คำสั่ง BEGIN

หากต้องการย้อนกลับการดำเนินการทั้งหมดที่อยู่หลัง BEGIN ให้ใช้คำสั่ง ROLLBACK

และเพื่อนำไปใช้ - คำสั่ง COMMIT

ดูและยุติการสืบค้นที่ทำงานอยู่

เพื่อรับข้อมูลเกี่ยวกับคำร้องขอ ให้รันคำสั่งต่อไปนี้:

เลือก pid, อายุ (query_start, clock_timestamp ()), usename, แบบสอบถามจาก pg_stat_activity WHERE แบบสอบถาม != " " และการสืบค้นไม่ใช่ ILIKE "%pg_stat_activity%" เรียงลำดับตาม query_start desc;

หากต้องการหยุดคำขอเฉพาะ ให้รันคำสั่งต่อไปนี้เพื่อระบุรหัสกระบวนการ (pid):

SELECT pg_cancel_backend(procpid);

หากต้องการหยุดคำขอ ให้รัน:

SELECT pg_terminate_backend(procpid);

การทำงานกับการกำหนดค่า

การค้นหาและการเปลี่ยนตำแหน่งของอินสแตนซ์คลัสเตอร์

สถานการณ์เกิดขึ้นได้เมื่อมีการกำหนดค่าอินสแตนซ์ PostgreSQL หลายรายการบนระบบปฏิบัติการเดียว ซึ่ง "นั่ง" บนพอร์ตที่ต่างกัน ในกรณีนี้ การหาวิธีจัดวางแต่ละตัวอย่างทางกายภาพถือเป็นงานที่ค่อนข้างลำบากใจ เพื่อให้ได้ข้อมูลนี้ ให้รันเคียวรีต่อไปนี้สำหรับฐานข้อมูลใดๆ ในคลัสเตอร์ที่สนใจ:

แสดง data_directory;

มาเปลี่ยนตำแหน่งเป็นตำแหน่งอื่นโดยใช้คำสั่ง:

ตั้งค่า data_directory เป็น new_directory_path;

แต่เพื่อให้การเปลี่ยนแปลงมีผล จำเป็นต้องรีบูต

รับรายการประเภทข้อมูลที่มีอยู่

เราได้รับรายการประเภทข้อมูลที่มีอยู่โดยใช้คำสั่ง:

เลือกชื่อพิมพ์, พิมพ์จาก pg_type โดยที่ typtype="b";

typname - ชื่อของประเภทข้อมูล
typelen - ขนาดของชนิดข้อมูล

การเปลี่ยนการตั้งค่า DBMS โดยไม่ต้องรีบูตเครื่อง

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

เลือก pg_reload_conf();

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

เราดูคำสั่งที่จะช่วยลดความซับซ้อนของการทำงานของนักพัฒนาและผู้ดูแลระบบฐานข้อมูลโดยใช้ PostgreSQL แต่นี่ไม่ใช่วิธีการที่เป็นไปได้ทั้งหมด หากคุณพบงานที่น่าสนใจ เขียนเกี่ยวกับงานเหล่านั้นในความคิดเห็น มาแบ่งปันประสบการณ์ที่เป็นประโยชน์กันเถอะ!

ในบทความนี้ ฉันจะแสดงคำสั่งที่มีประโยชน์ที่สุด 15 คำสั่งในการจัดการ postgreSQL.

1. จะเปลี่ยนรหัสผ่านรูทใน PostgreSQL ได้อย่างไร?

$ /usr/local/pgsql/bin/psql postgres postgresรหัสผ่าน: (รหัสผ่านเก่า) # แก้ไขผู้ใช้ postgres ด้วยรหัสผ่าน 'tmppassword'; $ /usr/local/pgsql/bin/psql postgres postgresรหัสผ่าน: (tmppassword)

การเปลี่ยนรหัสผ่านสำหรับผู้ใช้ทั่วไปเกิดขึ้นในลักษณะเดียวกัน ผู้ใช้รูทสามารถเปลี่ยนรหัสผ่านสำหรับผู้ใช้คนใดก็ได้

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

2. จะติดตั้ง PostgreSQL ลงใน 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 / ฯลฯ/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 (Red Hat 4.1.2-42) (1 แถว) ทดสอบ=#

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

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

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

# สร้าง ramesh ผู้ใช้ด้วยรหัสผ่าน '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 คุณสามารถทำงานกับ PosgreSQL ได้ทั้งแบบโต้ตอบและจากบรรทัดคำสั่ง โปรแกรมเป็น psql ฉันแน่ใจว่ารายการนี้จะเป็นประโยชน์กับคุณมากและจะช่วยคุณประหยัดเวลาในการค้นหาผ่านแหล่งข้อมูลต่างๆ ฉันขอเตือนคุณว่านี่เป็นโครงการโอเพ่นซอร์สซึ่งมีพื้นฐานมาจาก Postgres DBMS ซึ่งเปิดตัวในปี 1986 และกำลังได้รับการพัฒนาโดยกลุ่มนักพัฒนา PGDG ทั่วโลก เป็นหลักจำนวน 5-8 คน แต่ถึงกระนั้นก็ยังพัฒนาอย่างเข้มข้นมาก , แนะนำฟังก์ชั่นใหม่และแก้ไขข้อบกพร่องและข้อผิดพลาดเก่า

คำสั่ง PostgreSQL พื้นฐานในโหมดโต้ตอบ:

• \connect db_name – เชื่อมต่อกับฐานข้อมูลชื่อ db_name
• \du – รายชื่อผู้ใช้
• \dp (หรือ \z) – รายการตาราง มุมมอง ลำดับ สิทธิ์การเข้าถึง
• \di – ดัชนี
• \ds – ลำดับ
• \dt – รายการตาราง
• \dt+ - รายการตารางทั้งหมดพร้อมคำอธิบาย
• \dt *s* - รายการตารางทั้งหมดที่มี s อยู่ในชื่อ
• \dv – การแสดง
• \dS – ตารางระบบ
• \d+ – คำอธิบายตาราง
• \o – ส่งผลลัพธ์การสืบค้นไปยังไฟล์
• \l – รายการฐานข้อมูล
• \i – อ่านข้อมูลขาเข้าจากไฟล์
• \e – เปิดเนื้อหาปัจจุบันของบัฟเฟอร์คำขอในตัวแก้ไข (เว้นแต่จะระบุไว้เป็นอย่างอื่นในสภาพแวดล้อมของตัวแปร EDITOR, vi จะถูกใช้เป็นค่าเริ่มต้น)
• \d “table_name” – คำอธิบายของตาราง
• \i รันคำสั่งจากไฟล์ภายนอก เช่น \i /my/directory/my.sql
• \pset – คำสั่งเพื่อกำหนดค่าตัวเลือกการจัดรูปแบบ
• \echo – แสดงข้อความ
• \set – ตั้งค่าของตัวแปรสภาพแวดล้อม หากไม่มีพารามิเตอร์ ให้แสดงรายการตัวแปรปัจจุบัน (\unset – ลบ)
• - – การอ้างอิง psql
• \help – การอ้างอิง SQL
• \q (หรือ Ctrl+D) – ออกจากโปรแกรม

การทำงานกับ PostgreSQL จากบรรทัดคำสั่ง:

• -c (หรือ –command) – เรียกใช้คำสั่ง SQL โดยไม่ต้องเข้าสู่โหมดโต้ตอบ
• -f file.sql - รันคำสั่งจากไฟล์ file.sql
• -l (หรือ –list) – แสดงรายการฐานข้อมูลที่มีอยู่
• -U (หรือ –ชื่อผู้ใช้) – ระบุชื่อผู้ใช้ (เช่น postgres)
• -W (หรือ –รหัสผ่าน) – พร้อมท์รหัสผ่าน
• -d dbname - เชื่อมต่อกับฐานข้อมูล dbname
• -h – ชื่อโฮสต์ (เซิร์ฟเวอร์)
• -s – โหมดทีละขั้นตอนนั่นคือคุณจะต้องยืนยันคำสั่งทั้งหมด
• –S – โหมดบรรทัดเดียว นั่นคือ การไปที่บรรทัดใหม่จะดำเนินการค้นหา (กำจัดออก; ที่ส่วนท้ายของโครงสร้าง SQL)
• -V – เวอร์ชันของ PostgreSQL โดยไม่ต้องเข้าสู่โหมดโต้ตอบ

ตัวอย่าง:

psql -U postgres -d dbname -c “สร้างตารางของฉัน (คีย์หลักอนุกรม some_id, ข้อความ some_text);” - การดำเนินการคำสั่งในฐานข้อมูล dbname

psql -d dbname -H -c “SELECT * FROM my” -o my.html - ส่งออกผลลัพธ์การสืบค้นไปยังไฟล์ html

ยูทิลิตี้ PosgreSQL (โปรแกรม):

• createb และ dropdb – การสร้างและการลบฐานข้อมูล (ตามลำดับ)
• createuser และ dropuser – การสร้างและผู้ใช้ (ตามลำดับ)
• pg_ctl – โปรแกรมที่ออกแบบมาเพื่อแก้ปัญหางานควบคุมทั่วไป (การสตาร์ท การหยุด การตั้งค่าพารามิเตอร์ ฯลฯ)
• postmaster – โมดูลเซิร์ฟเวอร์ PostgreSQL ที่มีผู้ใช้หลายราย (การกำหนดค่าระดับการดีบัก พอร์ต ไดเร็กทอรีข้อมูล)
• initdb – สร้างคลัสเตอร์ PostgreSQL ใหม่
• initlocation – โปรแกรมสำหรับสร้างไดเร็กทอรีสำหรับจัดเก็บฐานข้อมูลรอง
• Vacuumdb – รองรับฐานข้อมูลทางกายภาพและการวิเคราะห์
• pg_dump – การเก็บถาวรและการกู้คืนข้อมูล
• pg_dumpall – สำรองข้อมูลคลัสเตอร์ PostgreSQL ทั้งหมด
• pg_restore – กู้คืนฐานข้อมูลจากไฟล์เก็บถาวร (.tar, .tar.gz)

ตัวอย่างการสร้างการสำรองข้อมูล:

การสร้างการสำรองข้อมูลของฐานข้อมูล mydb ในรูปแบบบีบอัด

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

การสร้างการสำรองข้อมูลฐานข้อมูล mydb ในรูปแบบไฟล์ข้อความปกติพร้อมคำสั่งในการสร้างฐานข้อมูล

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

การสร้างการสำรองข้อมูลของฐานข้อมูล mydb ในรูปแบบบีบอัด โดยมีตารางที่มีการชำระเงินในชื่อ

Pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *การชำระเงิน* -f pay_tables.backup mydb

ดัมพ์ข้อมูลจากตารางเฉพาะตารางเดียว หากจำเป็นต้องสำรองข้อมูลหลายตาราง ชื่อตารางจะแสดงรายการโดยใช้สวิตช์ -t สำหรับแต่ละตาราง

Pg_dump -a -t table_name -f file_name ฐานข้อมูล_ชื่อ

การสร้างการสำรองข้อมูลด้วยการบีบอัด gz

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

รายการตัวเลือกที่ใช้บ่อยที่สุด:

• -h host - โฮสต์ หากไม่ได้ระบุ จะใช้ localhost หรือค่าจากตัวแปรสภาพแวดล้อม PGHOST
• -p port - port หากไม่ได้ระบุ จะใช้ 5432 หรือค่าจากตัวแปรสภาพแวดล้อม PGPORT
• -u - ผู้ใช้ หากไม่ได้ระบุ ผู้ใช้ปัจจุบันจะถูกใช้ ค่ายังสามารถระบุในตัวแปรสภาพแวดล้อม PGUSER
• -a, -data-only - ดัมพ์ข้อมูลเท่านั้น โดยค่าเริ่มต้น ข้อมูลและสคีมาจะถูกบันทึก
• -b - รวมวัตถุขนาดใหญ่ (บล็อก) ไว้ในดัมพ์
• -s, -schema-only - ดัมพ์เฉพาะสคีมา
• -C, -create - เพิ่มคำสั่งเพื่อสร้างฐานข้อมูล
• -c - เพิ่มคำสั่งเพื่อลบ (วาง) วัตถุ (ตาราง มุมมอง ฯลฯ )
• -O - ​​​​อย่าเพิ่มคำสั่งเพื่อกำหนดเจ้าของวัตถุ (ตาราง มุมมอง ฯลฯ )
• -F, -format (c|t|p) - รูปแบบเอาต์พุตดัมพ์ กำหนดเอง tar หรือข้อความธรรมดา
• -t, -table=TABLE - ระบุตารางเฉพาะสำหรับดัมพ์
• -v, -verbose - ส่งออกข้อมูลโดยละเอียด
• -D, -attribute-inserts - ดัมพ์โดยใช้คำสั่ง INSERT พร้อมรายการชื่อคุณสมบัติ

สำรองฐานข้อมูลทั้งหมดโดยใช้คำสั่ง pg_dumpall

Pg_dumpall > all.sql

การคืนค่าตารางจากการสำรองข้อมูล:

psql - กู้คืนข้อมูลสำรองที่จัดเก็บไว้ในไฟล์ข้อความธรรมดา
pg_restore - กู้คืนข้อมูลสำรองที่บีบอัด (tar);

กู้คืนข้อมูลสำรองทั้งหมดโดยไม่สนใจข้อผิดพลาด

Psql -h localhost -U someuser -d dbname -f mydb.sql

กู้คืนข้อมูลสำรองทั้งหมด โดยหยุดทำงานที่ข้อผิดพลาดแรก

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

หากต้องการกู้คืนจากไฟล์เก็บถาวร tar ก่อนอื่นเราต้องสร้างฐานข้อมูลโดยใช้ CREATE DATABASE mydb (หากไม่ได้ระบุตัวเลือก -C เมื่อสร้างการสำรองข้อมูล) และกู้คืน

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

การกู้คืนการสำรองฐานข้อมูลที่บีบอัด gz

psql -U postgres -d mydb -f mydb

ฉันคิดว่าฐานข้อมูล postgresql จะทำให้คุณเข้าใจได้ง่ายขึ้น ฉันหวังว่ารายการคำสั่ง PostgreSQL นี้มีประโยชน์สำหรับคุณ

อัปเดตครั้งล่าสุด: 03/17/2018

หากต้องการสร้างตาราง ให้ใช้คำสั่ง CREATE TABLE ตามด้วยชื่อตาราง คุณยังสามารถใช้ตัวดำเนินการจำนวนหนึ่งกับคำสั่งนี้ซึ่งกำหนดคอลัมน์ของตารางและคุณลักษณะของมันได้ ไวยากรณ์ทั่วไปสำหรับการสร้างตารางมีดังนี้:

สร้างตาราง table_name (column_name1 data_type column_attributes1, column_name2 data_type column_attributes2, ..................................... ........ column_nameN data_type column_attributesN, table_attributes);

หลังชื่อตาราง ข้อกำหนดสำหรับคอลัมน์ทั้งหมดจะแสดงอยู่ในวงเล็บ นอกจากนี้ สำหรับแต่ละคอลัมน์ คุณต้องระบุชื่อและประเภทของข้อมูลที่จะแสดง ชนิดข้อมูลจะกำหนดว่าคอลัมน์สามารถมีข้อมูลใด (ตัวเลข สตริง ฯลฯ)

ตัวอย่างเช่น เรามาสร้างตารางในฐานข้อมูลโดยใช้ pgAdmin ในการดำเนินการนี้ ขั้นแรกให้เลือกฐานข้อมูลเป้าหมายใน pgAdmin คลิกขวาที่ฐานข้อมูลแล้วเลือกเครื่องมือสืบค้น... ในเมนูบริบท:

หลังจากนี้ช่องสำหรับป้อนรหัส SQL จะเปิดขึ้น นอกจากนี้ ตารางจะถูกสร้างขึ้นโดยเฉพาะสำหรับฐานข้อมูลที่เราเปิดฟิลด์อินพุต SQL นี้

สร้างตารางลูกค้า (Id SERIAL PRIMARY KEY, FirstName CHARACTER VARYING(30), LastName CHARACTER VARYING(30), Email CHARACTER VARYING(30), Age INTEGER);

ในกรณีนี้ มีการกำหนดห้าคอลัมน์ในตารางลูกค้า: Id, FirstName, LastName, Age, Email คอลัมน์แรก Id แสดงถึงรหัสไคลเอ็นต์ ซึ่งทำหน้าที่เป็นคีย์หลัก ดังนั้นจึงอยู่ในประเภท SERIAL ที่จริงแล้ว คอลัมน์นี้จะเก็บค่าตัวเลข 1, 2, 3 ฯลฯ ซึ่งจะเพิ่มขึ้นทีละหนึ่งโดยอัตโนมัติสำหรับแต่ละแถวใหม่

สามคอลัมน์ถัดไปแสดงถึงชื่อ นามสกุล และที่อยู่อีเมลของลูกค้า และเป็นประเภท CHARACTER VARYING(30) ซึ่งหมายความว่าเป็นสตริงที่มีความยาวไม่เกิน 30 อักขระ

คอลัมน์สุดท้าย Age แสดงถึงอายุของผู้ใช้และเป็นประเภท INTEGER ซึ่งหมายถึงเก็บตัวเลข

และหลังจากดำเนินการคำสั่งนี้แล้ว ตารางลูกค้าจะถูกเพิ่มลงในฐานข้อมูลที่เลือก

การถอดตาราง

หากต้องการวางตาราง ให้ใช้คำสั่ง DROP TABLE ซึ่งมีไวยากรณ์ดังต่อไปนี้:

วางตาราง table1 [, table2, ... ];

เช่น การลบตารางลูกค้า

อีกทางเลือกหนึ่งในการทำงานกับฐานข้อมูลผ่านคอนโซลคือสภาพแวดล้อมที่ใช้งานง่ายกว่าของไคลเอนต์ GUI ใด ๆ ตัวอย่างเช่น, pgAdmin- การติดตั้งนั้นง่ายมาก เราดำเนินการด้วยสิทธิ์ superuser:

sudo apt-get ติดตั้ง pgadmin3

ตอนนี้คุณสามารถวิ่งได้แล้ว pgAdminผ่านอินเทอร์เฟซแบบกราฟิก เราเลือกเซิร์ฟเวอร์ฐานข้อมูลท้องถิ่นจากรายการ (มีพอร์ตเริ่มต้น 5432) และเพิ่มฐานข้อมูลที่เราสร้างไว้แล้วด้วยพารามิเตอร์ที่เราระบุไว้

pgAdmin

ในเซิร์ฟเวอร์ท้องถิ่นนี้นอกเหนือจากโหนด ฐานข้อมูลคุณยังสามารถค้นหาโหนดที่เรียกว่า บทบาทการเข้าสู่ระบบ- บทบาทที่มีอยู่ทั้งหมด

จากฐานข้อมูลที่มีอยู่ทั้งหมด ให้เลือกฐานข้อมูลที่เราสร้างขึ้นด้วย ฐานข้อมูลประกอบด้วยวัตถุหลายประเภท ในหมู่พวกเขาควรให้ความสนใจเป็นพิเศษกับตารางและลำดับ ( ลำดับ).

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

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

เมื่อสร้างลำดับแล้ว เรามาเริ่มสร้างตารางกันดีกว่า เรายังระบุชื่อและเจ้าของด้วย บนแท็บที่สี่ คอลัมน์ก่อนอื่น เราเพิ่มคีย์หลัก ปุ่ม เพิ่มในหน้าต่างที่ปรากฏขึ้น ให้ระบุชื่อคอลัมน์ เช่น รหัส- สำหรับประเภทข้อมูลที่เราเลือก ใหญ่- บนแท็บที่สอง คำนิยามในสนาม ค่าเริ่มต้นเราระบุลำดับของเรา ฟิลด์จะต้องมีค่าเช่น nextval("message_id_seq"::regclass)- นั่นคือ แต่ละครั้งที่มีการเพิ่มแถวใหม่ ค่าถัดไปจะถูกดึงมาจากลำดับ เพิ่มคอลัมน์อื่นๆ ตามความจำเป็น ในที่สุดบนแท็บ ข้อจำกัดเพิ่มข้อจำกัดในคีย์หลัก ( คีย์หลัก- ในแท็บสุดท้าย เราสามารถชื่นชมผลลัพธ์โค้ด SQL ที่ pgAdmin สร้างขึ้น หลังจากคลิกตกลง ตารางจะถูกสร้างขึ้น

แทนที่จะให้ bigint เป็นคีย์หลัก คุณสามารถระบุให้เป็นประเภทคอลัมน์ได้ เรื่องใหญ่- ประเภทนี้จะเพิ่มขึ้นโดยอัตโนมัติเมื่อมีการเพิ่มแถวใหม่แต่ละแถว ดังนั้นจึงไม่จำเป็นต้องสร้างลำดับสำหรับแถวนั้น นั่นคือในกรณีที่ง่ายที่สุด หากคุณไม่มีกฎพิเศษใดๆ ในการสร้างรหัสบันทึก เราสามารถแนะนำให้ใช้ bigserial

ลองดูที่เนื้อหาของตารางของเรา โดยคลิกขวาที่มันแล้วเลือกในเมนูบริบท ดูข้อมูล - ดู 100 แถว.

ในหน้าต่างเดียวกัน คุณสามารถแก้ไขค่าของเซลล์ตารางใดๆ ได้อย่างรวดเร็ว หากตารางของคุณมีมากกว่า 100 เรคคอร์ด ให้เปลี่ยนจำนวนเรคคอร์ดที่แสดงในรายการดรอปดาวน์ที่ด้านบนของหน้าต่าง คุณสามารถแสดง 100, 500, 1,000 หรือรายการทั้งหมด แต่นี่ไม่ใช่อะไรมากไปกว่าวิธีการดูเนื้อหาของตารางอย่างรวดเร็วและคุณไม่ควรชินกับมัน หากคุณมีบันทึกหลายหมื่นรายการในตารางของคุณ ฉันไม่แนะนำให้แสดงบันทึกทั้งหมดในครั้งเดียว - ในกรณีนี้ ควรเขียนแบบสอบถามโดยใช้พารามิเตอร์จะดีกว่า ขีด จำกัดและ ชดเชย.