Pengurusan & Penggunaan Pangkalan Data PostgreSQL

PostgreSQL ialah sistem pengurusan pangkalan data relasi sumber terbuka yang berkuasa, sangat boleh diperluas, dan patuh kepada piawaian, sangat popular dalam komuniti pembangun. Sebagai persekitaran pembangunan web tempatan yang menyokong macOS dan Windows, ServBay disertakan dengan PostgreSQL, memudahkan proses pemasangan serta pengurusan.

Artikel ini menjadi panduan lengkap untuk pembangun, menjelaskan langkah demi langkah bagaimana mengurus dan menggunakan PostgreSQL secara berkesan dalam ServBay, merangkumi dari pemasangan, konfigurasi, operasi harian, sandaran & pemulihan, pengoptimuman prestasi hingga keselamatan.

Pemasangan dan Konfigurasi PostgreSQL

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

Pemasangan PostgreSQL

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

Gambar: Pemilihan dan pemasangan versi PostgreSQL dalam ServBay

Memulakan, Memberhenti & Mengurus Perkhidmatan PostgreSQL

Selepas pemasangan, anda boleh mengurus status perkhidmatan PostgreSQL melalui platform pengurusan ServBay atau alat CLI servbayctl.

Menggunakan Platform Pengurusan ServBay

1. Buka aplikasi ServBay.
2. Klik pada tab navigasi kiri Pakej.
3. Cari versi PostgreSQL yang telah dipasang.
4. Gunakan suis di sebelah pakej untuk memulakan, memberhenti atau memulakan semula perkhidmatan.

Menggunakan alat baris perintah servbayctl

servbayctl ialah antara muka CLI yang berkuasa untuk mengurus komponen ServBay.

# Memulakan PostgreSQL versi tertentu (contoh PostgreSQL 16)
servbayctl start postgresql 16

# Memberhenti PostgreSQL versi tertentu (contoh PostgreSQL 16)
servbayctl stop postgresql 16

# Mulakan semula PostgreSQL versi tertentu (contoh PostgreSQL 16)
servbayctl restart postgresql 16

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

Nota: Nombor versi (seperti 16) dalam arahan mesti sama dengan versi PostgreSQL yang anda telah pasang dalam ServBay.

Konfigurasi PostgreSQL

ServBay membenarkan anda mengubah konfigurasi PostgreSQL dengan mudah. Konfigurasi yang betul penting untuk prestasi pangkalan data yang lebih baik, keselamatan dan penggunaan sumber sistem secara optimum.

ServBay menyediakan antara muka grafik untuk mengubah tetapan PostgreSQL, atau anda boleh sunting fail konfigurasi secara manual. Sila rujuk dokumen Ubah Konfigurasi PostgreSQL untuk maklumat lanjut tentang cara melakukan perubahan dan pengoptimuman parameter konfigurasi PostgreSQL melalui ServBay atau secara manual.

Sambungkan Pangkalan Data PostgreSQL

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

ServBay menawarkan dua kaedah utama untuk sambungan: sambungan TCP/IP (untuk alat luaran atau merentasi kontena/perkhidmatan) dan sambungan socket tempatan (disyorkan untuk kegunaan dalaman ServBay, lebih cepat dan selamat).

Dapatkan Maklumat Kelayakan Sambungan

Sebelum menyambung, dapatkan nama pengguna dan kata laluan PostgreSQL. ServBay biasanya menyediakan kelayakan lalai semasa pemasangan/permulaan pangkalan data. Anda boleh melihat butiran ini pada antara muka pengurusan ServBay, terutama untuk pengguna lalai (postgres) atau pengguna lain yang anda cipta.

Sambung menggunakan alat baris perintah psql

psql ialah klien interaktif rasmi PostgreSQL.

1. Sambungan TCP/IP: Kaedah ini melalui protokol rangkaian, sesuai untuk localhost dan alamat IP 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 pangkalan data anda & your_database dengan nama pangkalan data yang ingin disambung.

