postgresql 数据库使用小手册 cheat sheet

安装

apt install postgresql

登录进入postgresql

sudo su postgres

输入psql 使用数据库管理工具：

postgres@user:/home/user $ psql

如果提示连接失败是端口或者 host 不对，我安装时默认使用的端口是5433/ host localhost, psql 默认使用的端口是5432 / host localhost ,

这时候修改postgresql 默认配置

sudo nano /etc/postgresql/14/main/postgresql.conf

搜索到 port

修改成 5432 保存，重启postgresql service : sudo service postgresql restart

按照上面方法顺利进入 psql 管理工具。

登录方法二

sudo su postgres

输入psql 使用数据库管理工具：

postgres@user:/home/user $ psql
postgres=#

修改postgres 用户密码：

ALTER USER postgres with password ‘xxx’;

\q 退出 psql

在命令窗口：

psql -Upostgress -h127.0.0.1

输入密码，成功登录

psql 使用小手册

你已经安装PostgreSQL。现在使用 psql 你想要学习的绝对最低要完成工作。

这既是一个短暂的教程和一个快速参考绝对至少你需要知道 psql. 我假定你很熟悉命令行和有一个粗略的想法什么样的数据库管理任务，但是不熟悉如何使用 psql 要做到的基本知识。

该 PostgreSQL教程是令人难以置信的好，但坦率地说，我不知道从哪里开始阅读。此是我的回答对这一问题。

它显示了，如何做到以下的 psql 提示：

如果你没有访问一个生活PostgreSQL安装的时刻，我们仍然有你的背后。你可以按照通过实例和输出显示，如果你有没有类型的一切。

这里command line utility

许多行政任务可以或应该做你的本地的机器，虽然如果数据库的生活。你可以做一些他们通过一些用户接口，但是，我们在这里不会对此进行说明。知道如何执行这些行动的命令线意味着你可以剧本他们，和脚本意味着你可以自动测试，检查的错误，并做到数据输入命令行。

这一部分不是一个完整的小抄 psql. 它涵盖了最常见的行动，并显示他们大致在序列，因为你会用他们在一个典型的工作会议。

开始和退出的原因终端的交互式

命令行提示的原因

退出psql

打开连接本地

打开连接的远程

使用帮助的提示

获取信息的数据库

创建和使用表和记录

维护和操作

你需要知道什么

之前使用这一节中，你会需要：

用户名和密码PostgreSQL数据库
IP地址的远程实例

命令行提示

的 $ 开始的命令行在下面的例子表示你的操作系统的提示。提示是否可配置的，因此它可能不是这样的。

在Windows它可能看起来像 C:\Program Files\PostgreSQL> 但是Windows提示也可配置的。

$ psql -U sampleuser -h localhost

一线开始 # 代表了评论。同样，用于一切的权利 #. 如果你不小心类型或复制，并将其粘贴在，不用担心。什么都不会发生。

This worked to connect to Postgres on DigitalOcean
# -U is the username (it will appear in the \l command)
# -h is the name of the machine where the server is running.
# -p is the port where the database listens to connections. Default is 5432.
# -d is the name of the database to connect to. I think DO generated this for me, or maybe PostgreSQL.
# Password when asked is csizllepewdypieiib
$ psql -U doadmin -h production-sfo-test1-do-user-4866002-0.db.ondigitalocean.com -p 25060 -d mydb
# Open a database in a remote location.
$ psql -U sampleuser -h production-sfo-test1-do-user-4866002-0.db.ondigitalocean.com -p 21334

使用的原因

你会用 psql (又名 PostgreSQL交互式终端 )最重要的，因为它是用于创建数据库的数据和表格，显示信息有关的表格，并甚至进入信息(记录)进入该数据库。

退出 psql

之前我们学习其他东西的，这里就如何要退出 psql 和返回操作系统的提示。你的类型反斜杠，这封信 q 然后你按下输入或返回的关键。

# Press enter after typing \q
# Remember this is backslash, not forward slash
postgres=# \q

这需要你回到操作系统的提示。

打开连接本地

一个常见的情况期间展开连接到本地数据库。运行 psql 与 -U (用户名)的名称后的数据库，正常使用 psql 就能进入输入 \c dbname 进入数据库：

