win10安装配置使用PostgreSQL

心已赠人 2024-03-16 22:42 74阅读 0赞

## win10安装配置PostgreSQL ##

### 1 下载安装PostgreSQL ###

①进入官网https://www.postgresql.org/，点击页面中心处的download

> 也可以直接跳过下面的步骤(下面的步骤主要是为了帮助大家了解一般外国软件是如何从官网进入下载页面)，直接进入下载页面，链接地址：
> 
>  *  https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

②选择对应的操作系统，点击进入特定的下载页面

> 本文以windows10为例，点击windows

![在这里插入图片描述][0660f1a0739441789d877bd3179c39b1.png]  
③进入windows的下载页面，然后点击download the installer  
![在这里插入图片描述][90dacdbd7c1744949b96e844aed70f11.png]  
④选择对应系统的对应版本即可  
![在这里插入图片描述][b92cf7ee2eca4f8abfe3209a0ff1f41b.png]

下载完成后以管理员权限运行安装，安装过程都可以默认下一步

> `注意：`如果双击.exe文件报错：  
> ![在这里插入图片描述][acda8ad87fd248009159aac405274308.png]
> 
>  *  是因为我们当前用户名中包含中文，导致安装过程，因此我们只需要将win10用户名改为英文的即可

### 2 配置PostgreSQL ###

#### 2.1 配置环境变量 ####

①右击win10桌面上的此电脑，选择属性，然后选择高级系统设置  
![在这里插入图片描述][09a0b8f42b5a453eb1859e0623ca1ce3.png]  
②选择环境变量  
![在这里插入图片描述][538aeee505a245d5ba681c5a2712074e.png]  
③根据自己需求，看配置用户环境变量还是系统环境变量

> 用户环境变量，只针对当前用户，系统环境变量则针对该电脑所有用户
> 
>  *  一般配置系统环境变量

在系统环境变量下，选择新建  
![在这里插入图片描述][b6a00093152a471ea518e8b2a0dece6d.png]

# 新建PG_HOME，对应的值填入自己postgre的安装目录
    E:\database\PostgreSQL
    # 在path下追加%PG_HOME%\bin\
    %PG_HOME%\bin\
    # 新建PG_DATA
    E:\database\PostgreSQL\data

#### 2.2 初始化 ####

①安装完成后我们需要对数据库进行初始化，初始化方法是执行initdb.exe.  
![在这里插入图片描述][29891a70be974bf0b63bb5d752e6910e.png]

> E:\\database\\PostgreSQL\\data 初始化执行之前是空的，执行之后会看到很多文件  
> ![在这里插入图片描述][4a547a68baa64da5a5b5f58c006618ca.png]

②修改data目录下的配置文件postgresql.conf

> 如果仅想本地访问则`listen_addresses`改为localhost，如果想配置都访问，则配置为`*`

#listen_addresses = 'localhost'		# what IP address(es) to listen on;
    			# comma-separated list of addresses;
    			# defaults to 'localhost'; use '*' for all
    			# (change requires restart)

### 3 启动并测试 ###

> 因为我们开始配置了环境变量，所以任意位置进入cmd，然后执行

psql -U postgres

然后输入我们开始安装过程中设置的密码，即可：  
![在这里插入图片描述][d01bc65846ca437a95867b51b69be6a8.png]

*  测试通过navicat连接  
    ![在这里插入图片描述][f3913d57c7ee4cc1b48addd5fc336676.png]  
    点击，测试连接，结果：  
    ![在这里插入图片描述][ecdc2c08232647138623d91c9477d8fe.png]

### 4 相关命令 ###

#### 4.1 创建用户 ####

# 创建用户
    CREATE USER testUser WITH PASSWORD '*****';

# yaml中url配置：
    # url: "postgres://user_name:pass_word@ip:port/database?sslmode=disable"
    url: "postgres://s3batch_u:postgres@127.0.0.1:5432/s3batch?sslmode=disable"

#### 4.2 注册为系统服务 ####

pg_ctl register -N "postgresql" -D "C:\Program Files\PostgreSQL\your_postgresql_version\data" -U "your_username" -P "your_password"
    
    # 或者
    sc create postgresql binPath= "E:\database\PostgreSQL\bin\pg_ctl.exe runservice -N postgresql -D "E:\database\PostgreSQL\data" -w" start= auto