2. Sambungan socket tempatan (hanya macOS): Dalam macOS, ServBay mengkonfigurasi PostgreSQL untuk menggunakan socket Unix Domain tempatan, mempertingkatkan prestasi dan keselamatan. Fail socket biasanya di direktori sementara ServBay.

   macOS:

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

   Gantikan your_username dengan nama pengguna anda, your_database dengan nama pangkalan data, /Applications/ServBay/tmp ialah laluan lalai socket PostgreSQL dalam ServBay.

   Windows: Versi Windows menggunakan sambungan TCP/IP. Sila rujuk cara sambungan di atas.

Sambung menggunakan alat grafik pgAdmin

pgAdmin ialah alat pengurusan pangkalan data PostgreSQL sumber terbuka yang terkenal.

1. Pasang pgAdmin: Jika ServBay tidak menyertakan pgAdmin, muat turun dan pasang dari laman rasmi pgAdmin.
2. Buka pgAdmin.
3. Cipta sambungan pelayan baharu: Klik Add New Server.
4. Masukkan maklumat sambungan:
   • Tab General: Namakan sambungan (cth. ServBay PostgreSQL).
   • Tab Connection:
     • Hostname/address: localhost
     • Port: 5432
     • Maintenance database: Biasanya postgres
     • Username: Nama pengguna dari panel ServBay (cth. postgres)
     • Password: Kata laluan dari panel ServBay
   • SSL mode: Pilih ikut keperluan (biasanya Prefer atau Require lebih selamat, tapi konfigurasi ServBay lalai mungkin Allow atau Disable—semak tetapan SSL ServBay).
5. Klik Save untuk sambungan ke pelayan pangkalan data.

Operasi Asas Pengurusan Pangkalan Data

Selepas sambungan ke PostgreSQL, anda boleh melakukan pelbagai operasi pentadbiran pangkalan data.

Senarai Pangkalan Data & Pengguna

Dalam psql, arahan meta (\) boleh digunakan untuk paparan pangkalan data dan pengguna:

• Senarai semua pangkalan data:

  \l

• Senarai semua pengguna (peranan):

  \du

Cipta Pangkalan Data & Pengguna

SQL berikut membenarkan anda cipta pangkalan data baharu, pengguna dan tetapkan keizinan.

1. Cipta pangkalan data baharu:

   CREATE DATABASE servbay_demo_db;

   Gunakan nama yang jelas untuk pangkalan data, sebaiknya berkaitan projek anda.

2. Cipta pengguna (peranan) baharu dan tetapkan kata laluan:

   CREATE USER servbay_user WITH PASSWORD 'a_strong_password';

   Gantikan servbay_user dengan nama pengguna pilihan anda & a_strong_password dengan kata laluan yang selamat.

3. Berikan keizinan kepada pengguna:

   GRANT ALL PRIVILEGES ON DATABASE servbay_demo_db TO servbay_user;

   Ini akan memberikan semua keizinan servbay_user atas pangkalan data servbay_demo_db. Untuk keselamatan, hanya berikan keizinan minimum yang diperlukan.

Tetapkan semula kata laluan root PostgreSQL

Jika anda lupa kata laluan pengguna root (postgres) pangkalan data PostgreSQL, ServBay menyediakan fungsi tetapan semula kata laluan mudah. Biasanya, ini boleh dilakukan melalui antara muka pengurusan ServBay — tanpa perlu arahan rumit atau sunting fail konfigurasi. Rujuk panel pengguna atau dokumentasi ServBay untuk lokasi fungsi tetapan semula pada pakej PostgreSQL.

Sandaran dan Pemulihan Pangkalan Data

Sandaran berkala adalah langkah penting bagi keselamatan data. Dalam ServBay, anda boleh menggunakan alat standard PostgreSQL seperti pg_dump & pg_restore, atau fungsi sandaran terbina ServBay.

Sandaran manual menggunakan pg_dump

Alat pg_dump digunakan untuk eksport kandungan pangkalan data PostgreSQL.

