Pada artikel ini kita akan menginstal DBMS PostgreSQL 11 di Linux CentOS 7, kami akan melakukan konfigurasi dasar server dan DBMS, mempertimbangkan parameter utama dari file konfigurasi, serta cara menyetel kinerja. PostgreSQL adalah sistem manajemen basis data objek-relasional populer yang populer. Meskipun tidak seluas MySQL / MariDB, ini adalah yang paling profesional.
Kekuatan PostgreSQL:
- Kepatuhan penuh dengan standar SQL;
- Kinerja tinggi melalui manajemen konkurensi multivariat (MVCC);
- Skalabilitas (banyak digunakan di lingkungan yang sarat muatan);
- Dukungan untuk banyak bahasa pemrograman;
- Mekanisme transaksi dan replikasi yang andal;
- Dukungan data JSON.
Konten:
- Instal PostgreSQL di CentOS / RHEL
- Hubungkan ke PostgreSQL, buat database, pengguna
- Opsi file konfigurasi PostgreSQL dasar
- Mencadangkan dan memulihkan database di PostgreSQL
- Optimasi dan Tuning PostgreSQL
Instal PostgreSQL di CentOS / RHEL
Meskipun PostgreSQL dapat diinstal dari repositori basis CentOS, kami akan menginstal repositori dari pengembang, karena selalu memiliki versi terbaru dari paket.
Langkah pertama adalah menginstal repositori PosgreSQL (saat ini diinstal sebagai berikut):
yum instal -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Repositori ini berisi versi terbaru PostgreSQL dan versi yang lebih lama. Informasi repositori adalah sebagai berikut:
Instal versi terbaru yang tersedia (PostrgeSQL 11) menggunakan yum.
yum instal postgresql11-server -y
Selama instalasi, server PostgreSQL itu sendiri dan pustaka yang diperlukan diinstal:
Menginstal: libicu-50.2-3.el7.x86_64 1/4 Instalasi: postgresql11-libs-11.5-1PGDG.rhel7.x86_64 2/4 Instalasi: postgresql11-11.5-1PGDG.rhel7.x86_64 3/4 Instalasi: postgresql11-server- 11.5-1PGDG.rhel7.x86_64 4/4
Setelah menginstal paket, Anda perlu menginisialisasi database:
/ usr / pgsql-11 / bin / postgresql-11-setup initdb
Juga, segera tambahkan server database ke startup dan mulai:
systemctl aktifkan postgresql-11
systemctl mulai postgresql-11
Untuk memastikan server telah dimulai dan tidak ada masalah, periksa statusnya:
[root @ server ~] # systemctl status postgresql-11
● postgresql-11.service - Server database PostgreSQL 11 Dimuat: dimuat (/usr/lib/systemd/system/postgresql-11.service; diaktifkan; vendor telah disetel: dinonaktifkan) Aktif: aktif (aktif) sejak Wed 2019-09-18 13:01:56 +06; 26d lalu Documents: https://www.postgresql.org/docs/11/static/ Proses: 6614 ExecStartPre = / usr / pgsql-11 / bin / postgresql-11-check-db-dir $ PGDATA (code = keluar, status = 0 / SUKSES) PID Utama: 6619 (postmaster) CGroup: /system.slice/postgresql-11.service ├─6619 / usr / pgsql-11 / bin / postmaster -D / var / lib / pgsql / 11 / data / ├─6621 postgres: logger ├─6623 postgres: checkpointer ├─6624 postgres: penulis latar belakang ├─6625 postgres: walwriter ├─6626 postgres: peluncur autovacuum ├─6627 postgres: statistik collector └─6628 postgres: peluncur replikasi logis: peluncur replikasi logis 18 Sep 13:01:56 server.1.com systemd [1]: Memulai server database PostgreSQL 11 ... 18 Sep 13:01:56 server.1.com postmaster [6619]: 2019-09-18 13: 01: 56.399 +06 [6619] LOG: mendengarkan pada alamat IPv6 ":: 1", port 5432 Sep 18 13:01:56 server.1.com postmaster [6619]: 2019-09-18 13: 01: 56.399 +06 [6619 ] LOG: mendengarkan pada alamat IPv4 "127.0.0.1", port 5432 Sep 18 13:01:56 server.1.com postmaster [6619]: 2019-09-18 13: 01: 56.401 +06 [6619] LOG: mendengarkan pada soket Unix "/var/run/postgresql/.s.PGSQL.5432" 18 Sep 13:01:56 server.1.com postmaster [6619]: 2019-09-18 13: 01: 56.409 +06 [6619] LOG: mendengarkan pada soket Unix "/tmp/.s.PGSQL.5432" 18 Sep 13:01:56 server.1.com postmaster [6619]: 2019-09-18 13: 01: 56.427 +06 [ 6619] LOG: pengalihan log output ke proses collector logging 18 Sep 13:01:56 server.1.com postmaster [6619]: 2019-09-18 13: 01: 56.427 +06 [6619] PETUNJUK: Output log di masa depan akan muncul dalam direktori "log". 18 Sep 13:01:56 server.1.com systemd [1]: Memulai server database PostgreSQL 11.
Jika Anda memerlukan akses ke PostgreSQL dari luar, Anda perlu membuka port TCP / 5432 di firewall standar di Centos 7:
# firewall-cmd --get-active-zones
antarmuka publik: eth0
# firewall-cmd --zone = publik --add-port = 5432 / tcp --permanent
# firewall-cmd --reload
Atau via iptables:
# iptables-A INPUT -m state --state NEW -m tcp -p tcp --port 5432 -j MENERIMA
#service iptables restart
Jika SELinux diaktifkan, lakukan:
setsebool -P httpd_can_network_connect_db 1
Hubungkan ke PostgreSQL, buat database, pengguna
Secara default, ketika menginstal PostgreSQL, ada satu user -postgres di sistem.
Saya tidak merekomendasikan menggunakannya untuk bekerja dengan database, lebih baik untuk membuat pengguna untuk setiap database secara terpisah.
Untuk terhubung ke server postgres, Anda harus memasukkan perintah:
[root @ server /] # sudo -u postgres psql
psql (11.5) Ketik "bantuan" untuk bantuan.
postgres = #
Konsol PostgreSQL telah dibuka. Mari kita tunjukkan beberapa contoh kontrol PostgreSQL sederhana dari konsol psql.
Karena setiap pengguna sistem dapat masuk ke postrgesql, pertama-tama Anda perlu mengubah kata sandi pengguna postgres.
ALTER PERAN postgres DENGAN PASSWORD 'super_str0ng_pa $$ word';
Segera buat database baru, pengguna dan beri dia hak penuh untuk database ini:
postgres = # BUAT DATABASE mydbtest;
postgres = # CREATE USER mydbuser WITH password '123456789';
postgres = # GRANT SEMUA HAK PRIBADI PADA DATABASE mydbtest TO mydbuser;
Terhubung ke database:
postgres = # \ c databasename
Daftar tabel:
postgres = # \ dt
Daftar pertanyaan basis data:
postgres = # select * from pg_stat_activity di mana datname = "dbname"
Setel ulang semua koneksi basis data:
postgres = # select pg_terminate_backend (pid) dari pg_stat_activity di mana datname = 'dbname'
Informasi tentang sesi saat ini dapat diperoleh sebagai berikut:
postgres = # \ conninfo
Untuk menyelesaikan konsol psql, jalankan:
postgres = # \ q
Seperti yang sudah Anda perhatikan, sintaksinya tidak berbeda dari MariaDB atau MySQL yang sama, dan oleh karena itu kami tidak akan memikirkan perintah-perintah dari tipe yang sama.
Perhatikan bahwa untuk manajemen database PostgreSQL yang lebih mudah dari antarmuka web, disarankan untuk menggunakan pgAdmin4 (ditulis dengan Python dan Javascript / jQuery). Ini adalah analog dengan pengembang web PhpMyAdmin yang biasa.
Opsi file konfigurasi PostgreSQL dasar
File konfigurasi Postgresql terletak di direktori / var / lib / pgsql / 11 / data:
- postgresql.conf - File konfigurasi postgresql itu sendiri
- pg_hba.conf - file dengan pengaturan akses. Dalam file ini, Anda dapat mengatur berbagai batasan untuk pengguna, menetapkan kebijakan untuk menghubungkan ke database;
- pg_ident.conf - file ini digunakan saat mengidentifikasi klien menggunakan protokol ident.
Untuk mencegah pengguna lokal masuk ke postgres tanpa otorisasi, tentukan dalam file pg_hba.conf:
lokal semua host md5 semua 127.0.0.1/32 md5
Pertimbangkan parameter terpenting dalam file konfigurasi postgresql.conf:
- listen_addresses - menunjukkan alamat IP mana server akan menerima koneksi klien. Secara default, localhost ditentukan, yang berarti hanya koneksi lokal yang memungkinkan. Untuk melakukan ini pada semua antarmuka IPv4, tentukan 0.0.0.0
- max_connections - seperti pada DBMS lainnya, ini adalah jumlah maksimum koneksi simultan ke server database;
- temp_buffers - ukuran maksimum buffer sementara;
- shared_buffers - Jumlah memori bersama yang digunakan oleh server database. Biasanya diatur pada 25% dari memori yang dipasang di server;
- ukuran efektif_cache_ - Parameter yang membantu penjadwal postgres menentukan jumlah memori yang tersedia untuk cache ke disk. Biasanya, parameter ditetapkan pada ukuran 50-75% dari total RAM di server;
- work_mem - jumlah memori yang akan digunakan oleh operasi sortir DBMS internal - ORDER BY, DISTINCT, dan menggabungkan;
- maintenance_work_mem - jumlah memori yang akan digunakan oleh operasi internal - VACUUM, CREATE INDEX dan ALTER TABLE ADD ASE KUNCI;
- fsync - jika opsi ini diaktifkan, DBMS akan menunggu data fisik ditulis ke hard drive. Dengan fsync diaktifkan, Anda akan lebih mudah mengembalikan database setelah kegagalan sistem atau perangkat keras. Secara alami, dimasukkannya parameter ini secara signifikan mengurangi kinerja DBMS, tetapi meningkatkan keandalan penyimpanan. Saat menonaktifkan parameter ini, ada baiknya menonaktifkan full_page_writes;
- max_stack_depth - ukuran tumpukan maksimum (2 MB secara default);
- max_fsm_pages - menggunakan parameter ini, Anda dapat mengatur ruang disk kosong di server. Misalnya, setelah menghapus data dari tabel, ruang yang sebelumnya ditempati tidak dibebaskan pada disk, tetapi ditandai pada peta ruang bebas dengan label "bebas" dan kemudian digunakan untuk entri baru dalam tabel. Jika server secara aktif merekam / menghapus data dalam tabel, meningkatkan parameter ini secara positif akan mempengaruhi kinerja;
- wal_buffers - jumlah memori bersama (shared_buffers) yang digunakan untuk menyimpan data WAL;
- wal_writer_delay - waktu antara periode penulisan WAL ke disk;
- commit_delay - keterlambatan antara menulis transaksi ke buffer WAL dan membuangnya ke disk;
- syncous_commit - parameter menentukan bahwa hasil penyelesaian transaksi yang berhasil akan dikirim ketika data WAL secara fisik ditulis ke disk.
Mencadangkan dan memulihkan database di PostgreSQL
Ada beberapa cara untuk membuat cadangan database PostgreSQL. Mari kita pertimbangkan opsi paling sederhana..
Pertama, periksa database mana yang berjalan di server:
postgres = # \ list
Kami memiliki 4 database, 3 di antaranya adalah sistem (postgres dan templat).
Sebelumnya, kami membuat database dengan nama "mydbtest", menggunakan contohnya dan membuat cadangan.
Salah satu cara untuk membuat cadangan adalah dengan menjalankannya menggunakan utilitas pg_dump:
sudo -u postgres pg_dump mydbtest> /root/dupm.sql - kami menjalankan permintaan dari pengguna postgres, tentukan database yang diinginkan dan path ke file tempat Anda ingin menyimpan dump database. Sistem cadangan Anda dapat mengambil dump basis data, atau jika Anda menggunakan server web, Anda dapat mengirimkannya ke penyimpanan cloud Anda.
Untuk mengembalikan dump yang ditentukan ke database yang diinginkan, Anda dapat menggunakan utilitas psql:
sudo -u postgres psql mydbtest < /root/dupm.sql
Anda juga dapat membuat cadangan dalam format dump khusus dan dikompres menggunakan gzip:
sudo -u postgres pg_dump -Fc mydbtest> /root/dumptest.sql
Dump seperti itu dipulihkan menggunakan utilitas pg_restore:
sudo -u postgres pg_restore -d mydbtest /root/dumptest.sql
Pengaturan lebih lanjut dapat ditemukan dalam bantuan untuk utilitas ini:
man psql
man pg_dump
man pg_restore
Optimasi dan Tuning PostgreSQL
Pada artikel sebelumnya tentang MariaDB, kami menunjukkan bagaimana parameter file konfigurasi my.cnf dapat direduksi menjadi ideal menggunakan tuner. Untuk PostgreSQL ada, meskipun itu lebih tepat untuk mengatakan utilitas seperti PgTun, tetapi sayangnya itu belum diperbarui untuk waktu yang lama. Pada saat yang sama, ada banyak layanan online yang dengannya Anda dapat mengonfigurasi konfigurasi optimal untuk PostgreSQL Anda. Saya suka layanannya pgtune.leopard.in.ua.
Antarmukanya sangat sederhana. Anda perlu menentukan parameter server Anda (profil, prosesor, memori, jenis disk) dan klik tombol "Buat". Sebagai hasilnya, Anda akan ditawari varian file konfigurasi postgresql.conf dengan nilai yang direkomendasikan dari parameter DBMS utama.
Sebagai contoh, untuk server SSD VPS dengan 2 GB RAM, 2 CPU, pengaturan berikut ini di postgresql.conf direkomendasikan untuk memulai beberapa situs:
# Versi DB: 11 # Tipe OS: linux # Tipe DB: web # Total Memori (RAM): 2 GB # CPU num: 2 # Koneksi num: 20 # Penyimpanan Data: ssd max_connections = 20 shared_buffers = 512MB efektif_cache_size = 1536MB maintenance_work_mem = 128MB checkpoint_completion_target = 0,7 wal_buffers = 16MB default_statistics_target = 100 random_page_cost = 1,1 efektif_io_concurrency = 200 work_mem = 26214kB min_wal_size = 1GB max_wal_size = 2GB max_worker_process_parallel_parallel_parallel_parallel_parallel_parallel_parallel_parallel_parallel_parallel_parallel_parallel_parallel_parallel_parallel
Dan ini sebenarnya bukan satu-satunya sumber daya, pada saat penulisan, layanan serupa tersedia:
- Cybertec PostgreSQL Configurator
- Alat Konfigurasi PostgreSQL
Dengan menggunakan layanan ini, Anda dapat dengan cepat mengkonfigurasi parameter DBMS awal untuk peralatan dan tugas Anda. Di masa depan, Anda perlu mengandalkan tidak hanya pada sumber daya server, tetapi juga menganalisis database secara keseluruhan, ukurannya, jumlah koneksi, dan berdasarkan ini, melakukan fine-tuning lebih lanjut dari parameter PostgreSQL.
