【免责声明】本公众号文章仅供学习交流，观点代表个人，与任何公司无关。

编辑|SQL和数据库技术(ID:SQLplusDB)

• 一分钟安装和配置 PostgreSQL（基于Multipass 和Ubuntu 22.04）

• 通过Multipass创建虚拟机

• 安装PostgreSQL

• 1. 升级apt仓库为最新

• 2. 安装PostgreSQL

• 初始化配置PostgreSQL

• 默认用户登录（无需密码）

• 修改数据库用户密码登录

• 服务相关操作

一分钟安装和配置 PostgreSQL（基于Multipass 和Ubuntu 22.04）

通过Multipass创建虚拟机

我们通过multipass launch 命令创建一台虚拟机。

C:\Users\Administrator>multipass launch -n pg-vm
Launched: pg-vm


C:\Users\Administrator>multipass list
Name                    State             IPv4             Image
mysql-vm                Running           N/A              Ubuntu 22.04 LTS
oracle-vm               Running           N/A              Ubuntu 22.04 LTS
pg-vm                   Running           N/A              Ubuntu 22.04 LTS

C:\Users\Administrator>multipass info pg-vm
Name:           pg-vm
State:          Running
IPv4:           N/A
Release:        Ubuntu 22.04.2 LTS
Image hash:     9bbe9364e298 (Ubuntu 22.04 LTS)
Load:           0.29 0.24 0.09
Disk usage:     1.5G out of 4.7G
Memory usage:   169.3M out of 969.5M
Mounts:         --

C:\Users\Administrator>multipass shell pg-vm
Welcome to Ubuntu 22.04.2 LTS (GNU/Linux 5.15.0-71-generic x86_64)

 * Documentation:  https://help.ubuntu.com
 * Management:     https://landscape.canonical.com
 * Support:        https://ubuntu.com/advantage

  System information as of Wed May  3 14:22:04 CST 2023

  System load:  0.13427734375     Processes:               91
  Usage of :   31.1% of 4.67GB   Users logged in:         0
  Memory usage: 20%               IPv4 address for enp0s3: 10.0.2.15
  Swap usage:   0%


Expanded Security Maintenance for Applications is not enabled.

0 updates can be applied immediately.

Enable ESM Apps to receive additional future security updates.
See https://ubuntu.com/esm or run: sudo pro status


To run a command as administrator (user "root"), use "sudo <command>".
See "man sudo_root" for details.

ubuntu@pg-vm:~$

安装PostgreSQL

使用的主要命令：

sudo apt update
sudo apt show postgresql
--安装postgreSQL数据库
sudo apt install postgresql postgresql-contrib
--安装客户端
sudo apt install  postgresql-client
sudo apt install postgresql-client-common
--启动服务
sudo systemctl start postgresql.service

1. 升级apt仓库为最新

ubuntu@mysql-vm:~$ sudo apt update

2. 安装PostgreSQL

检查 Ubuntu 存储库 中可用的 PostgreSQL 版本

ubuntu@pg-vm:~$ sudo apt show postgresql

安装PostgreSQL

ubuntu@pg-vm:~$ sudo apt install postgresql postgresql-contrib

注：postgresql-contrib 或者说 contrib 包，包含一些不属于 PostgreSQL 核心包的实用工具和功能。在大多数情况下，最好将 contrib 包与 PostgreSQL 核心一起安装。

安装后查看pgsql相关的服务。

ubuntu@pg-vm:~$  systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Wed 2023-05-03 14:30:21 CST; 18s ago
    Process: 4843 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 4843 (code=exited, status=0/SUCCESS)
        CPU: 2ms

May 03 14:30:21 pg-vm systemd[1]: Starting PostgreSQL RDBMS...
May 03 14:30:21 pg-vm systemd[1]: Finished PostgreSQL RDBMS.

安装PostgreSQL客户端。

ubuntu@pg-vm:~$ sudo apt install  postgresql-client
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following NEW packages will be installed:
  postgresql-client
0 upgraded, 1 newly installed, 0 to remove and 5 not upgraded.
Need to get 3292 B of archives.
After this operation, 71.7 kB of additional disk space will be used.
Get:1 http://archive.ubuntu.com/ubuntu jammy/main amd64 postgresql-client all 14+238 [3292 B]
Fetched 3292 B in 1s (6499 B/s)
Selecting previously unselected package postgresql-client.
(Reading database ... 66182 files and directories currently installed.)
Preparing to unpack .../postgresql-client_14+238_all.deb ...
Unpacking postgresql-client (14+238) ...
Setting up postgresql-client (14+238) ...
Scanning processes...
Scanning linux images...

Running kernel seems to be up-to-date.

No services need to be restarted.

No containers need to be restarted.

No user sessions are running outdated binaries.

No VM guests are running outdated hypervisor (qemu) binaries on this host.
ubuntu@pg-vm:~$

初始化配置PostgreSQL

通过ubuntu成功安装PostgreSQL后，会自动创建一个名为postgres的OS用户以及一个名为postgres的数据库，默认登录时是不需要密码验证就可以直接登录的。
实际上使用Ident 认证进行登录，即获得客户端的操作系统用户名并且用它作为被允许的数据库用户名来工作。

默认用户登录（无需密码）

使用默认创建的OS用户postgres登录，通过psql进入到PostgreSQL提示符（postgres=#即连接数据库）。

ubuntu@pg-vm:~$ sudo -i -u postgres
postgres@pg-vm:~$ psql
psql (14.7 (Ubuntu 14.7-0ubuntu0.22.04.1))
Type "help" for help.

postgres=#

或者

ubuntu@pg-vm:~$ sudo -u postgres psql
could not change directory to "/home/ubuntu": Permission denied
psql (14.7 (Ubuntu 14.7-0ubuntu0.22.04.1))
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=#

修改数据库用户密码登录

默认登录后修改数据库postgres用户的密码后，就可以通过密码登录。

postgres=# ALTER USER postgres WITH PASSWORD 'pass';
ALTER ROLE
postgres=# \q
ubuntu@pg-vm:~$

例1：

ubuntu@pg-vm:~$ psql -U postgres -h 127.0.0.1 -d postgres
Password for user postgres:
psql (14.7 (Ubuntu 14.7-0ubuntu0.22.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "127.0.0.1" at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
postgres=#

例2：

ubuntu@pg-vm:~$ psql -U postgres -h localhost -d postgres
Password for user postgres:
psql (14.7 (Ubuntu 14.7-0ubuntu0.22.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" (address "::1") at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
postgres=#

服务相关操作

启动和停止mysql服务。

systemctl  start postgresql
systemctl stop postgresql
systemctl  status postgresql

service postgresql status

例：

ubuntu@pg-vm:~$  service postgresql status
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Wed 2023-05-03 14:30:21 CST; 7min ago
    Process: 4843 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 4843 (code=exited, status=0/SUCCESS)
        CPU: 2ms

May 03 14:30:21 pg-vm systemd[1]: Starting PostgreSQL RDBMS...
May 03 14:30:21 pg-vm systemd[1]: Finished PostgreSQL RDBMS.

这样一个简单的PostgreSQL 环境就算构筑完成了。

PostgreSQL自带的命令行工具--psql
PostgreSQL数据库中的角色(Role)与用户（User）的基本操作