> 数据库 > PostgreSQL >

第 4 课 PostgreSQL 安装最新的版本

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)