参考文档
数据库敲门人 - 带你敲开数据库大门 (oracleonlinux.cn)
http://www.oracleonlinux.cn/
http://www.knockatdatabase.com/
具体文档地址
http://www.knockatdatabase.com/2021/03/25/how-to-install-postgresql-on-centos-via-sourcecode/
源码安装先决条件
软件下载
https://www.postgresql.org/download/linux/redhat/
Last login: Wed Jul 12 01:25:06 2023 from 192.168.255.1
[root@pg ~]# cat /etc/redhat-release
CentOS Linux release 7.7.1908 (Core)
1、安装gmake，gmake或者make要求至少3.80版本以上
[root@pg ~]# gmake -v
GNU Make 3.82
Built for x86_64-redhat-linux-gnu
Copyright (C) 2010 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
[root@pg ~]# make -v
GNU Make 3.82
Built for x86_64-redhat-linux-gnu
Copyright (C) 2010 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
[root@pg ~]# which make
/usr/bin/make
[root@pg ~]# which gmake
/usr/bin/gmake
gmake是一个链接文件，其指向了make。其实，在CentOS/RHEL平台上，默认情况下，gmake等同于make。
2、安装gcc
[root@pg ~]# which gcc
/usr/bin/which: no gcc in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin)
[root@pg ~]# yum install gcc -y
Loaded plugins: fastestmirror
Determining fastest mirrors
Could not retrieve mirrorlist http://mirrorlist.centos.org/?release=7&arch=x86_64&repo=os&infra=stock error was
14: curl#6 - "Could not resolve host: mirrorlist.centos.org; Unknown error"
One of the configured repositories failed (Unknown),
and yum doesn't have enough cached data to continue. At this point the only
safe thing yum can do is fail. There are a few ways to work "fix" this:
1. Contact the upstream for the repository and get them to fix the problem.
2. Reconfigure the baseurl/etc. for the repository, to point to a working
upstream. This is most often useful if you are using a newer
distribution release than is supported by the repository (and the
packages for the previous distribution release still work).
3. Run the command with the repository temporarily disabled
yum --disablerepo=<repoid> ...
4. Disable the repository permanently, so yum won't use it by default. Yum
will then just ignore the repository until you permanently enable it
again or use --enablerepo for temporary usage:
yum-config-manager --disable <repoid>
or
subscription-manager repos --disable=<repoid>
5. Configure the failing repository to be skipped, if it is unavailable.
Note that yum will try to contact the repo. when it runs most commands,
so will have to try and fail each time (and thus. yum will be be much
slower). If it is a very temporary problem though, this is often a nice
compromise:
yum-config-manager --save --setopt=<repoid>.skip_if_unavailable=true
配置yum源
[root@pg ~]# mount /dev/sr0 /mnt/
mount: /dev/sr0 is write-protected, mounting read-only
[root@pg ~]# cd /etc/yum.repos.d/
[root@pg yum.repos.d]# ll -h
total 32K
-rw-r--r--. 1 root root 1.7K Sep 5 2019 CentOS-Base.repo
-rw-r--r--. 1 root root 1.3K Sep 5 2019 CentOS-CR.repo
-rw-r--r--. 1 root root 649 Sep 5 2019 CentOS-Debuginfo.repo
-rw-r--r--. 1 root root 314 Sep 5 2019 CentOS-fasttrack.repo
-rw-r--r--. 1 root root 630 Sep 5 2019 CentOS-Media.repo
-rw-r--r--. 1 root root 1.3K Sep 5 2019 CentOS-Sources.repo
-rw-r--r--. 1 root root 6.5K Sep 5 2019 CentOS-Vault.repo
[root@pg yum.repos.d]# mv CentOS-Base.repo CentOS-Base.repo.bak
[root@pg yum.repos.d]# vim pg.repo
-bash: vim: command not found
[root@pg yum.repos.d]# vi pg.repo
[pg]
name=pg
baseurl=file:///mnt
gpgcheck=0
enabled=1
安装成功
[root@pg yum.repos.d]# yum install gcc* -y
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
pg | 3.6 kB 00:00
(1/2): pg/group_gz | 165 kB 00:00
(2/2): pg/primary_db | 3.2 MB 00:00
Resolving Dependencies
--> Running transaction check
---> Package gcc.x86_64 0:4.8.5-39.el7 will be installed
--> Processing Dependency: cpp = 4.8.5-39.el7 for package: gcc-4.8.5-39.el7.x86_64
--> Processing Dependency: glibc-devel >= 2.2.90-12 for package: gcc-4.8.5-39.el7.x86_64
--> Processing Dependency: libmpfr.so.4()(64bit) for package: gcc-4.8.5-39.el7.x86_64
--> Processing Dependency: libmpc.so.3()(64bit) for package: gcc-4.8.5-39.el7.x86_64
---> Package gcc-c++.x86_64 0:4.8.5-39.el7 will be installed
--> Processing Dependency: libstdc++-devel = 4.8.5-39.el7 for package: gcc-c++-4.8.5-39.el7.x86_64
---> Package gcc-gfortran.x86_64 0:4.8.5-39.el7 will be installed
--> Processing Dependency: libquadmath-devel = 4.8.5-39.el7 for package: gcc-gfortran-4.8.5-39.el7.x86_64
--> Processing Dependency: libquadmath = 4.8.5-39.el7 for package: gcc-gfortran-4.8.5-39.el7.x86_64
--> Processing Dependency: libgfortran = 4.8.5-39.el7 for package: gcc-gfortran-4.8.5-39.el7.x86_64
--> Processing Dependency: libgfortran.so.3()(64bit) for package: gcc-gfortran-4.8.5-39.el7.x86_64
---> Package gcc-gnat.x86_64 0:4.8.5-39.el7 will be installed
--> Processing Dependency: libgnat-devel = 4.8.5-39.el7 for package: gcc-gnat-4.8.5-39.el7.x86_64
--> Processing Dependency: libgnat = 4.8.5-39.el7 for package: gcc-gnat-4.8.5-39.el7.x86_64
---> Package gcc-objc.x86_64 0:4.8.5-39.el7 will be installed
--> Processing Dependency: libobjc = 4.8.5-39.el7 for package: gcc-objc-4.8.5-39.el7.x86_64
--> Processing Dependency: libobjc.so.4()(64bit) for package: gcc-objc-4.8.5-39.el7.x86_64
---> Package gcc-objc++.x86_64 0:4.8.5-39.el7 will be installed
--> Running transaction check
---> Package cpp.x86_64 0:4.8.5-39.el7 will be installed
---> Package glibc-devel.x86_64 0:2.17-292.el7 will be installed
--> Processing Dependency: glibc-headers = 2.17-292.el7 for package: glibc-devel-2.17-292.el7.x86_64
--> Processing Dependency: glibc-headers for package: glibc-devel-2.17-292.el7.x86_64
---> Package libgfortran.x86_64 0:4.8.5-39.el7 will be installed
---> Package libgnat.x86_64 0:4.8.5-39.el7 will be installed
---> Package libgnat-devel.x86_64 0:4.8.5-39.el7 will be installed
---> Package libmpc.x86_64 0:1.0.1-3.el7 will be installed
---> Package libobjc.x86_64 0:4.8.5-39.el7 will be installed
---> Package libquadmath.x86_64 0:4.8.5-39.el7 will be installed
---> Package libquadmath-devel.x86_64 0:4.8.5-39.el7 will be installed
---> Package libstdc++-devel.x86_64 0:4.8.5-39.el7 will be installed
---> Package mpfr.x86_64 0:3.1.1-4.el7 will be installed
--> Running transaction check
---> Package glibc-headers.x86_64 0:2.17-292.el7 will be installed
--> Processing Dependency: kernel-headers >= 2.2.1 for package: glibc-headers-2.17-292.el7.x86_64
--> Processing Dependency: kernel-headers for package: glibc-headers-2.17-292.el7.x86_64
--> Running transaction check
---> Package kernel-headers.x86_64 0:3.10.0-1062.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository
Size
================================================================================
Installing:
gcc x86_64 4.8.5-39.el7 pg 16 M
gcc-c++ x86_64 4.8.5-39.el7 pg 7.2 M
gcc-gfortran x86_64 4.8.5-39.el7 pg 6.7 M
gcc-gnat x86_64 4.8.5-39.el7 pg 13 M
gcc-objc x86_64 4.8.5-39.el7 pg 5.7 M
gcc-objc++ x86_64 4.8.5-39.el7 pg 6.1 M
Installing for dependencies:
cpp x86_64 4.8.5-39.el7 pg 5.9 M
glibc-devel x86_64 2.17-292.el7 pg 1.1 M
glibc-headers x86_64 2.17-292.el7 pg 687 k
kernel-headers x86_64 3.10.0-1062.el7 pg 8.7 M
libgfortran x86_64 4.8.5-39.el7 pg 300 k
libgnat x86_64 4.8.5-39.el7 pg 967 k
libgnat-devel x86_64 4.8.5-39.el7 pg 2.7 M
libmpc x86_64 1.0.1-3.el7 pg 51 k
libobjc x86_64 4.8.5-39.el7 pg 80 k
libquadmath x86_64 4.8.5-39.el7 pg 190 k
libquadmath-devel x86_64 4.8.5-39.el7 pg 53 k
libstdc++-devel x86_64 4.8.5-39.el7 pg 1.5 M
mpfr x86_64 3.1.1-4.el7 pg 203 k
Transaction Summary
================================================================================
Install 6 Packages (+13 Dependent packages)
Total download size: 77 M
Installed size: 189 M
Downloading packages:
--------------------------------------------------------------------------------
Total 77 MB/s | 77 MB 00:01
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : mpfr-3.1.1-4.el7.x86_64 1/19
Installing : libmpc-1.0.1-3.el7.x86_64 2/19
Installing : libquadmath-4.8.5-39.el7.x86_64 3/19
Installing : libgfortran-4.8.5-39.el7.x86_64 4/19
Installing : cpp-4.8.5-39.el7.x86_64 5/19
Installing : libstdc++-devel-4.8.5-39.el7.x86_64 6/19
Installing : libobjc-4.8.5-39.el7.x86_64 7/19
Installing : libgnat-devel-4.8.5-39.el7.x86_64 8/19
Installing : kernel-headers-3.10.0-1062.el7.x86_64 9/19
Installing : glibc-headers-2.17-292.el7.x86_64 10/19
Installing : glibc-devel-2.17-292.el7.x86_64 11/19
Installing : gcc-4.8.5-39.el7.x86_64 12/19
Installing : gcc-c++-4.8.5-39.el7.x86_64 13/19
Installing : libquadmath-devel-4.8.5-39.el7.x86_64 14/19
Installing : gcc-objc-4.8.5-39.el7.x86_64 15/19
Installing : libgnat-4.8.5-39.el7.x86_64 16/19
Installing : gcc-gnat-4.8.5-39.el7.x86_64 17/19
Installing : gcc-objc++-4.8.5-39.el7.x86_64 18/19
Installing : gcc-gfortran-4.8.5-39.el7.x86_64 19/19
Verifying : glibc-devel-2.17-292.el7.x86_64 1/19
Verifying : libgnat-4.8.5-39.el7.x86_64 2/19
Verifying : kernel-headers-3.10.0-1062.el7.x86_64 3/19
Verifying : libgfortran-4.8.5-39.el7.x86_64 4/19
Verifying : gcc-gnat-4.8.5-39.el7.x86_64 5/19
Verifying : libgnat-devel-4.8.5-39.el7.x86_64 6/19
Verifying : gcc-c++-4.8.5-39.el7.x86_64 7/19
Verifying : mpfr-3.1.1-4.el7.x86_64 8/19
Verifying : gcc-gfortran-4.8.5-39.el7.x86_64 9/19
Verifying : libobjc-4.8.5-39.el7.x86_64 10/19
Verifying : libquadmath-4.8.5-39.el7.x86_64 11/19
Verifying : gcc-objc++-4.8.5-39.el7.x86_64 12/19
Verifying : libmpc-1.0.1-3.el7.x86_64 13/19
Verifying : libquadmath-devel-4.8.5-39.el7.x86_64 14/19
Verifying : cpp-4.8.5-39.el7.x86_64 15/19
Verifying : gcc-4.8.5-39.el7.x86_64 16/19
Verifying : gcc-objc-4.8.5-39.el7.x86_64 17/19
Verifying : libstdc++-devel-4.8.5-39.el7.x86_64 18/19
Verifying : glibc-headers-2.17-292.el7.x86_64 19/19
Installed:
gcc.x86_64 0:4.8.5-39.el7 gcc-c++.x86_64 0:4.8.5-39.el7
gcc-gfortran.x86_64 0:4.8.5-39.el7 gcc-gnat.x86_64 0:4.8.5-39.el7
gcc-objc.x86_64 0:4.8.5-39.el7 gcc-objc++.x86_64 0:4.8.5-39.el7
Dependency Installed:
cpp.x86_64 0:4.8.5-39.el7
glibc-devel.x86_64 0:2.17-292.el7
glibc-headers.x86_64 0:2.17-292.el7
kernel-headers.x86_64 0:3.10.0-1062.el7
libgfortran.x86_64 0:4.8.5-39.el7
libgnat.x86_64 0:4.8.5-39.el7
libgnat-devel.x86_64 0:4.8.5-39.el7
libmpc.x86_64 0:1.0.1-3.el7
libobjc.x86_64 0:4.8.5-39.el7
libquadmath.x86_64 0:4.8.5-39.el7
libquadmath-devel.x86_64 0:4.8.5-39.el7
libstdc++-devel.x86_64 0:4.8.5-39.el7
mpfr.x86_64 0:3.1.1-4.el7
Complete!
[root@pg ~]# which gcc
/usr/bin/gcc
[root@pg ~]# gcc -v
Using built-in specs.
COLLECT_GCC=gcc
COLLECT_LTO_WRAPPER=/usr/libexec/gcc/x86_64-redhat-linux/4.8.5/lto-wrapper
Target: x86_64-redhat-linux
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-bugurl=http://bugzilla.redhat.com/bugzilla --enable-bootstrap --enable-shared --enable-threads=posix --enable-checking=release --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-gnu-unique-object --enable-linker-build-id --with-linker-hash-style=gnu --enable-languages=c,c++,objc,obj-c++,java,fortran,ada,go,lto --enable-plugin --enable-initfini-array --disable-libgcj --with-isl=/builddir/build/BUILD/gcc-4.8.5-20150702/obj-x86_64-redhat-linux/isl-install --with-cloog=/builddir/build/BUILD/gcc-4.8.5-20150702/obj-x86_64-redhat-linux/cloog-install --enable-gnu-indirect-function --with-tune=generic --with-arch_32=x86-64 --build=x86_64-redhat-linux
Thread model: posix
gcc version 4.8.5 20150623 (Red Hat 4.8.5-39) (GCC)
3、安装tar软件包
[root@pg ~]# which tar
/usr/bin/tar
[root@pg ~]# tar --version
tar (GNU tar) 1.26
Copyright (C) 2011 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
Written by John Gilmore and Jay Fenlason.
4、GNU readline library
该库文件默认启用。用于在psql命令行工具下，可以通过键盘的上下箭头调出历史命令以及编辑之前的命令。
[root@pg ~]# yum install readline* -y
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Package readline-6.2-11.el7.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package readline-devel.x86_64 0:6.2-11.el7 will be installed
--> Processing Dependency: ncurses-devel for package: readline-devel-6.2-11.el7.x86_64
--> Running transaction check
---> Package ncurses-devel.x86_64 0:5.9-14.20130511.el7_4 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository
Size
================================================================================
Installing:
readline-devel x86_64 6.2-11.el7 pg 139 k
Installing for dependencies:
ncurses-devel x86_64 5.9-14.20130511.el7_4 pg 712 k
Transaction Summary
================================================================================
Install 1 Package (+1 Dependent package)
Total download size: 851 k
Installed size: 2.4 M
Downloading packages:
--------------------------------------------------------------------------------
Total 8.4 MB/s | 851 kB 00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : ncurses-devel-5.9-14.20130511.el7_4.x86_64 1/2
Installing : readline-devel-6.2-11.el7.x86_64 2/2
Verifying : readline-devel-6.2-11.el7.x86_64 1/2
Verifying : ncurses-devel-5.9-14.20130511.el7_4.x86_64 2/2
Installed:
readline-devel.x86_64 0:6.2-11.el7
Dependency Installed:
ncurses-devel.x86_64 0:5.9-14.20130511.el7_4
Complete!
5、zlib compression library
[root@pg ~]# yum install zlib* -y
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Package zlib-1.2.7-18.el7.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package zlib-devel.x86_64 0:1.2.7-18.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
zlib-devel x86_64 1.2.7-18.el7 pg 50 k
Transaction Summary
================================================================================
Install 1 Package
Total download size: 50 k
Installed size: 132 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : zlib-devel-1.2.7-18.el7.x86_64 1/1
Verifying : zlib-devel-1.2.7-18.el7.x86_64 1/1
Installed:
zlib-devel.x86_64 0:1.2.7-18.el7
Complete!
源码安装部署
1、创建postgres用户组和用户
[root@pg ~]# id postgres
id: postgres: no such user
[root@pg ~]# groupadd postgres -g 54322
[root@pg ~]# useradd -g postgres -u 54321 postgres
[root@pg ~]# id postgres
uid=54321(postgres) gid=54322(postgres) groups=54322(postgres)
[root@pg ~]# passwd postgres
Changing password for user postgres.
New password:
BAD PASSWORD: The password contains the user name in some form
Retype new password:
passwd: all authentication tokens updated successfully.
2、创建数据库软件的安装路径
把数据库软件安装在/data/postgres/13.2路径下，数据库的数据存放在/data/postgres/13.2/data路径
先把/data/postgres/13.2/路径创建出来即可，/data/postgres/13.2/data路径不需提前创建，届时初始化数据库的时候，会自动创建。
[root@pg ~]# mkdir -p /data/postgres/13.2/
[root@pg ~]# chown -R postgres:postgres /data/
[root@pg ~]# ll /data/
total 0
drwxr-xr-x. 3 postgres postgres 18 Jul 12 02:39 postgres
3、获取源码
[root@pg ~]# su - postgres
[postgres@pg ~]$ wget https://ftp.postgresql.org/pub/source/v13.2/postgresql-13.2.tar.gz
-bash: wget: command not found
[postgres@pg ~]$ yum install wget -y
Loaded plugins: fastestmirror
You need to be root to perform this command.
[postgres@pg ~]$ logout
[root@pg ~]# yum install wget -y
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Resolving Dependencies
--> Running transaction check
---> Package wget.x86_64 0:1.14-18.el7_6.1 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
wget x86_64 1.14-18.el7_6.1 pg 547 k
Transaction Summary
================================================================================
Install 1 Package
Total download size: 547 k
Installed size: 2.0 M
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : wget-1.14-18.el7_6.1.x86_64 1/1
Verifying : wget-1.14-18.el7_6.1.x86_64 1/1
Installed:
wget.x86_64 0:1.14-18.el7_6.1
Complete!
[root@pg ~]# ping qq.com
ping: qq.com: Name or service not known
[root@pg ~]# vim /etc/resolv.conf
-bash: vim: command not found
[root@pg ~]# yum install vim -y
[root@pg ~]# vim /etc/resolv.conf
nameserver 8.8.8.8
[root@pg ~]# ping qq.com -c 4
PING qq.com (61.129.7.47) 56(84) bytes of data.
64 bytes from 61.129.7.47 (61.129.7.47): icmp_seq=1 ttl=128 time=42.0 ms
64 bytes from 61.129.7.47 (61.129.7.47): icmp_seq=2 ttl=128 time=36.1 ms
64 bytes from 61.129.7.47 (61.129.7.47): icmp_seq=3 ttl=128 time=39.7 ms
64 bytes from 61.129.7.47 (61.129.7.47): icmp_seq=4 ttl=128 time=49.9 ms
--- qq.com ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 3007ms
rtt min/avg/max/mdev = 36.153/41.944/49.903/5.046 ms
[root@pg ~]# su - postgres
Last login: Wed Jul 12 02:40:17 CST 2023 on pts/0
[postgres@pg ~]$ wget https://ftp.postgresql.org/pub/source/v13.2/postgresql-13.2.tar.gz
--2023-07-12 02:44:46-- https://ftp.postgresql.org/pub/source/v13.2/postgresql-13.2.tar.gz
Resolving ftp.postgresql.org (ftp.postgresql.org)... 147.75.85.69, 217.196.149.55, 72.32.157.246, ...
Connecting to ftp.postgresql.org (ftp.postgresql.org)|147.75.85.69|:443... connected.
ERROR: cannot verify ftp.postgresql.org's certificate, issued by ‘/C=US/O=Let's Encrypt/CN=R3’:
Issued certificate has expired.
To connect to ftp.postgresql.org insecurely, use `--no-check-certificate'.
[postgres@pg ~]$ wget https://ftp.postgresql.org/pub/source/v13.2/postgresql-13.2.tar.gz --no-check-certificate
--2023-07-12 02:45:18-- https://ftp.postgresql.org/pub/source/v13.2/postgresql-13.2.tar.gz
Resolving ftp.postgresql.org (ftp.postgresql.org)... 147.75.85.69, 217.196.149.55, 72.32.157.246, ...
Connecting to ftp.postgresql.org (ftp.postgresql.org)|147.75.85.69|:443... connected.
WARNING: cannot verify ftp.postgresql.org's certificate, issued by ‘/C=US/O=Let's Encrypt/CN=R3’:
Issued certificate has expired.
HTTP request sent, awaiting response... 200 OK
Length: 27548921 (26M) [application/octet-stream]
Saving to: ‘postgresql-13.2.tar.gz’
100%[======================================>] 27,548,921 59.5KB/s in 4m 22s
2023-07-12 02:49:42 (103 KB/s) - ‘postgresql-13.2.tar.gz’ saved [27548921/27548921]
[postgres@pg ~]$ ll -h postgresql-13.2.tar.gz
-rw-rw-r--. 1 postgres postgres 27M Feb 9 2021 postgresql-13.2.tar.gz
4、解压缩
[postgres@pg ~]$ tar -zxvf postgresql-13.2.tar.gz
[postgres@pg ~]$ ll -h postgresql-13.2
total 744K
-rw-r--r--. 1 postgres postgres 490 Feb 9 2021 aclocal.m4
drwxrwxr-x. 2 postgres postgres 4.0K Feb 9 2021 config
-rwxr-xr-x. 1 postgres postgres 556K Feb 9 2021 configure
-rw-r--r--. 1 postgres postgres 81K Feb 9 2021 configure.in
drwxrwxr-x. 57 postgres postgres 4.0K Feb 9 2021 contrib
-rw-r--r--. 1 postgres postgres 1.2K Feb 9 2021 COPYRIGHT
drwxrwxr-x. 3 postgres postgres 87 Feb 9 2021 doc
-rw-r--r--. 1 postgres postgres 4.2K Feb 9 2021 GNUmakefile.in
-rw-r--r--. 1 postgres postgres 277 Feb 9 2021 HISTORY
-rw-r--r--. 1 postgres postgres 63K Feb 9 2021 INSTALL
-rw-r--r--. 1 postgres postgres 1.7K Feb 9 2021 Makefile
-rw-r--r--. 1 postgres postgres 1.2K Feb 9 2021 README
drwxrwxr-x. 16 postgres postgres 4.0K Feb 9 2021 src
5、执行configure
[postgres@pg ~]$ cd postgresql-13.2
[postgres@pg postgresql-13.2]$ ./configure --help
`configure' configures PostgreSQL 13.2 to adapt to many kinds of systems.
Usage: ./configure [OPTION]... [VAR=VALUE]...
To assign environment variables (e.g., CC, CFLAGS...), specify them as
VAR=VALUE. See below for descriptions of some of the useful variables.
Defaults for the options are specified in brackets.
Configuration:
-h, --help display this help and exit
--help=short display options specific to this package
--help=recursive display the short help of all the included packages
-V, --version display version information and exit
-q, --quiet, --silent do not print `checking ...' messages
--cache-file=FILE cache test results in FILE [disabled]
-C, --config-cache alias for `--cache-file=config.cache'
-n, --no-create do not create output files
--srcdir=DIR find the sources in DIR [configure dir or `..']
Installation directories:
--prefix=PREFIX install architecture-independent files in PREFIX
[/usr/local/pgsql]
--exec-prefix=EPREFIX install architecture-dependent files in EPREFIX
[PREFIX]
By default, `make install' will install all the files in
`/usr/local/pgsql/bin', `/usr/local/pgsql/lib' etc. You can specify
an installation prefix other than `/usr/local/pgsql' using `--prefix',
for instance `--prefix=$HOME'.
For better control, use the options below.
Fine tuning of the installation directories:
--bindir=DIR user executables [EPREFIX/bin]
--sbindir=DIR system admin executables [EPREFIX/sbin]
--libexecdir=DIR program executables [EPREFIX/libexec]
--sysconfdir=DIR read-only single-machine data [PREFIX/etc]
--sharedstatedir=DIR modifiable architecture-independent data [PREFIX/com]
--localstatedir=DIR modifiable single-machine data [PREFIX/var]
--libdir=DIR object code libraries [EPREFIX/lib]
--includedir=DIR C header files [PREFIX/include]
--oldincludedir=DIR C header files for non-gcc [/usr/include]
--datarootdir=DIR read-only arch.-independent data root [PREFIX/share]
--datadir=DIR read-only architecture-independent data [DATAROOTDIR]
--infodir=DIR info documentation [DATAROOTDIR/info]
--localedir=DIR locale-dependent data [DATAROOTDIR/locale]
--mandir=DIR man documentation [DATAROOTDIR/man]
--docdir=DIR documentation root [DATAROOTDIR/doc/postgresql]
--htmldir=DIR html documentation [DOCDIR]
--dvidir=DIR dvi documentation [DOCDIR]
--pdfdir=DIR pdf documentation [DOCDIR]
--psdir=DIR ps documentation [DOCDIR]
System types:
--build=BUILD configure for building on BUILD [guessed]
--host=HOST cross-compile to build programs to run on HOST [BUILD]
Optional Features:
--disable-option-checking ignore unrecognized --enable/--with options
--disable-FEATURE do not include FEATURE (same as --enable-FEATURE=no)
--enable-FEATURE[=ARG] include FEATURE [ARG=yes]
--disable-integer-datetimes
obsolete option, no longer supported
--enable-nls[=LANGUAGES]
enable Native Language Support
--disable-rpath do not embed shared library search path in
executables
--disable-spinlocks do not use spinlocks
--disable-atomics do not use atomic operations
--enable-debug build with debugging symbols (-g)
--enable-profiling build with profiling enabled
--enable-coverage build with coverage testing instrumentation
--enable-dtrace build with DTrace support
--enable-tap-tests enable TAP tests (requires Perl and IPC::Run)
--enable-depend turn on automatic dependency tracking
--enable-cassert enable assertion checks (for debugging)
--disable-thread-safety disable thread-safety in client libraries
--disable-largefile omit support for large files
Optional Packages:
--with-PACKAGE[=ARG] use PACKAGE [ARG=yes]
--without-PACKAGE do not use PACKAGE (same as --with-PACKAGE=no)
--with-extra-version=STRING
append STRING to version
--with-template=NAME override operating system template
--with-includes=DIRS look for additional header files in DIRS
--with-libraries=DIRS look for additional libraries in DIRS
--with-libs=DIRS alternative spelling of --with-libraries
--with-pgport=PORTNUM set default port number [5432]
--with-blocksize=BLOCKSIZE
set table block size in kB [8]
--with-segsize=SEGSIZE set table segment size in GB [1]
--with-wal-blocksize=BLOCKSIZE
set WAL block size in kB [8]
--with-CC=CMD set compiler (deprecated)
--with-llvm build with LLVM based JIT support
--with-icu build with ICU support
--with-tcl build Tcl modules (PL/Tcl)
--with-tclconfig=DIR tclConfig.sh is in DIR
--with-perl build Perl modules (PL/Perl)
--with-python build Python modules (PL/Python)
--with-gssapi build with GSSAPI support
--with-krb-srvnam=NAME default service principal name in Kerberos (GSSAPI)
[postgres]
--with-pam build with PAM support
--with-bsd-auth build with BSD Authentication support
--with-ldap build with LDAP support
--with-bonjour build with Bonjour support
--with-openssl build with OpenSSL support
--with-selinux build with SELinux support
--with-systemd build with systemd support
--without-readline do not use GNU Readline nor BSD Libedit for editing
--with-libedit-preferred
prefer BSD Libedit over GNU Readline
--with-uuid=LIB build contrib/uuid-ossp using LIB (bsd,e2fs,ossp)
--with-ossp-uuid obsolete spelling of --with-uuid=ossp
--with-libxml build with XML support
--with-libxslt use XSLT support when building contrib/xml2
--with-system-tzdata=DIR
use system time zone data in DIR
--without-zlib do not use Zlib
--with-gnu-ld assume the C compiler uses GNU ld [default=no]
Some influential environment variables:
CC C compiler command
CFLAGS C compiler flags
LDFLAGS linker flags, e.g. -L<lib dir> if you have libraries in a
nonstandard directory <lib dir>
LIBS libraries to pass to the linker, e.g. -l<library>
CPPFLAGS (Objective) C/C++ preprocessor flags, e.g. -I<include dir> if
you have headers in a nonstandard directory <include dir>
CXX C++ compiler command
CXXFLAGS C++ compiler flags
LLVM_CONFIG path to llvm-config command
CLANG path to clang compiler to generate bitcode
CPP C preprocessor
PKG_CONFIG path to pkg-config utility
PKG_CONFIG_PATH
directories to add to pkg-config's search path
PKG_CONFIG_LIBDIR
path overriding pkg-config's built-in search path
ICU_CFLAGS C compiler flags for ICU, overriding pkg-config
ICU_LIBS linker flags for ICU, overriding pkg-config
XML2_CONFIG path to xml2-config utility
XML2_CFLAGS C compiler flags for XML2, overriding pkg-config
XML2_LIBS linker flags for XML2, overriding pkg-config
LDFLAGS_EX extra linker flags for linking executables only
LDFLAGS_SL extra linker flags for linking shared libraries only
PERL Perl program
PYTHON Python program
MSGFMT msgfmt program for NLS
TCLSH Tcl interpreter program (tclsh)
Use these variables to override the choices made by `configure' or to help
it to find libraries and programs with nonstandard names/locations.
Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>.
默认软件安装路径是/usr/local/pgsql
--prefix=PREFIX install architecture-independent files in PREFIX
[/usr/local/pgsql]
[postgres@pg postgresql-13.2]$ ./configure --prefix=/data/postgres/13.2
...
checking whether gcc -std=gnu99 supports -Wl,--as-needed... yes
configure: using compiler=gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39)
configure: using CFLAGS=-Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2
configure: using CPPFLAGS= -D_GNU_SOURCE
configure: using LDFLAGS= -Wl,--as-needed
configure: creating ./config.status
config.status: creating GNUmakefile
config.status: creating src/Makefile.global
config.status: creating src/include/pg_config.h
config.status: creating src/include/pg_config_ext.h
config.status: creating src/interfaces/ecpg/include/ecpg_config.h
config.status: linking src/backend/port/tas/dummy.s to src/backend/port/tas.s
config.status: linking src/backend/port/posix_sema.c to src/backend/port/pg_sema.c
config.status: linking src/backend/port/sysv_shmem.c to src/backend/port/pg_shmem.c
config.status: linking src/include/port/linux.h to src/include/pg_config_os.h
config.status: linking src/makefiles/Makefile.linux to src/Makefile.port
6、执行make world
[postgres@pg postgresql-13.2]$ gmake world
。。。
gmake[2]: Leaving directory `/home/postgres/postgresql-13.2/contrib/vacuumlo'
gmake[1]: Leaving directory `/home/postgres/postgresql-13.2/contrib'
PostgreSQL, contrib, and documentation successfully made. Ready to install.
7、执行make install-world
[postgres@pg postgresql-13.2]$ gmake install-world
。。。
gmake[2]: Leaving directory `/home/postgres/postgresql-13.2/contrib/vacuumlo'
gmake[1]: Leaving directory `/home/postgres/postgresql-13.2/contrib'
PostgreSQL, contrib, and documentation installation complete.
8、初始化数据库
注意使用绝对路径
[postgres@pg postgresql-13.2]$ /data/postgres/13.2/bin/initdb -D /data/postgres/13.2/data
[postgres@pg postgresql-13.2]$ /data/postgres/13.2/bin/initdb -D /data/postgres/13.2/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.
creating directory /data/postgres/13.2/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:
/data/postgres/13.2/bin/pg_ctl -D /data/postgres/13.2/data -l logfile start
9、启动数据库
[postgres@pg postgresql-13.2]$ ps -ef | grep postgres
root 9567 9361 0 02:44 pts/0 00:00:00 su - postgres
postgres 9568 9567 0 02:44 pts/0 00:00:00 -bash
postgres 30135 9568 0 03:05 pts/0 00:00:00 ps -ef
postgres 30136 9568 0 03:05 pts/0 00:00:00 grep --color=auto postgres
[postgres@pg postgresql-13.2]$ /data/postgres/13.2/bin/pg_ctl -D /data/postgres/13.2/data -l logfile start
waiting for server to start.... done
server started
[postgres@pg postgresql-13.2]$ ps -ef | grep postgres
root 9567 9361 0 02:44 pts/0 00:00:00 su - postgres
postgres 9568 9567 0 02:44 pts/0 00:00:00 -bash
postgres 30140 1 0 03:06 ? 00:00:00 /data/postgres/13.2/bin/postgres -D /data/postgres/13.2/data
postgres 30142 30140 0 03:06 ? 00:00:00 postgres: checkpointer
postgres 30143 30140 0 03:06 ? 00:00:00 postgres: background writer
postgres 30144 30140 0 03:06 ? 00:00:00 postgres: walwriter
postgres 30145 30140 0 03:06 ? 00:00:00 postgres: autovacuum launcher
postgres 30146 30140 0 03:06 ? 00:00:00 postgres: stats collector
postgres 30147 30140 0 03:06 ? 00:00:00 postgres: logical replication launcher
postgres 30148 9568 0 03:06 pts/0 00:00:00 ps -ef
postgres 30149 9568 0 03:06 pts/0 00:00:00 grep --color=auto postgres
[postgres@pg postgresql-13.2]$
10、修改环境变量
[postgres@pg ~]$ vim .bash_profile
[postgres@pg ~]$ vim .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
export PG_BASE=/data/postgres/13.2
export PG_DATA=/data/postgres/13.2/data
export PATH=$PG_BASE/bin:$PATH
~
~
~
~
~
~
".bash_profile" 17L, 301C written
[postgres@pg ~]$ source .bash_profile
[postgres@pg ~]$ env | grep PG
PG_DATA=/data/postgres/13.2/data
PG_BASE=/data/postgres/13.2
[postgres@pg ~]$ psql
psql (13.2)
Type "help" for help.
postgres=#
至此，我们已经完成了在CentOS 7.7 操作系统的Linux服务器上，通过编译源代码的方式安装PostgreSQL 13.2的数据库。
修改数据库参数
1、修改pg_hba.conf文件
在/data/postgres/13.2/data/pg_hba.conf文件中，添加下面的一行：
[postgres@pg ~]$ vim /data/postgres/13.2/data/pg_hba.conf
# PostgreSQL Client Authentication Configuration File
# ===================================================
#
# Refer to the "Client Authentication" section in the PostgreSQL
# documentation for a complete description of this file. A short
# synopsis follows.
#
# This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access. Records take one of these forms:
#
# local DATABASE USER METHOD [OPTIONS]
# host DATABASE USER ADDRESS METHOD [OPTIONS]
# hostssl DATABASE USER ADDRESS METHOD [OPTIONS]
# hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
# hostgssenc DATABASE USER ADDRESS METHOD [OPTIONS]
# hostnogssenc DATABASE USER ADDRESS METHOD [OPTIONS]
#
# (The uppercase items must be replaced by actual values.)
#
# The first field is the connection type: "local" is a Unix-domain
# socket, "host" is either a plain or SSL-encrypted TCP/IP socket,
# "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a
"/data/postgres/13.2/data/pg_hba.conf" 97L, 4760C 1,1 Top
# "host" records. In that case you will also need to make PostgreSQL
# listen on a non-local interface via the listen_addresses
# configuration parameter, or via the -i or -h command line switches.
# CAUTION: Configuring the system for local "trust" authentication
# allows any local user to connect as any PostgreSQL user, including
# the database superuser. If you do not trust all your local users,
# use another authentication method.
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
#IPv4 local connections:
host all all 0.0.0.0/0 md5
"/data/postgres/13.2/data/pg_hba.conf" 100L, 4817C written
2、修改postgresql.conf 文件
在/data/postgres/13.2/data/data/postgresql.conf 文件中，修改#listen_addresses = ‘localhost’为listen_addresses = ‘*’
正确启停数据库
1、查看数据库是否正常运行
PostgreSQL数据库想要对外提供正常服务，其前提条件是数据库必须正常启动运行。我们可以通过查看数据库的后台进程是否存在来做判断。
[postgres@pg ~]$ ps -ef | grep postgres
root 9506 9412 0 13:12 pts/0 00:00:00 su - postgres
postgres 9507 9506 0 13:12 pts/0 00:00:00 -bash
postgres 30115 1 0 14:19 ? 00:00:00 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
postgres 30117 30115 0 14:19 ? 00:00:00 postgres: checkpointer
postgres 30118 30115 0 14:19 ? 00:00:00 postgres: background writer
postgres 30119 30115 0 14:19 ? 00:00:00 postgres: walwriter
postgres 30120 30115 0 14:19 ? 00:00:00 postgres: autovacuum launcher
postgres 30121 30115 0 14:19 ? 00:00:00 postgres: stats collector
postgres 30122 30115 0 14:19 ? 00:00:00 postgres: logical replication launcher
root 30144 30127 0 14:22 pts/2 00:00:00 su - postgres
postgres 30145 30144 0 14:22 pts/2 00:00:00 -bash
postgres 30182 9507 0 14:28 pts/0 00:00:00 ps -ef
postgres 30183 9507 0 14:28 pts/0 00:00:00 grep --color=auto postgres
守护进程30115
[postgres@pg ~]$ pstree -p 30115
-bash: pstree: command not found
[root@pg ~]# pstree
-bash: pstree: command not found
[root@pg ~]# yum install psmisc -y
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Resolving Dependencies
--> Running transaction check
---> Package psmisc.x86_64 0:22.20-16.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
psmisc x86_64 22.20-16.el7 pg 141 k
Transaction Summary
================================================================================
Install 1 Package
Total download size: 141 k
Installed size: 475 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : psmisc-22.20-16.el7.x86_64 1/1
Verifying : psmisc-22.20-16.el7.x86_64 1/1
Installed:
psmisc.x86_64 0:22.20-16.el7
Complete!
[postgres@pg ~]$ pstree -p 30115
postgres(30115)─┬─postgres(30117)
├─postgres(30118)
├─postgres(30119)
├─postgres(30120)
├─postgres(30121)
└─postgres(30122)
[postgres@pg ~]$ pg_ctl status
pg_ctl: server is running (PID: 30115)
/usr/local/pgsql/bin/postgres "-D" "/usr/local/pgsql/data"
2、如何手工启动PostgreSQL数据库
正确启动Linux服务器上的PostgreSQL数据库，我们依赖于pg_ctl命令，和PGDATA这个环境变量
[postgres@pg ~]$ pg_ctl status
pg_ctl: server is running (PID: 30115)
/usr/local/pgsql/bin/postgres "-D" "/usr/local/pgsql/data"
[postgres@pg ~]$ which pg_ctl
/usr/local/pgsql/bin/pg_ctl
[postgres@pg ~]$ pg_ctl start -D /usr/local/pgsql/data -l /home/postgres/startup.log
waiting for server to start.... done
server started
-D 选项，是指指出数据库集群的根路径。由于，我们之前已经配置了PGDATA环境变量，所以，我们可以在命令行上省略该选项。如果，没有正确配置指定PGDATA环境变量，则要显示指出。
-l选项，表示数据库启动日志写入到哪个文件中。非必需项。
[postgres@pg ~]$ ps -ef | grep postgres
root 9506 9412 0 13:12 pts/0 00:00:00 su - postgres
postgres 9507 9506 0 13:12 pts/0 00:00:00 -bash
root 30144 30127 0 14:22 pts/2 00:00:00 su - postgres
postgres 30145 30144 0 14:22 pts/2 00:00:00 -bash
postgres 30222 1 0 14:42 ? 00:00:00 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
postgres 30224 30222 0 14:42 ? 00:00:00 postgres: checkpointer
postgres 30225 30222 0 14:42 ? 00:00:00 postgres: background writer
postgres 30226 30222 0 14:42 ? 00:00:00 postgres: walwriter
postgres 30227 30222 0 14:42 ? 00:00:00 postgres: autovacuum launcher
postgres 30228 30222 0 14:42 ? 00:00:00 postgres: stats collector
postgres 30229 30222 0 14:42 ? 00:00:00 postgres: logical replication launcher
postgres 30230 9507 0 14:43 pts/0 00:00:00 ps -ef
postgres 30231 9507 0 14:43 pts/0 00:00:00 grep --color=auto postgres
[postgres@pg ~]$ pstree -p 30222
postgres(30222)─┬─postgres(30224)
├─postgres(30225)
├─postgres(30226)
├─postgres(30227)
├─postgres(30228)
└─postgres(30229)
[postgres@pg ~]$ more startup.log
2023-07-12 14:42:40.882 CST [30222] LOG: starting PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
2023-07-12 14:42:40.883 CST [30222] LOG: listening on IPv4 address "0.0.0.0", port 5432
2023-07-12 14:42:40.883 CST [30222] LOG: listening on IPv6 address "::", port 5432
2023-07-12 14:42:40.885 CST [30222] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-07-12 14:42:40.910 CST [30223] LOG: database system was shut down at 2023-07-12 14:40:09 CST
2023-07-12 14:42:40.912 CST [30222] LOG: database system is ready to accept connections
3、如何手工关闭PostgreSQL数据库
[postgres@pg ~]$ pg_ctl stop -m fast
waiting for server to shut down....2023-07-12 14:40:09.984 CST [30115] LOG: received fast shutdown request
2023-07-12 14:40:09.985 CST [30115] LOG: aborting any active transactions
2023-07-12 14:40:09.986 CST [30115] LOG: background worker "logical replication launcher" (PID 30122) exited with exit code 1
2023-07-12 14:40:09.986 CST [30117] LOG: shutting down
2023-07-12 14:40:09.991 CST [30115] LOG: database system is shut down
done
server stopped
[postgres@pg ~]$ ps -ef | grep postgres
root 9506 9412 0 13:12 pts/0 00:00:00 su - postgres
postgres 9507 9506 0 13:12 pts/0 00:00:00 -bash
root 30144 30127 0 14:22 pts/2 00:00:00 su - postgres
postgres 30145 30144 0 14:22 pts/2 00:00:00 -bash
postgres 30217 9507 0 14:40 pts/0 00:00:00 ps -ef
postgres 30218 9507 0 14:40 pts/0 00:00:00 grep --color=auto postgres
-m fast，表示以fast这个模式mode，快速干净的关闭数据库。
关闭过程中会回滚相关未提交事务，下次启动数据库时，无需instance recovery，类似于Oracle的shutdown immediate方式关闭数据库。
4、关于pg_ctl的更多使用选项
[postgres@pg ~]$ 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
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
Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
psql工具使用
1、用psql连接数据库
[postgres@pg ~]$ psql
psql (13.2)
Type "help" for help.
postgres=#
psql是PostgreSQL软件安装家目录下的bin路径下的可执行程序;
-h选项表示host，要连接数据库服务器名或者IP地址；如果要访问的数据库在远端，不在本地服务器上，则这里应该用那台机器的IP地址；如果是云服务器的话，则用云服务商提供的域名字符串即可；
-p选项表示port，数据库运行在哪个端口上，默认是5432，这个可以在postgres.conf配置文件里修改，但是需要restart数据库才生效；
-d选项表示database，我们要连接访问的数据库名；
-U选项表示username，我们以哪个用户来访问数据库。
[postgres@pg ~]$ psql -hlocalhost -p5432
psql (13.2)
Type "help" for help.
postgres=#
2、关于psql工具的更多帮助
[postgres@pg ~]$ psql --help
psql is the PostgreSQL interactive terminal.
Usage:
psql [OPTION]... [DBNAME [USERNAME]]
General options:
-c, --command=COMMAND run only single command (SQL or internal) and exit
-d, --dbname=DBNAME database name to connect to (default: "postgres")
-f, --file=FILENAME execute commands from file, then exit
-l, --list list available databases, then exit
-v, --set=, --variable=NAME=VALUE
set psql variable NAME to VALUE
(e.g., -v ON_ERROR_STOP=1)
-V, --version output version information, then exit
-X, --no-psqlrc do not read startup file (~/.psqlrc)
-1 ("one"), --single-transaction
execute as a single transaction (if non-interactive)
-?, --help[=options] show this help, then exit
--help=commands list backslash commands, then exit
--help=variables list special variables, then exit
Input and output options:
-a, --echo-all echo all input from script
-b, --echo-errors echo failed commands
-e, --echo-queries echo commands sent to server
-E, --echo-hidden display queries that internal commands generate
-L, --log-file=FILENAME send session log to file
-n, --no-readline disable enhanced command line editing (readline)
-o, --output=FILENAME send query results to file (or |pipe)
-q, --quiet run quietly (no messages, only query output)
-s, --single-step single-step mode (confirm each query)
-S, --single-line single-line mode (end of line terminates SQL command)
Output format options:
-A, --no-align unaligned table output mode
--csv CSV (Comma-Separated Values) table output mode
-F, --field-separator=STRING
field separator for unaligned output (default: "|")
-H, --html HTML table output mode
-P, --pset=VAR[=ARG] set printing option VAR to ARG (see \pset command)
-R, --record-separator=STRING
record separator for unaligned output (default: newline)
-t, --tuples-only print rows only
-T, --table-attr=TEXT set HTML table tag attributes (e.g., width, border)
-x, --expanded turn on expanded table output
-z, --field-separator-zero
set field separator for unaligned output to zero byte
-0, --record-separator-zero
set record separator for unaligned output to zero byte
Connection options:
-h, --host=HOSTNAME database server host or socket directory (default: "local socket")
-p, --port=PORT database server port (default: "5432")
-U, --username=USERNAME database user name (default: "postgres")
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
For more information, type "\?" (for internal commands) or "\help" (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.
Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
3、psql中执行SQL语句
Data Definition Language(create|drop|truncate)；
Data Control Language(grant|revoke)；
Data Manipulate Language(insert|update|delete)；
Transaction Control Language(commit|rollback|savepoint)
psql命令行工具默认对DML语句是自动提交事务的。也就是说，默认情况下，是开启事务并自动提交的。
如果我们在执行SQL语句时想要手工控制事务的话，我们可以通过begin;来显示开启事务，然后执行SQL语句，通过end;
或者commit;来提交事务，想回滚事务的话，则通过rollback;
[postgres@pg ~]$ psql -hlocalhost -p5432
psql (13.2)
Type "help" for help.
postgres=# \c
You are now connected to database "postgres" as user "postgres".
postgres=# \d
Did not find any relations.
postgres=# create table test(id int,name text);
CREATE TABLE
postgres=# \d t
Did not find any relation named "t".
postgres=# \d test
Table "public.test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
name | text | | |
postgres=# insert into test values(110113,'北京市顺义区');
INSERT 0 1
postgres=# select * from test;
id | name
--------+--------------
110113 | 北京市顺义区
(1 row)
postgres=# \!
[postgres@pg ~]$ vim time.sql
select current_timestamp;
[postgres@pg ~]$ exit
exit
[postgres@pg ~]$ exit
exit
postgres=# \i time.sql
current_timestamp
-------------------------------
2023-07-12 15:15:49.709733+08
(1 row)
其中的\i是表示执行外部命令，\!表示的是在psql命令行上临时执行shell命令；
postgres=# \h
Available help:
ABORT CREATE USER
ALTER AGGREGATE CREATE USER MAPPING
ALTER COLLATION CREATE VIEW
ALTER CONVERSION DEALLOCATE
ALTER DATABASE DECLARE
ALTER DEFAULT PRIVILEGES DELETE
ALTER DOMAIN DISCARD
ALTER EVENT TRIGGER DO
ALTER EXTENSION DROP ACCESS METHOD
ALTER FOREIGN DATA WRAPPER DROP AGGREGATE
ALTER FOREIGN TABLE DROP CAST
ALTER FUNCTION DROP COLLATION
ALTER GROUP DROP CONVERSION
ALTER INDEX DROP DATABASE
ALTER LANGUAGE DROP DOMAIN
ALTER LARGE OBJECT DROP EVENT TRIGGER
ALTER MATERIALIZED VIEW DROP EXTENSION
ALTER OPERATOR DROP FOREIGN DATA WRAPPER
ALTER OPERATOR CLASS DROP FOREIGN TABLE
ALTER OPERATOR FAMILY DROP FUNCTION
ALTER POLICY DROP GROUP
ALTER PROCEDURE DROP INDEX
postgres=# \h create database
Command: CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
[ [ WITH ] [ OWNER [=] user_name ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ LOCALE [=] locale ]
[ LC_COLLATE [=] lc_collate ]
[ LC_CTYPE [=] lc_ctype ]
[ TABLESPACE [=] tablespace_name ]
[ ALLOW_CONNECTIONS [=] allowconn ]
[ CONNECTION LIMIT [=] connlimit ]
[ IS_TEMPLATE [=] istemplate ] ]
URL: https://www.postgresql.org/docs/13/sql-createdatabase.html
postgres=# \h create index
Command: CREATE INDEX
Description: define a new index
Syntax:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ INCLUDE ( column_name [, ...] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ TABLESPACE tablespace_name ]
[ WHERE predicate ]
URL: https://www.postgresql.org/docs/13/sql-createindex.html
数据库常用维护管理命令
1、查看数据库版本号
postgres=# select version();
version
--------------------------------------------------------------------------------
-------------------------
PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (R
ed Hat 4.8.5-39), 64-bit
(1 row)
2、查看所有数据库信息
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7901 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7737 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7737 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(3 rows)
3、查看数据库启动时间信息
postgres=# select pg_postmaster_start_time();
pg_postmaster_start_time
-------------------------------
2023-07-12 14:42:40.909396+08
(1 row)
4、查看用户信息
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
5、显示所有的表
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | test | table | postgres
(1 row)
6、查看表结构
postgres=# \d test
Table "public.test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
name | text | | |
7、查看表大小
postgres=# \dt++ test
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+------+-------+----------+-------------+-------+-------------
public | test | table | postgres | permanent | 16 kB |
(1 row)
8、查看索引
postgres=# create index idx_id_test on test(id);
CREATE INDEX
postgres=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+-------------+-------+----------+-------
public | idx_id_test | index | postgres | test
(1 row)
postgres=# \di+ idx_id_test
List of relations
Schema | Name | Type | Owner | Table | Persistence | Size | Descrip
tion
--------+-------------+-------+----------+-------+-------------+-------+--------
-----
public | idx_id_test | index | postgres | test | permanent | 16 kB |
(1 row)
9、创建新用户
postgres=# create user hr login password 'oracle';
CREATE ROLE
10、创建和使用数据库
postgres=# create user hr login password 'oracle';
CREATE ROLE
postgres=# create database dbtest01 owner hr;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
dbtest01 | hr | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
[postgres@pg ~]$ psql -ddbtest01 -Uhr
psql (13.2)
Type "help" for help.
dbtest01=> \c
You are now connected to database "dbtest01" as user "hr".
远程连接
1、Navicat连接pg，开放5432端口
[root@pg ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
Active: active (running) since Wed 2023-07-12 12:40:11 CST; 3h 17min ago
Docs: man:firewalld(1)
Main PID: 798 (firewalld)
CGroup: /system.slice/firewalld.service
└─798 /usr/bin/python2 -Es /usr/sbin/firewalld --nofork --nopid
Jul 12 12:40:10 pg systemd[1]: Starting firewalld - dynamic firewall daemon...
Jul 12 12:40:11 pg systemd[1]: Started firewalld - dynamic firewall daemon.
[root@pg ~]# firewall-cmd --add-service=http --permanent
success
[root@pg ~]# firewall-cmd --add-port=5432/tcp --permanent
success
[root@pg ~]# firewall-cmd --reload
success
[root@pg ~]# firewall-cmd --list-all
public (active)
target: default
icmp-block-inversion: no
interfaces: ens33
sources:
services: dhcpv6-client http ssh
ports: 5432/tcp
protocols:
masquerade: no
forward-ports:
source-ports:
icmp-blocks:
rich rules:
2、修改postgres用户密码
hr/oracle
postgres/oracle
postgres=# select usename,passwd from pg_shadow;
usename | passwd
----------+-------------------------------------
postgres |
hr | md50de1d31e0909f08fd1dcf0789a01a4e4
(2 rows)
postgres=# alter user postgres with encrypted password 'oracle';
ALTER ROLE
postgres=# select usename,passwd from pg_shadow;
usename | passwd
----------+-------------------------------------
hr | md50de1d31e0909f08fd1dcf0789a01a4e4
postgres | md57e1a83ffb47df2396c5eafd05bac4ade
(2 rows)
扫一扫
898
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