#### 4.3 操作数据库或表 ####

-- 以postgres用户角色登录postgresql数据库
    psql -U postgres;
    
    --
    ①postgres-#：短横杠的表明语句未结束，用分号标识结束
    ②postgres=#：postgres后面是等号的表明语句已经结束，可以开始一个新的语句
    --
    
    -- 切换数据库[以workflow_u的用户角色切换到workflow数据库]
    \c workflow workflow_u
    
    -- 展示当前数据库下所有的表
    \d
    
    -- 展示userinfo的表结构
    \d userinfo
    
    
    -- 创建表
    CREATE TABLE userdetail
    (
        uid integer,
        intro character varying(100),
        profile character varying(100) -- 类似于mysql的varchar(100)
    )
    WITH(OIDS=FALSE);
    
    -- 添加记录
    insert into userdetail (uid, intro, profile) values (1, '这是一个介绍', '这个是个人资料');
    
    -- 查询表中记录记录[起始记录从0开始，每页5条数据]
    select uid, intro, profile from public.userdetail offset 0 limit 5;
    
    -- 根据创建时间降序排列
    select uid,intro from userdetail  order by created_time desc offset 0 limit 1;
    
    -- 删除表中的intro字段
    alter table userdetail drop column intro;
    
    -- 向表中添加name字段
    alter table userdetail add column name character varying(100);
    
    -- 更新表中记录
    update userdetail set name = 'jack' where uid = 1;
    
    -- 给uid添加主键
    alter table userdetail drop constraint if exists userdetail_pkey, add primary key(uid);
    
    -- 给name添加唯一索引
    create unique index if not exists idx_userdetail_name on userdetail (name);
    
    -- 添加时间字段，并插入一条记录 timestamp：2023-06-30 16:44:41.660549
    alter table userdetail add column created_time timestamp;
    -- insert into userdetail(uid, profile, name, created_time) values(4, 'intro4', 'tom4', ' 2022-06-30 16:44:41.660549');
    insert into userdetail(uid, profile, name, created_time) values(2, '介绍2', 'tom', now());
    
    
    -- 创建序列sequence
    CREATE SEQUENCE uid_sequence
        AS integer
        START WITH 1
        INCREMENT BY 1
        NO MINVALUE
        NO MAXVALUE
        CACHE 1;
     
     -- 删除序列
     DROP SEQUENCE IF EXISTS uid_sequence;
    
       
    -- 修改表结构，将 uid 设置为序列的默认值
    ALTER TABLE userdetail 
        ALTER COLUMN uid SET DEFAULT nextval('uid_sequence'::regclass);    
    
    -- 删除表
    drop table userdetail;

# \l 列出所有数据库
    \l
    
    # \c 切换数据库
    \c testbase
    
    # \d 查询当前数据库下的所有表
    \d 
    # \d table_name 查看表结构
    
    # \d table_name 查看表的信息（如果有索引也会展示出来）
    \d mytable
    
    # \di 查看数据库的所有索引,在postgres=#模式下执行
    \di
    
    # \q 退出控制台
    \q

#### 4.4 备份与恢复 ####

> 1.  SQL 转储
> 2.  文件系统级别备份（冷备份）
> 3.  在线热备份（归档）

##### 4.4.1 SQL转储 #####

###### ①pg\_dump：备份单数据库（xxx.dmp） ######

# 1 创建数据库
    createdb pg
    # 2 连接数据库
    psql pg
    # 3 创建测试表，插入数据
    pg=# create table pg_test(a int);
    pg=# insert into pg_test(a) values(1);
    pg=# insert into pg_test(a) values(2);
    # 4 查看数据
    pg=# select * from tb;
    # 5 备份
    pg_dump pg > /usr/local/pgsql/backup/pg.dmp
    # 6 删除数据库pg
    dropdb pg
    # 7 创建新数据库（恢复之前需要创建数据库）
    createdb pg
    # 8 恢复数据
    psql pb < /usr/local/pgsql/backup/pg.dmp
    # 9 查看数据是否恢复
    pg=# select * from tb;

至此，数据已成功恢复！  
注： pg\_dump 可以对针对单表或者多表进行备份  
如：

