Ubuntu 上安装 PostgreSQL

OS：Ubuntu 24.04.2 LTS
PG：psql (PostgreSQL) 16.9

1. 安装

1.1 使用 apt-get 安装 PostgreSQL：

sudo apt-get update
sudo apt-get install postgresql postgresql-client

1.2 安装完毕后，系统会创建一个数据库超级用户 postgres，密码为空。

su - postgres

1.3 查看数据库进程

postgres@ws-gsevex-0:~$ ps -ef|grep post
postgres   25196       1  0 02:19 ?        00:00:00 /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main -c config_file=/etc/postgresql/16/main/postgresql.conf
postgres   25197   25196  0 02:19 ?        00:00:00 postgres: 16/main: checkpointer 
postgres   25198   25196  0 02:19 ?        00:00:00 postgres: 16/main: background writer 
postgres   25200   25196  0 02:19 ?        00:00:00 postgres: 16/main: walwriter 
postgres   25201   25196  0 02:19 ?        00:00:00 postgres: 16/main: autovacuum launcher 
postgres   25202   25196  0 02:19 ?        00:00:00 postgres: 16/main: logical replication launcher 
root       27083     101  0 02:25 pts/1    00:00:00 su - postgres
postgres   27084   27083  0 02:25 pts/1    00:00:00 -bash
postgres   41303   27084  0 03:10 pts/1    00:00:00 ps -ef
postgres   41304   27084  0 03:10 pts/1    00:00:00 grep post

这时使用以下命令进入 postgres 用户，连接数据库

postgres@ws-gsevex-0:~$ psql
psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1))
Type "help" for help.

postgres=#     

输入\q 退出 PostgreSQL 提示符

1.4 PostgreSQL 可以通过以下命令来手动启、停、重启服务。

/etc/init.d/postgresql start   # 启
/etc/init.d/postgresql stop    # 停
/etc/init.d/postgresql restart # 重启

~ /etc/init.d/postgresql restart
 * Restarting PostgreSQL 16 database server                                                                                                                   [ OK ]

2. 操作数据库

2.1 创建数据库

1、CREATE DATABASE SQL 语句
2、createdb 命令
3、pgAdmin 工具

postgres=# CREATE DATABASE WNM;
CREATE DATABASE

2.2 查看数据库

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

2.3 连接数据库

postgres=# \c wnm 
You are now connected to database "wnm" as user "postgres".
wnm=# 

2.4 删除数据库

在当前数据库下无法drop，FORCE 也不行

wnm=# DROP DATABASE wnm;
ERROR:  cannot drop the currently open database
wnm=# DROP DATABASE wnm with  (FORCE);
ERROR:  cannot drop the currently open database
wnm=# \c 
postgres   template0  template1  wnm        
wnm=# \c postgres 
You are now connected to database "postgres" as user "postgres".
postgres=# DROP DATABASE wnm;
DROP DATABASE
postgres=# 
postgres=# \l
                                                   List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges   
-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | 
 template0 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
(3 rows)

或者

postgres@ws-aggbwy-0:~$ createdb mydb
postgres@ws-aggbwy-0:~$ psql mydb
psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1))
Type "help" for help.

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

postgres@ws-aggbwy-0:~$ dropdb mydb