# Log into Postgres as the user named postgres
$ psql -U postgres

打开连接的远程

连接的远程PostgreSQL实例从你的地方机器，使用 psql 在你的操作系统的命令行。这是一个典型的连接。

# -U is the username (it will appear in the \l command)
# -h is the name of the machine where the server is running.
# -p is the port where the database listens to connections. Default is 5432.
# -d is the name of the database to connect to. I think DO generated this for me, or maybe PostgreSQL.
$ psql -U doadmin -h production-sfo-test1-do-user-4866002-0.db.ondigitalocean.com -p 25060 -d defaultdb

在这里，你会输入密码。在情况下某人被窥视你的肩膀，符被隐藏。你已经输入您的信息正确你将会得到这个消息(截断为清楚起见):

看这里提示

一些事情出现，然后 psql 提示。名称的当前数据库出现之前的提示。

psql (11.1, server 11.0)
Type "help" for help.
postgres=#

在这一点你要类型的命令和参数进命令行。

SQL命令

psql 有两种不同的命令。那些刚开始反斜杠都对 psql 本身，说明通过使用 \q 退出。

那些开始有效的SQL是当然的互动SQL用创建和修改PostgreSQL数据库。

警告：SQL命令端分号!

一个问题是，几乎所有SQL命令你进入 psql 必须结束在一个分号。

例如，假设你想要除去一个表叫 sample_property_5. 你想进入这个命令：

postgres=# DROP TABLE "sample_property_5";

