• March 1, 2023
• in Technical
• 4 min read

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

  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.

  # 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
  

  # 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'