Pengurusan & Penggunaan Pangkalan Data PostgreSQL

PostgreSQL merupakan sistem pengurusan pangkalan data relasi sumber terbuka yang berkuasa, sangat boleh diperluas, dan mematuhi piawaian, yang sangat digemari komuniti pembangun. ServBay, sebuah lingkungan pembangunan web tempatan khusus untuk macOS, didatangkan dengan PostgreSQL terbina dalam, memudahkan proses pemasangan dan pengurusannya secara besar-besaran.

Dokumen ini berfungsi sebagai panduan bagi pembangun, menerangkan langkah demi langkah cara mengurus dan menggunakan PostgreSQL dengan cekap dalam persekitaran ServBay, dari pemasangan dan konfigurasi sehingga operasi harian, sandaran, pemulihan, pengoptimuman prestasi, dan sekuriti.

Pemasangan dan Konfigurasi PostgreSQL

ServBay menyediakan antara muka grafik yang intuitif untuk mengurus pakej perisian, termasuk PostgreSQL.

Memasang PostgreSQL

1. Buka aplikasi ServBay.
2. Klik pada Pakej di bar navigasi kiri.
3. Cari PostgreSQL dalam senarai pakej.
4. Pilih versi PostgreSQL yang anda perlukan (ServBay menyokong pelbagai versi, contoh PostgreSQL 10 hingga 17).
5. Klik butang Pasang untuk memulakan pemasangan.

Rajah: Pilih dan pasang versi PostgreSQL dalam ServBay

Memulakan, Memberhentikan & Mengurus Perkhidmatan PostgreSQL

Setelah pemasangan selesai, anda boleh mengurus status perkhidmatan PostgreSQL melalui platform pengurusan ServBay atau alat baris perintah servbayctl.

Menggunakan Platform Pengurusan ServBay

1. Buka aplikasi ServBay.
2. Klik pada Pakej di bar navigasi kiri.
3. Cari versi PostgreSQL yang telah dipasang.
4. Gunakan suis di sebelah pakej untuk mula, hentikan atau muat semula perkhidmatan tersebut.

Menggunakan alat baris perintah servbayctl

servbayctl ialah antara muka baris perintah berkuasa yang disediakan oleh ServBay untuk mengurus semua komponen ServBay.

# Mulakan perkhidmatan PostgreSQL versi spesifik (contoh PostgreSQL 16)
servbayctl start postgresql 16

# Hentikan perkhidmatan PostgreSQL versi spesifik (contoh PostgreSQL 16)
servbayctl stop postgresql 16

# Muat semula perkhidmatan PostgreSQL versi spesifik (contoh PostgreSQL 16)
servbayctl restart postgresql 16

# Semak status perkhidmatan PostgreSQL versi spesifik (contoh PostgreSQL 16)
servbayctl status postgresql 16

Nota: Nombor versi (seperti 16) dalam perintah mestilah sepadan dengan versi PostgreSQL yang anda pasang dalam ServBay.

Konfigurasi PostgreSQL

ServBay membolehkan anda menukar konfigurasi PostgreSQL dengan mudah. Konfigurasi yang betul kritikal untuk meningkatkan prestasi pangkalan data, menjamin keselamatan dan memastikan penggunaan sumber sistem yang optimum.

ServBay dilengkapi antara muka grafik yang kuat untuk mengkonfigurasi perkhidmatan PostgreSQL, dan anda juga boleh mengedit fail konfigurasi secara manual. Rujuk dokumen Mengubah Konfigurasi PostgreSQL, untuk ketahui cara mengubah dan optimakan parameter PostgreSQL melalui antara muka ServBay atau pengeditan manual.

Menyambung ke Pangkalan Data PostgreSQL

Menyambung ke PostgreSQL adalah langkah pertama sebelum operasi data. Anda boleh menggunakan alat baris perintah psql atau alat grafik seperti pgAdmin untuk menyambung ke instance PostgreSQL dalam ServBay.

ServBay menyokong dua cara utama sambungan PostgreSQL: sambungan TCP/IP (biasa untuk alat luar atau sambungan antara kontena/perkhidmatan) dan sambungan Socket tempatan (disyorkan untuk dalaman ServBay kerana lebih laju dan selamat).

Mendapatkan Kelayakan Sambungan

