PostgreSQL

Jiyoung Joung, Niko D, Héctor H. Louzao P Versi F37 and newer Last review: 2022-12-19

Pengguna Fedora Server Edition dapat menemukan informasi tambahan di Pengaturan Server Database PostgreSQL

Instalasi

Pemasangan dan inisialisasi server PostgreSQL sedikit berbeda dibandingkan dengan paket lain dan distribusi Linux lainnya. Dokumen ini bertujuan untuk merangkum langkah-langkah pemasangan dasar yang relevan dengan rilis Fedora Linux terbaru.

$ sudo dnf install postgresql-server postgresql-contrib

Server postgresql tidak berjalan dan dinonaktifkan secara default. Untuk mengaturnya agar dimulai saat boot, jalankan:

$ sudo systemctl enable postgresql

Database perlu diisi dengan data awal setelah instalasi. Inisialisasi database dapat dilakukan menggunakan perintah berikut. Perintah ini akan membuat berkas konfigurasi postgresql.conf dan pg_hba.conf

$ sudo postgresql-setup --initdb --unit postgresql

Untuk memulai server postgresql secara manual, jalankan

$ sudo systemctl start postgresql

Pembuatan Pengguna dan Pembuatan Database

Sekarang Anda perlu membuat pengguna dan basis data untuk pengguna tersebut. Proses ini harus dijalankan dari akun pengguna postgres di sistem Anda.

$ sudo -u postgres psql

Dari sini Anda dapat membuat pengguna dan basis data postgres. Di sini, kita akan mengasumsikan akun pengguna komputer Anda bernama lenny. Catatan: Anda juga dapat menjalankan ini dari shell dengan createuser lenny dan createdb --owner=lenny carl.

postgres=# CREATE USER lenny WITH PASSWORD 'leonard';
postgres=# CREATE DATABASE my_project OWNER lenny;

Mungkin ide yang baik untuk menambahkan kata sandi untuk pengguna postgres saat Anda melakukannya:

postgres=# \password postgres

Tekan Ctrl + D atau \q untuk keluar dari sesi psql sambil tetap menjalankan sesi sebagai pengguna postgres. Sekarang Anda dapat mengakses basis data baru Anda dari akun pengguna Anda (lenny) dan mulai menggunakannya.

$ psql my_project

Konfigurasi Awal

Server postgresql menggunakan dua file konfigurasi utama

  • /var/lib/pgsql/data/postgresql.conf

  • /var/lib/pgsql/data/pg_hba.conf

Jika aplikasi Anda menampilkan error ident, Anda kemungkinan perlu menerapkan solusi yang dijelaskan di https://serverfault.com/questions/406606/postgres-error-message-fatal-ident-authentication-failed-for-user?newreg=a4fdc3e21349449985cc65b82399c5b4

$ sudo gedit /var/lib/pgsql/data/pg_hba.conf

dan edit host all all 127.0.0.1/32 ident menjadi host all all 127.0.0.1/32 md5 serta host all all ::1/128 ident menjadi host all all ::1/128 md5. Setelah melakukan perubahan ini, pastikan untuk me-restart layanan postgresql agar perubahan tersebut berlaku.

$ sudo systemctl restart postgresql

Perubahan ini seharusnya memungkinkan sebagian besar aplikasi untuk terhubung dengan nama pengguna/kata sandi.

Peningkatan

Seperti yang dapat Anda lihat dari pesan kesalahan dalam contoh saya, ini bukan instalasi baru, melainkan pembaruan.

Nov 14 11:45:56 mlich-lenovo.usersys.redhat.com postgresql-check-db-dir[2108]: An old version of the database format was found.
Nov 14 11:45:56 mlich-lenovo.usersys.redhat.com postgresql-check-db-dir[2108]: Use "postgresql-setup upgrade" to upgrade to version 9.3.

Dengan versi 9, Anda dapat menggunakan alat pembaruan. Alat ini dikemas sebagai postgresql-upgrade:

$ sudo postgresql-setup upgrade

Mengalihkan ke /bin/systemctl stop  postgresql.service
Memperbarui database: Berhasil

Berkas konfigurasi telah diganti dengan konfigurasi default.
Konfigurasi dan data sebelumnya disimpan di folder /var/lib/pgsql/data-old.

Lihat /var/lib/pgsql/pgupgrade.log untuk detailnya.

Data tersebut terletak di

  • /var/lib/pgsql/data

  • /var/lib/pgsql/data-old

