postgres9.6.13版本部署(postgresql源码部署)

• 部署包下载

部署包下载

1. 上传部署包

[root@sdgs-server01 ~]# ll /root/
total 23832
-rw-r--r-- 1 root root 24402248 Aug  7 11:17 postgresql-9.6.13.tar.gz

2. 创建postgres用户

[root@sdgs-server01 ~]# useradd postgres

3. 安装依赖

[root@sdgs-server01 ~]# yum -y install gcc gcc-c++ readline-devel zlib-devel libxml2 libxml2-devel libxslt libxslt-devel uuid-devel

4. 部署postgres

#解压部署包
[root@sdgs-server01 ~]# tar -xf postgresql-9.6.13.tar.gz
#生成makefile、因为用到了uuid插件所有要提前指定
[root@sdgs-server01 ~]# cd postgresql-9.6.13
[root@sdgs-server01 postgresql-9.6.13]# ./configure --prefix=/opt/postgresql --with-libxml --with-libxslt --with-uuid=ossp
#编译安装
[root@sdgs-server01 postgresql-9.6.13]# make && make install
#执行完需要等一会，出现下方截图代表安装成功

5. 安装插件

#hstore插件
cd /root/postgresql-9.6.13/contrib/hstore
make && make install
#uuid-ossp插件
cd /root/postgresql-9.6.13/contrib/uuid-ossp
make && make install

5. 配置环境变量

[root@sdgs-server01 ~]# echo -e "\nexport POSTGRESQL_HOME='/opt/postgresql'\nexport PATH=/opt/postgresql/bin:\$PATH\nexport PGDATA='/opt/postgresql/data'\n" > /etc/profile.d/postgresql.sh
[root@sdgs-server01 ~]# source /etc/profile.d/postgresql.sh
[root@sdgs-server01 ~]# source /etc/profile

6. 创建目录&&赋予权限

[root@sdgs-server01 ~]# mkdir -pv /opt/postgresql/{data,log,scripts}
[root@sdgs-server01 ~]# chown -R postgres.postgres /opt/postgresql

7. 初始化数据库

[root@sdgs-server01 data]# su - postgres -c "initdb"
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /opt/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
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 /opt/postgresql/data -l logfile start
#出现上面这些证明psql初始化成功了

8. 创建软连接

[root@sdgs-server01 ~]# ln -s /opt/postgresql/lib/libpq.so.5 /usr/lib/libpq.so.5
[root@sdgs-server01 ~]# ln -s /opt/postgresql/lib/libpq.so.5 /usr/lib64/libpq.so.5

9. 修改配置

vim /opt/postgresql/data/postgresql.conf
#修改为
listen_addresses = '*'
shared_buffers = 1GB
work_mem = 1310kB
maintenance_work_mem = 512MB
effective_io_concurrency = 200
max_worker_processes = 19
max_parallel_workers_per_gather = 4
wal_buffers = 16MB
max_wal_size = 8GB
min_wal_size = 4GB
checkpoint_completion_target = 0.9
random_page_cost = 1.1
effective_cache_size = 3GB
default_statistics_target = 500
log_timezone = 'Asia/Shanghai'
timezone = 'Asia/Shanghai'
max_locks_per_transaction = 64

修改配置根据自己需求去修改

10. 启动数据库

#启动数据库
su - postgres -c "pg_ctl -D /opt/postgresql/data -l /opt/postgresql/log/pg_server.log start"
#查看日志是否正常启动
[root@sdgs-server01 log]# tailf -n 3 /opt/postgresql/log/pg_server.log
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
启动成功

11. 设置密码

#修改配置
vim /opt/postgresql/data/pg_hba.conf
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
#以上这个三个全改成md5
#新增一行配置
host    all             all             0.0.0.0/0               md5
#重启psql
su - postgres -c "pg_ctl -D /opt/postgresql/data -l /opt/postgresql/log/pg_server.log restart"
#登录psql、默认没有密码
[root@sdgs-server01 ~]# psql -U postgres
#修改密码
postgres=# ALTER USER postgres PASSWORD 'newpassd';