ubuntu安装postgresSQL

官网地址： https://www.postgresql.org/download/

1.安装

# 添加源
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# 安装数字签名
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# 更新源
sudo apt-get update
# 安装
sudo apt-get -y install postgresql

2.登录

刚安装的数据库还没有登录密码，可以切换postgres账号直接登录

# 切换postgres
yantao@ubuntu20:~$ sudo -i -u postgres
# 执行
postgres@ubuntu20:~$ psql
psql (16.0 (Ubuntu 16.0-1.pgdg20.04+1))
输入 "help" 来获取帮助信息.

postgres=# 

3.账号

1.修改密码

postgres=# ALTER USER postgres WITH PASSWORD 'MyNewPass4!';
ALTER ROLE
postgres=# 
# 退出程序
postgres=# \q

2.使用密码登录

yantao@ubuntu20:~$ psql -U postgres -hlocalhost -p5432 -dpostgres
用户 postgres 的口令：
psql (16.0 (Ubuntu 16.0-1.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: 关闭)
输入 "help" 来获取帮助信息.

postgres=# 

第二步的方式还是可以使用的。

3.创建新的账号

# 如果创建普通账号就不需要写 SUPERUSER
postgres=# CREATE USER admin WITH PASSWORD '123456' SUPERUSER;
CREATE ROLE

4.删除账号

DROP USER admin;

3.库

1.创建库

CREATE DATABASE mydatabase;

2.查看数据库

postgres-# \l
                                                           数据库列表
    名称    |  拥有者  | 字元编码 | Locale Provider |  校对规则   |    Ctype    | ICU Locale | ICU Rules |       存取权限        
------------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
 mydatabase | admin    | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.UTF-8 |            |           | 
 postgres   | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.UTF-8 |            |           | 
 template0  | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.UTF-8 |            |           | =c/postgres          +
            |          |          |                 |             |             |            |           | postgres=CTc/postgres
 template1  | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.UTF-8 |            |           | =c/postgres          +
            |          |          |                 |             |             |            |           | postgres=CTc/postgres
(4 行记录)

3.选择库

postgres-# \c mydatabase;
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: 关闭)
您现在已经连接到数据库 "mydatabase",用户 "admin".

4.删除库

mydatabase=# DROP DATABASE mydatabase;
错误:  无法删除当前使用的数据库
mydatabase=# \c postgres;
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: 关闭)
您现在已经连接到数据库 "postgres",用户 "admin".
postgres=# DROP DATABASE mydatabase;
DROP DATABASE

5.表

1.创建表

CREATE TABLE mytable (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

2.列表

mydatabase=# \d
                  关联列表
 架构模式 |      名称      |  类型  | 拥有者 
----------+----------------+--------+--------
 public   | mytable        | 数据表 | admin
 public   | mytable_id_seq | 序列数 | admin
(2 行记录)

3.查看表格信息

mydatabase=# \d mytable
                                  数据表 "public.mytable"
 栏位  |          类型          | 校对规则 |  可空的  |                预设                 
-------+------------------------+----------+----------+-------------------------------------
 id    | integer                |          | not null | nextval('mytable_id_seq'::regclass)
 name  | character varying(100) |          |          | 
 email | character varying(100) |          |          | 
索引：
    "mytable_pkey" PRIMARY KEY, btree (id)

4.删除表

mydatabase=# DROP TABLE mytable;
DROP TABLE
mydatabase=# \d
没有找到任何关系.

6.CURD

1.增

mydatabase=# INSERT INTO mytable (name,email) VALUES ('yantao','yantao@qq.com');
INSERT 0 1
mydatabase=# INSERT INTO mytable (name,email) VALUES ('yantao','yantao@qq.com');
INSERT 0 1

2.删

mydatabase=# DELETE FROM mytable WHERE ID = 1;
DELETE 1
mydatabase=# DELETE FROM mytable WHERE ID = 1;
DELETE 0

3.改

mydatabase=# UPDATE mytable SET name = 'Bob', email = 'Bob@qq.com' WHERE id = 7;
UPDATE 1

4.查

mydatabase=# SELECT * FROM mytable;
 id |  name  |     email     
----+--------+---------------
  1 | yantao | yantao@qq.com
  2 | yantao | yantao@qq.com
  3 | yantao | yantao@qq.com
(3 行记录)

7.jsonb

1.建表

CREATE TABLE student (
    id SERIAL PRIMARY KEY,
	name VARCHAR(100),
    data jsonb 
);

2.插入数据

mydatabase=# INSERT INTO student (name, data) VALUES ('John Doe', '{"home": "北京", "age": "18"}');
INSERT 0 1

3.查询

mydatabase=# SELECT * FROM student;
 id |   name   |             data              
----+----------+-------------------------------
  1 | John Doe | {"age": "18", "home": "北京"}
(1 行记录)

8.远程连接

pgsql安装好后默认只能本地ip可以连接。需要修改才能远程连接。

cd /etc/postgresql/16/main

vim postgresql.conf

#listen_addresses = 'localhost'
修改如下
listen_addresses = '*'

udo vim pg_hba.conf

这里新添加一行
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/0               trust

重启数据库

udo systemctl restart postgresql.service