在Linux、Windows、Mac安装Postgresql

1、linux 7 安装 PG12.5

1.1、下载源码包

Postgresql

https://www.postgresql.org/ftp/source/v12.5/

---

1.2、依赖包安装

yum -y install coreutils glib2 lrzsz dstat sysstat e4fsprogs xfsprogs ntp readline-devel zlib-devel openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc gcc-c++ make smartmontools flex bison perl-devel perl-ExtUtils* openldap-devel jadetex  openjade bzip2  

---

1.3、创建用户和目录

mkdir -p /pg/pg12.5
mkdir -p /pg/pg12.5_data
useradd postgres
passwd postgres
chown postgres:postgres /pg -R

---

1.4、环境变量

export PS1="[`hostname`][\$LOGNAME][\$PWD]$"
export LANG=en_US.utf8

export PG_HOME=/pg/pg12.5
export PG_DATA=/pg/pg12.5_data
export PATH=$PG_HOME/bin:$PATH:
export MANPATH=$PG_HOME/share/man:$MANPATH
export LD_LIBRARY_PATH=$PG_HOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
#export PGHOST=$PG_DATA
#export PGUSER=postgres
#export PG_DATABASE=postgres
#export PGPORT=1921

---

1.5、编译安装

tar zxf postgresql-12.5.tar.gz
cd postgresql-12.5/

./configure --prefix=/pg/pg12.5
make  && make install

[pg][/pg/pg12.5]$psql -V
psql (PostgreSQL) 12.

如果在configure的时候报错

configure: error: readline library not found
If you have readline already installed, see config.log for details on the
failure.  It is possible the compiler isn't looking in the proper directory.
Use --without-readline to disable readline support.

解决办法：

sudo yum install -y readline-devel

---

1.6、初始化数据库

initdb -D /pg/pg12.5_data -U postgres --lc-collate=C --lc-ctype=en_US.UTF-8-E UTF8  

初始化的时候 $PG_HOME/share/postgresql/postgres.bki 存了初始化元数据的内容

参数说明：

[pg][/pg/pg12.5]$initdb --help
initdb initializes a PostgreSQL database cluster.

Usage:
  initdb [OPTION]... [DATADIR]

Options:
  -A, --auth=METHOD         default authentication method for local connections
      --auth-host=METHOD    default authentication method for local TCP/IP connections
      --auth-local=METHOD   default authentication method for local-socket connections
 [-D, --pgdata=]DATADIR     location for this database cluster
  -E, --encoding=ENCODING   set default encoding for new databases
  -g, --allow-group-access  allow group read/execute on data directory
      --locale=LOCALE       set default locale for new databases
      --lc-collate=, --lc-ctype=, --lc-messages=LOCALE
      --lc-monetary=, --lc-numeric=, --lc-time=LOCALE
                            set default locale in the respective category for
                            new databases (default taken from environment)
      --no-locale           equivalent to --locale=C
      --pwfile=FILE         read password for the new superuser from file
  -T, --text-search-config=CFG
                            default text search configuration
  -U, --username=NAME       database superuser name
  -W, --pwprompt            prompt for a password for the new superuser
  -X, --waldir=WALDIR       location for the write-ahead log directory
      --wal-segsize=SIZE    size of WAL segments, in megabytes

Less commonly used options:
  -d, --debug               generate lots of debugging output
  -k, --data-checksums      use data page checksums
  -L DIRECTORY              where to find the input files
  -n, --no-clean            do not clean up after errors
  -N, --no-sync             do not wait for changes to be written safely to disk
  -s, --show                show internal settings
  -S, --sync-only           only sync data directory

Other options:
  -V, --version             output version information, then exit
  -?, --help                show this help, then exit

If the data directory is not specified, the environment variable PGDATA
is used.

Report bugs to <pgsql-bugs@lists.postgresql.org>.

---

1.7、启动数据库

pg_ctl -D /pg/pg12.5_data -l /pg/pg12.5_data/log/postgresql.log  start

