Postgres基础篇

PostgreSQL基础篇

yum安装PostgreSQL

• 具体安装命令

yum install -y epel-release
yum install -y postgresql-server postgresql-contrib
postgresql-setup initdb
systemctl start postgresql
systemctl enable postgresql
psql --version
systemctl status postgresql
su - postgres
[postgres@cdesk_node_2 ~]$  psql --version
psql (PostgreSQL) 9.2.24

PostgreSQL基本设置

• 修改密码设置远程访问

[postgres@cdesk_node_2 ~]$ psql  
psql (9.2.24)
Type "help" for help.

postgres=# ALTER USER postgres WITH PASSWORD 'd@a88SZVCn72'; ALTER ROLE

#修改postgresql.conf 配置文件
[postgres@cdesk_node_2 ~]$ vim /var/lib/pgsql/data/postgresql.conf #修改远程访问
listen_addresses = '*'          # what IP address(es) to listen on;

#修改新增配置项pg_hba.conf文件
host    all             all             127.0.0.1/32           ident
host    all             all              0.0.0.0/0              md5

• 重启PostgreSQL服务,并远程连接PostgreSQL数据库

[root@cdesk_node_2 ~]# systemctl restart postgresql.service
[root@cdesk_node_2 ~]# psql -h10.130.41.128 -Upostgres -W
#输入密码d@a88SZVCn72
Password for user postgres: 
psql (9.2.24)
Type "help" for help.

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)

• 创建用户，并给超级权限

#创建用户时指定SUPERUSER选项。这将使得该用户拥有所有权限，包括管理数据库和用户的权限
CREATE USER ucds WITH PASSWORD 'd@a88SZVCn72' SUPERUSER;

• 创建数据库并使用多个SCHEMA

postgres=# CREATE DATABASE ucds;




#链接到ucds库中
postgres=# \c ucds;
Password for user ucds: 
You are now connected to database "ucds" as user "ucds".

ucds=# CREATE SCHEMA ccnp;  #创建ccnp schema

ucds=# set  search_path TO ccnp;  #设置搜索schema路径
SET