> pg\_dump databasename –t tablename1 –t tablename2 >filename

###### ②pg\_dumpall：备份所有数据库、角色、表空间 ######

> pg\_dump 只能备份单个数据库，而且恢复的时候需要创建空数据库。 pg\_dumpall 可以备份所有数据库，并且备份角色、表空间。

# 1 创建数据库
    createdb pg1
    createdb pg2
    # 2 向pg1中创建表并插入数据
    psql pg1
    pg=# create table tb1(a int)
    pg=# insert into tb1(a) values(1);
    # 3 pg2 中创建表并插入数据
    psql pg2
    pg=# create table tb2(a int)
    pg=# insert into tb2(a) values(2);
    # 4 备份数据库
    pg_dumpall > /usr/local/pgsql/backup/pg_all.dmp
    # 5 删除数据库
    dropdb pg1
    dropdb pg2
    # 6 恢复数据库
    psql –f /usr/local/pgsql/backup/pg_all.dmp postgres
    # 可以指定任何数据库名，如果恢复到一个空的集群中，通常使用 postgres 数据库
    # 7 查看数据库 pg1 是否恢复
    psql pg1
    pg=# select * from tb1;
    # 8 查看数据库 pg2 是否恢复
    psql pg2
    pg=# select * from tb2;

至此，数据已成功恢复！

##### 4.4.2 文件系统级别备份（冷备份:需要停止db） #####

> 文件系统级别的备份是冷备份，需要停止数据库。

# 1 停止数据库
    pg_ctl –D /usr/local/pgsql/data stop
    # 2 备份数据库
    tar –jcv –f /usr/local/pgsql/backup/filesystem.tar.bz2 /usr/local/pgsql/data/
    # 3 删除 /usr/local/pgsql/data/ 目录
    rm –r /usr/local/pgsql/data/
    # 4 解压备份文件到原目录
    tar –jxv –f /usr/local/pgsql/backup/filesystem.tar.bz2 –C /
    # 5 启动数据库
    pg_ctl –D /usr/local/pgsql/data start
    # 6 查看数据库 pg1 是否恢复
    psql pg1
    pg=# select * from tb1;
    # 7 查看数据库 pg2 是否恢复
    psql pg2
    pg=# select * from tb2;

至此，数据已成功恢复！

##### 4.4.3 在线热备份（归档） #####

###### ①备份 ######

1.  配置归档模式

# 配置归档需要编辑 postgresql.conf 文件，默认为与 /usr/local/pgsql/data/ 目录下
    vim /usr/local/pgsql/data/postgesql.conf
    archive_mode = on
    archive_command = ‘cp %p /usr/local/pgsql/backup/archived_log/%f’

注： %p 要被归档的日志文件的路径， %f 是要被归档的日志文件的文件名

1.  启动数据库

pg_ctl –D /usr/local/pgsql/data start

1.  创建数据库 arch

createdb arch

1.  创建表并插入记录

psql arch
    arch=# create table tb(a int);
    arch=# insert into tb(a) values(1);

1.  创建备份

arch=# select pg_start_backup(‘baseline’);

1.  备份整个 data 目录

tar –jcv –f /usr/local/pgsql/backup/baseline.tar.bz2 /usr/local/pgsql/data/

1.  停止备份

psql arch
    arch=# select pg_stop_backup();

1.  插入新记录，然后切换日志，重复 3 次

arch=# insert into tb(a) values(2);
    arch=# select pg_switch_xlog();
    arch=# insert into tb(a) values(3);
    arch=# select pg_switch_xlog();
    arch=# insert into tb(a) values(4);
    arch=# select pg_switch_xlog();

1.  把 /data/pg\_xlog/ 下的 WAL 日志文件复制到预设的归档目录下，保证产生的 WAL 日志都已归档。

###### ②恢复 ######

