跳转至
本文阅读量 次

1. PostgreSQL 的安装和使用

1.1 服务安装

参考这里 https://www.postgresql.org/download/linux/ubuntu/ ⧉, Ubuntu 可以添加 PostgreSQL 官方的 APT 仓库，之后使用 apt 命令进行安装

自动添加处理 PostgrelSQL 仓库 

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

安装后默认监听端口是 5432

1.2 服务启停

sudo systemctl start postgresql@16-main.service
sudo systemctl stop postgresql@16-main.service
sudo systemctl restart postgresql@16-main.service
sudo systemctl status postgresql@16-main.service

1.3 用户及权限

1.3.1 如何进入 psql

安装后会在 Ubuntu 中创建一个 postgres 的系统用户, 同时该用户也是 PostgreSQL 的用户。

通过如下命令以 postgres 用户的身份登录 psql

直接进入 

sudo  sudo -u postgres -i psql

分两步进入 

sudo -u postgres -i

psql

1.3.2 修改 postgres 用户的密码

修改系统账号密码 

sudo passwd postgres

修改 PostgreSQL 中账号的密码 

psql -c "ALTER USER postgres WITH PASSWORD 'newpassword'"

仅修改对 template1 数据库的访问密码 

psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'newpassword'";

1.3.3 其它命令

PostgreSQL 安装后都有哪些相关命令，通过如下查询可知

postgres@VM-16-14-ubuntu:~$ ls -l /usr/bin/* | grep postgresql
lrwxrwxrwx 1 root root          37 Apr 15 20:09 /usr/bin/clusterdb -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root          37 Apr 15 20:09 /usr/bin/createdb -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root          37 Apr 15 20:09 /usr/bin/createlang -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root          37 Apr 15 20:09 /usr/bin/createuser -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root          37 Apr 15 20:09 /usr/bin/dropdb -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root          37 Apr 15 20:09 /usr/bin/droplang -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root          37 Apr 15 20:09 /usr/bin/dropuser -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root          37 Apr 15 20:09 /usr/bin/pg_archivecleanup -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root          37 Apr 15 20:09 /usr/bin/pg_basebackup -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root          37 Apr 15 20:09 /usr/bin/pgbench -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root          37 Apr 15 20:09 /usr/bin/pg_dump -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root          37 Apr 15 20:09 /usr/bin/pg_dumpall -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root          37 Apr 15 20:09 /usr/bin/pg_isready -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root          37 Apr 15 20:09 /usr/bin/pg_receivewal -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root          37 Apr 15 20:09 /usr/bin/pg_receivexlog -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root          37 Apr 15 20:09 /usr/bin/pg_recvlogical -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root          37 Apr 15 20:09 /usr/bin/pg_restore -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root          37 Apr 15 20:09 /usr/bin/psql -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root          37 Apr 15 20:09 /usr/bin/reindexdb -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root          37 Apr 15 20:09 /usr/bin/vacuumdb -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root          37 Apr 15 20:09 /usr/bin/vacuumlo -> ../share/postgresql-common/pg_wrapper

1.3.4 查询当前数据库版本信息

psql -c "SELECT version();"

postgres=# select version();
                                                              version
-----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.2 (Ubuntu 16.2-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)

1.3.5 创建用户

1.3.5.1 管理员命令行

在 postgres 用户的 命令行，不是 psql, 执行

createuser testuser --pwprompt

CREATE USER test_user; 
GRANT ROOT TO test_user;
ALTER ROLE test_user WITH LOGIN;
1.3.5.2 psql

创建用户 

CREATE USER <username> WITH PASSWORD '<password>'

创建超管用户

CREATE USER [name] WITH [option];

CREATE USER <username> SUPERUSER LOGIN PASSWORD '<password>'

1.3.6 查看用户

psql \du

检查某个用户是否存在

1.3.7 修改用户

1.3.8 删除用户

1.3.8.1 psql
drop user [IF EXISTS] <username1> <username2> ...

1.4 数据库操作

1.4.1 查看数据库列表

通过 psql 登录到 PostgreSQL 的交互命令行

\l

                                                       List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           |
 template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |             |             |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |             |             |            |           | postgres=CTc/postgres
(3 rows)

1.4.2 创建数据库

在 postgres 用户的 命令行，不是 psql, 执行 

createdb mydb

1.4.3 连接或切换数据库

连接数据库 

psql testdatabase

以某个用户testuser连接数据库 

psql testdatabase -U testuser

切换连接到另外一个数据库 

\c anotherdatabase

1.4.4 删除数据库

在 postgres 用户的 命令行，不是 psql, 执行 

dropdb mydb

1.4.5 查看当前数据库

\conninfo

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

1.5 表操作

1.5.1 查看当前表列表

\dt

           List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | customers | table | postgres
(1 row)

1.5.2 查看某个表定义

\d+ customers

                                                  Table "public.customers"
   Column    |         Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
-------------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 customer_id | integer               |           |          |         | plain    |             |              |
 first_name  | character varying(80) |           |          |         | extended |             |              |
 last_name   | character varying(80) |           |          |         | extended |             |              |
Access method: heap

1.5.3 添加列

ALTER TABLE customers ADD branch_id int;

1.5.4 删除列

ALTER TABLE customers DROP first_name;

1.5.5 修改列

1.6 数据操作

1.6.1 插入新数据

1.6.2 删除数据

1.6.3 执行某条SQL文件

\i basics.sql

1.7 其它

1.7.1 查看已经安装的模块

\dx

List of installed extensions
   Name    | Version |   Schema   |               Description
-----------+---------+------------+-----------------------------------------
 adminpack | 2.0     | pg_catalog | administrative functions for PostgreSQL
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language

1.7.2 查看所有表的行数

首先连接到目标数据库

select relname AS table_name,n_live_tup AS approximate_row_count from pg_stat_all_tables WHERE schemaname = 'public';

              table_name               | approximate_row_count
---------------------------------------+-----------------------
 django_apscheduler_djangojobexecution |                     0
 problem_paragraph_mapping             |                    21
 paragraph                             |                   390
 application_chat_record               |                   226
 team                                  |                     2
 file                                  |                     0
 django_celery_beat_clockedschedule    |                     0
 django_celery_beat_intervalschedule   |                     0

1.8 参考