PostgreSQL快速入门

PostgreSQL下载安装

windows上安装PostgreSQL

访问官网下载地址

https://www.enterprisedb.com/software-downloads-postgres

安装完以后需要设置远程服务

在pgsql的安装目录的data子目录找到pg_hha.conf文件

在IPV4部分添加新的一行：

host all all 0.0.0.0/0 md5

PostgreSQL的基本使用

登录

# psql -h 服务器 -U 用户名 -d 数据库 -p 端口地址
psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432

psql (连接数据库，默认用户和数据库都是postgres)

数据库操作

命令行

# 创建数据库
create database mydb;

#查看所有数据库
\l

#切换当前数据库
\c mydb

数据库表操作

创建表格时每列必须使用数据类型，PostgreSQL中主要有三类数据类型

• 数值数据类型
• 字符串数据类型
• 日期/时间数据类型

数值常用类型包括

名字	存储长度	描述	范围
smallint	2字节	小范围整数	-32768到+32767
integer	4字节	常用的整数	-2147483648到+2147483647
bigint	8字节	大范围整数	-9223372036854775808到+9223372036854775807
decimal	可变长度	用户指定的精度，精确	小数点前131072位；小数点后16383位
numeric	可变长度	用户指定的精度，精确	小数点前131072位；小数点后16383位
real	4字节	可变精度，不精确	6位十进制数字精度
double	8字节	可变精度，不精确	15位十进制数字精度

字符串类型包括

• char(size), character(size):固定长度字符串，size规定了需存储的字符数，由右边的空格补齐；
• varchar(size), character varying(size):可变长度字符串，size规定了需存储的字符数；
• text: 可变长度字符串。

日期/时间数据类型包括

• timestamp: 日期和时间；
• date：日期，无时间；
• time：时间

其他数据类型还有布尔值boolean (true或false)，货币数额 money和几何数据等。

PostgreSQL使用序列来表示字段的自增长，数据类型有smallserial、serial和bigserial。这些属性类似于MySQL数据库支持的AUTO_INCREMENT属性，

伪类型	存储大小	范围
smallserial	2字节	1到32767
serial	4字节	1到2147483647
bigserial	8字节	1到9223372036854775807

其他表操作基本于MySQL大同小异

示例：

# 创建表
create table test(
id serial primary key,
name varchar(255)
);

#插入
insert into test(name) values('java')

Schema

PostgreSQL的模式（Schema）模式可以看成一个表的集合。

一个模式可以包含视图、索引、数据类型、函数和操作费等。

相同的对象名称可以被用于不同的模式中而不会出现冲突，例如schema1和mychema都可以包含名为mytable的表。

使用模式的优势：

• 允许多个用户使用同一个数据库并且不会相互干扰。
• 将数据库对象组织成逻辑组以便更容易管理。
• 第三方应用的对象可以放在独立的模式中，这样他们就不会于其他对象的名称发生冲突。

模式类似于操作系统层的目录，但是模式不能嵌套。

# 创建schema
create schema myschema;

create table myschema.company...

如何备份PostgreSQL数据库

如果您在生产环境中使用PostgreSQL，请务必采取预防措施以确保用户的数据不会丢失。

单数据库

PostgreSQL提供了pg_dump实用程序来简化备份单个数据库的过程。必须以对要备份数据库具有读取权限的用户身份运行此命令。

以postgres用户登录：

sudo su - postgres

通过运行以下命令将数据库的内容转储到文件中，替换dbname为要备份的数据库名称。

pg_dump dbname > .dbname.bak

生成的备份文件dbname.bak可以使用scp传输到另一台主机，也可以存储在本地以供以后使用。

要演示恢复丢失的数据，请删除示例数据库并在其位置创建一个空数据库：

使用psql恢复数据库

psql test < dbname.bak

备份格式有几种选择：

• .bak:压缩二进制格式
• .sql: 明文转储
• .tar：tarball

注意:默认情况下，PostgreSQL将忽略备份过程中发送的任何错误。这可能导致备份不完整，要防止这种情况，您可以使用-1选项运行pg_dump命令，这会将整个备份过程视为单个事务，这将在发生错误时组织部分备份。

所有数据库

由于pg_dump一次只创建一个数据库的备份，因此它不会存储有关数据库角色或其他集群范围配置信息。要存储此信息并同时备份所有数据库，可以使用pg_dumpall。

创建备份文件：

pg_dumpall > pg_backup.bak

从备份还原所有数据库：

psql -f pg_backup.bak postgres

#备份数据库
pg_dump -U postgres -f /tmp/postgres.sql postgres(导出postgres数据库保存为postgres.sql)
pg_dump -U postgres -f /tmp/postgres.sql -t test postgres(导出postgres数据库中表test数据)
pg_dump -U postgres -F t -f /tmp/postgres.tar postgres(导出postgres数据库以tar形式压缩保存为postgres.tar)

# 恢复数据库
psql -U postgres -f /tmp/postgres.sql bk01 (恢复postgres.sql数据到bk01数据库)
#pg_restore -- 从pg_dump创建的备份文件中恢复postgreSQL数据库，用于恢复有pg_dump转储的任何非纯文本格式中的postgreSQL数据库。
ps_restore -U postgres -d bk01 /tmp/postgres.tar (恢复postgres.tar数据到bk01数据库)

