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;
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)
[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 '\"';"
评论区