PostgreSQL数据库连接和数据库管理_postgre配置数据库连接

【免责声明】文章仅供学习交流，观点代表个人，与任何公司无关。
编辑|SQL和数据库技术(ID:SQLplusDB)

PostgreSQL数据库连接和数据库管理

首先我们需要通过multipass启动安装了PostgreSQL的虚拟机，然后我们就可以开启PostgreSQL数据库之旅了。

multipass list
multipass shell <虚拟机名>
1
2

例：

C:\Users\Administrator>multipass list
Name                    State             IPv4             Image
mysql-vm                Stopped           --               Ubuntu 20.04 LTS
pgvm                    Running           N/A              Ubuntu 20.04 LTS

C:\Users\Administrator>multipass shell pgvm
Welcome to Ubuntu 20.04.4 LTS (GNU/Linux 5.4.0-125-generic x86_64)

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

  System information as of Thu Sep 15 21:25:06 CST 2022

  System load:  0.48              Processes:               106
  Usage of /:   29.2% of 4.67GB   Users logged in:         0
  Memory usage: 20%               IPv4 address for enp0s3: 10.0.2.15
  Swap usage:   0%


0 updates can be applied immediately.


The list of available updates is more than a week old.
To check for new updates run: sudo apt update
New release '22.04.1 LTS' available.
Run 'do-release-upgrade' to upgrade to it.


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

ubuntu@pgvm:~$
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

PostgreSQL数据库连接

连接PostgreSQL命令

可以通过如下命令利用自带的psql工具连接和断开PostgreSQL服务器。

--连接数据库
shell> psql -h <主机名>  -p <端口号> -U <用户名> <数据库名>
1
2

例：

ubuntu@pg-vm:~$ psql -U postgres -h localhost -d postgres
Password for user postgres:                --》输入之前设置的密码：pass
psql (12.12 (Ubuntu 12.12-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#
1
2
3
4
5
6
7

断开PostgreSQL命令

对于PostgreSQL，通常使用\q推出连接。
但是有时候和MySQL一样，也可以通过输入exit或者quit或者Control+D也能够退出PostgreSQL连接。

例：

--首选 \q
postgres=# \q
或者
postgres=# exit
或者
postgres=# quit
或者
Control+D
1
2
3
4
5
6
7
8

PostgreSQL数据库基本操作

执行查询

和MySQL数据库相类似，连接了数据库以后就可以直接执行查询。

例：

postgres=# select version();

                                                               version                                                 
----------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.12 (Ubuntu 12.12-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
(1 row)

(END)
1
2
3
4
5
6
7
8

psql命令行工具

psql是PostgreSQL自带的命令行工具，功能全面，是PostgreSQL数据库最重要的命令行工具之一。
通过psql工具可以和PostgreSQL数据库服务器进行SQL命令行交互。另外，psql工具也提供了大量强大的元命令（以反斜杠“\”开头的命令）。

通过在psql命令行键入help可以获得使用帮助的内容。

例：

postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
1
2
3
4
5
6
7

psql工具元命令可以帮助我们更有效的利用数据库，下面是一些常用的元命令。

例1：列出所有的数据库

postgres=# \l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
(3 rows)
1
2
3
4
5
6
7
8
9
10

例2：列出所有的 schema

postgres=# \dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
(1 row)

1
2
3
4
5
6
7

例3：列出所有的数据库用户和角色

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# \dg
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
1
2
3
4
5
6
7
8
9
10
11

例4：获得连接信息

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)
1
2
3

注：通过在psql命令行键入 ? 可以获得更多元命令的帮助。

数据库操作

PostgreSQL数据库操作相关的常用命令如下：

查看数据库列表：/l
create database <数据库名>;
drop database <数据库名>;
切换数据库：\c <数据库名>
1
2
3
4

CREATE DATABASE创建数据库

可以通过CREATE DATABASE创建一个新的PostgreSQL数据库。

例1：创建新数据库

postgres=# create database mydb1;
CREATE DATABASE
1
2

例2：创建指定owner的数据库

postgres=# create database mydb2 owner user1 ;
CREATE DATABASE

1
2
3

查看已经存在的数据库

使用 \l 命令可以 查看已经存在的数据库。

例：

postgres=# \l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------
 mydb1     | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 mydb2     | user1    | UTF8     | C.UTF-8 | C.UTF-8 |
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
(5 rows)

1
2
3
4
5
6
7
8
9
10
11
12
13

ALTER DATABASE修改数据库

通过ALTER DATABASE命令更改一个数据库的属性。

例：修改数据库的所有者

postgres-# \l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------
 mydb1     | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 mydb2     | user1    | UTF8     | C.UTF-8 | C.UTF-8 |
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
(5 rows)

postgres=#
postgres=#
postgres=# alter database mydb1 owner to user1;
ALTER DATABASE
postgres=# \l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------
 mydb1     | user1    | UTF8     | C.UTF-8 | C.UTF-8 |
 mydb2     | user1    | UTF8     | C.UTF-8 | C.UTF-8 |
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
(5 rows)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

\c 切换数据库

可以通过 \c <数据库名>命令切换数据库。

postgres=# \c mydb1
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
You are now connected to database "mydb1" as user "postgres".
mydb1=# \conninfo
You are connected to database "mydb1" 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)
mydb1=#
1
2
3
4
5
6
7

查看数据库的版本

postgres=# SELECT version();

----------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.11 (Ubuntu 12.11-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
(1 row)

(END)
1
2
3
4
5
6
7

或者

postgres@pg-vm:~$ psql --version
psql (PostgreSQL) 12.11 (Ubuntu 12.11-0ubuntu0.20.04.1)
1
2

参考

https://www.postgresql.org/docs/14/managing-databases.html

Chapter 23. Managing Databases