Sebelum menyambung, anda perlu mendapatkan nama pengguna dan kata laluan PostgreSQL anda. ServBay biasanya menetapkan kelayakan lalai sewaktu pemasangan atau inisialisasi, dan anda boleh dapatkannya di antara muka pengurusan ServBay — terutamanya untuk pengguna lalai (postgres) atau pengguna lain yang anda cipta.

Menyambung dengan Alat Baris Perintah psql

psql ialah alat klien baris perintah interaktif yang rasmi untuk PostgreSQL.

1. Sambungan TCP/IP: Kaedah ini menggunakan protokol rangkaian, sesuai untuk localhost atau alamat rangkaian lain. Port lalai PostgreSQL dalam ServBay ialah 5432.

   psql -U your_username -h localhost -d your_database -p 5432

   Gantikan your_username dengan nama pengguna anda, dan your_database dengan nama pangkalan data yang ingin disambung.

2. Sambungan Socket Tempatan: Dalam konfigurasi biasa, PostgreSQL di ServBay diset untuk menggunakan Unix Domain Socket, tanpa perlu melalui jaringan, menjadikan prestasi lebih baik dan selamat. Fail socket biasanya terletak di direktori sementara ServBay.

   psql -U your_username -h /Applications/ServBay/tmp -d your_database

   Gantikan your_username dengan nama pengguna anda, dan your_database dengan nama pangkalan data yang ingin disambung. /Applications/ServBay/tmp adalah laluan lalai simpanan socket ServBay untuk PostgreSQL.

Menyambung dengan Alat Grafik pgAdmin

pgAdmin ialah alat pengurusan grafik sumber terbuka popular untuk PostgreSQL, menawarkan pelbagai ciri canggih.

1. Pasang pgAdmin: Jika ServBay tidak menyertakan pgAdmin, anda boleh muat turun dan pasang dari laman rasmi pgAdmin.
2. Buka pgAdmin.
3. Cipta sambungan pelayan baru: Klik Add New Server.
4. Isi maklumat sambungan:
   • Tab General: Masukkan nama sambungan (contoh ServBay PostgreSQL).
   • Tab Connection:
     • Hostname/address: localhost
     • Port: 5432
     • Maintenance database: biasa postgres
     • Username: nama pengguna dari panel pengurusan ServBay (cth postgres)
     • Password: kata laluan dari panel ServBay
   • SSL mode: Pilih mengikut keperluan anda (kebiasaannya Prefer atau Require lebih selamat, namun dalam konfigurasi lalai ServBay, Allow atau Disable juga mungkin berfungsi; sila rujuk tetapan SSL dalam ServBay).
5. Klik Save untuk menyambung ke pelayan pangkalan data.

Operasi Asas Pengurusan Pangkalan Data

Sebaik sahaja disambung ke PostgreSQL, anda boleh melaksanakan pelbagai tugas pengurusan pangkalan data.

Menyenaraikan Pangkalan Data & Pengguna

Dalam baris perintah psql, gunakan perintah meta (bermula dengan \) untuk melihat pangkalan data dan pengguna semasa:

• Menyenaraikan semua pangkalan data:

  \l

• Menyenaraikan semua pengguna (role):

  \du

Mencipta Pangkalan Data & Pengguna

Anda boleh mengguna arahan SQL untuk mencipta pangkalan data dan pengguna baharu serta menetapkan kebenaran.

1. Cipta pangkalan data baharu:

   CREATE DATABASE servbay_demo_db;

   Disyorkan guna nama pangkalan data yang menggambarkan projek anda.

2. Cipta pengguna (role) baharu & tetapkan kata laluan:

   CREATE USER servbay_user WITH PASSWORD 'a_strong_password';

   Gantikan servbay_user dengan nama pengguna diinginkan, dan a_strong_password dengan kata laluan yang kukuh.

3. Berikan kebenaran ke atas pangkalan data kepada pengguna:

   GRANT ALL PRIVILEGES ON DATABASE servbay_demo_db TO servbay_user;

   Ini memberikan semua kebenaran ke atas servbay_demo_db kepada servbay_user. Ikut amalan terbaik keselamatan, berikan hanya keizinan minimum yang diperlukan.

Menetapkan Semula Kata Laluan Pengguna Root PostgreSQL

Jika anda terlupa kata laluan pengguna root (postgres), ServBay menawarkan cara mudah untuk tetapkan semula kata laluan. Biasanya, anda boleh lakukan ini terus dari antara muka pengurusan ServBay tanpa perlu arahan baris perintah atau suntingan fail konfigurasi. Sila rujuk antara muka pengguna atau dokumentasi berkaitan, cari tetapan pada pakej PostgreSQL untuk fungsi tetapkan semula kata laluan.