Peningkatan itu sendiri akan mencadangkan data yang ada dan memigrasikan basis data Anda. Jangan lupa untuk memigrasikan konfigurasi Anda (dengan meld, misalnya: meld /var/lib/pgsql/data{,-old}/postgresql.conf).

Anda mungkin perlu mengalihkan postgresql ke mode trust sebelum memperbarui. Ini seharusnya sudah diperbaiki.

Anda juga dapat melakukan upgrade dengan cara men-dump basis data Anda dan memuatnya kembali. Untuk informasi lebih lanjut, lihat dokumentasi resmi.

Melewatkan rilis utama

Di Fedora 43, paket Postgres meningkat dari versi 16 menjadi 18. Pembaruan basis data tidak dapat melewati versi utama, dan prosedur di atas tidak akan berfungsi. Jika Anda belum melakukan pembaruan ke Postgres 17 di Fedora 42, Anda harus melakukan pembaruan dari versi 16 ke 18 dalam dua langkah di Fedora 43.

Pertama, Anda harus menghapus instalasi Postgres 18 dan menginstal Postgres 17. Anda dapat memeriksa paket-paket yang terinstal dengan perintah rpm -qa |grep ^postgres. Sebelum menginstal Postgres 17, silakan cadangkan direktori data Anda. Meskipun cadangan merupakan bagian dari prosedur upgrade, lebih baik berhati-hati daripada menyesal. Setelah menginstal paket Postgres 17, Anda dapat menggunakan prosedur upgrade di atas untuk melakukan upgrade sebenarnya dari 16 ke 17. Kemudian Anda harus menghapus instalasi Postgres 17 dan menginstal Postgres 18 kembali. Silakan jalankan prosedur upgrade di atas sekali lagi dan Anda siap untuk melanjutkan.

Selama prosedur dua langkah ini, Anda mungkin menemui beberapa kendala, yang dijelaskan di sini:

Jika Anda melihat error old cluster does not use data checksums but the new one does, Anda dapat memilih untuk membuat checksum atau melakukan upgrade dengan checksum dinonaktifkan. Untuk membuat checksum, masuk ke shell pengguna postgres lalu jalankan perintah berikut: pg_checksums -e -D <data-dir>. Namun, jika Anda melihat error ini, Anda sebenarnya sudah memperbarui paket ke rilis yang lebih baru sementara databasenya masih berada pada rilis sebelumnya. Karena itu, sering kali lebih mudah melakukan upgrade dengan PGSETUP_INITDB_OPTIONS="--no-data-checksums" postgresql-upgrade <data-dir> lalu membuat checksum setelahnya. Kecuali Anda mengubah direktori data Postgres, <data-dir> adalah /var/lib/pgsql/data.

Jangan lupa untuk menyesuaikan dan mengonsolidasikan berkas konfigurasi (*.conf) dari direktori data lama dan baru, serta memperbarui pengaturan kolasi untuk semua basis data.

Perbarui pengaturan kolasi (sebagai pengguna PostgreSQL): ALTER DATABASE <dbname> REFRESH COLLATION VERSION; \c <dbname>; REINDEX DATABASE;

Firewall

PostgreSQL berjalan pada port 5432 (atau port lain yang Anda atur di postgresql.conf). Di firewalld, agar perubahan tetap ada setelah reboot:

$ sudo firewall-cmd --permanent --add-port=5432/tcp

Untuk langsung menerapkan perubahan pada firewall yang sedang berjalan:

$ sudo firewall-cmd --add-port=5432/tcp

Dalam hal iptables:

$ sudo iptables -A INPUT -p tcp --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT

Perlu diingat bahwa Anda mungkin tidak ingin membuka server basis data Anda untuk seluruh dunia.

SELinux

Jika Anda menggunakan SELinux yang diaktifkan, Anda mungkin mengalami masalah saat mencoba melakukan konfigurasi non-standar. Misalnya, jika Anda ingin mengubah lokasi database Anda, Anda harus menambahkan pemetaan konteks baru untuk lokasi baru tersebut:

$ sudo semanage fcontext -a -t postgresql_db_t "/my/new/location(/.*)?"

Jika port default tidak berfungsi untuk Anda, Anda mungkin perlu memetakan jenis port Postgre ke port yang Anda inginkan:

$ sudo semanage port -a -t postgresql_port_t -p tcp 5433

Jika Anda menginstal aplikasi web yang ingin berkomunikasi dengan PostgreSQL melalui TCP/IP, Anda harus memberitahu SELinux untuk mengizinkan hal ini pada host server web:

