linux 安装PostgreSQL12
一、安装步骤
1、设置保存安装包的目录
# cd /usr/local/src
2、开始下载源包
# wget https://ftp.postgresql.org/pub/source/v12.1/postgresql-12.1.tar.gz （https://www.postgresql.org/ftp/source/ 这里是官网资源）
3、解压
# tar zxvf postgresql-12.1.tar.gz
4、移动位置/usr/local/pgsql/postgresql-12.1
# mv postgresql-12.1 /usr/local/pgsql/postgresql-12.1
6、进入移动后的目录
# cd /usr/local/pgsql/postgresql-12.1
8、配置选项生成Makefile，默认安装到目录/usr/local/pgsql/postgresql-12.1
# ./configure (如果要安装到自定义目录，这里修改为 # ./configure --prefix= /usr/local/pgsql 红字部分是你的自定义路径)
9、echo 一下返回是否为0, 0表示无错误
# echo $?
130
10、发现没有相关包依次安装
# yum install -y
bison
# yum install -y
flex
# yum install -y readline-devel
(https://www.linuxidc.com/Linux/2012-02/53982.htm 这里有解释为什么安装这个)
#
yum install -y zlib-devel
11、编译
# make (编译后再echo $? 如果为0 就可以安装了）
12、安装
# make install
13、创建，添加postgres 用户到 postgres组
# groupadd
postgres （创建组：postgres ）
#
useradd -g postgres postgres （命令解释：useradd -g 用户组 用户 ）
# mkdir -p
/usr/local/pgsql/data （创建一个data
目录）
# chown postgres /usr/local/pgsql/data
（ 改变data的权限为postgres用户）
# su -
postgres （
切换操作用户为 postgres）
14、初始化数据库
$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data/
(提示:初始化数据库的操作为： ./initdb -D /usr/local/pgsql/data initdb把用户指定的选项转换成对应的参数，通过外部程序调用的方式执行postgres程序。
postgres程序在这种方式下将进入bootstrap模式创建数据集簇，并读取后端接口postgres.bki文件来创建模板数据库。)
15、启动数据库
$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ -l logfile start（启动数据库）
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ stop （停止数据库）
/usr/local/pgsql/bin/pg_ctl restart -D /usr/local/pgsql/data/ -m fast （重启数据库）
二、postgreSQL配置
1、修改postgresql.conf，修改主要的配置参数。
修改前可以先备份一下
$ cp postgresql.conf postgresql.conf_bak
$ vim /usr/local/pgsql/data/postgresql.conf
修改listen_addresses = '*' 前面的#号需要去掉（按/键输入listen_addresses可以快速查找该行。）默认参数是‘localhost‘ 只监听本机IP修改为’*‘后，
可以让远端的其它设备访问 。另外没有vim 可以使用# yum install -y vim 安装vim文本编辑工具。
2、修改postgresql.conf，修改数据库的访问权限。
$ vim /usr/local/pgsql/data/pg_hba.conf
添加以下,表示主机所有IP都可以访问。
host all all 0.0.0.0/0 trust
3、添加环境变量
$ su (输入密码后切换为root管理员)
# cd /home/postgres (之前我们创建了一个postgres的用户所以这里有这个目录)
# vim .bash_profile
添加
export PGHOME=/usr/local/pgsql
export PGDATA=/usr/local/pgsql/data
PATH=$PATH:$HOME/bin:$PGHOME/bin
# source .bash_profile （使修改生效）
4、设置开机自启动
# cd /usr/local/pgsql/postgresql-12.1/contrib/start-scripts (这里有个文件名叫linux的文件，它是linux系统的启动脚本)
由于这个文件权限没有设置为X可以执行权限，我们需要设置它为可运行。
# chmod a+x linux
# cp linux /etc/init.d/postgresql （复制linux文件到/etc/init.d目录下,并更名postgresql）
# vim linux /etc/init.d/postgresql
查看
prefix=/usr/local/pgsql （安装程序的文件路径）
PGDATA="/usr/local/pgsql/data" （数据存放目录）
如果和自己的路径一致就不需要修改了，按ESC键 ，然后:q退出编辑。
# chkconfig --add postgresql （添加开机启动项目）
# chkconfig （看下是否设置成功）
4、设置系统配置，开放默认的5432端口
如果CentOS6 使用iptables执行以下步骤。
# vim /etc/sysconfig/iptables
添加
-A INPUT -p tcp -m tcp --dport 5432 -j ACCEPT
# cd /etc/init.d/iptables restart （重启服务）
如果CentOS7 有使用firewall防火墙执行以下步骤。
# firewall-cmd --zone=public --list-ports
# firewall-cmd --zone=public --add-port=5432/tcp --permanent （添加5432端口）
# firewall-cmd --reload (重启防火墙)
5、启动服务
# service postgresql start
6、查看PostgreSQL服务
# ps -ef | grep postgres
7、设置默认密码
PostgreSQL安装后会自动创建一个用户，名为postgres
# su - postgres （默认密码为空，切换到postgres用户）
# psql -U postgres
postgres=# ALTER USER postgres with encrypted password 'asd111'; （asd111密码自由设置）
postgres=# \q （可退出）
三、使用阿里云的5432端口打开
1、选择-安全组-配置规则
确定后就开放了5432端口了。
2、使用pgAdmin连接数据库
配置IP，5432端口，用户，密码：asd111 就可以连接了。
参考链接：
https://blog.csdn.net/min0324/article/details/85019434
https://blog.csdn.net/joy_chou12/article/details/89351634
https://www.cnblogs.com/Paul-watermelon/p/10654303.html
四：具体安装步骤
1：软件下载 [root@VM_0_2_centos bin]# cd /home/ [root@VM_0_2_centos home]# ls centos dbbak oracle sfeishui [root@VM_0_2_centos home]# mkdir postgres [root@VM_0_2_centos home]# ls centos dbbak oracle postgres sfeishui [root@VM_0_2_centos home]# cd /usr/local/src [root@VM_0_2_centos home]# wget https://ftp.postgresql.org/pub/source/v12.5/postgresql-12.5.tar.gz #我是手动下载的 然后上传到linux服务器的 [root@VM_0_2_centos src]# tar -zxvf postgresql-12.5.tar.gz #解压 [root@VM_0_2_centos src]# clear [root@VM_0_2_centos src]# ls jdk-8u281-linux-x64.rpm postgresql-12.5 postgresql-12.5.tar.gz [root@VM_0_2_centos src]# [root@VM_0_2_centos src]# pwd /usr/local/src [root@VM_0_2_centos src]# ls jdk-8u281-linux-x64.rpm postgresql-12.5 postgresql-12.5.tar.gz [root@VM_0_2_centos src]# pwd /usr/local/src [root@VM_0_2_centos src]# cd /usr/local/ [root@VM_0_2_centos local]# ls apache-tomcat-9.0.41 apache-tomcat-9.0.41.zip bin etc games include jdk1.7 lib lib64 libexec posql qcloud sbin share src tomcat8 tomcat8_old tomcat9 yd.socket.server [root@VM_0_2_centos local]# mkdir pgsql [root@VM_0_2_centos local]# ls apache-tomcat-9.0.41 apache-tomcat-9.0.41.zip bin etc games include jdk1.7 lib lib64 libexec pgsql posql qcloud sbin share src tomcat8 tomcat8_old tomcat9 yd.socket.server [root@VM_0_2_centos pgsql]# ls [root@VM_0_2_centos pgsql]# pwd /usr/local/pgsql [root@VM_0_2_centos pgsql]# mv /usr/local/src/postgresql-12.5 /usr/local/pgsql/postgresql-12.5 [root@VM_0_2_centos pgsql]# ls postgresql-12.5 [root@VM_0_2_centos pgsql]# cd postgresql-12.5/ #进入移动后的目录 [root@VM_0_2_centos postgresql-12.5]# ls aclocal.m4 config configure configure.in contrib COPYRIGHT doc GNUmakefile.in HISTORY INSTALL Makefile README src [root@VM_0_2_centos postgresql-12.5]# pwd /usr/local/pgsql/postgresql-12.5 [root@VM_0_2_centos postgresql-12.5]# ./configure #(如果要安装到自定义目录，这里修改为 # ./configure --prefix= /usr/local/pgsql 红字部分是你的自定义路径) [root@VM_0_2_centos postgresql-12.5]# echo $? #echo 一下返回是否为0, 0表示无错误 0 #发现没有相关包依次安装 [root@VM_0_2_centos postgresql-12.5]# yum install -y bison Loaded plugins: fastestmirror, langpacks Loading mirror speeds from cached hostfile Resolving Dependencies --> Running transaction check ---> Package bison.x86_64 0:3.0.4-2.el7 will be installed --> Processing Dependency: m4 >= 1.4 for package: bison-3.0.4-2.el7.x86_64 --> Running transaction check ---> Package m4.x86_64 0:1.4.16-10.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ============================================================================================================================================================================================================================================== Package Arch Version Repository Size ============================================================================================================================================================================================================================================== Installing: bison x86_64 3.0.4-2.el7 os 674 k Installing for dependencies: m4 x86_64 1.4.16-10.el7 os 256 k Transaction Summary ============================================================================================================================================================================================================================================== Install 1 Package (+1 Dependent package) Total download size: 931 k Installed size: 2.6 M Downloading packages: (1/2): m4-1.4.16-10.el7.x86_64.rpm | 256 kB 00:00:00 (2/2): bison-3.0.4-2.el7.x86_64.rpm | 674 kB 00:00:00 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Total 6.8 MB/s | 931 kB 00:00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : m4-1.4.16-10.el7.x86_64 1/2 Installing : bison-3.0.4-2.el7.x86_64 2/2 Verifying : m4-1.4.16-10.el7.x86_64 1/2 Verifying : bison-3.0.4-2.el7.x86_64 2/2 Installed: bison.x86_64 0:3.0.4-2.el7 Dependency Installed: m4.x86_64 0:1.4.16-10.el7 Complete! [root@VM_0_2_centos postgresql-12.5]# yum install -y flex Loaded plugins: fastestmirror, langpacks Loading mirror speeds from cached hostfile Resolving Dependencies --> Running transaction check ---> Package flex.x86_64 0:2.5.37-6.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ============================================================================================================================================================================================================================================== Package Arch Version Repository Size ============================================================================================================================================================================================================================================== Installing: flex x86_64 2.5.37-6.el7 os 293 k Transaction Summary ============================================================================================================================================================================================================================================== Install 1 Package Total download size: 293 k Installed size: 740 k Downloading packages: flex-2.5.37-6.el7.x86_64.rpm | 293 kB 00:00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : flex-2.5.37-6.el7.x86_64 1/1 Verifying : flex-2.5.37-6.el7.x86_64 1/1 Installed: flex.x86_64 0:2.5.37-6.el7 Complete! [root@VM_0_2_centos postgresql-12.5]# yum install -y readline-devel Loaded plugins: fastestmirror, langpacks Loading mirror speeds from cached hostfile Package readline-devel-6.2-11.el7.x86_64 already installed and latest version Nothing to do [root@VM_0_2_centos postgresql-12.5]# yum install -y zlib-devel Loaded plugins: fastestmirror, langpacks Loading mirror speeds from cached hostfile Package zlib-devel-1.2.7-19.el7_9.x86_64 already installed and latest version Nothing to do [root@VM_0_2_centos postgresql-12.5]# #编译 [root@VM_0_2_centos postgresql-12.5]# pwd /usr/local/pgsql/postgresql-12.5 [root@VM_0_2_centos postgresql-12.5]# make #安装 [root@VM_0_2_centos postgresql-12.5]# pwd /usr/local/pgsql/postgresql-12.5 [root@VM_0_2_centos postgresql-12.5]# make install #创建，添加postgres 用户到 postgres组 [root@VM_0_2_centos postgresql-12.5]# groupadd postgres （创建组：postgres ） [root@VM_0_2_centos postgresql-12.5]# useradd -g postgres postgres （命令解释：useradd -g 用户组 用户 ） useradd: warning: the home directory already exists. Not copying any file from skel directory into it. [root@VM_0_2_centos postgresql-12.5]# mkdir -p /usr/local/pgsql/data （创建一个data 目录） [root@VM_0_2_centos postgresql-12.5]# chown postgres /usr/local/pgsql/data （ 改变data的权限为postgres用户） [root@VM_0_2_centos postgresql-12.5]# su - postgres （ 切换操作用户为 postgres） -bash-4.2$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data/ #(提示:初始化数据库的操作为： ./initdb -D /usr/local/pgsql/data initdb把用户指定的选项转换成对应的参数，通过外部程序调用的方式执行postgres程序。postgres程序在这种方式下将进入bootstrap模式创建数据集簇，并读取后端接口postgres.bki文件来创建模板数据库。) 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.utf8". 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 /usr/local/pgsql/data ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... PRC 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: /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ -l logfile start #启动数据库 [root@192 postgresql-12.5]# su - postgres -bash-4.2$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data/ 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 /usr/local/pgsql/data ... 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: /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ -l logfile start -bash-4.2$ ls 二、postgreSQL配置 1、修改postgresql.conf，修改主要的配置参数。 -bash-4.2$ cd /usr/local/pgsql/data/ -bash-4.2$ ls base pg_hba.conf pg_notify pg_stat pg_twophase postgresql.auto.conf global pg_ident.conf pg_replslot pg_stat_tmp PG_VERSION postgresql.conf pg_commit_ts pg_logical pg_serial pg_subtrans pg_wal pg_dynshmem pg_multixact pg_snapshots pg_tblspc pg_xact -bash-4.2$ pwd /usr/local/pgsql/data -bash-4.2$ -bash-4.2$ cp postgresql.conf postgresql.conf_bak #修改前可以先备份一下 -bash-4.2$ ls base pg_hba.conf pg_notify pg_stat pg_twophase postgresql.auto.conf global pg_ident.conf pg_replslot pg_stat_tmp PG_VERSION postgresql.conf pg_commit_ts pg_logical pg_serial pg_subtrans pg_wal postgresql.conf_bak pg_dynshmem pg_multixact pg_snapshots pg_tblspc pg_xact -bash-4.2$ $ vim /usr/local/pgsql/data/postgresql.conf 修改listen_addresses = '*' 前面的#号需要去掉（按/键输入listen_addresses可以快速查找该行。）默认参数是‘localhost‘ 只监听本机IP修改为’*‘后， 可以让远端的其它设备访问 。另外没有vim 可以使用# yum install -y vim 安装vim文本编辑工具。 2、修改postgresql.conf，修改数据库的访问权限。 $ vim /usr/local/pgsql/data/pg_hba.conf 添加以下,表示主机所有IP都可以访问。 host all all 0.0.0.0/0 trust 3、添加环境变量 su: Authentication failure -bash-4.2$ su Password: [root@VM_0_2_centos data]# [root@VM_0_2_centos data]# cd /home/postgres/ [root@VM_0_2_centos postgres]# [root@VM_0_2_centos postgres]# cat .bash_profile cat: .bash_profile: No such file or directory [root@VM_0_2_centos postgres]# vim .bash_profile [root@VM_0_2_centos postgres]# cat .bash_profile #添加 export PGHOME=/usr/local/pgsql export PGDATA=/usr/local/pgsql/data PATH=$PATH:$HOME/bin:$PGHOME/bin [root@VM_0_2_centos postgres]# 4、设置开机自启动 [root@VM_0_2_centos postgres]# cd /usr/local/pgsql/postgresql-12.5/contrib/start-scripts/ [root@VM_0_2_centos start-scripts]# [root@VM_0_2_centos start-scripts]# ls -all total 20 drwxrwxrwx 3 1107 1107 4096 Nov 10 2020 . drwxrwxrwx 56 1107 1107 4096 Nov 10 2020 .. -rw-r--r-- 1 1107 1107 1467 Nov 10 2020 freebsd -rw-r--r-- 1 1107 1107 3552 Nov 10 2020 linux drwxrwxrwx 2 1107 1107 4096 Nov 10 2020 macos [root@VM_0_2_centos start-scripts]# chmod a+x linux # 由于这个文件权限没有设置为X可以执行权限，我们需要设置它为可运行。 [root@VM_0_2_centos start-scripts]# ls -all total 20 drwxrwxrwx 3 1107 1107 4096 Nov 10 2020 . drwxrwxrwx 56 1107 1107 4096 Nov 10 2020 .. -rw-r--r-- 1 1107 1107 1467 Nov 10 2020 freebsd -rwxr-xr-x 1 1107 1107 3552 Nov 10 2020 linux drwxrwxrwx 2 1107 1107 4096 Nov 10 2020 macos [root@VM_0_2_centos start-scripts]# [root@VM_0_2_centos start-scripts]# cp linux /etc/init.d/postgresql #（复制linux文件到/etc/init.d目录下,并更名postgresql） [root@VM_0_2_centos start-scripts]# 查看 prefix=/usr/local/pgsql （安装程序的文件路径） PGDATA="/usr/local/pgsql/data" （数据存放目录） 如果和自己的路径一致就不需要修改了，按ESC键 ，然后:q退出编辑。 [root@VM_0_2_centos start-scripts]# chkconfig --add postgresql #（添加开机启动项目） [root@VM_0_2_centos start-scripts]# chkconfig #（看下是否设置成功） Note: This output shows SysV services only and does not include native systemd services. SysV configuration data might be overridden by native systemd configuration. If you want to list systemd services use 'systemctl list-unit-files'. To see services enabled on particular target use 'systemctl list-dependencies [target]'. jexec 0:off 1:on 2:on 3:on 4:on 5:on 6:off netconsole 0:off 1:off 2:off 3:off 4:off 5:off 6:off network 0:off 1:off 2:on 3:on 4:on 5:on 6:off postgresql 0:off 1:off 2:on 3:on 4:on 5:on 6:off [root@VM_0_2_centos start-scripts]# 4、设置系统配置，开放默认的5432端口 [root@VM_0_2_centos start-scripts]# firewall-cmd --zone=public --list-ports FirewallD is not running [root@VM_0_2_centos start-scripts]# firewall-cmd --zone=public --list-ports FirewallD is not running [root@VM_0_2_centos start-scripts]# systemctl status firewalld ● firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled) Active: inactive (dead) Docs: man:firewalld(1) [root@VM_0_2_centos start-scripts]# systemctl start firewalld [root@VM_0_2_centos start-scripts]# systemctl status firewalld ● firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled) Active: active (running) since Fri 2021-11-12 16:48:11 CST; 7s ago Docs: man:firewalld(1) Main PID: 15850 (firewalld) CGroup: /system.slice/firewalld.service └─15850 /usr/bin/python -Es /usr/sbin/firewalld --nofork --nopid Nov 12 16:48:10 VM_0_2_centos systemd[1]: Starting firewalld - dynamic firewall daemon... Nov 12 16:48:11 VM_0_2_centos systemd[1]: Started firewalld - dynamic firewall daemon. [root@VM_0_2_centos start-scripts]# firewall-cmd --zone=public --list-ports [root@VM_0_2_centos start-scripts]# firewall-cmd --zone=public --add-port=5432/tcp --permanent success [root@VM_0_2_centos start-scripts]# firewall-cmd --reload success [root@VM_0_2_centos start-scripts]# systemctl stop firewalld [root@VM_0_2_centos start-scripts]# 启动服务 [root@VM_0_2_centos start-scripts]# service postgresql start Starting PostgreSQL: ok [root@VM_0_2_centos start-scripts]# 6、查看PostgreSQL服务 # ps -ef | grep postgres [root@VM_0_2_centos start-scripts]# ps -ef |grep postgres root 10206 30122 0 16:08 pts/3 00:00:00 su - postgres postgres 10207 10206 0 16:08 pts/3 00:00:00 -bash postgres 16283 1 0 16:49 ? 00:00:00 /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data postgres 16285 16283 0 16:49 ? 00:00:00 postgres: checkpointer postgres 16286 16283 0 16:49 ? 00:00:00 postgres: background writer postgres 16287 16283 0 16:49 ? 00:00:00 postgres: walwriter postgres 16288 16283 0 16:49 ? 00:00:00 postgres: autovacuum launcher postgres 16289 16283 0 16:49 ? 00:00:00 postgres: stats collector postgres 16290 16283 0 16:49 ? 00:00:00 postgres: logical replication launcher root 16421 12537 0 16:50 pts/3 00:00:00 grep --color=auto postgres [root@VM_0_2_centos start-scripts]# 设置默认密码 [root@VM_0_2_centos start-scripts]# su - postgres Last login: Fri Nov 12 16:49:36 CST 2021 on pts/3 -bash-4.2$ psql -U postgres psql (12.5) Type "help" for help. postgres=# ALTER USER postgres with encrypted password '123456'; ALTER ROLE postgres=#