在 AnolisOS8.2 上用源码编译方式安装 Postgresql15.2
确认环境
[root@pg ~]# uname -osr
Linux 4.18.0-193.el8.x86_64 GNU/Linux
[root@pg ~]# more /etc/redhat-release
Anolis OS release 8.2
关闭防火墙
[root@pg ~]# systemctl stop firewalld
[root@pg ~]# systemctl disable firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@pg ~]# systemctl status firewalld.service
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
Active: inactive (dead)
Docs: man:firewalld(1)
3月 17 01:51:38 pg systemd[1]: Starting firewalld - dynamic firewall daemon...
3月 17 01:51:39 pg systemd[1]: Started firewalld - dynamic firewall daemon.
3月 17 01:51:40 pg firewalld[1042]: WARNING: AllowZoneDrifting is enabled. This is considered an insecure configuration option. >
3月 18 18:10:51 pg systemd[1]: Stopping firewalld - dynamic firewall daemon...
3月 18 18:10:53 pg systemd[1]: Stopped firewalld - dynamic firewall daemon.
[root@pg ~]# vim /etc/selinux/config
[root@pg ~]# cat /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=disable
# SELINUXTYPE= can take one of these three values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are protected.
# mls - Multi Level Security protection.
SELINUXTYPE=targeted
安装依赖
[root@pg ~]# yum -y install readline readline-devel zlib zlib-devel gettext gettext-devel openssl openssl-devel pam pam-devel libxml2 libxml2-devel libxslt libxslt-devel perl perl-devel tcl-devel uuid-devel gcc gcc-c++ make flex bison perl-ExtUtils*
已安装:
annobin-10.67-3.0.1.an8.x86_64
bison-3.7.4-5.an8.x86_64
cmake-filesystem-3.20.2-4.0.1.an8.x86_64
cpp-8.5.0-10.1.0.3.an8.x86_64
dwz-0.14-3.an8.x86_64
efi-srpm-macros-3-3.0.3.an8.noarch
flex-2.6.4-9.an8.x86_64
gcc-8.5.0-10.1.0.3.an8.x86_64
gcc-c++-8.5.0-10.1.0.3.an8.x86_64
gettext-common-devel-0.19.8.1-17.0.1.an8.noarch
gettext-devel-0.19.8.1-17.0.1.an8.x86_64
ghc-srpm-macros-1.4.2-7.el8.noarch
glibc-devel-2.28-189.5.0.2.an8_6.x86_64
glibc-gconv-extra-2.28-189.5.0.2.an8_6.x86_64
glibc-headers-2.28-189.5.0.2.an8_6.x86_64
go-srpm-macros-2-17.0.2.an8.noarch
isl-0.16.1-6.0.1.an8.x86_64
keyutils-libs-devel-1.5.10-9.an8.x86_64
krb5-devel-1.18.2-22.0.1.an8_7.x86_64
libasan-8.5.0-10.1.0.3.an8.x86_64
libatomic-8.5.0-10.1.0.3.an8.x86_64
libcom_err-devel-1.46.0-1.0.1.an8.x86_64
libgcrypt-devel-1.8.5-7.0.1.an8_6.x86_64
libgpg-error-devel-1.42-5.0.1.an8.x86_64
libkadm5-1.18.2-22.0.1.an8_7.x86_64
libmpc-1.1.0-9.1.an8.x86_64
libselinux-devel-2.9-6.an8.x86_64
libsepol-devel-2.9-3.0.1.an8.x86_64
libstdc++-devel-8.5.0-10.1.0.3.an8.x86_64
libubsan-8.5.0-10.1.0.3.an8.x86_64
libverto-devel-0.3.2-2.an8.x86_64
libxcrypt-devel-4.1.1-6.0.3.an8.x86_64
libxml2-devel-2.9.7-15.0.1.an8.1.x86_64
libxslt-devel-1.1.32-6.0.1.an8.x86_64
m4-1.4.18-7.0.1.an8.x86_64
make-1:4.2.1-11.0.1.an8.x86_64
ncurses-c++-libs-6.1-9.20180224.0.1.an8.x86_64
ncurses-devel-6.1-9.20180224.0.1.an8.x86_64
ocaml-srpm-macros-5-4.el8.noarch
openblas-srpm-macros-2-2.0.1.an8.noarch
openssl-devel-1:1.1.1k-7.0.2.an8.x86_64
pam-devel-1.3.1-22.0.1.an8.x86_64
pcre2-devel-10.32-3.0.1.an8_6.x86_64
pcre2-utf16-10.32-3.0.1.an8_6.x86_64
pcre2-utf32-10.32-3.0.1.an8_6.x86_64
perl-4:5.26.3-421.0.1.an8.x86_64
perl-Algorithm-Diff-1.1903-9.0.1.an8.noarch
perl-Archive-Tar-2.30-1.0.1.an8.noarch
perl-Archive-Zip-1.60-3.el8.noarch
perl-Attribute-Handlers-0.99-421.0.1.an8.noarch
perl-B-Debug-1.26-2.0.1.an8.noarch
perl-CPAN-2.18-397.0.1.an8.noarch
perl-CPAN-Meta-2.150010-396.0.1.an8.noarch
perl-CPAN-Meta-Requirements-2.140-396.0.1.an8.noarch
perl-CPAN-Meta-YAML-0.018-397.0.1.an8.noarch
perl-Compress-Bzip2-2.26-6.0.1.an8.x86_64
perl-Compress-Raw-Bzip2-2.081-1.0.1.an8.x86_64
perl-Compress-Raw-Zlib-2.081-1.0.2.an8.x86_64
perl-Config-Perl-V-0.30-1.0.1.an8.noarch
perl-DB_File-1.842-1.0.1.an8.x86_64
perl-Data-Dumper-2.167-399.0.2.an8.x86_64
perl-Data-OptList-0.110-6.0.1.an8.noarch
perl-Data-Section-0.200007-3.el8.noarch
perl-Devel-PPPort-3.36-5.0.1.an8.x86_64
perl-Devel-Peek-1.26-421.0.1.an8.x86_64
perl-Devel-SelfStubber-1.06-421.0.1.an8.noarch
perl-Devel-Size-0.81-2.0.1.an8.x86_64
perl-Digest-1.17-395.0.1.an8.noarch
perl-Digest-MD5-2.55-396.0.1.an8.x86_64
perl-Digest-SHA-1:6.02-1.an8.x86_64
perl-Encode-4:2.97-3.0.1.an8.x86_64
perl-Encode-Locale-1.05-10.module+an8.7.0+10932+615184cb.noarch
perl-Encode-devel-4:2.97-3.0.1.an8.x86_64
perl-Env-1.04-395.0.1.an8.noarch
perl-ExtUtils-CBuilder-1:0.280230-2.0.1.an8.noarch
perl-ExtUtils-CBuilder-doc-1:0.280230-2.0.1.an8.noarch
perl-ExtUtils-Command-1:7.34-1.0.1.an8.noarch
perl-ExtUtils-Embed-1.34-421.0.1.an8.noarch
perl-ExtUtils-Install-2.14-4.0.1.an8.noarch
perl-ExtUtils-Install-doc-2.14-4.0.1.an8.noarch
perl-ExtUtils-MM-Utils-1:7.34-1.0.1.an8.noarch
perl-ExtUtils-MakeMaker-1:7.34-1.0.1.an8.noarch
perl-ExtUtils-MakeMaker-doc-1:7.34-1.0.1.an8.noarch
perl-ExtUtils-Manifest-1.70-395.0.1.an8.noarch
perl-ExtUtils-Manifest-doc-1.70-395.0.1.an8.noarch
perl-ExtUtils-Miniperl-1.06-421.0.1.an8.noarch
perl-ExtUtils-ParseXS-1:3.35-2.0.1.an8.noarch
perl-ExtUtils-ParseXS-doc-1:3.35-2.0.1.an8.noarch
perl-File-Fetch-0.56-2.0.1.an8.noarch
perl-File-HomeDir-1.002-4.0.1.an8.noarch
perl-File-Temp-0.230.600-1.0.1.an8.noarch
perl-File-Which-1.22-2.0.1.an8.noarch
perl-Filter-2:1.58-2.0.1.an8.x86_64
perl-Filter-Simple-0.94-2.0.1.an8.noarch
perl-Getopt-Long-1:2.50-4.0.1.an8.noarch
perl-HTTP-Tiny-0.074-1.0.1.an8.noarch
perl-IO-Compress-2.081-1.0.1.an8.noarch
perl-IO-Socket-IP-0.39-5.0.1.an8.noarch
perl-IO-Socket-SSL-2.066-4.module+an8.7.0+10903+63bf0065.noarch
perl-IO-Zlib-1:1.10-421.0.1.an8.noarch
perl-IPC-Cmd-2:1.02-1.0.1.an8.noarch
perl-IPC-SysV-2.07-397.0.1.an8.x86_64
perl-IPC-System-Simple-1.25-17.0.1.an8.noarch
perl-JSON-PP-1:2.97.001-3.0.1.an8.noarch
perl-Locale-Codes-3.57-1.0.1.an8.noarch
perl-Locale-Maketext-1.28-396.0.1.an8.noarch
perl-Locale-Maketext-Simple-1:0.21-421.0.1.an8.noarch
perl-MIME-Base64-3.15-396.0.1.an8.x86_64
perl-MRO-Compat-0.13-4.0.1.an8.noarch
perl-Math-BigInt-1:1.9998.11-7.0.1.an8.noarch
perl-Math-BigInt-FastCalc-0.500.600-6.0.1.an8.x86_64
perl-Math-BigRat-0.2614-1.0.1.an8.noarch
perl-Math-Complex-1.59-421.0.1.an8.noarch
perl-Memoize-1.03-421.0.1.an8.noarch
perl-Module-Build-2:0.42.24-5.0.1.an8.noarch
perl-Module-CoreList-1:5.20181130-1.0.1.an8.noarch
perl-Module-CoreList-tools-1:5.20181130-1.0.1.an8.noarch
perl-Module-Load-1:0.32-395.0.1.an8.noarch
perl-Module-Load-Conditional-0.68-395.0.1.an8.noarch
perl-Module-Loaded-1:0.08-421.0.1.an8.noarch
perl-Module-Metadata-1.000033-395.0.1.an8.noarch
perl-Mozilla-CA-20160104-7.module+an8.7.0+10932+615184cb.noarch
perl-Net-Ping-2.55-421.0.1.an8.noarch
perl-Net-SSLeay-1.88-2.module+an8.7.0+10903+63bf0065.x86_64
perl-Package-Generator-1.106-11.0.1.an8.noarch
perl-Params-Check-1:0.38-395.0.1.an8.noarch
perl-Params-Util-1.07-22.0.1.an8.x86_64
perl-Perl-OSType-1.010-396.0.1.an8.noarch
perl-PerlIO-via-QuotedPrint-0.08-395.0.1.an8.noarch
perl-Pod-Checker-4:1.73-395.0.1.an8.noarch
perl-Pod-Escapes-1:1.07-395.0.1.an8.noarch
perl-Pod-Html-1.22.02-421.0.1.an8.noarch
perl-Pod-Parser-1.63-396.0.1.an8.noarch
perl-Pod-Perldoc-3.28-396.0.1.an8.noarch
perl-Pod-Simple-1:3.35-395.0.1.an8.noarch
perl-Pod-Usage-4:1.69-395.0.1.an8.noarch
perl-SelfLoader-1.23-421.0.1.an8.noarch
perl-Software-License-0.103013-2.0.1.an8.noarch
perl-Storable-1:3.11-3.0.1.an8.x86_64
perl-Sub-Exporter-0.987-15.0.1.an8.noarch
perl-Sub-Install-0.928-14.0.1.an8.noarch
perl-Sys-Syslog-0.35-397.0.1.an8.x86_64
perl-Term-ANSIColor-4.06-396.0.1.an8.noarch
perl-Term-Cap-1.17-395.0.1.an8.noarch
perl-TermReadKey-2.37-7.0.1.an8.x86_64
perl-Test-1.30-421.0.1.an8.noarch
perl-Test-Harness-1:3.42-1.0.1.an8.noarch
perl-Test-Simple-1:1.302135-1.0.1.an8.noarch
perl-Text-Balanced-2.03-395.0.1.an8.noarch
perl-Text-Diff-1.45-2.0.1.an8.noarch
perl-Text-Glob-0.11-4.0.1.an8.noarch
perl-Text-ParseWords-3.30-395.0.1.an8.noarch
perl-Text-Template-1.51-1.0.1.an8.noarch
perl-Thread-Queue-3.13-1.0.1.an8.noarch
perl-Time-HiRes-4:1.9758-2.0.1.an8.x86_64
perl-Time-Local-1:1.280-1.0.1.an8.noarch
perl-Time-Piece-1.31-421.0.1.an8.x86_64
perl-URI-1.73-3.el8.noarch
perl-Unicode-Collate-1.25-2.0.1.an8.x86_64
perl-autodie-2.29-396.0.1.an8.noarch
perl-bignum-0.49-2.0.1.an8.noarch
perl-devel-4:5.26.3-421.0.1.an8.x86_64
perl-encoding-4:2.22-3.0.1.an8.x86_64
perl-experimental-0.019-2.0.1.an8.noarch
perl-inc-latest-2:0.500-9.0.1.an8.noarch
perl-libnet-3.11-3.0.1.an8.noarch
perl-libnetcfg-4:5.26.3-421.0.1.an8.noarch
perl-local-lib-2.000024-2.0.1.an8.noarch
perl-open-1.11-421.0.1.an8.noarch
perl-perlfaq-5.20180605-1.0.1.an8.noarch
perl-podlators-4.11-1.0.1.an8.noarch
perl-srpm-macros-1-25.el8.noarch
perl-utils-5.26.3-421.0.1.an8.noarch
perl-version-6:0.99.24-1.0.2.an8.x86_64
python-rpm-macros-3-43.an8.noarch
python-srpm-macros-3-43.an8.noarch
python3-pyparsing-2.1.10-7.el8.noarch
python3-rpm-macros-3-43.an8.noarch
qt5-srpm-macros-5.15.2-1.an8.noarch
readline-devel-7.0-10.0.1.an8.x86_64
rust-srpm-macros-5-2.0.1.an8.noarch
system-rpm-config-129-1.0.2.an8.noarch
systemtap-sdt-devel-4.6-4.an8.x86_64
tcl-1:8.6.8-2.el8.x86_64
tcl-devel-1:8.6.8-2.el8.x86_64
uuid-1.6.2-43.0.1.an8.x86_64
uuid-devel-1.6.2-43.0.1.an8.x86_64
xz-devel-5.2.4-4.an8_6.x86_64
zlib-devel-1.2.11-19.0.1.an8_6.x86_64
完毕!
修改主机名和host文件
[root@pg ~]# cat /etc/hostname
pg
[root@pg ~]# hostname
pg
[root@pg ~]# cat>>/etc/hosts<<EOF
> 192.168.109.101 pg
> EOF
[root@pg ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.109.101 pg
修改内核参数
[root@pg ~]# vim /etc/sysctl.conf
[root@pg ~]# cat /etc/sysctl.conf
# sysctl settings are defined through files in
# /usr/lib/sysctl.d/, /run/sysctl.d/, and /etc/sysctl.d/.
#
# Vendors settings live in /usr/lib/sysctl.d/.
# To override a whole file, create a new file with the same in
# /etc/sysctl.d/ and put new settings there. To override
# only specific settings, add a file with a lexically later
# name in /etc/sysctl.d/ and put new settings there.
#
# For more information, see sysctl.conf(5) and sysctl.d(5).
#
fs.file-max = 76724200
kernel.sem = 10000 10240000 10000 1024
kernel.shmmni = 4096
kernel.shmall = 253702
kernel.shmmax = 1039163392
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 1048576
fs.aio-max-nr = 40960000
vm.dirty_ratio=20
vm.dirty_background_ratio=3
vm.dirty_writeback_centisecs=100
vm.dirty_expire_centisecs=500
vm.swappiness=10
vm.min_free_kbytes=524288
vm.swappiness=0
vm.overcommit_memory=2
vm.overcommit_ratio=75
net.ipv4.ip_local_port_range = 10000 65535
使参数生效
[root@pg ~]# sysctl -p
fs.file-max = 76724200
kernel.sem = 10000 10240000 10000 1024
kernel.shmmni = 4096
kernel.shmall = 253702
kernel.shmmax = 1039163392
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 1048576
fs.aio-max-nr = 40960000
vm.dirty_ratio = 20
vm.dirty_background_ratio = 3
vm.dirty_writeback_centisecs = 100
vm.dirty_expire_centisecs = 500
vm.swappiness = 10
vm.min_free_kbytes = 524288
vm.swappiness = 0
vm.overcommit_memory = 2
vm.overcommit_ratio = 75
net.ipv4.ip_local_port_range = 10000 65535
确认时区
[root@pg ~]# timedatectl status
Local time: 五 2023-03-17 02:13:22 CST
Universal time: 四 2023-03-16 18:13:22 UTC
RTC time: 四 2023-03-16 18:13:22
Time zone: Asia/Shanghai (CST, +0800)
System clock synchronized: no
NTP service: active
RTC in local TZ: no
创建postgres用户名和用户组
[root@pg ~]# userdel -r postgres
userdel:用户“postgres”不存在
[root@pg ~]# groupdel dba
groupdel:“dba”组不存在
[root@pg ~]# groupadd dba -g 2000
[root@pg ~]# useradd postgres -g 2000
[root@pg ~]# id postgres
uid=1000(postgres) gid=2000(dba) 组=2000(dba)
[root@pg ~]# echo "postgres123" | passwd --stdin postgres
更改用户 postgres 的密码 。
passwd:所有的身份验证令牌已经成功更新。
创建数据库目录并授权
[root@pg ~]# mkdir /soft
[root@pg ~]# mkdir -p /opt/pg
[root@pg ~]# mkdir -p /opt/pgdata
[root@pg ~]# mkdir -p /opt/pgwal
[root@pg ~]# mkdir -p /opt/pgarchive
[root@pg ~]# chown -R postgres:dba /soft /opt/pg /opt/pgdata /opt/pgwal /opt/pgarchive
[root@pg ~]# chmod 0700 /opt/pgdata /opt/pgwal /opt/pgarchive
[root@pg ~]# ll /opt
总用量 0
drwxr-xr-x. 2 postgres dba 6 3月 17 02:17 pg
drwx------. 2 postgres dba 6 3月 17 02:17 pgarchive
drwx------. 2 postgres dba 6 3月 17 02:17 pgdata
drwx------. 2 postgres dba 6 3月 17 02:17 pgwal
修改安全限制
[root@pg ~]# vim /etc/security/limits.conf
[root@pg ~]# cat /etc/security/limits.conf
# /etc/security/limits.conf
# End of file
postgres soft nofile 1048576
postgres hard nofile 1048576
postgres soft nproc 131072
postgres hard nproc 131072
postgres soft stack 10240
postgres hard stack 32768
postgres soft core 6291456
postgres hard core 6291456
获取源码并解压缩
[root@pg ~]# su - postgres
[postgres@pg ~]$ cd /soft
[postgres@pg soft]$ wget https://ftp.postgresql.org/pub/source/v15.2/postgresql-15.2.tar.gz
--2023-03-17 02:24:20-- https://ftp.postgresql.org/pub/source/v15.2/postgresql-15.2.tar.gz
正在解析主机 ftp.postgresql.org (ftp.postgresql.org)... 217.196.149.55, 87.238.57.227, 147.75.85.69, ...
正在连接 ftp.postgresql.org (ftp.postgresql.org)|217.196.149.55|:443... 已连接。
已发出 HTTP 请求,正在等待回应... 200 OK
长度:29811750 (28M) [application/octet-stream]
正在保存至: “postgresql-15.2.tar.gz”
postgresql-15.2.tar.gz 100%[=======================================================>] 28.43M 8.04MB/s 用时 4.7s
2023-03-17 02:24:27 (6.02 MB/s) - 已保存 “postgresql-15.2.tar.gz” [29811750/29811750])
[postgres@pg soft]$ tar -zxvf postgresql-15.2.tar.gz
postgresql-15.2/.cirrus.yml
postgresql-15.2/HISTORY
postgresql-15.2/Makefile
postgresql-15.2/README
postgresql-15.2/COPYRIGHT
postgresql-15.2/GNUmakefile.in
postgresql-15.2/.gitattributes
postgresql-15.2/aclocal.m4
postgresql-15.2/INSTALL
源码编译
[postgres@pg soft]$ cd postgresql-15.2/
[postgres@pg postgresql-15.2]$ ./configure --prefix=/opt/pg12 --with-pgport=5432
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
[postgres@pg postgresql-15.2]$ gmake world
gmake[2]: 离开目录“/soft/postgresql-15.2/contrib/unaccent”
gmake -C vacuumlo all
gmake[2]: 进入目录“/soft/postgresql-15.2/contrib/vacuumlo”
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -I../../src/interfaces/libpq -I. -I. -I../../src/include -D_GNU_SOURCE -c -o vacuumlo.o vacuumlo.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 vacuumlo.o -L../../src/common -lpgcommon -L../../src/port -lpgport -L../../src/interfaces/libpq -lpq -L../../src/port -L../../src/common -Wl,--as-needed -Wl,-rpath,'/opt/pg12/lib',--enable-new-dtags -lpgcommon -lpgport -lz -lreadline -lpthread -lrt -ldl -lm -o vacuumlo
gmake[2]: 离开目录“/soft/postgresql-15.2/contrib/vacuumlo”
gmake[1]: 离开目录“/soft/postgresql-15.2/contrib”
[postgres@pg postgresql-15.2]$ gmake install-world
gmake[2]: 离开目录“/soft/postgresql-15.2/contrib/unaccent”
gmake -C vacuumlo install
gmake[2]: 进入目录“/soft/postgresql-15.2/contrib/vacuumlo”
/usr/bin/mkdir -p '/opt/pg12/bin'
/usr/bin/install -c vacuumlo '/opt/pg12/bin'
gmake[2]: 离开目录“/soft/postgresql-15.2/contrib/vacuumlo”
gmake[1]: 离开目录“/soft/postgresql-15.2/contrib”
确认版本
[postgres@pg postgresql-15.2]$ /opt/pg12/bin/postgres --version
postgres (PostgreSQL) 15.2
初始化数据库
链接目录
[postgres@pg postgresql-15.2]$ cd /opt
[postgres@pg opt]$ ln -s /opt/pg12 /opt/pgsql
[postgres@pg opt]$ ll
总用量 0
drwxr-xr-x. 6 postgres dba 56 3月 17 02:45 pg12
drwx------. 2 postgres dba 6 3月 17 02:17 pgarchive
drwx------. 2 postgres dba 6 3月 17 02:17 pgdata
lrwxrwxrwx. 1 postgres dba 9 3月 17 02:46 pgsql -> /opt/pg12
drwx------. 2 postgres dba 6 3月 17 02:17 pgwal
初始化数据库
[postgres@pg opt]$ /opt/pgsql/bin/initdb -D/opt/pgdata -X/opt/pgwal -EUTF8 -Upostgres -W
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 "zh_CN.UTF-8".
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
The default text search configuration will be set to "simple".
Data page checksums are disabled.
Enter new superuser password:
Enter it again:
fixing permissions on existing directory /opt/pgdata ... ok
fixing permissions on existing directory /opt/pgwal ... 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
initdb: hint: 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:
/opt/pgsql/bin/pg_ctl -D /opt/pgdata -l logfile start
修改参数
[root@pg ~]# vim /opt/pgdata/postgresql.conf
检查数据库状态
[root@pg ~]# /opt/pgsql/bin/pg_ctl -D /opt/pgdata status
pg_ctl: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.
[root@pg ~]# su - postgres
[postgres@pg ~]$ /opt/pgsql/bin/pg_ctl -D /opt/pgdata status
pg_ctl: no server running
启动数据库
启动服务
[postgres@pg ~]$ /opt/pgsql/bin/pg_ctl -D /opt/pgdata start &
[1] 52120
[postgres@pg ~]$ waiting for server to start....2023-03-17 15:15:38.975 CST [52122] LOG: redirecting log output to logging collector process
2023-03-17 15:15:38.975 CST [52122] HINT: Future log output will appear in directory "log".
done
server started
[1]+ 已完成 /opt/pgsql/bin/pg_ctl -D /opt/pgdata start
检查状态
[postgres@pg ~]$ /opt/pgsql/bin/pg_ctl -D /opt/pgdata status
pg_ctl: server is running (PID: 52122)
/opt/pg12/bin/postgres "-D" "/opt/pgdata"
检查进程
[postgres@pg ~]$ ps -ef | grep postgres
root 52074 50089 0 15:13 pts/0 00:00:00 su - postgres
postgres 52075 52074 0 15:13 pts/0 00:00:00 -bash
postgres 52122 1 0 15:15 ? 00:00:00 /opt/pg12/bin/postgres -D /opt/pgdata
postgres 52123 52122 0 15:15 ? 00:00:00 postgres: logger
postgres 52124 52122 0 15:15 ? 00:00:00 postgres: checkpointer
postgres 52125 52122 0 15:15 ? 00:00:00 postgres: background writer
postgres 52127 52122 0 15:15 ? 00:00:00 postgres: walwriter
postgres 52128 52122 0 15:15 ? 00:00:00 postgres: autovacuum launcher
postgres 52129 52122 0 15:15 ? 00:00:00 postgres: archiver
postgres 52130 52122 0 15:15 ? 00:00:00 postgres: logical replication launcher
postgres 52140 52075 0 15:16 pts/0 00:00:00 ps -ef
postgres 52141 52075 0 15:16 pts/0 00:00:00 grep --color=auto postgres
登录数据库
[postgres@pg ~]$ /opt/pgsql/bin/psql
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: 没有那个文件或目录
Is the server running locally and accepting connections on that socket?
[postgres@pg ~]$ /opt/pgsql/bin/psql -U postgres -h localhost
psql (15.2)
Type "help" for help.
postgres=# \?
General
\copyright show PostgreSQL usage and distribution terms
\crosstabview [COLUMNS] execute query and display result in crosstab
\errverbose show most recent error message at maximum verbosity
\g [(OPTIONS)] [FILE] execute query (and send result to file or |pipe);
\g with no arguments is equivalent to a semicolon
\gdesc describe result of query, without executing it
\gexec execute query, then execute each value in its result
\gset [PREFIX] execute query and store result in psql variables
\gx [(OPTIONS)] [FILE] as \g, but forces expanded output mode
\q quit psql
\watch [SEC] execute query every SEC seconds
Help
\? [commands] show help on backslash commands
\? options show help on psql command-line options
\? variables show help on special variables
\h [NAME] help on syntax of SQL commands, * for all commands
Query Buffer
\e [FILE] [LINE] edit the query buffer (or file) with external editor
\ef [FUNCNAME [LINE]] edit function definition with external editor
\ev [VIEWNAME [LINE]] edit view definition with external editor
\p show the contents of the query buffer
\r reset (clear) the query buffer
\s [FILE] display history or save it to file
\w FILE write query buffer to file
Input/Output
\copy ... perform SQL COPY with data stream to the client host
\echo [-n] [STRING] write string to standard output (-n for no newline)
\i FILE execute commands from file
\ir FILE as \i, but relative to location of current script
\o [FILE] send all query results to file or |pipe
\qecho [-n] [STRING] write string to \o output stream (-n for no newline)
\warn [-n] [STRING] write string to standard error (-n for no newline)
Conditional
\if EXPR begin conditional block
\elif EXPR alternative within current conditional block
\else final alternative within current conditional block
\endif end conditional block
Informational
(options: S = show system objects, + = additional detail)
\d[S+] list tables, views, and sequences
\d[S+] NAME describe table, view, sequence, or index
\da[S] [PATTERN] list aggregates
\dA[+] [PATTERN] list access methods
\dAc[+] [AMPTRN [TYPEPTRN]] list operator classes
\dAf[+] [AMPTRN [TYPEPTRN]] list operator families
\dAo[+] [AMPTRN [OPFPTRN]] list operators of operator families
\dAp[+] [AMPTRN [OPFPTRN]] list support functions of operator families
\db[+] [PATTERN] list tablespaces
\dc[S+] [PATTERN] list conversions
\dconfig[+] [PATTERN] list configuration parameters
\dC[+] [PATTERN] list casts
\dd[S] [PATTERN] show object descriptions not displayed elsewhere
\dD[S+] [PATTERN] list domains
\ddp [PATTERN] list default privileges
\dE[S+] [PATTERN] list foreign tables
\des[+] [PATTERN] list foreign servers
\det[+] [PATTERN] list foreign tables
\deu[+] [PATTERN] list user mappings
\dew[+] [PATTERN] list foreign-data wrappers
\df[anptw][S+] [FUNCPTRN [TYPEPTRN ...]]
list [only agg/normal/procedure/trigger/window] functions
\dF[+] [PATTERN] list text search configurations
\dFd[+] [PATTERN] list text search dictionaries
\dFp[+] [PATTERN] list text search parsers
\dFt[+] [PATTERN] list text search templates
\dg[S+] [PATTERN] list roles
\di[S+] [PATTERN] list indexes
\dl[+] list large objects, same as \lo_list
\dL[S+] [PATTERN] list procedural languages
\dm[S+] [PATTERN] list materialized views
\dn[S+] [PATTERN] list schemas
\do[S+] [OPPTRN [TYPEPTRN [TYPEPTRN]]]
list operators
\dO[S+] [PATTERN] list collations
\dp [PATTERN] list table, view, and sequence access privileges
\dP[itn+] [PATTERN] list [only index/table] partitioned relations [n=nested]
\drds [ROLEPTRN [DBPTRN]] list per-database role settings
\dRp[+] [PATTERN] list replication publications
\dRs[+] [PATTERN] list replication subscriptions
\ds[S+] [PATTERN] list sequences
\dt[S+] [PATTERN] list tables
\dT[S+] [PATTERN] list data types
\du[S+] [PATTERN] list roles
\dv[S+] [PATTERN] list views
\dx[+] [PATTERN] list extensions
\dX [PATTERN] list extended statistics
\dy[+] [PATTERN] list event triggers
\l[+] [PATTERN] list databases
\sf[+] FUNCNAME show a function's definition
\sv[+] VIEWNAME show a view's definition
\z [PATTERN] same as \dp
Large Objects
\lo_export LOBOID FILE write large object to file
\lo_import FILE [COMMENT]
read large object from file
\lo_list[+] list large objects
\lo_unlink LOBOID delete a large object
Formatting
\a toggle between unaligned and aligned output mode
\C [STRING] set table title, or unset if none
\f [STRING] show or set field separator for unaligned query output
\H toggle HTML output mode (currently off)
\pset [NAME [VALUE]] set table output option
(border|columns|csv_fieldsep|expanded|fieldsep|
fieldsep_zero|footer|format|linestyle|null|
numericlocale|pager|pager_min_lines|recordsep|
recordsep_zero|tableattr|title|tuples_only|
unicode_border_linestyle|unicode_column_linestyle|
unicode_header_linestyle)
\t [on|off] show only rows (currently off)
\T [STRING] set HTML <table> tag attributes, or unset if none
\x [on|off|auto] toggle expanded output (currently off)
Connection
\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
connect to new database (currently "postgres")
\conninfo display information about current connection
\encoding [ENCODING] show or set client encoding
\password [USERNAME] securely change the password for a user
Operating System
\cd [DIR] change the current working directory
\getenv PSQLVAR ENVVAR fetch environment variable
\setenv NAME [VALUE] set or unset environment variable
\timing [on|off] toggle timing of commands (currently off)
\! [COMMAND] execute command in shell or start interactive shell
Variables
\prompt [TEXT] NAME prompt user to set internal variable
\set [NAME [VALUE]] set internal variable, or list all if no parameters
\unset NAME unset (delete) internal variable
查看数据库和版本
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | libc |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(3 rows)
postgres=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# \d
Did not find any relations.
postgres=# select version();
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Anolis 8.5.0-10.0.3), 64-bit
(1 row)
postgres=# \q
[postgres@pg ~]$
关闭数据库
[postgres@pg ~]$ /opt/pgsql/bin/pg_ctl -D /opt/pgdata stop
waiting for server to shut down.... done
server stopped
[postgres@pg ~]$ /opt/pgsql/bin/pg_ctl -D /opt/pgdata status
pg_ctl: no server running
[postgres@pg ~]$ ps -ef | grep postgres
root 52074 50089 0 15:13 pts/0 00:00:00 su - postgres
postgres 52075 52074 0 15:13 pts/0 00:00:00 -bash
postgres 52276 52075 0 15:27 pts/0 00:00:00 ps -ef
postgres 52277 52075 0 15:27 pts/0 00:00:00 grep --color=auto postgres
[postgres@pg ~]$