PostgreSQL快速入门

robch
已于 2023-12-01 14:38:53 修改
阅读量204
点赞数
分类专栏：数据库文章标签： postgresql 数据库
于 2023-05-08 12:05:15 首次发布
本文链接：https://blog.csdn.net/cpxsxn/article/details/130556274
版权
数据库专栏收录该内容
35 篇文章 0 订阅
订阅专栏
文章描述了一位用户在Linux环境下安装和初始化PostgreSQL14的过程，包括启动服务和创建数据库。接着，用户遇到了权限问题，通过切换用户和设置权限解决了访问和操作数据库的限制。此外，文章还讨论了数据库和模式的概念，以及如何备份和恢复特定schema的表，最后提到了pg_dump工具的使用。
摘要由CSDN通过智能技术生成
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
sudo systemctl enable postgresql-14
sudo systemctl start postgresql-14
https://www.postgresql.org/download/linux/redhat/



psql -h 127.0.0.1 -p 5432 -U cpx  -d cpx -c "create schema bbb"



[root@dbs-test-rob ~]# 
[root@dbs-test-rob ~]# psql
psql: 错误: 连接到套接字"/var/run/postgresql/.s.PGSQL.5432"上的服务器失败:FATAL:  role "root" does not exist
[root@dbs-test-rob ~]#


[root@dbs-test-rob ~]# su - postgres
上一次登录：四 4月 13 18:00:06 CST 2023pts/1 上
-bash-4.2$
-bash-4.2$ 
-bash-4.2$ psql
psql (14.7)
Type "help" for help.
postgres=#
postgres=#
postgres=#


[root@dbs-test-rob ~]# sudo -u postgres psql
could not change directory to "/root": 权限不够
psql (14.7)
Type "help" for help.
postgres=#
postgres=#
postgres=#
postgres=#



[root@dbs-test-rob ~]# sudo -i -u postgres psql    //  psql -h 127.0.0.1 -p 5432 -U cpx  -d cpx
psql (14.7)
输入 "help" 来获取帮助信息.
postgres=#


[root@dbs-test-rob ~]# sudo -i -u postgres
-bash-4.2$
-bash-4.2$
-bash-4.2$ psql
psql (14.7)
输入 "help" 来获取帮助信息.

postgres=#
postgres=#
postgres=#
postgres=# create database robdb;   // 每当我们创建一个新的数据库时，PostgreSQL都会为我们自动创建public模式。当登录到该数据库时，如果没有特殊的指定，我们将以该模式(public)的形式操作各种数据对象
CREATE DATABASE
postgres=#
postgres=#
postgres=#\l
                                     数据库列表
   名称    |  拥有者  | 字元编码 |  校对规则   |    Ctype    |       存取权限
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 robdb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 runoobdb  | 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
(5 行记录)

