数据库敲门人 - 带你敲开数据库大门 (oracleonlinux)://www.knockatdatabase/具体文档地址
/
软件下载
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 <.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 <.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/gmakegmake是一个链接文件，其指向了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 /?release=7&arch=x86_64&repo=os&infra=stock error was
14: curl#6 - "Could not resolve host: s; Unknown error"One of the configured repositories failed (Unknown),and yum doesn't have enough cached data to continue. At this point the onlysafe 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 workingupstream. This is most often useful if you are using a newerdistribution release than is supported by the repository (and thepackages for the previous distribution release still work).3. Run the command with the repository temporarily disabledyum --disablerepo=<repoid> ...4. Disable the repository permanently, so yum won't use it by default. Yumwill then just ignore the repository until you permanently enable itagain or use --enablerepo for temporary usage:yum-config-manager --disable <repoid>orsubscription-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 muchslower). If it is a very temporary problem though, this is often a nicecompromise: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 /pos.d/
[root@pos.d]# ll -h
total 32K
-rw-r--r--. 1 root root 1.7K Sep 5 po
-rw-r--r--. 1 root root 1.3K Sep 5 po
-rw-r--r--. 1 root root 649 Sep 5 po
-rw-r--r--. 1 root root 314 Sep 5 po
-rw-r--r--. 1 root root 630 Sep 5 po
-rw-r--r--. 1 root root 1.3K Sep 5 po
-rw-r--r--. 1 root root 6.5K Sep 5 po
[root@pos.d]# po.bak
[root@pos.d]# po
-bash: vim: command not found
[root@pos.d]# po
[pg]
name=pg
baseurl=file:///mnt
gpgcheck=0
enabled=1安装成功
[root@pos.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 ResolutionDependencies Resolved================================================================================Package Arch Version RepositorySize
================================================================================
Installing:gcc x86_64 4.8.5-39.el7 pg 16 Mgcc-c++ x86_64 4.8.5-39.el7 pg 7.2 Mgcc-gfortran x86_64 4.8.5-39.el7 pg 6.7 Mgcc-gnat x86_64 4.8.5-39.el7 pg 13 Mgcc-objc x86_64 4.8.5-39.el7 pg 5.7 Mgcc-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 Mglibc-devel x86_64 2.17-292.el7 pg 1.1 Mglibc-headers x86_64 2.17-292.el7 pg 687 kkernel-headers x86_64 3.10.0-1062.el7 pg 8.7 Mlibgfortran x86_64 4.8.5-39.el7 pg 300 klibgnat x86_64 4.8.5-39.el7 pg 967 klibgnat-devel x86_64 4.8.5-39.el7 pg 2.7 Mlibmpc x86_64 1.0.1-3.el7 pg 51 klibobjc x86_64 4.8.5-39.el7 pg 80 klibquadmath x86_64 4.8.5-39.el7 pg 190 klibquadmath-devel x86_64 4.8.5-39.el7 pg 53 klibstdc++-devel x86_64 4.8.5-39.el7 pg 1.5 Mmpfr x86_64 3.1.1-4.el7 pg 203 kTransaction 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 transactionInstalling : 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= --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 <.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 ResolutionDependencies Resolved================================================================================Package Arch Version RepositorySize
================================================================================
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 kTransaction 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 transactionInstalling : 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 ResolutionDependencies Resolved================================================================================Package Arch Version Repository Size
================================================================================
Installing:zlib-devel x86_64 1.2.7-18.el7 pg 50 kTransaction Summary
================================================================================
Install 1 PackageTotal download size: 50 k
Installed size: 132 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transactionInstalling : 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 postgres3、获取源码[root@pg ~]# su - postgres
[postgres@pg ~]$ wget .2/postgresql-13.
-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 ResolutionDependencies Resolved================================================================================Package Arch Version Repository Size
================================================================================
Installing:wget x86_64 1.14-18.el7_6.1 pg 547 kTransaction Summary
================================================================================
Install 1 PackageTotal download size: 547 k
Installed size: 2.0 M
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transactionInstalling : 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
ping: qq: Name or service not known
[root@pg ~]# vim /f
-bash: vim: command not found
[root@pg ~]# yum install vim -y[root@pg ~]# vim /f
nameserver 8.8.8.8[root@pg ~]# ping qq -c 4
PING qq (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 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 .2/postgresql-13.
--2023-07-12 02:44:46-- .2/postgresql-13.
Resolving ftp.postgresql (ftp.postgresql)... 147.75.85.69, 217.196.149.55, 72.32.157.246, ...
Connecting to ftp.postgresql (ftp.postgresql)|147.75.85.69|: connected.
ERROR: cannot verify ftp.postgresql's certificate, issued by ‘/C=US/O=Let's Encrypt/CN=R3’:Issued certificate has expired.
To connect to ftp.postgresql insecurely, use `--no-check-certificate'.[postgres@pg ~]$ wget .2/postgresql-13. --no-check-certificate
--2023-07-12 02:45:18-- .2/postgresql-13.
Resolving ftp.postgresql (ftp.postgresql)... 147.75.85.69, 217.196.149.55, 72.32.157.246, ...
Connecting to ftp.postgresql (ftp.postgresql)|147.75.85.69|: connected.
WARNING: cannot verify ftp.postgresql's certificate, issued by ‘/C=US/O=Let's Encrypt/CN=R3’:Issued certificate has expired.
HTTP request sent, 200 OK
Length: 27548921 (26M) [application/octet-stream]
Saving to: ‘postgresql-13.’100%[======================================>] 27,548,921 59.5KB/s in 4m 22s 2023-07-12 02:49:42 (103 KB/s) - ‘postgresql-13.’ saved [27548921/27548921][postgres@pg ~]$ ll -h postgresql-13.
-rw-rw-r--. 1 postgres postgres 27M Feb 9 2021 postgresql-13.4、解压缩
[postgres@pg ~]$ tar -zxvf postgresql-13.[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 src5、执行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, ), 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-datetimesobsolete option, no longer supported--enable-nls[=LANGUAGES]enable Native Language Support--disable-rpath do not embed shared library search path inexecutables--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 filesOptional Packages:--with-PACKAGE[=ARG] use PACKAGE [ARG=yes]--without-PACKAGE do not use PACKAGE (same as --with-PACKAGE=no)--with-extra-version=STRINGappend 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=BLOCKSIZEset table block size in kB [8]--with-segsize=SEGSIZE set table segment size in GB [1]--with-wal-blocksize=BLOCKSIZEset 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-preferredprefer 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=DIRuse 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 commandCFLAGS C compiler flagsLDFLAGS linker flags, e.g. -L<lib dir> if you have libraries in anonstandard 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> ifyou have headers in a nonstandard directory <include dir>CXX C++ compiler commandCXXFLAGS C++ compiler flagsLLVM_CONFIG path to llvm-config commandCLANG path to clang compiler to generate bitcodeCPP C preprocessorPKG_CONFIG path to pkg-config utilityPKG_CONFIG_PATHdirectories to add to pkg-config's search pathPKG_CONFIG_LIBDIRpath overriding pkg-config's built-in search pathICU_CFLAGS C compiler flags for ICU, overriding pkg-configICU_LIBS linker flags for ICU, overriding pkg-configXML2_CONFIG path to xml2-config utilityXML2_CFLAGS C compiler flags for XML2, overriding pkg-configXML2_LIBS linker flags for XML2, overriding pkg-configLDFLAGS_EX extra linker flags for linking executables onlyLDFLAGS_SL extra linker flags for linking shared libraries onlyPERL Perl programPYTHON Python programMSGFMT msgfmt program for NLSTCLSH 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>.
PostgreSQL home page: </>.默认软件安装路径是/usr/local/pgsql
--prefix=PREFIX install architecture-independent files in PREFIX[/usr/local/pgsql][postgres@pg postgresql-13.2]$ ./configure --prefix=/data/postgres/
checking whether gcc -std=gnu99 supports -Wl,-- 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.port6、执行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 ating 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 ... okinitdb: warning: enabling "trust" authentication for local connections
You can change this by editing f 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 start9、启动数据库[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 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 programsPATH=$PATH:$HOME/.local/bin:$HOME/binexport PATHexport PG_BASE=/data/postgres/13.2
export PG_DATA=/data/postgres/13.2/dataexport 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、修改f文件在/data/postgres/13.2/data/f文件中，添加下面的一行：[postgres@pg ~]$ vim /data/postgres/13.2/data/f
# 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/f" 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/f" 100L, 4817C written 2、修改f 文件在/data/postgres/13.2/data/f 文件中，修改#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 ResolutionDependencies Resolved================================================================================Package Arch Version Repository Size
================================================================================
Installing:psmisc x86_64 22.20-16.el7 pg 141 kTransaction Summary
================================================================================
Install 1 PackageTotal download size: 141 k
Installed size: 475 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transactionInstalling : 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 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 connections3、如何手工关闭PostgreSQL数据库[postgres@pg ~]$ pg_ctl stop -m fast
waiting for server to 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 downdone
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 PIDCommon 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 necessaryOptions for stop or restart:-m, --mode=MODE MODE can be "smart", "fast", or "immediate"Shutdown modes are:smart quit after all clients have disconnectedfast quit directly, with proper shutdown (default)immediate quit without complete shutdown; will lead to recovery on restartAllowed signal names for kill:ABRT HUP INT KILL QUIT TERM USR1 USR2Report bugs to <pgsql-bugs@lists.postgresql>.
PostgreSQL home page: </>
1、用psql连接数据库[postgres@pg ~]$ psql
psql (13.2)
Type "help" for help.postgres=# psql是PostgreSQL软件安装家目录下的bin路径下的可执行程序;
-h选项表示host，要连接数据库服务器名或者IP地址；如果要访问的数据库在远端，不在本地服务器上，则这里应该用那台机器的IP地址；如果是云服务器的话，则用云服务商提供的域名字符串即可；
-p选项表示port，数据库运行在哪个端口上，默认是5432，这个可以在f配置文件里修改，但是需要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=VALUEset psql variable NAME to , -v ON_ERROR_STOP=1)-V, --version output version information, then exit-X, --no-psqlrc do not read startup file (~/.psqlrc)-1 ("one"), --single-transactionexecute 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 exitInput 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=STRINGfield 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=STRINGrecord 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-zeroset field separator for unaligned output to zero byte-0, --record-separator-zeroset record separator for unaligned output to zero byteConnection 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>.
PostgreSQL home page: </>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 testTable &#st"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.sqlcurrent_timestamp
-------------------------------2023-07-12 15:15:49.709733+08
(1 row)其中的i是表示执行外部命令，!表示的是在psql命令行上临时执行shell命令；postgres=# h
Available help:ABORT CREATE USERALTER AGGREGATE CREATE USER MAPPINGALTER COLLATION CREATE VIEWALTER CONVERSION DEALLOCATEALTER DATABASE DECLAREALTER DEFAULT PRIVILEGES DELETEALTER DOMAIN DISCARDALTER EVENT TRIGGER DOALTER EXTENSION DROP ACCESS METHODALTER FOREIGN DATA WRAPPER DROP AGGREGATEALTER FOREIGN TABLE DROP CASTALTER FUNCTION DROP COLLATIONALTER GROUP DROP CONVERSIONALTER INDEX DROP DATABASEALTER LANGUAGE DROP DOMAINALTER LARGE OBJECT DROP EVENT TRIGGERALTER MATERIALIZED VIEW DROP EXTENSIONALTER OPERATOR DROP FOREIGN DATA WRAPPERALTER OPERATOR CLASS DROP FOREIGN TABLEALTER OPERATOR FAMILY DROP FUNCTIONALTER POLICY DROP GROUPALTER 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: .htmlpostgres=# 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: .html1、查看数据库版本号
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=# lList of databasesName | 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/postgrestemplate1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| | | | | postgres=CTc/postgres
(3 rows)postgres=# l+List of databasesName | 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 databasetemplate0 | 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=# duList of rolesRole name | Attributes | Member of
-----------+------------------------------------------------------------+-----------postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}5、显示所有的表
postgres=# dList of relationsSchema | Name | Type | Owner
--------+------+-------+----------public | test | table | postgres
(1 row)6、查看表结构postgres=# d testTable &#st"Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------id | integer | | | name | text | | | 7、查看表大小postgres=# dt++ testList of relationsSchema | 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=# diList of relationsSchema | Name | Type | Owner | Table
--------+-------------+-------+----------+-------public | idx_id_test | index | postgres | test
(1 row)postgres=# di+ idx_id_testList of relationsSchema | 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 ROLE10、创建和使用数据库postgres=# create user hr login password 'oracle';
CREATE ROLE
postgres=# create database dbtest01 owner hr;
CREATE DATABASEpostgres=# lList of databasesName | 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/postgrestemplate1 | 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 daemonLoaded: 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 agoDocs: man:firewalld(1)Main PID: 798 (firewalld)CGroup: /system.slice/firewalld.service└─798 /usr/bin/python2 -Es /usr/sbin/firewalld --nofork --nopidJul 12 12:40:10 pg systemd[1]: Starting firewalld - dynamic
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: defaulticmp-block-inversion: nointerfaces: ens33sources: services: dhcpv6-client http sshports: 5432/tcpprotocols: masquerade: noforward-ports: source-ports: icmp-blocks: rich rules: 2、修改postgres用户密码hr/oracle
postgres/oraclepostgres=# 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 | md50de1d31e0909f08fd1dcf0789a01a4e4postgres | md57e1a83ffb47df2396c5eafd05bac4ade
(2 rows)本文发布于:2024-02-04 08:36:54，感谢您对本站的认可！
本文链接：https://www.4u4v.net/it/170703255754015.html
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系，我们将在24小时内删除。
| 留言与评论（共有 0 条评论） |
