源码安装

官方安装文档

源码下载地址

wget https://ftp.postgresql.org/pub/source/v12.3/postgresql-12.3.tar.gz

环境准备

• 添加系统用户 groupadd postgres && useradd -g postgres postgres
• 创建数据目录 mkdir /data/pg_data && cd /data && chown -R postgres: pg_data
• 添加依赖包 yum -y install readline-devel.x86_64 zlib-devel.x86_64

编译安装

tar -zxvf postgresql-12.3.tar.gz && cd postgresql-12.3
./configure --prefix=/opt/postgresql-12.3
make world
make install-world

配置环境变量并初始化

--添加软连接
ln -s /opt/postgresql-12.3 /opt/pgsql
chown -R postgres: pgsql postgresql-12.3

--postgres用户下编辑环境变量
cat ~/.bash_profile
.
.
.
export PATH=$PATH:$HOME/bin:/usr/local/mysql/bin:/opt/pgsql/bin

--postgres用户下环境变量生效
source ~/.bash_profile

--postgres用户下初始化数据库
initdb -D /data1/pg_data
.
.
.
Success. You can now start the database server using:

    pg_ctl -D /data1/pg_data -l logfile start

修改数据库参数

listen_addresses = '*'      # what IP address(es) to listen on;
port = 5432             # (change requires restart)
max_connections = 5000          # (change requires restart)
superuser_reserved_connections = 10 # (change requires restart)
unix_socket_directories = '/tmp'    # comma-separated list of directories
shared_buffers = 4GB            # min 128kB
temp_buffers = 32MB         # min 800kB
work_mem = 64MB             # min 64kB
maintenance_work_mem = 128MB        # min 1MB
shared_memory_type = mmap       # the default is the first option
dynamic_shared_memory_type = posix  # the default is the first option
max_files_per_process = 1000        # min 25
vacuum_cost_delay = 10          # 0-100 milliseconds (0 disables)
vacuum_cost_limit = 200     # 1-10000 credits
bgwriter_delay = 200ms          # 10-10000ms between rounds
effective_io_concurrency = 600      # 1-1000; 0 disables prefetching
max_worker_processes = 16       # (change requires restart)
max_parallel_maintenance_workers = 4    # taken from max_parallel_workers
max_parallel_workers_per_gather = 4 # taken from max_parallel_workers
parallel_leader_participation = on
max_parallel_workers = 8        # maximum number of max_worker_processes that
wal_level = logical         # minimal, replica, or logical
full_page_writes = off          # recover from partial page writes
wal_compression = off           # enable compression of full-page writes
wal_log_hints = off         # also do full page writes of non-critical updates
checkpoint_timeout = 30min      # range 30s-1d
max_wal_size = 4GB
min_wal_size = 512MB
checkpoint_completion_target = 0.8  # checkpoint target duration, 0.0 - 1.0
checkpoint_flush_after = 512kB      # measured in pages, 0 disables
checkpoint_warning = 30s        # 0 disables
archive_mode = on       # enables archiving; off, on, or always
archive_command = '/bin/true'       # command to use to archive a logfile max_wal_senders = 10        # max number of walsender processes
wal_keep_segments = 1000        # in logfile segments; 0 disables
max_replication_slots = 10  # max number of replication slots
hot_standby = on            # "off" disallows queries during recovery
max_standby_archive_delay = 30s # max delay before canceling queries
max_standby_streaming_delay = 30s   # max delay before canceling queries
max_logical_replication_workers = 4 # taken from max_worker_processes
max_sync_workers_per_subscription = 2   
min_parallel_table_scan_size = 8MB
min_parallel_index_scan_size = 512kB
effective_cache_size = 4GB
log_destination = 'stderr'      # Valid values are combinations of
logging_collector = on      # Enable capturing of stderr and csvlog
log_directory = 'log'           # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
log_rotation_size = 32MB        # Automatic rotation of logfiles will
log_min_duration_statement = 1000   # -1 is disabled, 0 logs all statements
log_checkpoints = off
log_connections = off
log_duration = off
log_hostname = off
log_line_prefix = '%m %d %r %u %p %x %v '       # special values:
log_statement = 'mod'           # none, ddl, mod, all
log_timezone = 'Asia/Shanghai'
autovacuum = on         # Enable autovacuum subprocess?  'on'
lock_timeout = 10000            # in milliseconds, 0 is disabled
datestyle = 'iso, mdy'
timezone = 'Asia/Shanghai'
lc_messages = 'en_US.UTF-8'         # locale for system error message
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_text_search_config = 'pg_catalog.english'
deadlock_timeout = 1s

启动数据库

pg_ctl -D /data1/pg_data start

psql
psql (12.3)
Type "help" for help.

postgres=#