Sebaiknya simpan fail sandaran dalam direktori sandaran yang disyorkan ServBay:

macOS:

/Applications/ServBay/backup/postgresql

Windows:

C:\ServBay\backup\postgresql

Contoh sandaran pangkalan data menggunakan pg_dump:

macOS:

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

Windows:

pg_dump -U your_username -d your_database -F c -b -v -f C:\ServBay\backup\postgresql\your_database_backup.dump

• -U your_username: Nama pengguna untuk sambungan pangkalan data.
• -d your_database: Nama pangkalan data yang ingin disandarkan.
• -F c: Format arkib khas (disyorkan untuk pemulihan fleksibel).
• -b: Termasuk objek besar (Blobs).
• -v: Paparan butiran proses.
• -f <filepath>: Laluan dan nama fail sandaran.

Pemulihan manual menggunakan pg_restore

pg_restore digunakan untuk memulihkan pangkalan data PostgreSQL dari fail arkib hasil pg_dump.

macOS:

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

Windows:

pg_restore -U your_username -d target_database -v C:\ServBay\backup\postgresql\your_database_backup.dump

• -U your_username: Nama pengguna untuk sambungan pangkalan data.
• -d target_database: Nama pangkalan data sasaran untuk pemulihan. Jika belum wujud, cipta dahulu.
• -v: Paparan butiran proses.
• <filepath>: Laluan fail sandaran.

Sandaran terbina dalam ServBay

ServBay menawarkan antara muka grafik serta pilihan automatik untuk sandaran seluruh persekitaran pembangunan tempatan anda, termasuk pangkalan data PostgreSQL.

Ciri sandaran ServBay termasuk:

• Sandaran manual: Satu klik snapshot dari UI ServBay.
• Sandaran automatik: Tetapkan jadual sandaran untuk proses berkala automatik.

Kandungan sandaran meliputi tetapan ServBay, fail projek web, semua pangkalan data (termasuk PostgreSQL), sijil SSL dan lain-lain. Sangat disyorkan untuk gunakan ciri ini bagi proses sandaran yang lebih mudah dan keselamatan data. Cari opsyen sandaran dalam tetapan/antara muka utama ServBay untuk konfigurasi lanjut.

Pengoptimuman Prestasi

Untuk memastikan pangkalan data PostgreSQL anda berjalan efisien dalam ServBay, pertimbangkan strategi pengoptimuman berikut.

Pengoptimuman Indeks

Indeks mempercepatkan pencarian data, terutamanya untuk jadual besar dan kolum yang kerap digunakan dalam syarat carian (WHERE), penghubung (JOIN) atau susunan (ORDER BY).

Pastikan anda mencipta indeks untuk kolum yang selalu digunakan sebagai syarat carian.

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, idx_your_column_name dengan nama indeks.

Pengoptimuman Syor SQL

Gunakan arahan EXPLAIN untuk analisis pelan pelaksanaan SQL, memahami bagaimana pangkalan data melaksanakan carian anda, dan mengenal pasti masalah prestasi.

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

Keputusan EXPLAIN menunjukkan sama ada indeks digunakan, kaedah penyambungan, bilangan baris yang diimbas, dll. Bertindak balas berdasarkan dapatan untuk optimasi SQL atau strategi indeksasi.

Pengoptimuman Konfigurasi

Suntingan fail konfigurasi PostgreSQL seperti postgresql.conf membolehkan pelarasan parameter agar sesuai dengan kapasiti dan beban kerja sistem anda.

Contohnya, ubah nilai shared_buffers untuk mengawal jumlah memori yang diperuntukkan kepada cache PostgreSQL. Meningkatkannya dapat mengurangkan I/O cakera—namun jangan melebihi RAM sistem.

# Contoh konfigurasi dalam postgresql.conf
shared_buffers = 1GB # Laraskan mengikut kapasiti RAM dan beban kerja

Restart perkhidmatan PostgreSQL bagi perubahan konfigurasi berkuat kuasa. Rujuk dokumentasi ServBay untuk kaedah suntingan konfigurasi.