用户操作

#创建用户并设置密码
create user 'username' with password 'password';
create user test with password 'test';

# 修改密码
alter user 'username' with password 'password';

# 数据库授权，赋予指定账户指定数据库所有权限
grant all privileges on database 'dbname' TO 'username';

#将数据库 mydb 权限授权于test
grant all privileges on database mydb to test;

# 注意， 该sql语句必须在所要操作的数据库里执行


# 移除指定账户指定数据库所有权限
remove all privileges on database mydb from test

# 删除用户
drop user test

PostgreSQL 角色管理

在PostgreSQL里没有区分用户和角色的概念，“create user" 为”create role“的别名，这两个命令几乎是完全相同的，唯一的区别就是”create user“ 命令创建的用户默认带有login属性，而“create role”命令创建的用户默认不带login属性。

创建david角色和sandy用户

create role davidl // 默认不带login属性

create user sandy； // 默认具有login属性

常用命令总结

\password：设置密码
\q:退出
\h：查看sql命令的解释，比如\h select
\?：查看psql命令列表
\l：列出所有数据库
\c：连接其他数据库
\d：列出当前数据库的所有表格
\d[table_name]：列出某一张表格的结构
\du:列出所有用户

Spring Boot中使用

第一步：创建一个基础的Spring Boot项目（如果您还不会，可以参考这篇文章：快速入门）

第二步：在<font style="color:rgb(51, 51, 51);background-color:rgb(243, 244, 244);">pom.xml</font>中引入访问PostgreSQL需要的两个重要依赖：

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

这里<font style="color:rgb(51, 51, 51);background-color:rgb(243, 244, 244);">postgresql</font>是必须的，<font style="color:rgb(51, 51, 51);background-color:rgb(243, 244, 244);">spring-boot-starter-data-jpa</font>的还可以替换成其他的数据访问封装框架，比如：MyBatis等，具体根据你使用习惯来替换依赖即可。因为已经是更上层的封装，所以基本使用与之前用MySQL是类似的，所以你也可以参考之前MySQL的文章进行配置，但数据源部分需要根据下面的部分配置。

第三步：在配置文件中为PostgreSQL数据库配置数据源、以及JPA的必要配置。

spring.datasource.url=jdbc:postgresql://localhost:5432/test
spring.datasource.username=postgres
spring.datasource.password=123456
spring.datasource.driver-class-name=org.postgresql.Driver

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.properties.hibernate.hbm2ddl.auto=create

第四步：创建用户信息实体，映射<font style="color:rgb(51, 51, 51);background-color:rgb(243, 244, 244);">user_info</font>表（最后完成可在pgAdmin中查看）

@Entity
@Data
@NoArgsConstructor
public class UserInfo {

    @Id
    @GeneratedValue
    private Long id;

    private String name;
    private Integer age;

    public UserInfo(String name, Integer age) {
        this.name = name;
        this.age = age;
    }
}

第五步：创建用户信息实体的增删改查

public interface UserInfoRepository extends JpaRepository<UserInfo, Long> {

    UserInfo findByName(String name);

    UserInfo findByNameAndAge(String name, Integer age);

    @Query("from UserInfo u where u.name=:name")
    UserInfo findUser(@Param("name") String name);

}

第六步：创建单元测试，尝试一下增删改查操作。

@Slf4j
@SpringBootTest
public class ApplicationTests {

    @Autowired
    private UserInfoRepository userRepository;

    @Test
    public void test() throws Exception {
        // 创建10条记录
        userRepository.save(new UserInfo("AAA", 10));
        userRepository.save(new UserInfo("BBB", 20));
        userRepository.save(new UserInfo("CCC", 30));
        userRepository.save(new UserInfo("DDD", 40));
        userRepository.save(new UserInfo("EEE", 50));
        userRepository.save(new UserInfo("FFF", 60));
        userRepository.save(new UserInfo("GGG", 70));
        userRepository.save(new UserInfo("HHH", 80));
        userRepository.save(new UserInfo("III", 90));
        userRepository.save(new UserInfo("JJJ", 100));

        // 测试findAll, 查询所有记录
        Assertions.assertEquals(10, userRepository.findAll().size());

        // 测试findByName, 查询姓名为FFF的User
        Assertions.assertEquals(60, userRepository.findByName("FFF").getAge().longValue());

        // 测试findUser, 查询姓名为FFF的User
        Assertions.assertEquals(60, userRepository.findUser("FFF").getAge().longValue());

        // 测试findByNameAndAge, 查询姓名为FFF并且年龄为60的User
        Assertions.assertEquals("FFF", userRepository.findByNameAndAge("FFF", 60).getName());

        // 测试删除姓名为AAA的User
        userRepository.delete(userRepository.findByName("AAA"));

        // 测试findAll, 查询所有记录, 验证上面的删除是否成功
        Assertions.assertEquals(9, userRepository.findAll().size());

    }

}

把单元测试跑起来：

一切顺利的话，因为这里用的是create策略，所以表还在，打开pgAdmin，可以看到user_info表自动创建出来了，里面的数据也可以查到，看看跟单元测试的逻辑是否符合。