【DB宝70】在Windows中绿色安装PostgreSQL 13.3数据库

1、安装概述
2、下载Windows的二进制格式
3、解压安装包
4、配置环境变量
5、初始化数据库
6、启动数据库
7、注册系统服务，需管理员权限方可
8、配置远程登录
9、登陆数据库

on error resume next
set sysenv=CreateObject("WScript.Shell").Environment("system") '系统环境变量的数组对象
Path = CreateObject("Scripting.FileSystemObject").GetFolder(".").Path
'添加变量
sysenv("PGHOME")="D:\db\PostgreSQL\13.3"
sysenv("PGHOST")="localhost"
sysenv("Path")=sysenv("PGHOME")+"\bin;"+sysenv("Path")
sysenv("PGLIB")=sysenv("PGHOME")+"\lib"
sysenv("PGDATA")=sysenv("PGHOME")+"\data"

wscript.echo "PostgreSQL环境变量安装成功！不需要重新启动计算机！"

SETX "PGHOME"  "D:\db\PostgreSQL\13.3"
SETX "PGHOST"  "localhost"
SETX "PGLIB"   "%PGHOME%\lib"
SETX "PGDATA"  "%PGHOME%\data"
SETX "PATH"   "%%PGHOME%%\bin;%path%"

C:\Users\lhrxxt>D:\db\PostgreSQL\13.3\bin\initdb.exe -D "D:\db\PostgreSQL\13.3\data" -E UTF-8 --locale=chs -U postgres -W
The files belonging to this database system will be owned by user "lhrxxt".
This user must also own the server process.

The database cluster will be initialized with locale "Chinese_China.936".
initdb: could not find suitable text search configuration for locale "Chinese_China.936"
The default text search configuration will be set to "simple".

Data page checksums are disabled.

Enter new superuser password:
Enter it again:

creating directory D:/db/PostgreSQL/13.3/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... windows
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Hong_Kong
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    ^"D^:^\db^\PostgreSQL^\13^.3^\bin^\pg^_ctl^" -D ^"D^:^\db^\PostgreSQL^\13^.3^\data^" -l logfile start

pg_ctl -D D:\db\PostgreSQL\13.3\data -l logfile start

pg_ctl register -N PostgreSQL -D D:\db\PostgreSQL\13.3\data

net start PostgreSQL 

listen_addresses='*'

# TYPE  DATABASE    USER    ADDRESS       METHOD
local     all       all                    trust
host      all       all    ::1/128         trust
host      all       all   127.0.0.1/32     trust
host      all       all    0.0.0.0/0        md5
host   replication  all    0.0.0.0/0        md5

-- 本地登陆
psql -U postgres

-- 远程登陆
psql -U postgres -h 192.168.66.35 -d postgres -p5432

-- 从Postgresql 9.2开始，还可以使用URI格式进行远程连接：psql postgresql://myuser:mypasswd@myhost:5432/mydb
psql postgresql://postgres:lhr@192.168.66.35:15432/postgres

C:\Users\lhrxxt>psql -U postgres -h 192.168.66.236 -d postgres -p5432
Password for user postgres:
psql (13.3)
Type "help" for help.

postgres=# \l
                                        List of databases
   Name    |  Owner   | Encoding |      Collate      |       Ctype       |   Access privileges
-----------+----------+----------+-------------------+-------------------+-----------------------
 postgres  | postgres | UTF8     | Chinese_China.936 | Chinese_China.936 |
 template0 | postgres | UTF8     | Chinese_China.936 | Chinese_China.936 | =c/postgres          +
           |          |          |                   |                   | postgres=CTc/postgres
 template1 | postgres | UTF8     | Chinese_China.936 | Chinese_China.936 | =c/postgres          +
           |          |          |                   |                   | postgres=CTc/postgres
(3 rows)


postgres=# CREATE DATABASE lhrdb WITH OWNER=postgres ENCODING='UTF-8';
CREATE DATABASE
postgres=# \c lhrdb
You are now connected to database "lhrdb" as user "postgres".
lhrdb=#
lhrdb=# create table student (
lhrdb(#   id integer not null,
lhrdb(#   name character(32),
lhrdb(#   number char(5),
lhrdb(#   constraint student_pkey primary key (id)
lhrdb(# );
CREATE TABLE
lhrdb=#
lhrdb=# \d student
                 Table "public.student"
 Column |     Type      | Collation | Nullable | Default
--------+---------------+-----------+----------+---------
 id     | integer       |           | not null |
 name   | character(32) |           |          |
 number | character(5)  |           |          |
Indexes:
    "student_pkey" PRIMARY KEY, btree (id)


lhrdb=#
lhrdb=# INSERT INTO student (id, name, number) VALUES (1, '张三', '1023');
INSERT 0 1
lhrdb=# SELECT * FROM student WHERE id=1;
 id |                name                | number
----+------------------------------------+--------
  1 | 张三                               | 1023
(1 row)

★DB宝分享的IT资料：https://mp.weixin.qq.com/s/Iwsy-zkzwgs8nYkcMz29ag
★DB宝笔试面试详解：https://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w