PostgreSQL数据库安装部署

PostgreSQL官网
https://www.postgresql.org/
PostgreSQL下载地址
http://www.postgresql.org/ftp/source/

一.安装所需要的依赖包

yum install -y perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake

二.上传并解压缩jar包

[root@node1 module]# tar -zxvf postgresql-11.1.tar.gz -C /opt/module
[root@node1 module]# cd postgresql-11.1/
[root@node1 postgresql-11.1]# ls
aclocal.m4  config.log     configure     contrib    doc          GNUmakefile.in  INSTALL   README
config      config.status  configure.in  COPYRIGHT  GNUmakefile  HISTORY         Makefile  src

三.开始编译postgresql源码

[root@node1 postgresql-11.1]# ./configure --prefix=/pgsql/postgresql
[root@node1 postgresql-11.1]# make
[root@node1 postgresql-11.1]# make install

四.创建启动postgresql的用户

#创建用户组postgres
[root@node1 postgresql-11.1]# groupadd postgres
#创建用户postgres
[root@node1 postgresql-11.1]# useradd -g postgres postgres
#创建用户密码
[root@node1 postgresql-11.1]# passwd postgres
#查看用户
[root@node1 postgresql-11.1]# id postgres
uid=1002(postgres) gid=1002(postgres) 组=1002(postgres)

五.创建postgresql数据库的数据目录并修改文件所有者

[root@node1 postgresql-11.1]# cd /pgsql/postgresql
[root@node1 postgresql]# mkdir data
[root@node1 postgresql]# chown postgres:postgres data
[root@node1 postgresql]# ls
bin  data  include  lib  share

六.配置环境变量

[root@node1 postgres]# cd /home/postgres/
[root@node1 postgres]# ls -al
总用量 24
drwx------  2 postgres postgres  120 1月  20 12:02 .
drwxr-xr-x. 6 root     root       59 1月  20 11:39 ..
-rw-------  1 postgres postgres 1123 1月  20 12:16 .bash_history
-rw-r--r--  1 postgres postgres   18 11月 25 2021 .bash_logout
-rw-r--r--  1 postgres postgres  301 1月  20 11:44 .bash_profile
-rw-r--r--  1 postgres postgres  231 11月 25 2021 .bashrc

[root@node1 postgres]# vim .bash_profile

添加以下内容到文件末尾

export PGHOME=/pgsql/postgresql
export PGDATA=/pgsql/postgresql/data
PATH=$PATH:$HOME/bin:$PGHOME/bin

使环境变量生效

[root@node1 postgres]# source .bash_profile

七.切换用户postgres并初始化数据库

[root@node1 postgres]# su - postgres
[postgres@node1 ~]$ initdb
...........
Success. You can now start the database server using:
 
    pg_ctl -D /pgsql/postgresql/data -l logfile start

可以看到 /pgsql/postgresql/data已经有文件了

[postgres@node1 data]$ cd /pgsql/postgresql/data
[postgres@node1 data]$ ls
base          pg_dynshmem    pg_multixact  pg_snapshots  pg_tblspc    pg_xact               postmaster.pid
global        pg_hba.conf    pg_notify     pg_stat       pg_twophase  postgresql.auto.conf  serverlog
logfile       pg_ident.conf  pg_replslot   pg_stat_tmp   PG_VERSION   postgresql.conf
pg_commit_ts  pg_logical     pg_serial     pg_subtrans   pg_wal       postmaster.opts

八.配置PostgreSQL服务

1.postgresql.conf 配置PostgreSQL数据库服务器的相应的参数

[postgres@node1 data]$ vim postgresql.conf
listen_addresses = '*'                  # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)

2.pg_hba.conf 配置对数据库的访问权限。

[postgres@node1 data]$ vim pg_hba.conf

找到最下面这一行 ，添加上配置，这样局域网的人才能访问

# IPv4 local connections:
host    all             all             0.0.0.0/0                  trust
host    all             all             127.0.0.1/32               trust

九.启动postgresql

[postgres@node1 ~]$ pg_ctl -D /pgsql/postgresql/data -l logfile start
waiting for server to start.... done
server started

进入postgresql

[postgres@node1 ~]$ psql
psql (11.1)
Type "help" for help.

postgres=# 

如果报错进不去，指定ip,用户,端口号

[postgres@node1 ~]$ psql -h node1 -U postgres -p 5432

至此大功告成，安装配置成功

10.设置postgresql的开机启动

1.在解压安装目录下找到启动脚本

[root@node1 start-scripts]# pwd
/opt/module/postgresql-11.1/contrib/start-scripts
[root@node1 start-scripts]# ll
总用量 8
-rw-r--r-- 1 linux linux 1467 11月  7 2018 freebsd
-rw-r--r-- 1 linux linux 3552 11月  7 2018 linux
drwxrwxr-x 2 linux linux   84 11月  7 2018 macos

2.拷贝linux文件到init.d目录下

[root@node1 start-scripts]# cp linux /etc/init.d/postgresqld

3.修改配置文件

[root@node1 start-scripts]# cd /etc/init.d/
[root@node1 init.d]# vim postgresqld

# Installation prefix   (pgsql主目录)
prefix=/home/postgres/pgsql

# Data directory                     (pg data目录)
PGDATA="/home/postgres/pgsql/data"

# Who to run the postmaster as, usually "postgres".  (NOT "root")  (pg用户)
PGUSER=postgres

# Where to keep a log file              (pg日志文件)
PGLOG="/home/postgres/pgsql/logs/serverlog"

4.修改文件的可执行权

[root@node1 init.d]# chmod 755 /etc/init.d/postgresqld

5.注册脚本

[root@node1 init.d]# chkconfig postgresqld on
[root@node1 init.d]# chkconfig --list
postgresqld    	0:off	1:off	2:on	3:on	4:on	5:on	6:off

6.查看服务状态

[root@node1 init.d]# systemctl status postgresqld
● postgresqld.service - SYSV: PostgreSQL RDBMS
   Loaded: loaded (/etc/rc.d/init.d/postgresqld; bad; vendor preset: disabled)
   Active: inactive (dead) since 六 2024-01-20 18:11:13 CST; 18min ago
     Docs: man:systemd-sysv-generator(8)
  Process: 1614 ExecStop=/etc/rc.d/init.d/postgresqld stop (code=exited, status=0/SUCCESS)
  Process: 1015 ExecStart=/etc/rc.d/init.d/postgresqld start (code=exited, status=0/SUCCESS)

7.启动postgresql

[root@node1 init.d]# ./postgresqld start
Starting PostgreSQL: ok
[root@node1 init.d]# ./postgresqld stop
Stopping PostgreSQL: ok

[root@node1 init.d]# systemctl status postgresqld
● postgresqld.service - SYSV: PostgreSQL RDBMS
   Loaded: loaded (/etc/rc.d/init.d/postgresqld; bad; vendor preset: disabled)
   Active: active (exited) since 六 2024-01-20 18:31:50 CST; 1min 24s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 1614 ExecStop=/etc/rc.d/init.d/postgresqld stop (code=exited, status=0/SUCCESS)
  Process: 1817 ExecStart=/etc/rc.d/init.d/postgresqld start (code=exited, status=0/SUCCESS)