$ sudo setsebool -P httpd_can_network_connect_db on

Konfigurasi

Seperti disebutkan di atas, server postgresql menggunakan dua file konfigurasi utama

  • /var/lib/pgsql/data/postgresql.conf

  • /var/lib/pgsql/data/pg_hba.conf

systemd

Beberapa parameter konfigurasi diteruskan ke daemon melalui opsi baris perintah. Perilaku ini dapat menggantikan pengaturan di postgresql.conf. Misalnya, jika Anda ingin mengubah nomor port server menjadi 5433, buatlah berkas bernama /etc/systemd/system/postgresql.service yang berisi:

.include /lib/systemd/system/postgresql.service
[Service]
Environment=PGPORT=5433

Catatan: Mengubah PGPORT atau PGDATA biasanya juga memerlukan penyesuaian konfigurasi SELinux; lihat bagian selinux.

Silakan ikuti dokumentasi systemd di 2 untuk informasi lebih lanjut.

postgresql.conf

Jika Anda ingin postgres menerima koneksi jaringan, Anda harus mengubah

listen_addresses = 'localhost'

menjadi

listen_addresses = '*'

pg_hba.conf

Setelah database Anda terkonfigurasi, Anda perlu mengatur akses ke server database Anda. Hal ini dapat dilakukan dengan mengedit berkas /var/lib/pgsql/data/pg_hba.conf. Di dalam berkas tersebut terdapat aturan-aturan seperti berikut:

# TYPE    DATABASE        USER            ADDRESS                 METHOD
  host    all             all             127.0.0.1/32            md5
  host    all             all             ::1/128                 md5
  local   all             postgres                                peer

Kolom pertama menunjukkan jenis koneksi. Kolom ini dapat memiliki nilai-nilai berikut:

  • lokal — Soket domain Unix

  • host — soket TCP/IP biasa atau yang dienkripsi SSL

  • hostssl — soket TCP/IP yang dienkripsi SSL

  • hostnossl — soket TCP/IP biasa

Kolom terakhir menentukan metode autentikasi yang akan digunakan.

  • md5 — Klien harus menyediakan kata sandi yang diproses menggunakan algoritma MD5.

  • ident — mendapatkan nama pengguna klien yang terhubung dari sistem operasi dan mencocokkannya dengan peta yang ditentukan

  • trust — anyone who is able to connect to PostgreSQL server may act as any user without supplying password

  • peer — mengambil nama pengguna dari sistem operasi dan memeriksa apakah nama tersebut sesuai dengan nama pengguna dalam database.

Ketika server basis data mengautentikasi klien, server mencari rekaman dengan tipe koneksi, alamat klien, basis data yang diminta, dan nama pengguna yang cocok. Segera setelah menemukan kredensial ini, server melakukan autentikasi. Jika autentikasi gagal, tidak ada rekaman selanjutnya yang diperhitungkan. Jika tidak ada rekaman yang cocok, akses klien ditolak.

Pengaturan default biasanya dibatasi untuk localhost.

Saat Anda menginstal server basis data dan pertama kali mencoba "membuatnya berfungsi", Anda harus mematikan firewall, SELinux, dan mengatur otentikasi postgres menjadi permisif. Perlu diingat bahwa hal ini akan sangat mengekspos server Anda, jadi lakukan hal ini hanya pada jaringan yang tepercaya — sebaiknya tanpa jaringan sama sekali:

host    all             all             127.0.0.1/32            trust

Segera setelah Anda dapat terhubung, nyalakan sistem keamanan satu per satu sambil memastikan koneksi dapat terhubung.

Untuk informasi lebih lanjut, lihat dokumentasi resmi untuk berkas pg_hba.conf.

Optimasi

Konfigurasi default postgres sangat kurang disetel. Ini dapat menangani aplikasi sederhana tanpa akses basis data yang konsisten, tetapi jika Anda memerlukan performa lebih tinggi, Anda harus mengonfigurasi ulang instans Anda. Semua pengaturan terjadi di /var/lib/pgsql/data/postgresql.conf. Selain itu, mekanisme pencatatan log tidak dikonfigurasi dengan sangat intuitif.

Kinerja

Jumlah klien yang dapat terhubung ke PostgreSQL secara bersamaan:

max_connections = <number>

shared_buffers adalah titik awal. Ini memberitahu PostgreSQL berapa banyak memori yang dialokasikan untuk caching. Menyetel nilai ini menjadi 25% dari total memori sistem Anda adalah langkah awal yang baik. Jika tidak cocok untuk Anda, coba atur antara 15% hingga 40% dari total memori.