# 1 停止数据库
    pg_ctl –D /usr/local/pgsql/data/ stop
    # 2 删除 /data/
    rm –r /usr/local/pgsql/data/
    # 3 恢复备份
    tar –jxv –f /usr/local/pgsql/backup/baseline.tar.bz2 –C /
    # 4 清空 /data/pg_xlog/ 目录下所有文件
    rm –r /usr/local/pgsql/data/pg_xlog/
    # 5 创建 /pg_xlog/ 及其下面的 archive_status 目录
    mkdir /usr/local/pgsql/data/pg_xlog/
    mkdir /usr/local/pgsql/data/pg_xlog/archive_status
    # 6 在 /data/ 目录下创建 recovery.conf
    vim /usr/local/pgsql/data/recovery.conf
    restore_command = ‘cp /usr/local/pgsql/backup/archived_log/%f “%p”’
    # 7 启动数据库
    pg_ctl –D /usr/local/pgsql/data/ start

一切正常的话数据库就会自动应用 WAL 日志进行恢复

# 8 查看数据库 arch 是否恢复
    psql arch
    arch=# select * from tb;

至此，数据已经成功恢复！

参考：https://blog.csdn.net/gbz2000/article/details/114382382

#### 4.5 DDL：Data Definition Language，create、drop、alter ####

>  *  DML(Data Manipulation Language，数据操纵语言):insert、update、delete
>  *  DCL(Data Control Language，数据控制语言)：创建、修改、删除用户等
>  *  DQL(Data Query Language，数据查询语言)

###### 4.5.1 修改字段类型 ######

-- 将表activity_detail 字段update_time 从date 修改成timestamp
    alter table activity_detail  alter column update_time  type timestamp using update_time::timestamp;

### 5 bug ###

#### 5.1 pq: SSL is not enabled on the server ####

# 在datasource中设置sslmode=disable
    datasource := "user=postgres password=*** host=localhost port=5432 dbname=testgo sslmode=disable"

#### 5.2 控制台乱码 ####

# 连接到数据库
    psql -U your_username -d your_database_name
    
    # 查看当前编码
    SHOW client_encoding;

打开 PostgreSQL 的配置文件 postgresql.conf。该文件通常位于 PostgreSQL 安装目录下的 data 子目录中。

在文件中找到 client\_encoding 参数，并将其值修改为 UTF8

client_encoding = 'UTF8'

保存，并关闭文件，然后重启psql

> net stop postgresql  
> net start postgresql

### 6 字段概念 ###

#### 6.1 时间相关 ####

-- 将表activity_detail 字段update_time 从date 修改成timestamp
    alter table activity_detail  alter column update_time  type timestamp using update_time::timestamp;

##### 6.1.1 date：日期，YYYY-MM-DD #####

##### 6.1.2 time：时间，HH:MI:SS #####

##### 6.1.3 timestamp：日期时间，YYYY-MM-DD HH:MI:SS #####

##### 6.1.4 timestamptz：带有时区的时间，YYYY-MM-DD HH:MI:SS TZ #####

##### 6.1.5 interval：时间间隔,可以是年、月、日、小时、分钟、秒等 #####

##### 6.1.6 时区偏移量：timezonetz，+HH:MI或-HH:MI #####

[0660f1a0739441789d877bd3179c39b1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/5352354851994273a11ddbf3d9c6d448.png
[90dacdbd7c1744949b96e844aed70f11.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/005a62a361a84c939df10eca9b7b433e.png
[b92cf7ee2eca4f8abfe3209a0ff1f41b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/6f0cd82f511d4cee942757660f53e9b2.png
[acda8ad87fd248009159aac405274308.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/e296c82847794d23aa68edaa67e2a873.png
[09a0b8f42b5a453eb1859e0623ca1ce3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/3e6ada4e8a5f40bbb32b58986f49dcd7.png
[538aeee505a245d5ba681c5a2712074e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/9134dcbcceeb4d37b4f904d0345e6f6a.png
[b6a00093152a471ea518e8b2a0dece6d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/f1acbab971d844bdb0d4e55a14c2c28f.png
[29891a70be974bf0b63bb5d752e6910e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/6136ba2c85204d8ea188bf46a982523d.png
[4a547a68baa64da5a5b5f58c006618ca.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/6dda8a7a800a46e5bedf9c9626e7d7e5.png
[d01bc65846ca437a95867b51b69be6a8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/bc13bd209f084c06b3cb190833ece4fe.png
[f3913d57c7ee4cc1b48addd5fc336676.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/773834b30ebc445096af2b14d1bf22da.png
[ecdc2c08232647138623d91c9477d8fe.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/4bc03a67f96a4caa9c75876d5a692879.png