pg系列一 centos7.7编译安装12.2
1.建立用户$ id postgres
uid=1006(postgres) gid=1008(postgres) groups=1008(postgres)
2.修改变量
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
export PGDATA=/app/postgres/12/data
export LD_LIBRARY_PATH=/app/postgres/12/lib
export PATH=/app/postgres/12/bin:$PATH3.建立目录
# mkdir -p /app/postgres/12/data
# chown -R postgres:postgres /app/postgres4.编译安装
$ tar -zxvf postgresql-12.2.tar.gz
$ ls
aclocal.m4configconfigureconfigure.incontribCOPYRIGHTdocGNUmakefile.inHISTORYINSTALLMakefileREADMEsrc
$ ll
total 740
-rw-r--r--1 postgres postgres 522 Feb 11 06:14 aclocal.m4
drwxrwxr-x2 postgres postgres 4096 Feb 11 06:28 config
-rwxr-xr-x1 postgres postgres 572134 Feb 11 06:14 configure
-rw-r--r--1 postgres postgres83465 Feb 11 06:14 configure.in
drwxrwxr-x 56 postgres postgres 4096 Feb 11 06:28 contrib
-rw-r--r--1 postgres postgres 1192 Feb 11 06:14 COPYRIGHT
drwxrwxr-x3 postgres postgres 87 Feb 11 06:28 doc
-rw-r--r--1 postgres postgres 3909 Feb 11 06:14 GNUmakefile.in
-rw-r--r--1 postgres postgres 284 Feb 11 06:14 HISTORY
-rw-r--r--1 postgres postgres61606 Feb 11 06:29 INSTALL
-rw-r--r--1 postgres postgres 1665 Feb 11 06:14 Makefile
-rw-r--r--1 postgres postgres 1212 Feb 11 06:14 README
drwxrwxr-x 16 postgres postgres 328 Feb 11 06:29 src
./configure --prefix=/app/postgres/12$ ./configure --prefix=/app/postgres/12
checking build system type... x86_64-pc-linux-gnu
checking host system type... x86_64-pc-linux-gnu
checking which template to use... linux
checking whether NLS is wanted... no
checking for default port number... 5432
checking for block size... 8kB
checking for segment size... 1GB
checking for WAL block size... 8kB
checking for gcc... no
checking for cc... no
configure: error: in `/tmp/postgresql-12.2':
configure: error: no acceptable C compiler found in $PATH
See `config.log' for more details
$ ./configure --prefix=/app/postgres/12
checking build system type... x86_64-pc-linux-gnu
checking host system type... x86_64-pc-linux-gnu
checking which template to use... linux
checking whether NLS is wanted... no
checking for default port number... 5432
checking for block size... 8kB
checking for segment size... 1GB
checking for WAL block size... 8kB
checking for gcc... gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables...
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking for gcc option to accept ISO C99... -std=gnu99
checking for g++... no
checking for c++... no
checking whether we are using the GNU C++ compiler... no
checking whether g++ accepts -g... no
checking for gawk... gawk
checking whether gcc -std=gnu99 supports -Wdeclaration-after-statement, for CFLAGS... yes
checking whether gcc -std=gnu99 supports -Werror=vla, for CFLAGS... yes
checking whether gcc -std=gnu99 supports -Wendif-labels, for CFLAGS... yes
checking whether g++ supports -Wendif-labels, for CXXFLAGS... no
checking whether gcc -std=gnu99 supports -Wmissing-format-attribute, for CFLAGS... yes
checking whether g++ supports -Wmissing-format-attribute, for CXXFLAGS... no
checking whether gcc -std=gnu99 supports -Wformat-security, for CFLAGS... yes
checking whether g++ supports -Wformat-security, for CXXFLAGS... no
checking whether gcc -std=gnu99 supports -fno-strict-aliasing, for CFLAGS... yes
checking whether g++ supports -fno-strict-aliasing, for CXXFLAGS... no
checking whether gcc -std=gnu99 supports -fwrapv, for CFLAGS... yes
checking whether g++ supports -fwrapv, for CXXFLAGS... no
checking whether gcc -std=gnu99 supports -fexcess-precision=standard, for CFLAGS... yes
checking whether g++ supports -fexcess-precision=standard, for CXXFLAGS... no
checking whether gcc -std=gnu99 supports -funroll-loops, for CFLAGS_VECTOR... yes
checking whether gcc -std=gnu99 supports -ftree-vectorize, for CFLAGS_VECTOR... yes
checking whether gcc -std=gnu99 supports -Wunused-command-line-argument, for NOT_THE_CFLAGS... no
checking whether gcc -std=gnu99 supports -Wformat-truncation, for NOT_THE_CFLAGS... no
checking whether gcc -std=gnu99 supports -Wstringop-truncation, for NOT_THE_CFLAGS... no
checking whether the C compiler still works... yes
checking how to run the C preprocessor... gcc -std=gnu99 -E
checking allow thread-safe client libraries... yes
checking whether to build with ICU support... no
checking whether to build with Tcl... no
checking whether to build Perl modules... no
checking whether to build Python modules... no
checking whether to build with GSSAPI support... no
checking whether to build with PAM support... no
checking whether to build with BSD Authentication support... no
checking whether to build with LDAP support... no
checking whether to build with Bonjour support... no
checking whether to build with OpenSSL support... no
checking whether to build with SELinux support... no
checking whether to build with systemd support... no
checking for grep that handles long lines and -e... /bin/grep
checking for egrep... /bin/grep -E
checking for ld used by GCC... /bin/ld
checking if the linker (/bin/ld) is GNU ld... yes
checking for ranlib... ranlib
checking for strip... strip
checking whether it is possible to strip libraries... yes
checking for ar... ar
checking for a BSD-compatible install... /bin/install -c
checking for tar... /bin/tar
checking whether ln -s works... yes
checking for a thread-safe mkdir -p... /bin/mkdir -p
checking for bison... no
configure: WARNING:
*** Without Bison you will not be able to build PostgreSQL from Git nor
*** change any of the parser definition files.You can obtain Bison from
*** a GNU mirror site.(If you are using the official distribution of
*** PostgreSQL then you do not need to worry about this, because the Bison
*** output is pre-generated.)
checking for flex... no
configure: WARNING:
*** Without Flex you will not be able to build PostgreSQL from Git nor
*** change any of the scanner definition files.You can obtain Flex from
*** a GNU mirror site.(If you are using the official distribution of
*** PostgreSQL then you do not need to worry about this because the Flex
*** output is pre-generated.)
checking for perl... /bin/perl
configure: using perl 5.16.3
checking for a sed that does not truncate output... /bin/sed
checking for ANSI C header files... yes
checking for sys/types.h... yes
checking for sys/stat.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for memory.h... yes
checking for strings.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for unistd.h... yes
checking whether gcc -std=gnu99 is Clang... no
checking whether pthreads work with -pthread... yes
checking for joinable pthread attribute... PTHREAD_CREATE_JOINABLE
checking whether more special flags are required for pthreads... no
checking for PTHREAD_PRIO_INHERIT... yes
checking pthread.h usability... yes
checking pthread.h presence... yes
checking for pthread.h... yes
checking for strerror_r... yes
checking for getpwuid_r... yes
checking for gethostbyname_r... yes
checking whether strerror_r returns int... no
checking for main in -lm... yes
checking for library containing setproctitle... no
checking for library containing dlopen... -ldl
checking for library containing socket... none required
checking for library containing shl_load... no
checking for library containing getopt_long... none required
checking for library containing crypt... -lcrypt
checking for library containing shm_open... -lrt
checking for library containing shm_unlink... none required
checking for library containing clock_gettime... none required
checking for library containing fdatasync... none required
checking for library containing sched_yield... none required
checking for library containing gethostbyname_r... none required
checking for library containing shmget... none required
checking for library containing readline... -lreadline
checking for inflate in -lz... yes
checking for stdbool.h that conforms to C99... yes
checking for _Bool... yes
checking atomic.h usability... no
checking atomic.h presence... no
checking for atomic.h... no
checking copyfile.h usability... no
checking copyfile.h presence... no
checking for copyfile.h... no
checking crypt.h usability... yes
checking crypt.h presence... yes
checking for crypt.h... yes
checking fp_class.h usability... no
checking fp_class.h presence... no
checking for fp_class.h... no
checking getopt.h usability... yes
checking getopt.h presence... yes
checking for getopt.h... yes
checking ieeefp.h usability... no
checking ieeefp.h presence... no
checking for ieeefp.h... no
checking ifaddrs.h usability... yes
checking ifaddrs.h presence... yes
checking for ifaddrs.h... yes
checking langinfo.h usability... yes
checking langinfo.h presence... yes
checking for langinfo.h... yes
checking mbarrier.h usability... no
checking mbarrier.h presence... no
checking for mbarrier.h... no
checking poll.h usability... yes
checking poll.h presence... yes
checking for poll.h... yes
checking sys/epoll.h usability... yes
checking sys/epoll.h presence... yes
checking for sys/epoll.h... yes
checking sys/ipc.h usability... yes
checking sys/ipc.h presence... yes
checking for sys/ipc.h... yes
checking sys/prctl.h usability... yes
checking sys/prctl.h presence... yes
checking for sys/prctl.h... yes
checking sys/procctl.h usability... no
checking sys/procctl.h presence... no
checking for sys/procctl.h... no
checking sys/pstat.h usability... no
checking sys/pstat.h presence... no
checking for sys/pstat.h... no
checking sys/resource.h usability... yes
checking sys/resource.h presence... yes
checking for sys/resource.h... yes
checking sys/select.h usability... yes
checking sys/select.h presence... yes
checking for sys/select.h... yes
checking sys/sem.h usability... yes
checking sys/sem.h presence... yes
checking for sys/sem.h... yes
checking sys/shm.h usability... yes
checking sys/shm.h presence... yes
checking for sys/shm.h... yes
checking sys/sockio.h usability... no
checking sys/sockio.h presence... no
checking for sys/sockio.h... no
checking sys/tas.h usability... no
checking sys/tas.h presence... no
checking for sys/tas.h... no
checking sys/un.h usability... yes
checking sys/un.h presence... yes
checking for sys/un.h... yes
checking termios.h usability... yes
checking termios.h presence... yes
checking for termios.h... yes
checking ucred.h usability... no
checking ucred.h presence... no
checking for ucred.h... no
checking utime.h usability... yes
checking utime.h presence... yes
checking for utime.h... yes
checking wchar.h usability... yes
checking wchar.h presence... yes
checking for wchar.h... yes
checking wctype.h usability... yes
checking wctype.h presence... yes
checking for wctype.h... yes
checking for net/if.h... yes
checking for sys/ucred.h... no
checking for netinet/tcp.h... yes
checking readline/readline.h usability... yes
checking readline/readline.h presence... yes
checking for readline/readline.h... yes
checking readline/history.h usability... yes
checking readline/history.h presence... yes
checking for readline/history.h... yes
checking zlib.h usability... yes
checking zlib.h presence... yes
checking for zlib.h... yes
checking whether byte ordering is bigendian... no
checking for inline... inline
checking for printf format archetype... gnu_printf
checking for flexible array members... yes
checking for signed types... yes
checking for __func__... yes
checking for _Static_assert... yes
checking for typeof... typeof
checking for __builtin_types_compatible_p... yes
checking for __builtin_constant_p... yes
checking for __builtin_unreachable... yes
checking for computed goto support... yes
checking whether struct tm is in sys/time.h or time.h... time.h
checking for struct tm.tm_zone... yes
checking for tzname... yes
checking for union semun... no
checking for struct sockaddr_un... yes
checking for struct sockaddr_storage... yes
checking for struct sockaddr_storage.ss_family... yes
checking for struct sockaddr_storage.__ss_family... no
checking for struct sockaddr_storage.ss_len... no
checking for struct sockaddr_storage.__ss_len... no
checking for struct sockaddr.sa_len... no
checking for struct addrinfo... yes
checking for intptr_t... yes
checking for uintptr_t... yes
checking for unsigned long long int... yes
checking for long long int... yes
checking for locale_t... yes
checking for C/C++ restrict keyword... __restrict
checking for struct cmsgcred... no
checking for struct option... yes
checking for z_streamp... yes
checking whether assembler supports x86_64 popcntq... yes
checking for special C compiler options needed for large files... no
checking for _FILE_OFFSET_BITS value needed for large files... no
checking size of off_t... 8
checking size of bool... 1
checking for int timezone... yes
checking types of arguments for accept()... int, int, struct sockaddr *, socklen_t *
checking whether gettimeofday takes only one argument... no
checking for wcstombs_l declaration... no
checking for cbrt... yes
checking for clock_gettime... yes
checking for copyfile... no
checking for fdatasync... yes
checking for getifaddrs... yes
checking for getpeerucred... no
checking for getrlimit... yes
checking for mbstowcs_l... no
checking for memmove... yes
checking for poll... yes
checking for posix_fallocate... yes
checking for ppoll... yes
checking for pstat... no
checking for pthread_is_threaded_np... no
checking for readlink... yes
checking for setproctitle... no
checking for setproctitle_fast... no
checking for setsid... yes
checking for shm_open... yes
checking for strchrnul... yes
checking for strsignal... yes
checking for symlink... yes
checking for sync_file_range... yes
checking for uselocale... yes
checking for utime... yes
checking for utimes... yes
checking for wcstombs_l... no
checking for __builtin_bswap16... yes
checking for __builtin_bswap32... yes
checking for __builtin_bswap64... yes
checking for __builtin_clz... yes
checking for __builtin_ctz... yes
checking for __builtin_popcount... yes
checking for fseeko... yes
checking for _LARGEFILE_SOURCE value needed for large files... no
checking how gcc -std=gnu99 reports undeclared, standard C functions... error
checking for posix_fadvise... yes
checking whether posix_fadvise is declared... yes
checking whether fdatasync is declared... yes
checking whether strlcat is declared... no
checking whether strlcpy is declared... no
checking whether strnlen is declared... yes
checking whether F_FULLFSYNC is declared... no
checking whether RTLD_GLOBAL is declared... yes
checking whether RTLD_NOW is declared... yes
checking for struct sockaddr_in6... yes
checking for PS_STRINGS... no
checking for isinf... yes
checking for crypt... yes
checking for dlopen... yes
checking for fls... no
checking for getopt... yes
checking for getrusage... yes
checking for inet_aton... yes
checking for mkdtemp... yes
checking for pread... yes
checking for pwrite... yes
checking for random... yes
checking for rint... yes
checking for srandom... yes
checking for strlcat... no
checking for strlcpy... no
checking for strnlen... yes
checking for strtof... yes
checking for unsetenv... yes
checking for getpeereid... no
checking for getaddrinfo... yes
checking for getopt_long... yes
checking for syslog... yes
checking syslog.h usability... yes
checking syslog.h presence... yes
checking for syslog.h... yes
checking for opterr... yes
checking for optreset... no
checking for strtoll... yes
checking for strtoull... yes
checking whether strtoll is declared... yes
checking whether strtoull is declared... yes
checking for rl_completion_append_character... yes
checking for rl_completion_matches... yes
checking for rl_filename_completion_function... yes
checking for rl_reset_screen_size... yes
checking for append_history... yes
checking for history_truncate_file... yes
checking test program... ok
checking whether long int is 64 bits... yes
checking for __builtin_mul_overflow... no
checking size of void *... 8
checking size of size_t... 8
checking size of long... 8
checking whether to build with float4 passed by value... yes
checking whether to build with float8 passed by value... yes
checking alignment of short... 2
checking alignment of int... 4
checking alignment of long... 8
checking alignment of double... 8
checking for int8... no
checking for uint8... no
checking for int64... no
checking for uint64... no
checking for __int128... yes
checking for __int128 alignment bug... ok
checking alignment of PG_INT128_TYPE... 16
checking for builtin __sync char locking functions... yes
checking for builtin __sync int32 locking functions... yes
checking for builtin __sync int32 atomic operations... yes
checking for builtin __sync int64 atomic operations... yes
checking for builtin __atomic int32 atomic operations... yes
checking for builtin __atomic int64 atomic operations... yes
checking for __get_cpuid... yes
checking for __cpuid... no
checking for _mm_crc32_u8 and _mm_crc32_u32 with CFLAGS=... no
checking for _mm_crc32_u8 and _mm_crc32_u32 with CFLAGS=-msse4.2... yes
checking for __crc32cb, __crc32ch, __crc32cw, and __crc32cd with CFLAGS=... no
checking for __crc32cb, __crc32ch, __crc32cw, and __crc32cd with CFLAGS=-march=armv8-a+crc... no
checking which CRC-32C implementation to use... SSE 4.2 with runtime check
checking for library containing sem_init... -lpthread
checking which semaphore API to use... unnamed POSIX
checking for /dev/urandom... yes
checking which random number source to use... /dev/urandom
checking for xmllint... /bin/xmllint
checking for DocBook XML V4.2... yes
checking for dbtoepub... no
checking for xsltproc... /bin/xsltproc
checking for fop... no
checking thread safety of required library functions... yes
checking whether gcc -std=gnu99 supports -Wl,--as-needed... yes
configure: using compiler=gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39)
configure: using CFLAGS=-Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2
configure: using CPPFLAGS= -D_GNU_SOURCE
configure: using LDFLAGS=-Wl,--as-needed
configure: creating ./config.status
config.status: creating GNUmakefile
config.status: creating src/Makefile.global
config.status: creating src/include/pg_config.h
config.status: creating src/include/pg_config_ext.h
config.status: creating src/interfaces/ecpg/include/ecpg_config.h
config.status: linking src/backend/port/tas/dummy.s to src/backend/port/tas.s
config.status: linking src/backend/port/posix_sema.c to src/backend/port/pg_sema.c
config.status: linking src/backend/port/sysv_shmem.c to src/backend/port/pg_shmem.c
config.status: linking src/include/port/linux.h to src/include/pg_config_os.h
config.status: linking src/makefiles/Makefile.linux to src/Makefile.portmake
,-rpath,'/app/postgres/12/lib',--enable-new-dtags-lpgcommon -lpgport -lpthread -lz -lreadline -lrt -lcrypt -ldl -lm-o pg_isolation_regress
make: Leaving directory `/tmp/postgresql-12.2/src/test/isolation'
make -C test/perl all
make: Entering directory `/tmp/postgresql-12.2/src/test/perl'
make: Nothing to be done for `all'.
make: Leaving directory `/tmp/postgresql-12.2/src/test/perl'
make: Leaving directory `/tmp/postgresql-12.2/src'
make -C config all
make: Entering directory `/tmp/postgresql-12.2/config'
make: Nothing to be done for `all'.
make: Leaving directory `/tmp/postgresql-12.2/config'
All of PostgreSQL successfully made. Ready to install.make install
make: Leaving directory `/tmp/postgresql-12.2/src/test/perl'
/bin/mkdir -p '/app/postgres/12/lib/pgxs/src'
/bin/install -c -m 644 Makefile.global '/app/postgres/12/lib/pgxs/src/Makefile.global'
/bin/install -c -m 644 Makefile.port '/app/postgres/12/lib/pgxs/src/Makefile.port'
/bin/install -c -m 644 ./Makefile.shlib '/app/postgres/12/lib/pgxs/src/Makefile.shlib'
/bin/install -c -m 644 ./nls-global.mk '/app/postgres/12/lib/pgxs/src/nls-global.mk'
make: Leaving directory `/tmp/postgresql-12.2/src'
make -C config install
make: Entering directory `/tmp/postgresql-12.2/config'
/bin/mkdir -p '/app/postgres/12/lib/pgxs/config'
/bin/install -c -m 755 ./install-sh '/app/postgres/12/lib/pgxs/config/install-sh'
/bin/install -c -m 755 ./missing '/app/postgres/12/lib/pgxs/config/missing'
make: Leaving directory `/tmp/postgresql-12.2/config'
PostgreSQL installation complete.5.初始化数据库
$ which initdb
/app/postgres/12/bin/initdb
$ initdb -D /app/postgre/12/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating directory /app/postgre/12/data ... initdb: error: could not create directory "/app/postgre": Permission denied
$ initdb -D /app/postgres/12/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /app/postgres/12/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /app/postgres/12/data -l logfile start6.启动数据库
$ pg_ctl -D /app/postgres/12/data -l logfile start
waiting for server to start.... done
server started
$ psql
psql (12.2)
Type "help" for help.
postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type:\copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=#\l
List of databases
Name |Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres=# 7.建立测试库，测试表
postgres=# create database zhyu;
CREATE DATABASE
postgres=# \c zhyu
You are now connected to database "zhyu" as user "postgres".
zhyu=# create table zhyu(id int,name varchar(50));
CREATE TABLE
zhyu=# insert into zhyu values(1,'zhangyu');
INSERT 0 1
zhyu=# select * from zhyu;
id |name
----+---------
1 | zhangyu
(1 row)8.postgresql得进程
# ps -ef | grep postgres
postgres1874 10 21:53 ? 00:00:00 /app/postgres/12/bin/postgres -D /app/postgres/12/data
postgres202318740 21:53 ? 00:00:00 postgres: checkpointer
postgres202418740 21:53 ? 00:00:00 postgres: background writer
postgres202518740 21:53 ? 00:00:00 postgres: walwriter
postgres202618740 21:53 ? 00:00:00 postgres: autovacuum launcher
postgres202718740 21:53 ? 00:00:00 postgres: stats collector
postgres202818740 21:53 ? 00:00:00 postgres: logical replication launcher
root 781777530 23:15 pts/1 00:00:00 grep --color=auto postgres9.配置远程访问
vi postgresql.conf
listen_addresses = '*' vi pg_hba.conf
host all all 192.168.56.0/24 md510.修改postgres用户密码
$ psql
psql (12.2)
Type "help" for help.
postgres=# alter user postgres with password 'postgres';
ALTER ROLE11.远程访问数据库
jdbc:postgresql://192.168.56.101:5432/postgres
页:
[1]