shared_buffers = <memory unit>

Nilai ini digunakan oleh perencana kueri untuk mengetahui berapa banyak memori yang tersedia di sistem. Perencana kueri menggunakan informasi ini untuk menentukan apakah rencana kueri dapat muat dalam memori atau tidak. Menyetel nilai ini menjadi 50% dari total memori adalah praktik umum.

effective_cache_size = <memory unit>

Ketika PostgreSQL melakukan operasi pengurutan, ia merencanakan strateginya apakah akan mengurutkan kueri di disk atau di memori. Perlu diingat bahwa memori ini tersedia untuk setiap instans pengurutan. Dalam kasus beberapa pengguna yang mengirimkan kueri ke server basis data Anda, ini dapat meningkat cukup tinggi. Oleh karena itu ini terikat erat dengan max_connections.

work_mem = <memory unit>

Untuk informasi lebih lanjut tentang topik ini, saya menyarankan Anda untuk membaca dokumentasi resmi tentang penyetelan PostgreSQL.

Pencatatan

Secara default, log diputar setiap minggu dan Anda mungkin tidak menemukan banyak informasi di dalamnya. Anda mungkin melewatkan tingkat log, tanggal, waktu, dan sebagainya. Selain itu, untuk aplikasi web sederhana, beberapa orang lebih memilih untuk meningkatkan tingkat detail.

log_destination = 'stderr'

Ini sudah cukup baik. Jika Anda ingin syslog menangani log Anda, ubah 'stderr' menjadi 'syslog', atau bahkan 'syslog,stderr'. Jika Anda memilih syslog, jangan lupa untuk mengonfigurasi syslog itu sendiri juga; untuk info lebih lanjut, lihat dokumentasi resmi.

logging_collector = on

Jika logging dilakukan ke stderr, postgres akan mengumpulkan semua log jika Anda mengaktifkan opsi logging_collector.

Ini adalah opsi default:

log_filename = 'postgresql-%a.log'

Metode yang disarankan adalah memberi nama berkas log berdasarkan tanggal pembuatannya:

log_filename = 'postgresql-%G-%m.log

Rotasi. Hal ini benar-benar tergantung pada aplikasi itu sendiri. Dalam kasus aplikasi sederhana dengan sedikit data di database, semua log dapat disimpan secara permanen di disk tanpa perlu rotasi.

log_truncate_on_rotation = off
log_rotation_age = 31d

Tingkatkan jumlah entri dalam log:

client_min_messages = notice      # default notice
log_min_messages = info           # default warning
log_min_error_statement = notice  # default error

Jika Anda ingin mencatat kueri lambat, silakan gunakan opsi ini:

log_min_duration_statement = 1000  # in ms

Entri log default tidak mengandung banyak informasi:

FATAL:  Ident authentication failed for user "test"
DETAIL:  Connection matched pg_hba.conf line 84: "host    all             all             ::1/128                 ident"

Mari kita perbaiki menjadi:

2013-12-30 17:51:36 CET testx@::1(50867):postgres [11213] FATAL:  password authentication failed for user "testx"
2013-12-30 17:51:36 CET testx@::1(50867):postgres [11213] DETAIL:  Connection matched pg_hba.conf line 84: "host   all             all             ::1/128                 md5 "

Anda hanya perlu mengubah opsi log_line_prefix.

# %t -- timestamp
# %u -- user
# %r -- client's host
# %d -- database
# %p -- PID
log_line_prefix = '%t %u@%r:%d [%p] '

Jika Anda hanya menjalankan satu database dengan satu pengguna yang terhubung, lebih masuk akal untuk menyederhanakan prefiks menjadi

log_line_prefix = '%t [%p] '

Resep akhir

log_destination = 'stderr'
logging_collector = on
log_filename = 'postgresql-%G-%m.log'
log_truncate_on_rotation = off
log_rotation_age = 31d
client_min_messages = notice
log_min_messages = info
log_min_error_statement = notice
log_line_prefix = '%t %u@%r:%d [%p] '

Referensi

Dokumentasi lengkap paket RPM

Tuning kinerja

Konfigurasi pencatatan

Meningkatkan versi PostgreSQL

berkas pg_hba.conf

Melihat kesalahan ketik, sesuatu yang hilang atau sudah usang, atau hal lain yang dapat ditingkatkan? Edit dokumen ini di https://forge.fedoraproject.org/docs/quick-docs.

Want to help? Learn how to contribute to Fedora Docs ›