Sandaran & Pemulihan Pangkalan Data

Sandaran berkala adalah kunci keselamatan data. Dalam ServBay, anda boleh menggunakan alat standard PostgreSQL seperti pg_dump dan pg_restore ataupun fungsi sandaran terbina dalam ServBay.

Sandaran Manual Dengan pg_dump

pg_dump digunakan untuk mengeksport kandungan pangkalan data PostgreSQL.

Letakkan fail sandaran di direktori sandaran disyorkan oleh ServBay:

/Applications/ServBay/backup/postgresql

Contoh sandaran database menggunakan pg_dump:

pg_dump -U your_username -d your_database -F c -b -v -f /Applications/ServBay/backup/postgresql/your_database_backup.dump

• -U your_username: Tentukan nama pengguna pangkalan data.
• -d your_database: Nama pangkalan data untuk disandarkan.
• -F c: Format output "custom archive" (disyorkan untuk pemulihan fleksibel).
• -b: Termasuk objek besar (Blobs).
• -v: Paparkan butiran proses.
• -f <filepath>: Lokasi dan nama fail sandaran.

Pemulihan Manual Dengan pg_restore

pg_restore digunakan untuk memulihkan pangkalan data dari fail archive yang dihasilkan oleh pg_dump.

pg_restore -U your_username -d target_database -v /Applications/ServBay/backup/postgresql/your_database_backup.dump

• -U your_username: Nama pengguna untuk sambungan.
• -d target_database: Nama pangkalan data sasaran (cipta terlebih dahulu jika belum ada).
• -v: Paparkan butiran proses.
• <filepath>: Laluan ke fail sandaran.

Menggunakan Fungsi Sandaran Terbina Dalam ServBay

ServBay menawarkan antara muka grafik dan pilihan automasi untuk menyandarkan keseluruhan persekitaran pembangunan tempatan anda, termasuk pangkalan data PostgreSQL.

Ciri sandaran terbina dalam ServBay menyokong:

• Sandaran manual: Satu klik dari antara muka UI ServBay untuk cipta snapshot lingkungan semasa.
• Sandaran automatik: Tetapkan jadual sandaran supaya ServBay membuat sandaran secara berkala.

Kandungan sandaran termasuk tetapan ServBay, fail laman web, semua pangkalan data (termasuk PostgreSQL), sijil SSL, dan lain-lain. Disyorkan guna ciri ini untuk memudahkan proses sandaran dan memastikan keselamatan data. Pergi ke tetapan atau halaman utama ServBay untuk mengkonfigurasi dan mengurus pilihan sandaran.

Pengoptimuman Prestasi

Untuk memastikan PostgreSQL berjalan secara optimum dalam persekitaran ServBay, pertimbangkan langkah-langkah pengoptimuman berikut.

Pengoptimuman Indeks

Indeks dapat mempercepat carian data, terutamanya pada jadual besar atau kolum yang sering digunakan dalam pertanyaan.

Pastikan kolum yang kerap digunakan dalam syarat carian (WHERE), penghubung (JOIN) atau pengisihan (ORDER BY) telah diindeks.

CREATE INDEX idx_your_column_name ON your_table_name(your_column_name);

Gantikan your_table_name dengan nama jadual, your_column_name dengan nama kolum, dan idx_your_column_name dengan nama indeks.

Pengoptimuman Pertanyaan

Gunakan perintah EXPLAIN untuk menganalisis pelan eksekusi SQL. Ia membantu memahami bagaimana query anda diproses, mengenal pasti laluan pantas dan jalan lambat.

EXPLAIN SELECT * FROM your_table_name WHERE column_name = 'value';

Hasil EXPLAIN memaparkan maklumat sama ada query menggunakan indeks, kaedah sambungan, bilangan baris diimbas, dan sebagainya. Dari sini, anda boleh optimakan query atau strategi indeks.

Pengoptimuman Konfigurasi

Menukar fail konfigurasi PostgreSQL (biasanya postgresql.conf) membolehkan pelarasan parameter khusus agar sepadan dengan sumber perkakasan dan beban kerja anda.

Contohnya, pelarasan parameter shared_buffers menentukan jumlah memori yang digunakan PostgreSQL untuk cache data. Meningkatkan nilai ini dapat mengurangkan I/O cakera dan mempertingkatkan prestasi (pastikan tidak melebihi memori sistem anda).