postgres=#
postgres=#
postgres=# \c robdb;
您现在已经连接到数据库 "robdb",用户 "postgres".
robdb=#
robdb=#
robdb=#
robdb=# CREATE TABLE DEPARTMENT(
robdb(#    ID INT PRIMARY KEY      NOT NULL,
robdb(#    DEPT           CHAR(50) NOT NULL,
robdb(#    EMP_ID         INT      NOT NULL
robdb(# );
CREATE TABLE
robdb=#
robdb=#
robdb=#
robdb=# \dt
                 关联列表
 架构模式 |    名称    |  类型  |  拥有者
----------+------------+--------+----------
 public   | department | 数据表 | postgres
(1 行记录)
robdb=#
robdb=#
robdb=#
robdb=# \d department
             数据表 "public.department"
  栏位  |     类型      | 校对规则 |  可空的  | 预设
--------+---------------+----------+----------+------
 id     | integer       |          | not null |
 dept   | character(50) |          | not null |
 emp_id | integer       |          | not null |
索引：
    "department_pkey" PRIMARY KEY, btree (id)

robdb=#
robdb=#
robdb=#
robdb=#
robdb=#
robdb=#
robdb=#
robdb=#
robdb=# create schema myschema;
CREATE SCHEMA
robdb=#
robdb=#
robdb=# \dn
    架构模式列表
   名称   |  拥有者
----------+----------
 myschema | postgres
 public   | postgres
(2 行记录)

robdb=#
robdb=#
robdb=# create table myschema.student(
robdb(#    ID   INT              NOT NULL,
robdb(#    NAME VARCHAR (20)     NOT NULL,
robdb(#    AGE  INT              NOT NULL,
robdb(#    ADDRESS  CHAR (25),
robdb(#    SALARY   DECIMAL (18, 2),
robdb(#    PRIMARY KEY (ID)
robdb(# );
CREATE TABLE
robdb=#
robdb=#
robdb=# \dt           // 只显示 search_path 中 schema 下的表，命令  show  search_path; 查看 search_path  的值
                 关联列表
 架构模式 |    名称    |  类型  |  拥有者
----------+------------+--------+----------
 public   | department | 数据表 | postgres
(1 行记录)

robdb=#
robdb=# select * from student;   // 当 search_path 的值为默认值时，“非限定名”访问用户只能访问到与 自己同名模式 下及 public 模式下的表; search_path 可以被修改也就是说：非限定名访问默认只能访问 search_path 中模式列表中的表。
ERROR:  relation "student" does not exist    // 当多个模式下出现同名表，使用非限定名访问表时，数据库系统搜索路径根据 search_path 配置的模式列表顺序依次检索，匹配到指定名称的表为止。
第1行select * from student;                   
                   ^
robdb=#
robdb=# select * from myschema.student;
 id | name | age | address | salary
----+------+-----+---------+--------
(0 行记录)

robdb=#
robdb=#
robdb=# show  search_path;    // 当用户登录数据库，默认使用的当前模式为 search_path 中模式列表排在第一位的模式。 可以通过 set search_path to public,"$user" (修改将public放置第一位，这是临时只限在当前回话有效);
   search_path                // 永久生效：1. alter system set search_path = "$user",public,myschema; (添加模式myschema到search_path)  2. SELECT pg_reload_conf(); 重新加载配置文件，是修改的search_path生效
-----------------
 "$user", public              // $user代表与当前会话用户同名的模式，public为公共模式，create database时默认创建;  
(1 行记录)
robdb=#
robdb=#
robdb=#
robdb=# select current_user;  // 如当前会话用户是 postgres, 由于数据库中没有名为 postgres 的模式，因此默认当前模式为 public。
 current_user
--------------
 postgres
(1 行记录)
robdb=#
robdb=#
robdb=# \dn
    架构模式列表
   名称   |  拥有者
----------+----------
 myschema | postgres
 public   | postgres
(2 行记录)
robdb=#
robdb=# select current_schema;
 current_schema
----------------
 public
(1 行记录)
robdb=#
robdb=#
robdb=#
robdb=#
robdb=#
robdb=#
robdb=#
robdb=#
robdb=# create user cpx with password 'mypwd';   // 创建新用户可以访问，指定 IP 需要修改配置文件 /var/lib/pgsql/14/data/pg_hba.conf   postgresql.conf   (HBA stands for host-based authentication.)
CREATE ROLE
robdb=# 
robdb=# 
robdb=# create database cpx;
CREATE DATABASE
robdb=#
robdb=#
robdb-# \l
                                     数据库列表
   名称    |  拥有者  | 字元编码 |  校对规则   |    Ctype    |       存取权限
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 robdb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 runoobdb  | 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
(5 行记录)

robdb-#


[root@dbs-test-rob ~]# psql -h 127.0.0.1 -p 5432 -U cpx    //  psql -h127.0.0.1 -p 5432 -U cpx -d sxn 链接到别的数据库
用户 cpx 的口令：
psql: 错误: 连接到"127.0.0.1"上的服务器，端口5432失败：FATAL:  database "cpx" does not exist  // 根据报错信息，需要创建数据库 cpx
[root@dbs-test-rob ~]# psql -h 127.0.0.1 -p 5432 -U cpx
用户 cpx 的口令：
psql (14.7)
输入 "help" 来获取帮助信息.

cpx=>
cpx=> select * from cbaplayer;    // 需要授权：表授权  grant ALL on cbaplayer  to cpx;     
ERROR: permission denied for table cbaplayer
cpx=> 
cpx=> // db授权：grant create on database robdb to cpx; schema   
cpx=> // 回收权限：revoke create on database robdb from cpx;
cpx=> // 授权：grant ALL on schema cpx to cpx;
cpx=> 
cpx=> select * from cbaplayer;
 id | name | age | address | salary
----+------+-----+---------+--------
(0 行记录)

cpx=>
cpx=> SELECT pg_catalog.has_table_privilege('cpx', 'cpx.books', 'INSERT'); // 检查有没有权限
 has_table_privilege
---------------------
 t
(1 行记录)

cpx=>


备份数据库表：通过非交互式为 pg_dump 指定密码？
    1. 可以指定 schemaA 全部备份 和 schemaB 部分备份吗？
    2. 如果在备份过程中，有无等待锁的问题？比如有用户在执行 DDL 等
    3. 你们 pg dump 的命令参数一般是什么？ 增加 -x -o 参数
    4. 怎么 show tables
    5. 你们在云舰上的备份情况，公有云只有物理备份，tpaas逻辑


    hello 现在对接 PostgreSQL 有些接口上的问题想听听你的意见？ 
        mysql 的数据组织方式是：实例--库--表， 
        postgresql 的数据组织方式是：实例--库--schema--表，备份计划只能对某个库进行备份，不能备份整个实例；
         a. 咱们就不加 show databases 了，直接 show tables;  否则接口改动特别大，


    SELECT nspname FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema';

备份命令：
    PGPASSWORD=mypwd pg_dump -h 127.0.0.1 -Ucpx -p 5432  cpx -x -O -n cpx -n public  > cpx_public.sql  // 备份指定的 schema 的所有表
    PGPASSWORD=mypwd pg_dump -h 127.0.0.1 -Ucpx -p 5432  cpx -x -O -t cpx.books  -t cpx.teacher -t public.cbaplayer  > cpx_public.sql   // 备份指定的 schema 的指定表；注意：对于单 schema 下的多表备份，备份文件中没有创建 schema 的语句
恢复命令：
    cat cpx_public.sql  | PGPASSWORD=mypwd psql -h127.0.0.1 -p 5432 -U cpx -d sxn

  



create table cpx.books(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AUTHOR  CHAR (25),
   PRIMARY KEY (ID)
);


create table teacher(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT,
   PRIMARY KEY (ID)
);