参数说明：

[pg][/pg/pg12.5]$pg_ctl --help
pg_ctl is a utility to initialize, start, stop, or control a PostgreSQL server.

Usage:
  pg_ctl init[db]   [-D DATADIR] [-s] [-o OPTIONS]
  pg_ctl start      [-D DATADIR] [-l FILENAME] [-W] [-t SECS] [-s]
                    [-o OPTIONS] [-p PATH] [-c]
  pg_ctl stop       [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
  pg_ctl restart    [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
                    [-o OPTIONS] [-c]
  pg_ctl reload     [-D DATADIR] [-s]
  pg_ctl status     [-D DATADIR]
  pg_ctl promote    [-D DATADIR] [-W] [-t SECS] [-s]
  pg_ctl logrotate  [-D DATADIR] [-s]
  pg_ctl kill       SIGNALNAME PID

Common options:
  -D, --pgdata=DATADIR   location of the database storage area
  -s, --silent           only print errors, no informational messages
  -t, --timeout=SECS     seconds to wait when using -w option
  -V, --version          output version information, then exit
  -w, --wait             wait until operation completes (default)
  -W, --no-wait          do not wait until operation completes
  -?, --help             show this help, then exit
If the -D option is omitted, the environment variable PGDATA is used.

Options for start or restart:
  -c, --core-files       allow postgres to produce core files
  -l, --log=FILENAME     write (or append) server log to FILENAME
  -o, --options=OPTIONS  command line options to pass to postgres
                         (PostgreSQL server executable) or initdb
  -p PATH-TO-POSTGRES    normally not necessary

Report bugs to <pgsql-bugs@lists.postgresql.org>.

---

1.8、关闭数据库

pg_ctl stop -D /pg/pg12.5_data/  -m fast 

Options for stop or restart:
  -m, --mode=MODE        MODE can be "smart", "fast", or "immediate"

Shutdown modes are:
  smart       quit after all clients have disconnected
  fast        quit directly, with proper shutdown (default)
  immediate   quit without complete shutdown; will lead to recovery on restart

Allowed signal names for kill:
  ABRT HUP INT KILL QUIT TERM USR1 USR2

---

1.9、进程说明

ps -ef|grep postgres
pg        79357      1  0 15:33 ?        00:00:00 /pg/pg12.5/bin/postgres -D /pg/pg12.5_data
pg        79359  79357  0 15:33 ?        00:00:00 postgres: checkpointer   
pg        79360  79357  0 15:33 ?        00:00:00 postgres: background writer   
pg        79361  79357  0 15:33 ?        00:00:00 postgres: walwriter   
pg        79362  79357  0 15:33 ?        00:00:00 postgres: autovacuum launcher   
pg        79363  79357  0 15:33 ?        00:00:00 postgres: stats collector   
pg        79364  79357  0 15:33 ?        00:00:00 postgres: logical replication launcher   
pg        80101  79402  0 16:28 pts/2    00:00:00 grep --color=auto postgres

• postgres：主进程
• checkpointer：检查点进程
• background writer：后台写进程
• walwriter：日志写进程
• autovacuum launcher：vacuum进程（垃圾收集并根据需要分析一个数据库），自动化 VACUUM and ANALYZE，降低高水位，清理空间的作用

• stats collector：统计信息收集进程

---

1.10、德哥建议的配置文件参数

cd $PGDATA  
vi postgresql.conf  

listen_addresses = '0.0.0.0'  
port = 1921  # 监听端口  
max_connections = 2000  # 最大允许的连接数  
superuser_reserved_connections = 10  
unix_socket_directories = '.'  
unix_socket_permissions = 0700  
tcp_keepalives_idle = 60  
tcp_keepalives_interval = 60  
tcp_keepalives_count = 10  
shared_buffers = 256MB          # 共享内存，建议设置为系统内存的1/4  .  
#  vm.nr_hugepages = 102352    
#  建议shared buffer设置超过64GB时 使用大页，页大小 /proc/meminfo Hugepagesize
maintenance_work_mem = 64MB     # 系统内存超过32G时，建议设置为1GB。超过64GB时，建议设置为2GB。超过128GB时，建议设置为4GB。  
work_mem = 64MB                        # 1/4 主机内存 / 256 (假设256个并发同时使用work_mem)
wal_buffers = 512MB                    # min( 2047MB, shared_buffers/32 ) 
dynamic_shared_memory_type = posix  
vacuum_cost_delay = 0  
bgwriter_delay = 10ms  
bgwriter_lru_maxpages = 500  
bgwriter_lru_multiplier = 5.0  
effective_io_concurrency = 0  
max_worker_processes = 128                 
max_parallel_workers_per_gather = 32        # 建议设置为主机CPU核数的一半。  
max_parallel_workers = 32                   # 看业务AP和TP的比例，以及AP TP时间交错分配。实际情况调整。例如 主机CPU cores-2
wal_level = replica  
fsync = on  
synchronous_commit = off  
full_page_writes = on                  # 支持原子写超过BLOCK_SIZE的块设备，在对齐后可以关闭。或者支持cow的文件系统可以关闭。
wal_writer_delay = 10ms  
wal_writer_flush_after = 1MB  
checkpoint_timeout = 35min  
max_wal_size = 32GB                    # shared_buffers*2 
min_wal_size = 8GB                     # max_wal_size/4 
archive_mode = on  
archive_command = '/bin/date'  
max_wal_senders = 10  
max_replication_slots = 10  
wal_receiver_status_interval = 1s  
max_logical_replication_workers = 4  
max_sync_workers_per_subscription = 2  
random_page_cost = 1.2  
parallel_tuple_cost = 0.1  
parallel_setup_cost = 1000.0  
min_parallel_table_scan_size = 8MB  
min_parallel_index_scan_size = 512kB  
effective_cache_size = 10GB                 # 建议设置为主机内存的5/8。     
log_destination = 'csvlog'  
logging_collector = on  
log_directory = 'log'  
log_filename = 'postgresql-%a.log'  
log_truncate_on_rotation = on  
log_rotation_age = 1d  
log_rotation_size = 0  
log_min_duration_statement = 5s  
log_checkpoints = on  
log_connections = on                            # 如果是短连接，并且不需要审计连接日志的话，建议OFF。
log_disconnections = on                         # 如果是短连接，并且不需要审计连接日志的话，建议OFF。
log_error_verbosity = verbose  
log_line_prefix = '%m [%p] '  
log_lock_waits = on  
log_statement = 'ddl'  
log_timezone = 'PRC'  
log_autovacuum_min_duration = 0   
autovacuum_max_workers = 5  
autovacuum_vacuum_scale_factor = 0.1  
autovacuum_analyze_scale_factor = 0.05  
autovacuum_freeze_max_age = 1000000000  
autovacuum_multixact_freeze_max_age = 1200000000  
autovacuum_vacuum_cost_delay = 0  
statement_timeout = 0                                # 单位ms, s, min, h, d.  表示语句的超时时间，0表示不限制。  
lock_timeout = 0                                     # 单位ms, s, min, h, d.  表示锁等待的超时时间，0表示不限制。  
idle_in_transaction_session_timeout = 2h             # 单位ms, s, min, h, d.  表示空闲事务的超时时间，0表示不限制。  
vacuum_freeze_min_age = 50000000  
vacuum_freeze_table_age = 800000000  
vacuum_multixact_freeze_min_age = 50000000  
vacuum_multixact_freeze_table_age = 800000000  
datestyle = 'iso, ymd'  
timezone = 'PRC'  
lc_messages = 'en_US.UTF8'  
lc_monetary = 'en_US.UTF8'  
lc_numeric = 'en_US.UTF8'  
lc_time = 'en_US.UTF8'  
default_text_search_config = 'pg_catalog.simple'  
shared_preload_libraries='pg_stat_statements,pg_pathman

---

2、Windows 安装 PG12.5

2.1、重启服务

pg_ctl start -D G:\PostgreSQL\12\data   

pg_ctl stop -D G:\PostgreSQL\12\data  -m fast 

---

2.2、修改为英文

编辑数据文件下的 postgresql.conf

# These settings are initialized by initdb, but they can be changed.
lc_messages = 'Chinese (Simplified)_China.936'            # locale for system error message
# strings
lc_monetary = 'Chinese (Simplified)_China.936'            # locale for monetary formatting
lc_numeric = 'Chinese (Simplified)_China.936'            # locale for number formatting
lc_time = 'Chinese (Simplified)_China.936'                # locale for time formatting

# default configuration for text search
default_text_search_config = 'pg_catalog.simple'

# These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.utf8'                      # locale for system error message
# strings
lc_monetary = 'en_US.utf8'                      # locale for monetary formatting
lc_numeric = 'en_US.utf8'                       # locale for number formatting
lc_time = 'en_US.utf8'                          # locale for time formatting

# default configuration for text search
default_text_search_config = 'pg_catalog.english'

---

3、Mac安装 PG13.3

3.1、通过brew安装数据库，默认安装最新版本

brew list postgresql
brew install postgresql

3.2、查看下安装的版本

rhl@rhldeMacBook-Pro log % pg_ctl -V      
pg_ctl (PostgreSQL) 13.3

3.3、初始化数据库

rhl@rhldeMacBook-Pro ~ % initdb -D /Users/rhl/other/postgresql -U rhl --lc-collate=C --lc-ctype=en_US.UTF-8 --lc-messages=en_US.UTF-8 -E UTF8 
The files belonging to this database system will be owned by user "rhl".
This user must also own the server process.

The database cluster will be initialized with locales
  COLLATE:  C
  CTYPE:    en_US.UTF-8
  MESSAGES: en_US.UTF-8
  MONETARY: zh_CN.UTF-8
  NUMERIC:  zh_CN.UTF-8
  TIME:     zh_CN.UTF-8
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /Users/rhl/other/postgresql ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /Users/rhl/other/postgresql -l logfile start

3.4、修改配置文件

vi postgresql.conf
listen_addresses = '0.0.0.0' 
port = 5432 
log_destination = 'csvlog'  
logging_collector = on  
log_directory = 'log'  
log_filename = 'postgresql-%a.log'  
log_truncate_on_rotation = on  
log_rotation_age = 1d  
log_rotation_size = 1GB

3.5、启动数据库

pg_ctl -D /Users/rhl/other/postgresql  start

ps -ef|grep postgres

3.6、创建user

createdb

rhl@rhldeMacBook-Pro var % psql
psql (13.3)
Type "help" for help.

rhl=# \l
                            List of databases
   Name    | Owner | Encoding | Collate |    Ctype    | Access privileges 
-----------+-------+----------+---------+-------------+-------------------
 postgres  | rhl   | UTF8     | C       | en_US.UTF-8 | 
 rhl       | rhl   | UTF8     | C       | en_US.UTF-8 | 
 template0 | rhl   | UTF8     | C       | en_US.UTF-8 | =c/rhl           +
           |       |          |         |             | rhl=CTc/rhl
 template1 | rhl   | UTF8     | C       | en_US.UTF-8 | =c/rhl           +
           |       |          |         |             | rhl=CTc/rhl
(4 rows)

可以看到已存在用户同名数据库、postgres数据库、template0、template1，但是postgres数据库的所有者是当前用户，没有postgres用户。所以我们需要创建postgres用户。

CREATE USER postgres WITH PASSWORD 'postgres';
GRANT ALL PRIVILEGES ON DATABASE postgres to postgres;
ALTER ROLE postgres CREATEDB;
DROP DATABASE postgres;
CREATE DATABASE postgres OWNER postgres;

psql -U postgres -d postgres