这很容易忘记。如果你忘记的分号,你会看到这种令人费解的提示。注意， [ 已经插入以前的用户名部分提示，而另一个提示出现下：

[postgres=# DROP TABLE "sample_property_5"
postgres=#

当你做的时候，记得命令行最后的分号:

[postgres=# DROP TABLE "sample_property_5"
postgres=# ;

滚动通过命令的历史

使用箭头键移动向前和向后通过命令的历史。

获取信息的数据库

这些不是SQL命令，所以只要按输入后他们。请记住：

当有更多出于适用屏幕上，它暂停。按空间来继续
如果你想停止的产出，新闻 q.

\h帮助

# Get help. Note it's a backslash, not a forward slash.
postgres=# \h

你会得到一个长长的清单的命令，然后再输出暂停:

Available help:
  ABORT                            CREATE USER
  ...
  ALTER AGGREGATE                  CREATE USER MAPPING
  ALTER PROCEDURE                  DROP INDEX
:

按空间的继续，或者 q 停止输出。

你可以得到帮助，在一个特定的项目通过清单之后 \h 命令。

例如，获得的帮助 DROP TABLE:

postgres=# \h drop table

你会得到帮助就这一项目：

Command:     DROP TABLE
Description: remove a table
Syntax:
DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

\l 数据库列表

大多数人认为，作为一个数据库(就是说，一个名单的客户)实际上是一个表中。数据库是一套表格，关于这些表格、关于用户的信息和他们的权限，以及更多。这些数据库(以及表格内)自动更新的PostgreSQL为你使用它们。

列出所有数据库：

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 visitor   | tom      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 markets   | tom      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 tom       | tom      | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

你可以得到单一的数据库的信息，通过以下的 \l 提示与其名称。

例如，查看有关的信息 template0 数据库：

postgres=# \l template0

输出为：

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

\l+ 列表数据库的大小，表空间，并说明

以获得额外的信息空间数据库的数据表和信息，描述的这些表中，使用 \l+:

postgres=# \l+

\x 展开/简略列出信息

使用 \x (X扩大清单)，以控制是否表的列表使用详细或简略的格式。

命令效果 \x off 显示表列广泛的格式 \x on 显示表列在狭窄的格式 \x 反之前的状态 \x auto 使用终端，以确定格式 例子： 这是一个详细的清单：

/* List all databases. */
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 foo       | tom      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 foobarino | tom      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 tom       | tom      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(6 rows)

使用 \x on 对于简略的名单：

/* Turn on narrow listings. */
postgres=# \x on
postgres=# \l
-[ RECORD 1 ]-----+----------------------
Name              | foo
Owner             | tom
Encoding          | UTF8
Collate           | en_US.UTF-8
Ctype             | en_US.UTF-8
Access privileges | 
-[ RECORD 2 ]-----+----------------------
Name              | foobarino
Owner             | tom
Encoding          | UTF8
Collate           | en_US.UTF-8
Ctype             | en_US.UTF-8
Access privileges | 
-[ RECORD 3 ]-----+----------------------
Name              | postgres
Owner             | postgres
Encoding          | UTF8
Collate           | en_US.UTF-8
Ctype             | en_US.UTF-8
Access privileges |

\c 连接数据库

看看里面有什么一个数据库，使用连接到它 \c 随后通过的数据库名称。迅速变化的名称匹配的数据库连接。 (一个名为 postgres 总是很有趣。) 在这里，我们正在连接到一个叫 markets:

postgres=# \c markets
psql (11.1, server 11.0)
You are now connected to database "markets" as user "tom".
markets=#

\dt 表显示

使用 \dt 列出的所有表(从技术上讲，关系 )在数据库：

markets=# \dt<>>
                    List of relations
 Schema |             Name             | Type  |  Owner   
--------+------------------------------+-------+----------
 public | addresspool                  | table | tom
 public | adlookup                     | table | tom
 public | bidactivitysummary           | table | tom
 public | bidactivitydetail            | table | tom
 public | customerpaymentsummary       | table | tom
 ...

如果你选择一个数据库，例如 
postgres
 可能有许多表。 记住，你可以暂停出，按空间或制止这一按 
q
. 
\d \d+ 显示表的列(域名称)
查表的模式，使用 \d 随后 跟上表名。 
查表的模式命名 customerpaymentsummary 输入 
markets=# \d customerpaymentsummary<>>
                            Table "public.customerpaymentsummary"
            Column            |            Type             | Collation | Nullable | Default 
------------------------------+-----------------------------+-----------+----------+--------
 usersysid                    | integer                     |           | not null |        
 paymentattemptsall           | integer                     |           |          |        
 paymentattemptsmailin        | integer                     |           |          |        
 paymentattemptspaypal        | integer                     |           |          |        
 paymentattemptscreditcard    | integer                     |           |          |        
 paymentacceptedoutagecredit  | integer                     |           |          |        
 totalmoneyin                 | numeric(12,2)               |           |          |        
 updatewhen1                  | timestamp without time zone |           |          |        
 updatewhen2                  | timestamp without time zone |           |          |        

图更详细的资料表格，使用 
\d+: 
markets=# \d customerpaymentsummary<>>
                            Table "public.customerpaymentsummary"
            Column            |            Type             | Collation | Nullable | Default | Storage | Stats target |
------------------------------+-----------------------------+-----------+----------+---------+---------+---------------
 usersysid                    | integer                     |           | not null |         | plain   |              |   
 paymentattemptsall           | integer                     |           |          |         | plain   |              |   
 paymentattemptsmailin        | integer                     |           |          |         | plain   |              |   
 paymentattemptspaypal        | integer                     |           |          |         | plain   |              |   
 paymentattemptscreditcard    | integer                     |           |          |         | plain   |              |   
 paymentacceptedoutagecredit  | integer                     |           |          |         | plain   |              |    
 totalmoneyin                 | numeric(12,2)               |           |          |         | main    |              |    
 updatewhen1                  | timestamp without time zone |           |          |         | plain   |              |   
 updatewhen2                  | timestamp without time zone |           |          |         | plain   |              |    
Indexes:
\du显示用户的角色 
看到所有用户及他们的角色，使用 \du: 
postgres=# \du
                                    List of roles
 Role name  |                         Attributes                         | Member of 
------------+------------------------------------------------------------+-----------
 smanager   | Superuser                                                  | {}
 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 tom        | Superuser, Create role, Create DB                          | {}


查一个具体的用户，通过这之后的 \du命令。 例如，看到的只有 tom 的作用： 
postgres=# \du tom
                                    List of roles
 Role name  |                         Attributes                         | Member of 
------------+------------------------------------------------------------+-----------
 tom        | Superuser, Create role, Create DB                          | {}
创建一个数据库 
createdb (独立的外部命令；见PostgreSQL 教程  文件)在操作系统的命令行： 
# Replace markets with your database name
$ createdb markets
在成功，没有视觉的反馈意见。 谢谢，PostgreSQL。 

psql 命令创建数据库 
create database dbName;
添加表和记录 
创建一个表 
添加一个表格模式的数据库： 
postgres=# create table if not exists product (
  id              SERIAL,
  name            VARCHAR(100) NOT NULL,
  sku             CHAR(8)
);

和 
psql 作出响应： 
CREATE TABLE

更多看到的 CREATE TABLE 在 PostgreSQL官方文档 . 
增加一个记录(插入) 
这里是如何增加一个记录，填写各个领域： 
# The id field is an automatically assigned
# when you use DEFAULT. The serial primary key means
# that number will be increased by at least
# 1 and assigned to that same field when
# a new record is created.
# Using DEFAULT is a placeholder. 
# In its place PostgreSQL automatically generates a unique integer for it.
postgres=# INSERT INTO product VALUES(DEFAULT, 'Apple, Fuji', '4131');
PostgreSQL作出响应： 
INSERT 0 1

再试一次，你会得到一个类似的反应。 
postgres=# INSERT INTO product VALUES(DEFAULT, 'Banana', '4011');
INSERT 0 1
添加(插入)几个记录一次 

postgres=# INSERT INTO product VALUES
(DEFAULT, 'Carrots', 4562),
(DEFAULT, 'Durian', 5228)
;
只增加特定(列)领域
你可以添加记录，但只指明所选择的领域(也称为列)。 MySQL将使用常识的默认值的其余部分。 
在这个例子中，只有 name 场将被填充。 的 sku 栏留空白， id 列是递增的，并插入。 
两种记录的增加： 
postgres=# INSERT INTO product (name) VALUES
('Endive'),
('Figs')
;
PostgreSQL作出响应的记录数量插入: 
INSERT 0 2

欲了解更多关于插入，请参阅 
INSERT
 在 
PostgreSQL正式的文档 

一个简单的查询
 这是一个 巨大的主题 
让我们 查询 product 表： 
postgres=# SELECT * FROM product;

响应： 
postgres=# select * from product;
 id |    name     |   sku    
----+-------------+----------
  1 | Apple, Fuji | 4131    
  2 | Banana      | 4011    
(2 rows)

注意到 
如果你的表已经混合的情况下对象，例如列名字或指标，则需要将它们在双引号。 例如，如果一个列名都是 Product 而不是的 product 你的查询需要这样的： 
SELECT * FROM "product";

欲了解更多关于查询语法 SELECT 请查看 PostgreSQL官方文档 . 

维护和操作问题 
时间 

\t time SQL operations 
使用 \t 以显示所有SQL操作的时间。 
   命令  效果      \timing off 禁止显示执行时间    \timing on  显示执行命令时间
tom=# insert into todo values ('Retry on Android before app submission,'8.x and earlier');
INSERT 0 1
tom=# \timing on
Timing is on.
tom=# insert into todo values ('Correct footer bug','Mobile version only');
INSERT 0 1
Time: 1.067 ms
tom=# insert into todo values ('Retry on Android before app submission', '8.x and earlier');
INSERT 0 1
Time: 23.312 ms
tom=# \timing
Timing is off.
 \watch
 \watch 命令，在指定的时间间隔重复先前的命令。
例 \watch
这是一个实例的使用 \watch 每5秒查询一次记录个数。 
tom=# select count(*);
  count 
--------
    726
(726 rows)
tom=# \watch 5
Mon Nov 16 13:50:36 2020 (every 2s)
  count 
--------
    726
(726 rows)
Mon Nov 16 13:50:38 2020 (every 2s)
  count 
--------
    726
(726 rows)
Mon Nov 16 13:50:40 2020 (every 2s)
  count 
--------
    726
(726 rows)
找到pg_hba.conf文件 
Postgres配置存储在一个文件命名为 pg_hba.conf 某个地方  ，在文件系统，该位置会变化。 使用 show hba_file 找到它，命令是这样的： 
show  hba_file;

见下文热重新装载这个文件的同时Postgres运行。 
刷新文件的配置，同时Postgres运行 
如果你改变的 pg_hba.conf Postgres配置有时候你需要重新启动。 但你可以选择重新加载 pg_hba.conf 配置文件是这样的： 
SELECT pg_reload_conf();