Pengurusan Keselamatan

Keselamatan pangkalan data adalah kritikal, terutama bila mengurus data sensitif semasa pembangunan.

Tetapkan kata laluan yang kuat

Untuk semua pengguna, khususnya pengguna berkuasa tinggi (cth. postgres), tetapkan kata laluan kompleks dan unik. Tukar kata laluan secara berkala.

ALTER USER your_username WITH PASSWORD 'your_new_strong_password';

Sandaran berkala

Seperti dijelaskan sebelum ini, sandaran berkala sangat penting untuk melindungi data. Gabungkan ciri sandaran terbina dalam ServBay dengan strategi manual pg_dump bagi sistem sandaran yang kukuh.

Sekat keizinan pengguna

Amalkan prinsip keizinan minimum—benarkan hanya keizinan yang diperlukan kepada setiap pengguna pangkalan data. Elakkan beri ALL PRIVILEGES kepada semua pengguna.

-- Batalkan semua keizinan pengguna ke atas pangkalan data
REVOKE ALL PRIVILEGES ON DATABASE mydatabase FROM your_username;

-- Benarkan pengguna akses pangkalan data & cipta jadual sementara (contoh)
GRANT CONNECT, TEMPORARY ON DATABASE mydatabase TO your_username;

-- Benarkan pengguna SELECT, INSERT, UPDATE, DELETE ke atas jadual tertentu
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE your_table_name TO your_username;

Kawalan akses rangkaian

Konfigurasi lalai ServBay mengikat perkhidmatan pangkalan data ke antara muka tempatan (localhost atau 127.0.0.1), menyekat akses rangkaian luar secara terus—menambah keselamatan. Kecuali ada keperluan khusus dan faham risikonya, elakkan dedahkan pangkalan data ke rangkaian luar.

Soalan Lazim & Penyelesaian Masalah

Gagal sambung ke PostgreSQL

1. Periksa perkhidmatan PostgreSQL berjalan: Gunakan UI ServBay atau arahan servbayctl status postgresql <version> untuk semak status. Jika tak berjalan, sila mula permkhidmatan.
2. Periksa parameter sambungan: Pastikan nama pengguna, kata laluan, nama pangkalan data, hos dan port betul. Semak maklumat kelayakan pada UI ServBay.
   • macOS: localhost atau /Applications/ServBay/tmp, port 5432.
   • Windows: localhost, port 5432.
3. Periksa log ServBay: Lihat fail log ServBay untuk mesej ralat berkaitan PostgreSQL—ini membantu mengenal pasti punca kegagalan sambungan.
4. Periksa tetapan firewall: Biasanya tidak jadi masalah dengan konfigurasi ServBay lalai, tapi jika ada perisian keselamatan atau firewall aktif, pastikan PostgreSQL boleh menerima sambungan tempatan dari direktori ServBay.

Masalah keizinan

Ralat “Permission Denied” berlaku apabila pengguna sekarang tiada keizinan mencukupi.

1. Semak keizinan pengguna sekarang: Dalam psql, gunakan \du untuk senarai pengguna & atribut, dan \dp <table_name> untuk semak keizinan jadual tertentu.
2. Berikan keizinan yang diperlukan: Sambung sebagai pengguna berkuasa (cth. postgres), kemudian gunakan arahan GRANT untuk beri keizinan kepada pengguna bermasalah.

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

Kesimpulan

PostgreSQL ialah sistem pangkalan data yang berkuasa & fleksibel, dan ServBay sangat memudahkan proses pemasangan serta pengurusan PostgreSQL di macOS & Windows. Dengan rujukan artikel ini, anda boleh menguasai pemasangan, konfigurasi, sambungan, pengurusan asas pangkalan data, memanfaatkan fungsi sandaran terbina dalam ServBay, melakukan sandaran/pemulihan manual, mengoptimumkan prestasi serta menangani aspek keselamatan. Kemahiran ini akan membantu anda bekerja secara lebih cekap dan selamat dalam pembangunan web tempatan.