ucds=# CREATE TABLE test (
ucds(#   id SERIAL PRIMARY KEY,
ucds(#   name VARCHAR(255)
ucds(# );  #创建test表
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE

ucds=# INSERT INTO test (name) VALUES ('John Doe');  #创建
INSERT 0 1


ucds=# select * from  test;
 id |   name   
----+----------
  1 | John Doe
(1 row)

• 使用ccnp–>schema导入sql文件

[postgres@test ~]$ more test.sql
CREATE TABLE test2 (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255)
);
INSERT INTO test2 (name) VALUES ('John Doe');

postgres-# \c  ucds
ucds=# set  search_path TO ccnp; 
ucds=# \i test.sql ;

ucds=# select * from test2;
 id |   name   
----+----------
  1 | John Doe
(1 row)

PostgreSQL元命令

• 元命令

ucds=# \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \h [NAME]              help on syntax of SQL commands, * for all commands
  \q                     quit psql

Query Buffer
  \e [FILE] [LINE]       edit the query buffer (or file) with external editor
  \ef [FUNCNAME [LINE]]  edit function 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)

Informational
  (options: S = show system objects, + = additional detail)
  \d[S+]                 list tables, views, and sequences
  \d[S+]  NAME           describe table, view, sequence, or index
  \da[S]  [PATTERN]      list aggregates
  \db[+]  [PATTERN]      list tablespaces
  \dc[S+] [PATTERN]      list conversions
  \dC[+]  [PATTERN]      list casts
  \dd[S]  [PATTERN]      show object descriptions not displayed elsewhere
  \ddp    [PATTERN]      list default privileges
  \dD[S+] [PATTERN]      list domains
  \det[+] [PATTERN]      list foreign tables
  \des[+] [PATTERN]      list foreign servers
  \deu[+] [PATTERN]      list user mappings
  \dew[+] [PATTERN]      list foreign-data wrappers
  \df[antw][S+] [PATRN]  list [only agg/normal/trigger/window] functions
  \dF[+]  [PATTERN]      list text search configurations
  \dFd[+] [PATTERN]      list text search dictionaries
  \dFp[+] [PATTERN]      list text search parsers
  \dFt[+] [PATTERN]      list text search templates
  \dg[+]  [PATTERN]      list roles
  \di[S+] [PATTERN]      list indexes
  \dl                    list large objects, same as \lo_list
  \dL[S+] [PATTERN]      list procedural languages
  \dn[S+] [PATTERN]      list schemas
  \do[S]  [PATTERN]      list operators
  \dO[S+] [PATTERN]      list collations
  \dp     [PATTERN]      list table, view, and sequence access privileges
  \drds [PATRN1 [PATRN2]] list per-database role settings
  \ds[S+] [PATTERN]      list sequences
  \dt[S+] [PATTERN]      list tables
  \dT[S+] [PATTERN]      list data types
  \du[+]  [PATTERN]      list roles
  \dv[S+] [PATTERN]      list views
  \dE[S+] [PATTERN]      list foreign tables
  \dx[+]  [PATTERN]      list extensions
  \l[+]                  list all databases
  \sf[+] FUNCNAME        show a function's definition
  \z      [PATTERN]      same as \dp

Formatting
  \a                     toggle between unaligned and aligned output mode
  \C [STRING]            set table title, or unset if none
  \f [STRING]            show or set field separator for unaligned query output
  \H                     toggle HTML output mode (currently off)
  \pset NAME [VALUE]     set table output option
                         (NAME := {format|border|expanded|fieldsep|fieldsep_zero|footer|null|
                         numericlocale|recordsep|recordsep_zero|tuples_only|title|tableattr|pager})
  \t [on|off]            show only rows (currently off)
  \T [STRING]            set HTML <table> tag attributes, or unset if none
  \x [on|off|auto]       toggle expanded output (currently off)

Connection
  \c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
                         connect to new database (currently "ucds")
  \encoding [ENCODING]   show or set client encoding
  \password [USERNAME]   securely change the password for a user
  \conninfo              display information about current connection

Operating System
  \cd [DIR]              change the current working directory
  \setenv NAME [VALUE]   set or unset environment variable
  \timing [on|off]       toggle timing of commands (currently off)
  \! [COMMAND]           execute command in shell or start interactive shell

Variables
  \prompt [TEXT] NAME    prompt user to set internal variable
  \set [NAME [VALUE]]    set internal variable, or list all if no parameters
  \unset NAME            unset (delete) internal variable

Large Objects
  \lo_export LOBOID FILE
  \lo_import FILE [COMMENT]
  \lo_list
  \lo_unlink LOBOID      large object operations

• 常用命令

# 1. 退出登录
postgres=# \q

# 2. 获取帮助信息
postgres=# \h
postgres=# \h create table

# 3. 查看\相关命令
postgres=# \?

# 4. 数据库版本
postgres=# select version();
postgres=# show server_version;

# 5. 查看有哪些数据库
postgres=# \l
postgres=# \l+
postgres=# select oid,datname from pg_database;

# 6. 查看当前登录的数据库
postgres=# select current_database();

# 7. 切换数据库
postgres=# \c test

# 8. 切换用户
postgres=# \c - zhangsan

# 9. 查看当前用户
postgres=# select user;
postgres=# select current_user;

# 10. 查看当前数据库下所有表
postgres=# \d
postgres=# select tableowner,schemaname,tablename,tablespace from pg_tables where tableowner='postgres';

# 11. 查看登录信息
postgres=# \conninfo

# 12. 查看当前连接信息
postgres=# select * from pg_stat_activity;

# 13. 查看表结构
postgres=# \d demo;
postgres=# \d+ demo;
postgres=# select table_schema,table_name,column_name,data_type,character_maximum_length from information_schema.columns where table_name='demo';

# 14. 查看角色信息
postgres=# select rolname,rolsuper,rolcreatedb from pg_roles;

# 15. 查看用户角色
postgres=# \dg
postgres=# \du

# 16. 查看用户表权限
postgres=# select * from information_schema.table_privileges where grantee = 'cjc';
postgres=# \dp

# 16. 查看表空间
postgres=# \db

# 17. 格式化输出\pset
postgres=# select * from demo;
 id | age
----+-----
  1 |  50
  2 |  18
(2 行记录)
postgres=# \pset border 2;
边缘风格是 2.
postgres=# select * from demo;
+----+-----+
| id | age |
+----+-----+
|  1 |  50 |
|  2 |  18 |
+----+-----+
(2 行记录)
# 18. 取消边框
postgres=# \pset border 0;
边缘风格是 0.
postgres=# select * from demo;
id age
-- ---
 1  50
 2  18

# 19. 显示信息
postgres=# \echo hahaha
hahaha

# 20. 执行SQL脚本
cat t1.sql  
psql -h 127.0.0.1 -p 5432 -U postgres -W cjcdb -f t1.sql 

备份/还原、导出/导入

• 备份/还原
• 备份共有四种方式，Custom、Tar、Directory以及Plain，使用–format参数进行区分。还原时不需要指定该参数，工具会自行判断。对于Direcotry方式，备份出来是一个文件夹，还原时路径也是指定到该文件夹。对于Plain方式，备份出来是一个SQL文件，不能使用还原工具（pg_restore）进行恢复，但是可以用psql命令进行。
• 表：对于表，使用 --table 参数即是备份指定表，例如：–table “public.tab” ，public为模式名，tab为表名。
• 模式：对于模式，使用 --schema 参数即是备份指定模式，例如：–schema “public”
• 数据库：对于数据库，不加 --table 或 --schema 参数，即是备份整个数据库。
  如下所示，为表的备份和还原

--format=c       # Custom方式
--format=t       # Tar方式
--format=p       # Plain方式
--format=d       # Diretory方式

# 备份：
pg_dump --file "/tmp/tab.backup" --host "localhost" --port "5432" --username "postgres" --verbose --format=c --blobs --table "public.tab" "postgres"

# 还原：
pg_restore --host "localhost" --port "5432" --username "postgres" --dbname "postgres" --verbose --schema "public" --table "tab" "/tmp/tab.backup"

# 使用psql还原plain方式的备份
psql --host "localhost" --port "5432" --username "postgres" --dbname "postgres" --file "/tmp/tab.backup"

• 导出/导入
• 导出/导入只针对表（数据），都使用psql命令进行操作。导出与导入命令格式基本一致，区别仅在于导出使用"copy to"，而导入使用"copy from"。默认导出格式为text，若要导出CSV格式，则需要加上CSV相关参数，如下所示。

psql --port 5432 --username postgres --dbname postgres --command "\copy public.tab (id, name) TO '/tmp/tab.text';"

# 导入text格式
psql --port 5432 --username postgres --dbname postgres --command "\copy public.tab (id, name) FROM '/tmp/tab.text';"

# 导出CSV格式
psql --port 5432 --username postgres --dbname postgres --command "\copy public.tab (id, name) TO '/tmp/tab.csv' CSV QUOTE '\"' ESCAPE '\"';"

# 导入CSV格式
psql --port 5432 --username postgres --dbname postgres --command "\copy public.tab (id, name) FROM '/tmp/tab.csv' CSV QUOTE '\"' ESCAPE '\"';"