介质下载：
官网
下载安装
download/linux/redhat/
源码包
ftp/source/v15.1/
postgresql-15.1.tar.gz
二进制包
Binaries from installer Version 10.23
[root@cjc-db-01 pg]# ls -lrth postgresql-10.23-1-linux-x64-binaries.tar.gz -rw-r--r-- 1 root root 156M Jan 13 14:28 postgresql-10.23-1-linux-x64-binaries.tar.gz [root@cjc-db-01 pg]# md5sum postgresql-10.23-1-linux-x64-binaries.tar.gz bbfd9452247ec92cd8cd32d23d922f2f postgresql-10.23-1-linux-x64-binaries.tar.gz
安装前配置：
检查是否已经存在postgresql
[root@cjc-db-01 pg]# ps -ef|grep postg root 3602 3388 0 14:29 pts/0 00:00:00 grep --color=auto postg [root@cjc-db-01 pg]# rpm -qa|grep -i postg postgresql-libs-9.2.24-4.el7_8.x86_64 postgresql-server-9.2.24-4.el7_8.x86_64 postgresql-9.2.24-4.el7_8.x86_64
卸载
rpm -e postgresql-libs-9.2.24-4.el7_8.x86_64 --nodeps rpm -e postgresql-server-9.2.24-4.el7_8.x86_64 --nodeps rpm -e postgresql-9.2.24-4.el7_8.x86_64 --nodeps
创建用户组
groupadd -g 1500 postgres useradd -g 1500 -u 1500 postgres passwd postgres
配置环境变量
vi /home/postgres/.bash_profile export PGHOME=/pg/app/10/pgsql export PATH=$PATH:$PGHOME/bin export PGDATA=/pg/data export PGLOG=/pg/log [postgres@cjc-db-01 ~]$ source .bash_profile
创建目录并授权
mkdir -p /pg/{app/10,data,log,conf}
chown postgres.postgres /pg -R
解压
tar -zxvf postgresql-10.23-1-linux-x64-binaries.tar.gz -C /pg/app/10
授权
chown postgres.postgres /pg -R
解压后大小
[root@cjc-db-01 10]# du -sh * 423Mpgsql
文件
[root@cjc-db-01 10]# ls pgsql/ bin doc include lib pgAdmin 4 share stackbuilder
安装：
su - posgres
查看帮助信息
[postgres@cjc-db-01 ~]$ initdb --help
初始化
initdb -D /pg/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 /pg/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default timezone ... Asia/Shanghai selecting dynamic shared memory implementation ... posix creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok 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 /pg/data -l logfile start
目录大小
[postgres@cjc-db-01 pg]$ du -sh data/ 43Mdata/
目录文件
[postgres@cjc-db-01 pg]$ ls -lrth data/ total 48K drwx------ 2 postgres postgres 6 Jan 13 14:42 pg_twophase drwx------ 2 postgres postgres 6 Jan 13 14:42 pg_tblspc drwx------ 2 postgres postgres 6 Jan 13 14:42 pg_stat_tmp drwx------ 2 postgres postgres 6 Jan 13 14:42 pg_stat drwx------ 2 postgres postgres 6 Jan 13 14:42 pg_snapshots drwx------ 2 postgres postgres 6 Jan 13 14:42 pg_serial drwx------ 2 postgres postgres 6 Jan 13 14:42 pg_replslot drwx------ 4 postgres postgres 36 Jan 13 14:42 pg_multixact drwx------ 2 postgres postgres 6 Jan 13 14:42 pg_dynshmem drwx------ 2 postgres postgres 6 Jan 13 14:42 pg_commit_ts -rw------- 1 postgres postgres 3 Jan 13 14:42 PG_VERSION -rw------- 1 postgres postgres 23K Jan 13 14:42 postgresql.conf -rw------- 1 postgres postgres 88 Jan 13 14:42 postgresql.auto.conf -rw------- 1 postgres postgres 1.6K Jan 13 14:42 pg_ident.conf -rw------- 1 postgres postgres 4.5K Jan 13 14:42 pg_hba.conf drwx------ 2 postgres postgres 18 Jan 13 14:42 pg_xact drwx------ 3 postgres postgres 60 Jan 13 14:42 pg_wal drwx------ 2 postgres postgres 18 Jan 13 14:42 pg_subtrans drwx------ 2 postgres postgres 18 Jan 13 14:42 pg_notify drwx------ 2 postgres postgres 4.0K Jan 13 14:42 global drwx------ 5 postgres postgres 41 Jan 13 14:42 base drwx------ 4 postgres postgres 68 Jan 13 14:42 pg_logical
修改参数文件
[postgres@cjc-db-01 data]$ ls -lrth *.conf -rw------- 1 postgres postgres 23K Jan 13 14:42 postgresql.conf -rw------- 1 postgres postgres 88 Jan 13 14:42 postgresql.auto.conf -rw------- 1 postgres postgres 1.6K Jan 13 14:42 pg_ident.conf -rw------- 1 postgres postgres 4.5K Jan 13 14:42 pg_hba.conf [postgres@cjc-db-01 data]$ vi /pg/data/postgresql.conf #listen_addresses = 'localhost' # what IP address(es) to listen on; listen_addresses = '*' # what IP address(es) to listen on; #port = 5432 # (change requires restart) port = 5678
启动数据库
[postgres@cjc-db-01 ~]$ 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 kill SIGNALNAME PID Common options: -D, --pgdata=DATADIR location of the database storage area -s, --silent only print errors, no informational messages -t, --timeout=SECS seconds to wait when using -w option -V, --version output version information, then exit -w, --wait wait until operation completes (default) -W, --no-wait do not wait until operation completes -?, --help show this help, then exit If the -D option is omitted, the environment variable PGDATA is used. Options for start or restart: -c, --core-files allow postgres to produce core files -l, --log=FILENAME write (or append) server log to FILENAME -o, --options=OPTIONS command line options to pass to postgres (PostgreSQL server executable) or initdb -p PATH-TO-POSTGRES normally not necessary Options for stop or restart: -m, --mode=MODE MODE can be "smart", "fast", or "immediate" Shutdown modes are: smart quit after all clients have disconnected fast quit directly, with proper shutdown (default) immediate quit without complete shutdown; will lead to recovery on restart Allowed signal names for kill: ABRT HUP INT QUIT TERM USR1 USR2 Report bugs to <pgsql-bugs@postgresql.org>.
启动
[postgres@cjc-db-01 data]$ pg_ctl -D /pg/data start
启动日志如下：
waiting for server to start....2023-01-13 15:06:56.195 CST [6267] LOG: listening on IPv4 address "0.0.0.0", port 5678 2023-01-13 15:06:56.195 CST [6267] LOG: listening on IPv6 address "::", port 5678 2023-01-13 15:06:56.657 CST [6267] LOG: listening on Unix socket "/tmp/.s.PGSQL.5678" 2023-01-13 15:06:56.719 CST [6269] LOG: database system was shut down at 2023-01-13 14:42:08 CST 2023-01-13 15:06:56.749 CST [6267] LOG: database system is ready to accept connections done server started
登录数据库
查看psql帮助信息
[postgres@cjc-db-01 ~]$ psql --help
psql is the PostgreSQL interactive terminal.
Usage:
psql [OPTION]... [DBNAME [USERNAME]]
General options:
-c, --command=COMMAND run only single command (SQL or internal) and exit
-d, --dbname=DBNAME database name to connect to (default: "postgres")
-f, --file=FILENAME execute commands from file, then exit
-l, --list list available databases, then exit
-v, --set=, --variable=NAME=VALUE
set psql variable NAME to VALUE
(e.g., -v ON_ERROR_STOP=1)
-V, --version output version information, then exit
-X, --no-psqlrc do not read startup file (~/.psqlrc)
-1 ("one"), --single-transaction
execute as a single transaction (if non-interactive)
-?, --help[=options] show this help, then exit
--help=commands list backslash commands, then exit
--help=variables list special variables, then exit
Input and output options:
-a, --echo-all echo all input from script
-b, --echo-errors echo failed commands
-e, --echo-queries echo commands sent to server
-E, --echo-hidden display queries that internal commands generate
-L, --log-file=FILENAME send session log to file
-n, --no-readline disable enhanced command line editing (readline)
-o, --output=FILENAME send query results to file (or |pipe)
-q, --quiet run quietly (no messages, only query output)
-s, --single-step single-step mode (confirm each query)
-S, --single-line single-line mode (end of line terminates SQL command)
Output format options:
-A, --no-align unaligned table output mode
-F, --field-separator=STRING
field separator for unaligned output (default: "|")
-H, --html HTML table output mode
-P, --pset=VAR[=ARG] set printing option VAR to ARG (see \pset command)
-R, --record-separator=STRING
record separator for unaligned output (default: newline)
-t, --tuples-only print rows only
-T, --table-attr=TEXT set HTML table tag attributes (e.g., width, border)
-x, --expanded turn on expanded table output
-z, --field-separator-zero
set field separator for unaligned output to zero byte
-0, --record-separator-zero
set record separator for unaligned output to zero byte
Connection options:
-h, --host=HOSTNAME database server host or socket directory (default: "local socket")
-p, --port=PORT database server port (default: "5432")
-U, --username=USERNAME database user name (default: "postgres")
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
For more information, type "\?" (for internal commands) or "\help" (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.
Report bugs to <pgsql-bugs@postgresql.org>.
本地登录
[postgres@cjc-db-01 ~]$ psql psql.bin: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
添加环境变量
[postgres@cjc-db-01 ~]$ vi /home/postgres/.bash_profile export PGPORT=5678 [postgres@cjc-db-01 ~]$ source .bash_profile
再次登录
[postgres@cjc-db-01 ~]$ psql psql.bin (10.23) 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=# \q [postgres@cjc-db-01 ~]$
远程登录
[postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 2023-01-13 15:28:19.452 CST [7676] FATAL: no pg_hba.conf entry for host "172.16.6.137", user "postgres", database "postgres", SSL off psql.bin: FATAL: no pg_hba.conf entry for host "172.16.6.137", user "postgres", database "postgres", SSL off
修改pg_hba.conf
[postgres@cjc-db-01 ~]$ cd /pg/data/ [postgres@cjc-db-01 data]$ vi pg_hba.conf # IPv4 local connections: host all all 0.0.0.0/0 trust
其中：0代表所有ip地址都允许访问
重启数据库
[postgres@cjc-db-01 data]$ pg_ctl -D /pg/data restart
记录日志
[postgres@cjc-db-01 data]$ pg_ctl -D /pg/data -l /pg/log/pg.log restart
查看日志
[postgres@cjc-db-01 data]$ cat ../log/pg.log 2023-01-13 15:44:04.484 CST [8752] LOG: listening on IPv4 address "0.0.0.0", port 5678 2023-01-13 15:44:04.484 CST [8752] LOG: listening on IPv6 address "::", port 5678 2023-01-13 15:44:05.201 CST [8752] LOG: listening on Unix socket "/tmp/.s.PGSQL.5678" 2023-01-13 15:44:05.337 CST [8754] LOG: database system was shut down at 2023-01-13 15:43:53 CST 2023-01-13 15:44:05.366 CST [8752] LOG: database system is ready to accept connections
远程登录
[postgres@cjc-db-01 data]$ psql -h 172.16.6.137 -p 5678 psql.bin (10.23) Type "help" for help. postgres=#
创建数据库
postgres=# create database cjcdb; CREATE DATABASE
查看数据库
postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- cjcdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (4 rows)
###chenjuchao 20230117 14:45###