第 4 课 PostgreSQL 安装最新的版本
时间:2019-05-23 13:37 来源:linux.it.net.cn 作者:IT
1. 打开官网
https://www.postgresql.org/
2. 点击download
3. 选择版本类型
根据实际情况选择你的目标主机类型，我的是Centos，所以选择Red Hat.
4. 选择系统配置
选择后会生成对对应的rpm URL地址。
5. 安装rpm
复制rpm命令，在系统下执行，保证主机有联网。提示输入: y
yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
6. 安装postgresql
安装客户端：
yum install postgresql10
安装服务器端：
yum install postgresql10-server
7. 启动postgresql
初始化启动数据库
/usr/pgsql-10/bin/postgresql-10-setup initdb
初始化成功后：数据库目录在：/var/lib/pgsql/10， data目录
drwx------ 5 postgres postgres 4096 Jul 30 15:52 base
-rw------- 1 postgres postgres 30 Jul 30 15:55 current_logfiles
drwx------ 2 postgres postgres 4096 Jul 30 16:02 global
drwx------ 2 postgres postgres 4096 Jul 30 15:55 log
drwx------ 2 postgres postgres 4096 Jul 30 15:52 pg_commit_ts
drwx------ 2 postgres postgres 4096 Jul 30 15:52 pg_dynshmem
-rw------- 1 postgres postgres 4269 Jul 30 15:52 pg_hba.conf
-rw------- 1 postgres postgres 1636 Jul 30 15:52 pg_ident.conf
drwx------ 4 postgres postgres 4096 Jul 30 17:10 pg_logical
drwx------ 4 postgres postgres 4096 Jul 30 15:52 pg_multixact
drwx------ 2 postgres postgres 4096 Jul 30 15:55 pg_notify
drwx------ 2 postgres postgres 4096 Jul 30 15:52 pg_replslot
drwx------ 2 postgres postgres 4096 Jul 30 15:52 pg_serial
drwx------ 2 postgres postgres 4096 Jul 30 15:52 pg_snapshots
drwx------ 2 postgres postgres 4096 Jul 30 15:52 pg_stat
drwx------ 2 postgres postgres 4096 Jul 30 17:15 pg_stat_tmp
drwx------ 2 postgres postgres 4096 Jul 30 15:52 pg_subtrans
drwx------ 2 postgres postgres 4096 Jul 30 15:52 pg_tblspc
drwx------ 2 postgres postgres 4096 Jul 30 15:52 pg_twophase
-rw------- 1 postgres postgres 3 Jul 30 15:52 PG_VERSION
drwx------ 3 postgres postgres 4096 Jul 30 15:52 pg_wal
drwx------ 2 postgres postgres 4096 Jul 30 15:52 pg_xact
-rw------- 1 postgres postgres 88 Jul 30 15:52 postgresql.auto.conf
-rw------- 1 postgres postgres 22761 Jul 30 15:52 postgresql.conf
-rw------- 1 postgres postgres 58 Jul 30 15:55 postmaster.opts
-rw------- 1 postgres postgres 103 Jul 30 15:55 postmaster.pid
设置自动启动配置:
systemctl enable postgresql-10
启动服务器：
systemctl start postgresql-10
查看运行状态：
systemctl status postgresql-10
查看进程：
ps -ef|grep postgres
8. 使用psql连接数据库
在UNIX平台中安装PostgreSQL之后，PostgreSQL会在UNIX系统中创建一个名为“postgres”当用户。PostgreSQL的默认用户名和数据库也是“postgres”，
不过没有默认密码。在安装PostgreSQL之后可以默认用户登录，也可以创建新当用户名。
[root@izwz90tx4egvh4qj3p95vsz ~]# sudo su postgres
bash-4.2$
bash-4.2$
bash-4.2$ psql postgres
could not change directory to "/root": Permission denied
psql (10.4)
Type "help" for help.
postgres=# select version(); --查看版本
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)
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=# \dn --查看模式
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
postgres=# \q --退出
bash-4.2$
查看更多命令参数：
bash-4.2$ psql -? --查看帮助
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>.
连接服务器后，使用 "\?" 命令查看更多参数：
bash-4.2$ psql postgres
psql (10.4)
Type "help" for help.
postgres=# \?
General
\copyright show PostgreSQL usage and distribution terms
\crosstabview [COLUMNS] execute query and display results in crosstab
\errverbose show most recent error message at maximum verbosity
\g [FILE] or ; execute query (and send results to file or |pipe)
\gexec execute query, then execute each value in its result
\gset [PREFIX] execute query and store results in psql variables
\gx [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 [STRING] write string to standard output
\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 [STRING] write string to query output stream (see \o)
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)
--More--
例如：查看所有函数：
postgres=# \df *
List of functions
Schema | Name | Result data type |
Argument data types
| Type
------------+----------------------------------------------+-----------------------------------+-------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------+---------
pg_catalog | RI_FKey_cascade_del | trigger |
| trigger
pg_catalog | RI_FKey_cascade_upd | trigger |
| trigger
pg_catalog | RI_FKey_check_ins | trigger |
| trigger
pg_catalog | RI_FKey_check_upd | trigger |
| trigger
模糊匹配查看函数：
postgres=# \df ab*
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+-------------+------------------+-----------------------------+--------
pg_catalog | abbrev | text | cidr | normal
pg_catalog | abbrev | text | inet | normal
pg_catalog | abs | bigint | bigint | normal
pg_catalog | abs | double precision | double precision | normal
pg_catalog | abs | integer | integer | normal
pg_catalog | abs | numeric | numeric | normal
pg_catalog | abs | real | real | normal
pg_catalog | abs | smallint | smallint | normal
pg_catalog | abstime | abstime | timestamp without time zone | normal
pg_catalog | abstime | abstime | timestamp with time zone | normal
pg_catalog | abstimeeq | boolean | abstime, abstime | normal
pg_catalog | abstimege | boolean | abstime, abstime | normal
pg_catalog | abstimegt | boolean | abstime, abstime | normal
pg_catalog | abstimein | abstime | cstring | normal
pg_catalog | abstimele | boolean | abstime, abstime | normal
pg_catalog | abstimelt | boolean | abstime, abstime | normal
pg_catalog | abstimene | boolean | abstime, abstime | normal
pg_catalog | abstimeout | cstring | abstime | normal
pg_catalog | abstimerecv | abstime | internal | normal
pg_catalog | abstimesend | bytea | abstime | normal
(责任编辑：IT)
1. 打开官网https://www.postgresql.org/ 2. 点击download3. 选择版本类型根据实际情况选择你的目标主机类型，我的是Centos，所以选择Red Hat. 4. 选择系统配置选择后会生成对对应的rpm URL地址。 5. 安装rpm复制rpm命令，在系统下执行，保证主机有联网。提示输入: y yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm 6. 安装postgresql安装客户端： yum install postgresql10 安装服务器端： yum install postgresql10-server 7. 启动postgresql初始化启动数据库 /usr/pgsql-10/bin/postgresql-10-setup initdb 初始化成功后：数据库目录在：/var/lib/pgsql/10， data目录 drwx------ 5 postgres postgres 4096 Jul 30 15:52 base -rw------- 1 postgres postgres 30 Jul 30 15:55 current_logfiles drwx------ 2 postgres postgres 4096 Jul 30 16:02 global drwx------ 2 postgres postgres 4096 Jul 30 15:55 log drwx------ 2 postgres postgres 4096 Jul 30 15:52 pg_commit_ts drwx------ 2 postgres postgres 4096 Jul 30 15:52 pg_dynshmem -rw------- 1 postgres postgres 4269 Jul 30 15:52 pg_hba.conf -rw------- 1 postgres postgres 1636 Jul 30 15:52 pg_ident.conf drwx------ 4 postgres postgres 4096 Jul 30 17:10 pg_logical drwx------ 4 postgres postgres 4096 Jul 30 15:52 pg_multixact drwx------ 2 postgres postgres 4096 Jul 30 15:55 pg_notify drwx------ 2 postgres postgres 4096 Jul 30 15:52 pg_replslot drwx------ 2 postgres postgres 4096 Jul 30 15:52 pg_serial drwx------ 2 postgres postgres 4096 Jul 30 15:52 pg_snapshots drwx------ 2 postgres postgres 4096 Jul 30 15:52 pg_stat drwx------ 2 postgres postgres 4096 Jul 30 17:15 pg_stat_tmp drwx------ 2 postgres postgres 4096 Jul 30 15:52 pg_subtrans drwx------ 2 postgres postgres 4096 Jul 30 15:52 pg_tblspc drwx------ 2 postgres postgres 4096 Jul 30 15:52 pg_twophase -rw------- 1 postgres postgres 3 Jul 30 15:52 PG_VERSION drwx------ 3 postgres postgres 4096 Jul 30 15:52 pg_wal drwx------ 2 postgres postgres 4096 Jul 30 15:52 pg_xact -rw------- 1 postgres postgres 88 Jul 30 15:52 postgresql.auto.conf -rw------- 1 postgres postgres 22761 Jul 30 15:52 postgresql.conf -rw------- 1 postgres postgres 58 Jul 30 15:55 postmaster.opts -rw------- 1 postgres postgres 103 Jul 30 15:55 postmaster.pid 设置自动启动配置: systemctl enable postgresql-10 启动服务器： systemctl start postgresql-10 查看运行状态： systemctl status postgresql-10 查看进程： ps -ef|grep postgres 8. 使用psql连接数据库
在UNIX平台中安装PostgreSQL之后，PostgreSQL会在UNIX系统中创建一个名为“postgres”当用户。PostgreSQL的默认用户名和数据库也是“postgres”，
[root@izwz90tx4egvh4qj3p95vsz ~]# sudo su postgres
bash-4.2$
bash-4.2$
bash-4.2$ psql postgres
could not change directory to "/root": Permission denied
psql (10.4)
Type "help" for help.
postgres=# select version(); --查看版本
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)
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=# \dn --查看模式
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
postgres=# \q --退出
bash-4.2$
查看更多命令参数：
bash-4.2$ psql -? --查看帮助
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>.
连接服务器后，使用 "\?" 命令查看更多参数： bash-4.2$ psql postgres psql (10.4) Type "help" for help. postgres=# \? General \copyright show PostgreSQL usage and distribution terms \crosstabview [COLUMNS] execute query and display results in crosstab \errverbose show most recent error message at maximum verbosity \g [FILE] or ; execute query (and send results to file or |pipe) \gexec execute query, then execute each value in its result \gset [PREFIX] execute query and store results in psql variables \gx [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 [STRING] write string to standard output \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 [STRING] write string to query output stream (see \o) 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) --More-- 例如：查看所有函数：
postgres=# \df *
List of functions
Schema | Name | Result data type |
Argument data types
| Type
------------+----------------------------------------------+-----------------------------------+-------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------+---------
pg_catalog | RI_FKey_cascade_del | trigger |
| trigger
pg_catalog | RI_FKey_cascade_upd | trigger |
| trigger
pg_catalog | RI_FKey_check_ins | trigger |
| trigger
pg_catalog | RI_FKey_check_upd | trigger |
| trigger
模糊匹配查看函数：
postgres=# \df ab*
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+-------------+------------------+-----------------------------+--------
pg_catalog | abbrev | text | cidr | normal
pg_catalog | abbrev | text | inet | normal
pg_catalog | abs | bigint | bigint | normal
pg_catalog | abs | double precision | double precision | normal
pg_catalog | abs | integer | integer | normal
pg_catalog | abs | numeric | numeric | normal
pg_catalog | abs | real | real | normal
pg_catalog | abs | smallint | smallint | normal
pg_catalog | abstime | abstime | timestamp without time zone | normal
pg_catalog | abstime | abstime | timestamp with time zone | normal
pg_catalog | abstimeeq | boolean | abstime, abstime | normal
pg_catalog | abstimege | boolean | abstime, abstime | normal
pg_catalog | abstimegt | boolean | abstime, abstime | normal
pg_catalog | abstimein | abstime | cstring | normal
pg_catalog | abstimele | boolean | abstime, abstime | normal
pg_catalog | abstimelt | boolean | abstime, abstime | normal
pg_catalog | abstimene | boolean | abstime, abstime | normal
pg_catalog | abstimeout | cstring | abstime | normal
pg_catalog | abstimerecv | abstime | internal | normal
pg_catalog | abstimesend | bytea | abstime | normal
(责任编辑：IT) |