# Contoh konfigurasi dalam postgresql.conf
shared_buffers = 1GB # Laraskan mengikut memori sistem & beban kerja

Servis PostgreSQL perlu dimulakan semula selepas menukar parameter ini. Sila rujuk dokumentasi pengubahsuaian konfigurasi ServBay untuk maklumat lanjut.

Pengurusan Keselamatan

Menjamin keselamatan pangkalan data amat kritikal, khususnya apabila data sensitif terlibat semasa pembangunan.

Tetapan Kata Laluan Kuat

Tetapkan kata laluan yang kukuh dan unik untuk semua pengguna pangkalan data, terutamanya pengguna hak tinggi seperti postgres. Kemas kini kata laluan secara berkala.

ALTER USER your_username WITH PASSWORD 'your_new_strong_password';

Sandaran Berkala

Seperti yang dinyatakan, sandaran berkala ialah perlindungan utama daripada kehilangan data. Gabungkan sandaran terbina dalam ServBay dan strategi manual pg_dump untuk sistem sandaran yang teguh.

Sekat Keizinan Pengguna

Amalkan prinsip "keizinan minimum", hanya berikan keizinan perlu untuk pengguna melaksanakan kerja mereka. Elakkan membenarkan ALL PRIVILEGES untuk semua pengguna.

-- Tarik semula semua keizinan pada pangkalan data dari pengguna
REVOKE ALL PRIVILEGES ON DATABASE mydatabase FROM your_username;

-- Berikan keizinan sambungan dan penciptaan jadual sementara (contoh)
GRANT CONNECT, TEMPORARY ON DATABASE mydatabase TO your_username;

-- Hak SELECT, INSERT, UPDATE, DELETE pada jadual tertentu
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE your_table_name TO your_username;

Kawalan Akses Rangkaian

Konfigurasi lalai ServBay hanya mengikat perkhidmatan pangkalan datanya ke alamat lokal (localhost atau 127.0.0.1), menghadkan akses dari rangkaian luar. Melainkan benar-benar perlu, elakkan mendedahkan pangkalan data kepada rangkaian luar kecuali anda sedar risiko berkaitan.

Soalan Lazim & Penyelesaian

Tidak Dapat Sambung ke PostgreSQL

1. Periksa Status Perkhidmatan PostgreSQL: Gunakan UI ServBay atau perintah servbayctl status postgresql <version> untuk semak status. Sekiranya tidak berjalan, mulakannya.
2. Periksa Parameter Sambungan: Pastikan nama pengguna, kata laluan, nama pangkalan data, hostname (localhost atau /Applications/ServBay/tmp), dan port (5432) betul. Semak kelayakan dari UI ServBay.
3. Periksa Log ServBay: Lihat log ServBay untuk ralat berkaitan PostgreSQL. Ini sering memberi petunjuk mengapa sambungan gagal.
4. Periksa Tetapan Firewall: Biasanya tidak isu di konfigurasi lokal ServBay, namun jika sistem anda atau perisian keselamatan pihak ketiga menetapkan peraturan firewall ketat, pastikan proses PostgreSQL ServBay dibenarkan sambungan lokal.

Isu Keizinan

Jika menerima ralat "Permission Denied" ketika melaksanakan operasi pangkalan data, ini biasanya kerana pengguna tidak mencukupi hak.

1. Periksa Keizinan Pengguna Semasa: Dalam psql, gunakan perintah \du untuk lihat senarai pengguna & sifat mereka. \dp <table_name> untuk hak akses pada jadual tertentu.
2. Berikan Hak Perlu: Sambung sebagai pengguna hak tinggi (misal postgres), dan berikan keizinan dengan perintah GRANT.

   GRANT ALL PRIVILEGES ON DATABASE mydatabase TO your_username; -- Hak pangkalan data
   GRANT SELECT ON TABLE your_table_name TO your_username; -- Hak jadual

Rumusan

PostgreSQL adalah sistem pengurusan pangkalan data yang berkuasa dan fleksibel, sementara ServBay memudahkan penyediaan dan pengurusannya atas macOS. Dengan panduan ini, anda kini boleh memasang, konfigurasikan, sambung, mengurus asas pangkalan data, sandar dan pulih data secara manual dan automatik, mengoptimumkan prestasi, serta mengukuhkan keselamatan pangkalan data dalam persekitaran ServBay. Penguasaan kemahiran ini akan membantu anda membina aplikasi web lokal secara lebih pantas dan selamat.