postgresql-15.1源码安装
###################
安装依赖包:
yum groupinstall "Development tools" yum -y install gcc gcc-c++ make flex bison perl-ExtUtils-Embed perl-ExtUtils-MakeMaker 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 libicu libicu-devel openldap openldap-devel python python-devel yum -y install tcl-devel uuid-devel systemd-devel net-tools
mkdir /home/work/cdrom/postgresql_5432
ln -s /home/work/cdrom/postgresql_5432 /home/work/postgresql_5432
tar -xzvf postgresql-15.1.tar.gz cd postgresql-15.1
预编译./configure:生成Makefile文件
[root@a8-dba-cloud-db00.wh postgresql-15.1]# ./configure --prefix=/home/work/postgresql_5432 --exec-prefix=/home/work/postgresql_5432 --bindir=/home/work/postgresql_5432/bin --sysconfdir=/home/work/postgresql_5432/etc --libdir=/home/work/postgresql_5432/lib --includedir=/home/work/postgresql_5432/include --mandir=/home/work/postgresql_5432/man --docdir=/home/work/postgresql_5432/doc --htmldir=/home/work/postgresql_5432/html --enable-nls='zh_CN en_US' --with-pgport=5432 --with-perl --with-gssapi --with-pam --with-icu --with-tcl --with-ldap --with-systemd --with-readline --with-libxml --with-libxslt --with-openssl --with-segsize=1 --with-blocksize=8 --with-wal-blocksize=16 --with-wal-blocksize=8
通过configure的--prefix
选项指定数据库的安装路径,默认情况下,安装到/usr/local/pgsql目录。
[root@a8-dba-cloud-db00.wh postgresql-15.1]# ./configure --help `configure' configures PostgreSQL 15.1 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-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-lz4 build with LZ4 support --with-zstd build with ZSTD support --with-gnu-ld assume the C compiler uses GNU ld [default=no] --with-ssl=LIB use LIB for SSL/TLS support (openssl) --with-openssl obsolete spelling of --with-ssl=openssl 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 LZ4_CFLAGS C compiler flags for LZ4, overriding pkg-config LZ4_LIBS linker flags for LZ4, overriding pkg-config ZSTD_CFLAGS C compiler flags for ZSTD, overriding pkg-config ZSTD_LIBS linker flags for ZSTD, 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/>. [root@a8-dba-cloud-db00.wh postgresql-15.1]#
./configure编译参数详解: --prefix 指定安装目录。 --bindir 指定可执行程序的目录 --sysconfdir 默认设置各种配置文件的目录。 --libdir 设置安装库和动态可加载模块的位置。 --includedir 设置安装C和C ++头文件的目录 --datarootdir 设置各种类型的只读数据文件的根目录。share目录。 --datadir 设置已安装程序使用的只读数据文件的目录。默认值为DATAROOTDIR。 --localedir 设置用于安装区域设置数据的目录。默认值为DATAROOTDIR/locale。 --mandir PostgreSQL附带的手册页将安装在这个目录下. man目录。 --docdir 设置安装文档文件的根目录。 --htmldir PostgreSQL的HTML格式文档将安装在此目录下。默认值为DATAROOTDIR。 --with-extra-version = STRING 将STRING附加到PostgreSQL版本号 --with-libraries 用于搜索库。 --enable-nls 启用本机语言支持(NLS),即以英语以外的语言显示程序消息的功能。 --with-pgport 设置为服务器和客户端的默认端口号。默认值为5432。 --with-perl 支持perl语言。 --with-python 支持python语言。 --with-TCL 支持PL/Tcl语言。 --with-tclconfig 包含构建与Tcl接口的模块所需的配置信息。 --with-GSSAPI 构建支持GSSAPI身份验证。 --with-openssl 构建支持SSL(加密)连接 --with-pam 使用PAM(可插入身份验证模块)支持构建. --with-ldap 使用LDAP支持构建身份验证和连接参数查找. --without-readline 禁用psql中的命令行编辑和历史记录 . --with-libedit-preferred 有利于使用BSD许可的libedit库.默认使用Readline。 --with-bonjour 使用Bonjour支持构建 --with-uuid=LIBRARY 使用指定的UUID库构建uuid-ossp模块. --with-libxml 使用libxml构建(启用SQL/XML支持). --with-libxslt 在构建xml2 模块时使用libxslt 。xml2依赖于此库来执行XML的XSL转换。 --disable-integer-datetimes 禁用对时间戳和间隔的64位整数存储的支持,并将日期时间值存储为浮点数。 --disable-float4-byval 禁用“按值”传递float4值,导致它们“通过引用”传递。此选项会降低性能. --disable-float8-byval 禁用“按值”传递float8值,导致它们“通过引用”传递。此选项会降低性能. --with-segsize=SEGSIZE 设置段大小,以千兆字节为单位。大表分为多个操作系统文件,每个文件的大小等于段大小 --with-blocksize=BLOCKSIZE 设置块大小,以千字节为单位。这是表中的存储单元和I / O. 默认值为8千字节 --with-wal-segsize=SEGSIZE 设置WAL段大小,以兆字节为单位。这是WAL日志中每个文件的大小。 调整此大小以控制WAL日志传送的粒度可能很有用。默认大小为16兆字节。该值必须是1到64(兆字节)之间的2的幂. --with-wal-blocksize=BLOCKSIZE 设置WAL块大小,以千字节为单位。这是WAL日志中的存储和I / O单位。默认值为8千字节.该值必须是1到64(兆字节)之间的2的幂. --disable-spinlocks 禁用环形锁。 --disable-thread-safety 禁用线程安全,禁用客户端库的线程安全性。 --with-system-tzdata=DIRECTORY PostgreSQL包含自己的时区数据库,它需要日期和时间操作.默认与linux时区兼容。 --without-zlib 防止使用Zlib 库。 --enable-debug 使用调试符号编译所有程序和库。 --enable-depend 启用自动依赖关系跟踪。 --enable-dtrace 编译PostgreSQL,支持动态跟踪工具DTrace。 --enable-tap-tests 使用Perl TAP工具启用测试。
安装路径相关选项 该选项控制make install命令安装文件路径。大部分情况下,均可使用--prefix选项。 --prefix=PREFIX 将所有文件放置到PREFIX指定的路径下(而非默认的/usr/local/pgsql)。 --exec-prefix=EXEC-PREFIX 将体系结构相关的文件放置到与PREFIX不同的路径下。 --bindir=DIRECTORY 可执行文件路径。默认为EXEC-PREFIX/bin,即默认为/usr/local/pgsql/bin。 --sysconfdir=DIRECTORY 配置文件路径。默认为PREFIX/etc。 --libdir=DIRECTORY 设置库及动态加载模块安装路径。默认为EXECPREFIX/lib --includedir=DIRECTORY C和C++头文件所在路径,默认为PREFIX/include。 --datarootdir=DIRECTORY 只读数据文件的根目录。默认为PREFIX/share --localedir=DIRECTORY 本地数据,特别是信息翻译库文件的存放路径。默认为DATAROOTDIR/locale --mandir=DIRECTORY PostgreSQL的主页面所在路径。默认为DATAROOTDIR/man --docdir=DIRECTORY 文档文件的路径。默认为DATAROOTDIR/doc/postgresql。 --htmldir=DIRECTORY PostgreSQL的HTML格式文档的安装路径。默认为DATAROOTDIR。 PostgreSQL特性相关选项 本节介绍创建PostgreSQL非默认特性的选项。之所以这些特性为非默认的,是因为它们依赖于其他的软件。 --enable-nls[=LANGUAGES] 启用本地语言支持(NLS)。若不指定LANGUAGE,则会安装所有可用的语言。需要Gettext API。 --with-perl 创建PL/Perl服务端语言。 --with-python 创建PL/Python服务端语言。 --with-tcl 创建PL/Tcl服务端语言。 --with-tclconfig=DIRECTORY tcl配置文件tclConfig.sh所在路径。 --with-icu 支持ICU(International Component for Unicode)库。需要安装ICU4C包,版本至少为4.2。 --with-llvm 构建基于JIT的LLVM支持。需要安装LLVM库,最低版本为3.9。 --with-openssl 支持SSL(加密)连接。需要安装OpenSSL。 --with-gssapi 支持GSSAPI加密。 --with-ldap 支持LDAP认证及连接参数查看。unix下需要安装OpenLDAP包,windows下,需要安装WinLDAP包。 --with-pam 支持PAM(Pluggable Authentication Modules,可拔插认证模块)。 --with-bsd-auth 支持BSD认证。 --with-systemd 支持systemd服务提醒。需要安装libsystemd及其相关文件。 --with-bonjour 支持Bonjour自动服务发现。 --with-uuid=LIBRARY 创建使用指定UUID库的uuid-ossp模块(提供生成UUID的功能)。LIBRARY需为以下之一: bsd e2fs ossp --with-ossp-uuid 等同--with-uuid=ossp。 --with-libxml 支持SQL/XML。需要安装Libxml2 2.6.23及更新版本。 --with-libxslt 使用libxslt构建,启用xml2模块从而可以从xml到xsl的转换。必须同时指定--withlibxml 关闭PostgreSQL特性的选项 本节介绍关闭某些默认创建的PostgreSQL特性。如非必要,无需执行。 --without-readline 禁用readline。使得无法在命令行psql编辑和使用历史命令。 --with-libedit-preferred 使用BSD-licensed libedit库而非GPL-licensed ReadLine。默认使用ReadLine。 --without-zlib 不使用zlib库。 --disable-spinlocks 即使PostgreSQL不支持平台的CPU自旋锁,也可以构建成功。使用此选项,会导致很严重的数据库性能问题。 --disable-atomics 禁用CPU原子操作。可能导致数据库性能问题。 --disable-thread-safety 禁用客户端库的线程安全。 这样可以防止libpq和ECPG程序中的并发线程安全地控制其专用连接句柄。 仅在线程支持不足的平台上使用此功能。 构建过程详情选项 --with-includes=DIRECTORIES DIRECTORIES为冒号分隔的列表,编译器从该路径中检索头文件。如果包装在了非默认路径,则需要指定该参数。 示例:--with-includes=/opt/gnu/include:/usr/sup/include --with-libraries=DIRECTORIES DIRECTORIES为冒号分隔的列表,内为库文件。 示例:--with-libraries=/opt/gnu/lib:/usr/sup/lib --with-system-tzdata=DIRECTORY 使用外部的时区数据库。 --with-extra-version=STRING 添加STRING到PostgreSQL版本号上。 --disable-rpath 不要标记PostgreSQL的可执行文件以指示它们应该在安装的库目录中搜索共享库(请参见--libdir)。 其他选项,在构建时修改默认端口号--with-pgport。以下参数,限资深用户使用。 --with-pgport=NUMBER 为客户端及服务端设置默认端口号。默认为5432。 --with-krb-srvnam=NAME GSSAPI使用的Kerberos默认名称。默认为postgres。在windows下,必须设置为POSTGRES。 --with-segsize=SEGSIZE 以GB为单位设置segment size。大表被分为segment size大小的多个文件。默认为1GB。不同segment size,不可使用pg_upgrade升级。 --with-blocksize=BLOCKSIZE 以KB为单位设置block size。此为表中存储及I/O的单位。默认为8kb。值必须为2的1到32次方(kb)。不同block size,不可使用pg_upgrade升级。 --with-wal-blocksize=BLOCKSIZE 以kb为单位,设置WAL block size。此为WAL日志中存储及I/O的单位。默认为8kb。值必须为2的1到64次方(kb)。不同wal block size,不可使用pg_upgrade升级。 开发者选项 本节介绍开发及调试PostgreSQL所使用的选项。除了--enable-debug外,其他选项不建议在生产环境使用。对于支持DTrace的操作系统,生产环境也可以启用--enable-dtrace选项。 若构建代码开发服务端,则建议至少启用选项--enable-debug和--enable-cassert。 --enable-debug 以调试模式编译程序及库。即可以在调试模式下运行程序以对问题进行分析。如果使用了GCC编译器,建议启用该选项。 --enable-cassert 在服务端启用断言检查。不建议在生产环境使用该选项。 --enable-tap-tests 启用使用Perl TAP工具进行测试。需要安装Perl及其模块IPC::Run。 --enable-depend 启用自动依赖跟踪。仅支持GCC。 --enable-coverage 如果使用GCC,则所有程序和库都将使用代码覆盖率测试工具进行编译。仅支持GCC。 --enable-profiling 如果使用GCC,则会编译所有程序和库,以便对其进行概要分析。仅支持GCC。 --enable-dtrace 支持动态跟踪工具DTrace。
常见安装报错:
(1)问题1: no configure: error: could not determine flags for linking embedded Perl. This probably means that ExtUtils::Embed or ExtUtils::MakeMaker is not installed. 解决方法: yum install perl-ExtUtils-Embed (2)问题2: configure: error: readline library not found If you have readline already installed, see config.log for details on the failure. It is possible the compiler isn't looking in the proper directory. Use --without-readline to disable readline support. 解决方法: yum install readline readline-devel (3)问题3: checking for inflate in -lz... no configure: error: zlib library not found If you have zlib already installed, see config.log for details on the failure. It is possible the compiler isn't looking in the proper directory. Use --without-zlib to disable zlib support. 解决方法: yum install zlib zlib-devel (4)问题4: checking for CRYPTO_new_ex_data in -lcrypto... no configure: error: library 'crypto' is required for OpenSSL 解决方法: yum install openssl openssl-devel (5)问题5: checking for pam_start in -lpam... no configure: error: library 'pam' is required for PAM 解决方法: yum install pam pam-devel (6)问题6: checking for xmlSaveToBuffer in -lxml2... no configure: error: library 'xml2' (version >= 2.6.23) is required for XML support 解决方法: yum install libxml2 libxml2-devel (7)问题7: checking for xsltCleanupGlobals in -lxslt... no configure: error: library 'xslt' is required for XSLT support 解决方法: yum install libxslt libxslt-devel (8)问题8: configure: error: Tcl shell not found 解决方法: yum install tcl tcl-devel (9)问题9: checking for ldap.h... no configure: error: header file is required for LDAP 解决方法: yum install openldap openldap-devel (10)问题10: checking for Python.h... no configure: error: header file <Python.h> is required for Python 解决方法: yum install python python-devel (11)问题11: Error when bootstrapping CMake: Cannot find appropriate C++ compiler on this system. Please specify one using environment variable CXX. See cmake_bootstrap.log for compilers attempted. 解决方法: yum install gcc-c++
编译:
[root@a8-dba-cloud-db00.wh postgresql-15.1]# gmake world
安装:
[root@a8-dba-cloud-db00.wh postgresql-15.1]# gmake install-world
Makefile文件内容:
[root@a8-dba-cloud-db00.wh postgresql-15.1]# cat Makefile # The PostgreSQL make files exploit features of GNU make that other # makes do not have. Because it is a common mistake for users to try # to build Postgres with a different make, we have this make file # that, as a service, will look for a GNU make and invoke it, or show # an error message if none could be found. # If the user were using GNU make now, this file would not get used # because GNU make uses a make file named "GNUmakefile" in preference # to "Makefile" if it exists. PostgreSQL is shipped with a # "GNUmakefile". If the user hasn't run the configure script yet, the # GNUmakefile won't exist yet, so we catch that case as well. # AIX make defaults to building *every* target of the first rule. Start with # a single-target, empty rule to make the other targets non-default. all: all check install installdirs installcheck installcheck-parallel uninstall clean distclean maintainer-clean dist distcheck world check-world install-world installcheck-world: @if [ ! -f GNUmakefile ] ; then \ if [ -f INSTALL ] ; then \ INSTRUCTIONS="INSTALL"; \ else \ INSTRUCTIONS="README.git"; \ fi; \ echo "You need to run the 'configure' program first. See the file"; \ echo "'$$INSTRUCTIONS' for installation instructions, or visit: " ; \ echo "<https://www.postgresql.org/docs/devel/installation.html>" ; \ false ; \ fi @IFS=':' ; \ for dir in $$PATH; do \ for prog in gmake gnumake make; do \ if [ -f $$dir/$$prog ] && ( $$dir/$$prog -f /dev/null --version 2>/dev/null | grep GNU >/dev/null 2>&1 ) ; then \ GMAKE=$$dir/$$prog; \ break 2; \ fi; \ done; \ done; \ \ if [ x"$${GMAKE+set}" = xset ]; then \ echo "Using GNU make found at $${GMAKE}"; \ unset MAKELEVEL; \ $${GMAKE} $@ ; \ else \ echo "You must use GNU make to build PostgreSQL." ; \ false; \ fi [root@a8-dba-cloud-db00.wh postgresql-15.1]#
安装后/home/work/postresql_5432目录下的文件:
[root@a8-dba-cloud-db00.wh postgresql_5432]# ll 总用量 32 drwxr-xr-x 2 root root 4096 2月 9 23:51 bin drwxr-xr-x 2 root root 4096 2月 9 21:48 data drwxr-xr-x 3 root root 4096 2月 9 23:51 doc drwxr-xr-x 3 root root 4096 2月 9 23:51 html drwxr-xr-x 6 root root 4096 2月 9 23:51 include drwxr-xr-x 4 root root 4096 2月 9 23:51 lib drwxr-xr-x 5 root root 4096 2月 9 23:51 man drwxr-xr-x 7 root root 4096 2月 9 23:51 share [root@a8-dba-cloud-db00.wh postgresql_5432]#
不能root用户而要普通用户进行初始化:initdb -D -U
-D:指定pg数据文件存放的目录
-U:指定数据库默认的超级用户名
[work@a8-dba-cloud-db00.wh postgresql_5432]$ ./bin/initdb --help initdb initializes a PostgreSQL database cluster. Usage: initdb [OPTION]... [DATADIR] Options: -A, --auth=METHOD default authentication method for local connections --auth-host=METHOD default authentication method for local TCP/IP connections --auth-local=METHOD default authentication method for local-socket connections [-D, --pgdata=]DATADIR location for this database cluster -E, --encoding=ENCODING set default encoding for new databases -g, --allow-group-access allow group read/execute on data directory --icu-locale=LOCALE set ICU locale ID for new databases -k, --data-checksums use data page checksums --locale=LOCALE set default locale for new databases --lc-collate=, --lc-ctype=, --lc-messages=LOCALE --lc-monetary=, --lc-numeric=, --lc-time=LOCALE set default locale in the respective category for new databases (default taken from environment) --no-locale equivalent to --locale=C --locale-provider={libc|icu} set default locale provider for new databases --pwfile=FILE read password for the new superuser from file -T, --text-search-config=CFG default text search configuration -U, --username=NAME database superuser name -W, --pwprompt prompt for a password for the new superuser -X, --waldir=WALDIR location for the write-ahead log directory --wal-segsize=SIZE size of WAL segments, in megabytes Less commonly used options: -d, --debug generate lots of debugging output --discard-caches set debug_discard_caches=1 -L DIRECTORY where to find the input files -n, --no-clean do not clean up after errors -N, --no-sync do not wait for changes to be written safely to disk --no-instructions do not print instructions for next steps -s, --show show internal settings -S, --sync-only only sync database files to disk, then exit Other options: -V, --version output version information, then exit -?, --help show this help, then exit If the data directory is not specified, the environment variable PGDATA is used. Report bugs to <pgsql-bugs@lists.postgresql.org>. PostgreSQL home page: <https://www.postgresql.org/>
[root@a8-dba-cloud-db00.wh postgresql_5432]# chown -R work:work /home/work/postgresql_5432 [root@a8-dba-cloud-db00.wh postgresql_5432]# su - work [work@a8-dba-cloud-db00.wh postgresql_5432]$ /home/work/postgresql_5432/bin/initdb -D /home/work/postgresql_5432/data --username=work --encoding=UTF8 --locale=en_US.UTF-8 --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8 --lc-messages=en_US.UTF-8 --lc-monetary=en_US.UTF-8 --lc-numeric=en_US.UTF-8 --lc-time=en_US.UTF-8
The files belonging to this database system will be owned by user "work".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory 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
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:
bin/pg_ctl -D ./data -l logfile start
初始化后,data目录下面的文件如下:
[work@a8-dba-cloud-db00.wh postgresql_5432]$ cd data [work@a8-dba-cloud-db00.wh data]$ ll 总用量 120 drwx------ 5 work work 4096 2月 10 10:10 base drwx------ 2 work work 4096 2月 10 10:10 global drwx------ 2 work work 4096 2月 10 10:10 pg_commit_ts drwx------ 2 work work 4096 2月 10 10:10 pg_dynshmem -rw------- 1 work work 4789 2月 10 10:10 pg_hba.conf -rw------- 1 work work 1636 2月 10 10:10 pg_ident.conf drwx------ 4 work work 4096 2月 10 10:10 pg_logical drwx------ 4 work work 4096 2月 10 10:10 pg_multixact drwx------ 2 work work 4096 2月 10 10:10 pg_notify drwx------ 2 work work 4096 2月 10 10:10 pg_replslot drwx------ 2 work work 4096 2月 10 10:10 pg_serial drwx------ 2 work work 4096 2月 10 10:10 pg_snapshots drwx------ 2 work work 4096 2月 10 10:10 pg_stat drwx------ 2 work work 4096 2月 10 10:10 pg_stat_tmp drwx------ 2 work work 4096 2月 10 10:10 pg_subtrans drwx------ 2 work work 4096 2月 10 10:10 pg_tblspc drwx------ 2 work work 4096 2月 10 10:10 pg_twophase -rw------- 1 work work 3 2月 10 10:10 PG_VERSION drwx------ 3 work work 4096 2月 10 10:10 pg_wal drwx------ 2 work work 4096 2月 10 10:10 pg_xact -rw------- 1 work work 88 2月 10 10:10 postgresql.auto.conf -rw------- 1 work work 29462 2月 10 10:10 postgresql.conf [work@a8-dba-cloud-db00.wh data]$
配置:
(1)客户端认证配置文件:pg_hba.conf
主机类型TYPE:
- local:使用Unix-domainsocket
- host:使用TCP/IP连接,可以是SSL的,也可以不是
- hostssl:必须是SSL的
- hostnossl:必须是非SSL的
数据库名DATABASE:多个数据库用英文逗号隔开。
- all # all表示所有,但不包括replication
- replication
- apple
- amazon,airbnb,google
用户名USER:多个用户名用英文逗号隔开
- all # 表示所有客户端用户
- igoodful
- admin,apple,google
- @userfile
客户端地址ADDRESS:
- 192.168.10.122/32 # 表示单一主机,即ip地址为192.168.10.122的机器
- 192.168.10.0/24 # 表示192.168.0.1~192.168.0.255网段内所有主机
- 0.0.0.0/0 # 表示所有主机。
密码加密策略METHOD:
password # 使用明文密码进行身份验证,不安全;
md5 # 最常用;
scram-sha-256 # 会以对应的方式加密再发送密码;
trust # 用户不用密码就连到数据库,最不安全的身份验证;
ident #
(2)客户端认证配置文件 postgresql.conf
shared_buffers =3GB # 一般来说越大越好,至少应该达到系统总内存的1/4;
effective_cache_size=6GB # 专用的pg服务器,设为系统总内存的一半或者更多;
work_mem= #用于执行排序,哈希关联,表扫描等操作的最大内存量
mintenance_work_mem=512MB # 内部维护操作的内存总量,其值不应大于1GB
# 建议等于SHARED BUFFER,或2倍。
# 同时需要考虑崩溃恢复时间, 越大,检查点可能拉越长导致崩溃恢复耗时越长。但是越小,开启FPW时,WAL日志写入量又越大。 建议采用COW文件系统,关闭FPW。
# max_wal_size 公式: # min(shared_buffers*2 , 用户存储空间/10)
max_wal_size = 48GB
# 建议是SHARED BUFFER的2分之一
# min_wal_size 公式: # min(shared_buffers/2 , 用户存储空间/10)
min_wal_size = 12GB
# log_statement参数控制数据库日志记录哪些SQL,可以选择的选项有:none, ddl, mod, and all.
log_statement = 'ddl'
- none: 不记录任何语句
- ddl : 记录所有DDL 语句
- mod: 记录所有DDL以及涉及到数据修改的语句
- all: 记录所有语句
log_filename:日志的命名格式, 默认是postgresql-%Y-%m-%d_%H%M%S.log。支持strftime格式
格式符 说明
%a 星期的英文单词的缩写:如星期一, 则返回 Mon
%A 星期的英文单词的全拼:如星期一,返回 Monday
%b 月份的英文单词的缩写:如一月, 则返回 Jan
%B 月份的引文单词的缩写:如一月, 则返回 January
%c 返回datetime的字符串表示,如03/08/15 23:01:26
%d 返回的是当前时间是当前月的第几天
%f 微秒的表示: 范围: [0,999999]
%H 以24小时制表示当前小时
%I 以12小时制表示当前小时
%j 返回 当天是当年的第几天 范围[001,366]
%m 返回月份 范围[0,12]
%M 返回分钟数 范围 [0,59]
%P 返回是上午还是下午–AM or PM
%S 返回秒数 范围 [0,61]。。。手册说明的
%U 返回当周是当年的第几周 以周日为第一天
%W 返回当周是当年的第几周 以周一为第一天
%w 当天在当周的天数,范围为[0, 6],6表示星期天
%x 日期的字符串表示 :03/08/15
%X 时间的字符串表示 :23:22:08
%y 两个数字表示的年份 15
%Y 四个数字表示的年份 2015
%z 与utc时间的间隔 (如果是本地时间,返回空字符串)
%Z 时区名称(如果是本地时间,返回空字符串)
(3)客户端认证配置文件 pg_hba.conf
修改配置文件:pg_hba.conf和postgresql.conf
启动:
/home/work/postresql_5432/bin/pg_ctl -D /home/work/postresql_5432/data -l logfile start
或者执行:
/home/work/postresql_5432/bin/postgres -D /home/work/postresql_5432/data >logfile 2>&1 &
查看5432端口是否已经启动
[root@a8-dba-cloud-db00.wh data]# ss -tan | grep 5432 LISTEN 0 1024 *:5432 *:* LISTEN 0 1024 [::]:5432 [::]:* [root@a8-dba-cloud-db00.wh data]# netstat -nltp|grep 5432 tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 24232/postgres tcp6 0 0 :::5432 :::* LISTEN 24232/postgres [root@a8-dba-cloud-db00.wh data]#
登录:
[work@a8-dba-cloud-db00.wh postgresql_5432]$ ./bin/psql -U work -p 5433 -d postgres -h 127.0.0.1 psql (15.2) Type "help" for help. postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges -----------+-------+----------+-------------+-------------+------------+-----------------+------------------- postgres | work | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | template0 | work | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/work + | | | | | | | work=CTc/work template1 | work | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/work + | | | | | | | work=CTc/work (3 rows) postgres=#
修改密码:
postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# select * from pg_shadow; usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig ---------+----------+-------------+----------+---------+--------------+--------+----------+----------- work | 10 | t | t | t | t | | | (1 row) postgres=# ALTER USER work WITH PASSWORD 'work'; ALTER ROLE postgres=# select * from pg_shadow; usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig ---------+----------+-------------+----------+---------+--------------+---------------------------------------------------------------------------------------------------------------------------------------+----------+----------- work | 10 | t | t | t | t | SCRAM-SHA-256$4096:at/MhRAhMPV4CnrizPntuw==$bGctvlctxobloic4VQ6oJMtaxBXoBNiFJSI95xvQWZc=:qd0UEtR4H75ZP1j8PyhWvhFqnGV2i8vGbZiVmH9dGRg= | | (1 row) postgres=#
查看系统角色:
postgres=# SELECT rolname FROM pg_roles; rolname --------------------------- work pg_database_owner pg_read_all_data pg_write_all_data pg_monitor pg_read_all_settings pg_read_all_stats pg_stat_scan_tables pg_read_server_files pg_write_server_files pg_execute_server_program pg_signal_backend pg_checkpoint (13 行记录)
PG数据库源码在编译之前,需要首先通过configure脚本,加入一些参数进行配置。例如:想要支持lz4压缩,就需要在configure时,添加参数--with-lz4。
一般情况下,想要把相关功能都配置,详细的configure命令为(本人是Linux系统):
./configure --enable-debug --with-python --with-perl --with-tcl --with-gssapi --with-pam --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --without-oci --enable-dtrace --enable-depend --enable-cassert --with-systemd CFLAGS="-O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2" --prefix=/usr/local/pgsql
在第一次执行的时候,一般会报很多错,主要的原因,还是相关的库,并没有安装。
总结遇到的相关问题如下:
【问题1】:
checking for dtrace... no
configure: error: dtrace not found
解决方法:
yum search dtrace
Loaded plugins: fastestmirror, refresh-packagekit, security
Loading mirror speeds from cached hostfile
* base: mirrors.163.com
* extras: mirrors.163.com
* updates: mirrors.163.com
=============================================================================================== Matched: dtrace ===============================================================================================
systemtap-sdt-devel.i686 : Static probe support tools
systemtap-sdt-devel.x86_64 : Static probe support tools
找到了,就安装,我是64位的,安装第二个
yum install -y systemtap-sdt-devel.x86_64
【问题2】:
checking for flags to link embedded Perl... Can't locate ExtUtils/Embed.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .).
BEGIN failed--compilation aborted.
no
configure: error: could not determine flags for linking embedded Perl.
This probably means that ExtUtils::Embed or ExtUtils::MakeMaker is not
installed.
解决方法:
yum install perl-ExtUtils-Embed -y
【问题3】:
configure: error: could not determine flags for linking embedded Perl.
This probably means that ExtUtils::Embed or ExtUtils::MakeMaker is not
installed.
解决方法:
yum install perl-ExtUtils-Embed
【问题4】:
configure: error: readline library not found
If you have readline already installed, see config.log for details on the
failure. It is possible the compiler isn't looking in the proper directory.
Use --without-readline to disable readline support.
解决方法:
yum install readline readline-devel
【问题5】:
checking for inflate in -lz... no
configure: error: zlib library not found
If you have zlib already installed, see config.log for details on the
failure. It is possible the compiler isn't looking in the proper directory.
Use --without-zlib to disable zlib support.
解决方法:
yum install zlib zlib-devel
【问题6】:
checking for CRYPTO_new_ex_data in -lcrypto... no
configure: error: library 'crypto' is required for OpenSSL
解决方法:
yum install openssl openssl-devel
【问题7】:
checking for pam_start in -lpam... no
configure: error: library 'pam' is required for PAM
解决方法:
yum install pam pam-devel
【问题8】:
checking for xmlSaveToBuffer in -lxml2... no
configure: error: library 'xml2' (version >= 2.6.23) is required for XML support
解决方法:
yum install libxml2 libxml2-devel
【问题9】:
checking for xsltCleanupGlobals in -lxslt... no
configure: error: library 'xslt' is required for XSLT support
解决方法:
yum install libxslt libxslt-devel
【问题10】:
configure: error: Tcl shell not found
解决方法:
yum install tcl tcl-devel
【问题11】:
checking for ldap.h... no
configure: error: header file is required for LDAP
解决方法:
yum install openldap openldap-devel
【问题12】:
checking for Python.h... no
configure: error: header file <Python.h> is required for Python
解决方法:
yum install python python-devel (注意有的机器上需要指定python版本,例如python2-devel或python3.6-devel)
【问题13】:
Error when bootstrapping CMake:
Cannot find appropriate C++ compiler on this system.
Please specify one using environment variable CXX.
See cmake_bootstrap.log for compilers attempted.
解决方法:
yum install gcc-c++
【问题14】:
configure: error: Package requirements (liblz4) were not met:
No package 'liblz4' found
#########################################