Ubuntu 安装postgres 14.9

安装依赖：

apt install -y zlib1g-dev gcc make libreadline-dev libreadline8 libssh-dev
sudo apt-get install libcurl4-openssl-dev

上传软件包

生产限制联网，需要工作机上传

postgresql-14.9.tar.gz

解压及安装

mkdir -p /usr/local/pgsql/14/

cd /data/soft/ && tar xzfv postgresql-14.9.tar.gz

cd /data/soft/postgresql-14.9 && ./configure --prefix=/usr/local/pgsql/14 --with-openssl

make && make install
cd /usr/local/pgsql/14/ && ls -hl

添加postgres用户：（有密码，记录一下密码，安全会侦测没有密码的用户）

adduser postgres

配置环境变量

root账号下：

vim /etc/profile

export PATH=/usr/local/pgsql/14/bin:$PATH

LANG=en_US.UTF-8
LC_TIME=en_US.UTF-8
LC_ALL="en_US.UTF-8"
lc_collate="en_US.UTF-8"

postgres用户下：

su - postgres

source /home/postgres/.bash_profile

vim /home/postgres/.bash_profile

export PGHOME=/usr/local/pgsql/14
export PGDATA=/data/pgsql14/5432/data
export PGPORT=5432
export PGUSER=postgres
export PATH=$PGHOME/bin:$PATH:$HOME/bin
export LD_LIBRARY_PATH=$PGHOME/lib
export PGUSER=postgres
export PGHOST=$PGDATA
alias rm='rm -i'
alias ll='ls -lh'
export LANG=en_US.UTF-8
export LC_TIME=en_US.UTF-8
export LC_ALL="en_US.UTF-8"
export lc_collate="en_US.UTF-8"

创建数据路径：（使用port：5432示例)

mkdir -p /data/pgsql14/5432/{data,archive}

chown -R postgres:postgres /usr/local/pgsql/ /data/pgsql14/
chmod -R go-rwx /data/pgsql14/5432/

初始化数据库：

su - postgres

/usr/local/pgsql/14/bin/initdb -D /data/pgsql14/5432/data

cd /data/pgsql14/5432/data && ls 

修改配置文件：

修改pg_hba.conf

修改 postgresql.conf（已经与安全沟通确认，生产无需开ssl）

补充： random_page_cost 的值改为 1.0（SSD） 默认是4机械盘

external_pid_file = '/data/pgsql14/5432/data/14-main.pid'     # write an extra PID file # (change requires restart) listen_addresses = '*'          # what IP address(es) to listen on;(change requires restart) port = 5432                             # (change requires restart) max_connections = 5000                  # (change requires restart) max_replication_slots = 200     # max number of replication slots superuser_reserved_connections = 10     # (change requires restart) unix_socket_directories = '/data/pgsql14/5432/data/' # comma-separated list of directories  # (change requires restart) shared_buffers = 30GB                   # min 128kB work_mem = 64MB                         # min 64kB dynamic_shared_memory_type = posix      # the default is the first option wal_level = logical                 # minimal, archive, hot_standby, or logical max_wal_size = 50GB             #memory 1/4 到 1/2 之间 min_wal_size = 2GB archive_mode = on               # enables archiving; off, on, or always archive_command = 'cp %p /data/pgsql14/5432/archive/%f'         # command to use to archive a logfile segment max_wal_senders = 100            # max number of walsender processes                                 # (change requires restart) wal_sender_timeout = 180s        # in milliseconds; 0 disables hot_standby = on                       # "on" allows queries during recovery hot_standby_feedback = on               # send info from standby to prevent ssl = off logging_collector = on          # Enable capturing of stderr and csvlog log_directory = '/data/pgsql14/5432/data/pg_log/'               # directory where log files are written, log_line_prefix = '%t [%p-%l] %q%u@%d '                 # special values: log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_timezone = 'PRC' log_file_mode = 0600                    # creation mode for log files, log_rotation_age = 1d                   # Automatic rotation of logfiles will log_rotation_size = 100MB               # Automatic rotation of logfiles will #stats_temp_directory = '/var/run/postgresql/9.5-main.pg_stat_tmp' datestyle = 'iso, mdy' timezone = 'PRC' lc_messages = 'en_US.UTF-8'                     # locale for system error message # strings lc_monetary = 'en_US.UTF-8'                     # locale for monetary formatting lc_numeric = 'en_US.UTF-8'                      # locale for number formatting lc_time = 'en_US.UTF-8'                         # locale for time formatting # default configuration for text search default_text_search_config = 'pg_catalog.english' shared_preload_libraries = 'repmgr' wal_log_hints = on #data_checksums = on    #initdb set ,can't change

配置服务启动设置开机自启动使用port:5432举例)

systemctl daemon-reload
systemctl enable postgresql

systemctl stop postgresql
systemctl start postgresql
systemctl reload postgresql

vim /etc/systemd/system/postgresql.service

[Unit]
Description=PostgreSQL database server
After=network.target

[Service]
Type=forking
User=postgres
Group=postgres
Environment=PGPORT=5432
Environment=PGDATA=/data/pgsql14/5432/data
OOMScoreAdjust=-1000
ExecStart=/usr/local/pgsql/14/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
ExecRestart=/usr/local/pgsql/14/bin/pg_ctl restart -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
ExecStop=/usr/local/pgsql/14/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/usr/local/pgsql/14/bin/pg_ctl reload -D ${PGDATA} -s
TimeoutSec=300

[Install]
WantedBy=multi-user.target