Skip to content

Build postgreSQL from source

Khởi tạo máy ảo

  • Giống như các bài lab khác, ta sử dụng Vagrant để tạo các máy ảo phục vụ cho bài lab. Dưới đây là Vagrantfile cho bài lab này 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
    Vagrant.configure("2") do |config|
    config.ssh.insert_key = false
    config.vm.define "latex-test" do |cf1|
      cf1.vm.box = "centos/7"
      cf1.vm.hostname ="sonnh-lab"
      cf1.vm.network :private_network, ip: "192.168.1.10"
      cf1.vm.provider "virtualbox" do |vb|
          vb.memory = "2000"
      end
    end

end
  • Run vagrant và ssh vào máy ảo 
    vagrant up

Cài đặt các package cần thiết để build

  • Install epel 
    yum -y install epel-release
  • Install util 
    yum -y install yum-utils
  • Install package 
    yum install gcc make systemd-devel zlib-devel readline-devel openssl-devel libldb-devel python3-devel wget openldap openldap-devel -y
  • Install development tool 
    yum groupinstall 'Development Tools' -y
  • Install bison 
    wget http://ftp.gnu.org/gnu/bison/bison-3.8.1.tar.gz
tar -xvzf bison-3.8.1.tar.gz
cd bison-3.8.1
./configure --prefix=/opt/bison
make && make install

Cài đặt postgreSQL

  • Download postgreSQL source 
    cd ~
wget https://ftp.postgresql.org/pub/source/v15.2/postgresql-15.2.tar.gz --no-check-certificate
tar -xvzf postgresql-15.2.tar.gz
cd postgresql-15.2
./configure --prefix=/opt/pgsql-15 --with-openssl --with-python --with-ldap
make && make install
cd contrib
make && make install
  • Tạo và grant quyền cho user postgres 
    useradd -M -s /bin/bash postgres
usermod -aG wheel postgres
echo 'export PATH=$PATH:/opt/pgsql-15/bin' > /etc/profile.d/postgres.sh
  • Tạo mật khẩu và data dir cho database 
    passwd postgres
mkdir /mnt/pgsql-15/data/ -p
chown -R postgres:postgres /mnt/pgsql-15/data/
  • Tạo database mới 
    echo "5nKJkT2qBjuGU3SsV7t" >>  /mnt/pgsql-15/.password
su postgres
/opt/pgsql-15/bin/initdb -D /mnt/pgsql-15/data/ --pwfile /mnt/pgsql-15/.password
  • Sửa các config ở /mnt/pgsql-15/data/postgresql.conf và /mnt/pgsql-15/data/pg_hba.conf nếu cần thiết.
    /mnt/pgsql-15/data/postgresql.conf
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
    # Path to the host-based authentication file
hba_file = '/mnt/pgsql-15/data/pg_hba.conf'
# Path to the ident configuration file
ident_file = '/mnt/pgsql-15/data/pg_ident.conf'
# IP addresses to listen on for incoming connections
listen_addresses = 'localhost'
# Port on which the PostgreSQL server listens for connections
port = 5432
# Maximum number of concurrent connections allowed
max_connections = 100
# Memory dedicated to PostgreSQL for caching data
shared_buffers = 128MB
# Limit on simultaneously prepared transactions
max_prepared_transactions = 0  # zero disables the feature
# Write-ahead log level (minimal, replica, or logical)
wal_level = replica
# Full page writes of non-critical updates
wal_log_hints = on
# Archiving settings
archive_mode = on
archive_command = 'test ! -f /mnt/pgsql-15/data/archive/%f && cp %p /mnt/pgsql-15/data/%f'
# Maximum number of WAL sender processes
max_wal_senders = 10
# Number of log file segments retained for WAL
wal_keep_segments = 8
# Maximum number of replication slots
max_replication_slots = 10
# Collect timestamp of transaction commit
track_commit_timestamp = off
# Allow/disallow queries during recovery
hot_standby = on
# Timezone settings
log_timezone = 'Asia/Ho_Chi_Minh'
timezone = 'Asia/Ho_Chi_Minh'
# Cluster identifier for process titles
cluster_name = 'My Postgres Cluster'
# Locale settings
datestyle = 'iso, dmy'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'vi_VN'
lc_numeric = 'vi_VN'
lc_time = 'vi_VN'
# Maximum number of locks per transaction
max_locks_per_transaction = 64
    /mnt/pgsql-15/data/pg_hba.conf
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
    # TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
# Allow replication connections with specific authentication methods
host replication replicator 127.0.0.1/32 md5
# Allow connections from any IP address with md5 authentication
host all all 0.0.0.0/0 md5
  • Tạo và phân quyền file log 
    sudo -i
touch /var/log/postgres.log
chown postgres: /var/log/postgres.log
  • Tạo file init để quản lý postgres 
    mkdir /opt/init.d
cat << "EOT" > /opt/init.d/postgres_5432
#!/bin/sh

BIN_DIR="/opt/pgsql-15/bin"
DATA_DIR="/mnt/pgsql-15/data/"
LOG_DIR="/var/log"
PORTGRESS_PORT="5432"

case "$1" in
    start)
        $BIN_DIR/pg_ctl -D $DATA_DIR -l $LOG_DIR/postgres.log start
        ;;
    stop)
        $BIN_DIR/pg_ctl -D $DATA_DIR stop
        ;;
    restart)
        $BIN_DIR/pg_ctl -D $DATA_DIR reload
        ;;
    status)
        $BIN_DIR/pg_ctl -D $DATA_DIR status
        ;;
    *)
        echo "Please use start, stop, restart, status as first argument"
        ;;
esac

EOT
chmod 755 -R /opt/init.d/
  • Start server postgreSQL 
    su postgres -c '/opt/init.d/postgres_5432 start'