Postgres数据库作为一款完全开源的关系型数据库，号称是最先进的开源数据库。它不仅支持传统的关系型数据查询(SQL)，也支持非关系型数据的查询(Json)。它的起源可以追溯到1986年开发的以4.2版本为基础的POSTGRES，参考了同样在伯克利开发的旧 Ingres 数据库。到了1995年，两位华人把Postgres的sql引擎改掉之后，它就开始商业化了。这些年来，Postgres数据库一直在不断迭代和更新，目前Postgres已经更新到了16版本。
下面以Postgres11版本为例，简单介绍一下数据库的日常操作。
Postgres数据库的安装可以去官网(https://www.postgresql.org/download/)下载指定的压缩包，不同系统对应的压缩包也不相同，在这里不再赘述。
安装数据库的过程中需要指定数据目录，笔者用的是Ubuntu系统，指定的数据目录为/var/postgresql/data。
在系统安装好Postgres数据库后，我们通常需要创建一个postgres用户，该用户拥有对数据库及数据目录所有的操作权限。
准备就绪后，接下来利用PG数据库自带的pg_ctl命令启动PG数据库,启动时需要加上-D选项来指定数据目录的路径。
postgres@SZX5APLN622241:~$ pg_ctl -D /var/postgresql/data start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2024-05-13 16:06:58.631 CST [2449984] LOG: listening on IPv4 address "127.0.0.1", port 5442
2024-05-13 16:06:58.640 CST [2449984] LOG: listening on Unix socket "/tmp/.s.PGSQL.5442"
2024-05-13 16:06:58.674 CST [2449988] LOG: database system was interrupted; last known up at 2024-04-11 10:15:26 CST
2024-05-13 16:06:59.372 CST [2449988] LOG: database system was not properly shut down; automatic recovery in progress
2024-05-13 16:06:59.375 CST [2449988] LOG: redo starts at 1A/1A002B70
2024-05-13 16:06:59.376 CST [2449988] LOG: redo done at 1A/1B000108
2024-05-13 16:06:59.395 CST [2449984] LOG: database system is ready to accept connections
done
server startedPG数据库在成功启动之后，我们可以在数据目录查询到主进程文件postmaster.pid。
postgres@SZX5APLN622241:/var/postgresql/data$ ls -lrt postmaster.pid
-rw------- 1 postgres postgres 89 May 13 16:06 postmaster.pid然后我们就可以用psql命令登录数据库会话，默认登录的是系统自带的postgres数据库。首先我们用\l查看有哪些数据库。
postgres@SZX5APLN622241:/var/postgresql/data$ psql
psql (12.18 (Ubuntu 12.18-0ubuntu0.20.04.1), server 11.7)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | dbuser=CTc/postgres
ptsr_test | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | dbuser | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/dbuser +
| | | | | dbuser=CTc/dbuser
(5 rows)template0和template1是系统自带的模板，数据库接着用\c加数据库名称，就可以切换到指定的数据库。
postgres=# \c testdb
psql (12.18 (Ubuntu 12.18-0ubuntu0.20.04.1), server 11.7)
You are now connected to database "testdb" as user "postgres".
testdb=#
还有很多其他的命令，可以用\?来查看具体有哪些。
testdb=# \?
General
\copyright show PostgreSQL usage and distribution terms
\crosstabview [COLUMNS] execute query and display results in crosstab
\errverbose show most recent error message at maximum verbosity
\g [FILE] or ; execute query (and send results to file or |pipe)
\gdesc describe result of query, without executing it
\gexec execute query, then execute each value in its result
\gset [PREFIX] execute query and store results in psql variables
\gx [FILE] as \g, but forces expanded output mode
\q quit psql
\watch [SEC] execute query every SEC seconds
Help
\? [commands] show help on backslash commands
\? options show help on psql command-line options
\? variables show help on special variables
\h [NAME] help on syntax of SQL commands, * for all commands
Query Buffer
\e [FILE] [LINE] edit the query buffer (or file) with external editor
\ef [FUNCNAME [LINE]] edit function definition with external editor
\ev [VIEWNAME [LINE]] edit view definition with external editor
\p show the contents of the query buffer
\r reset (clear) the query buffer
\s [FILE] display history or save it to file
\w FILE write query buffer to file
Input/Output
\copy ... perform SQL COPY with data stream to the client host
\echo [STRING] write string to standard output
\i FILE execute commands from file
\ir FILE as \i, but relative to location of current script
\o [FILE] send all query results to file or |pipe
\qecho [STRING] write string to query output stream (see \o)
Conditional
\if EXPR begin conditional block
\elif EXPR alternative within current conditional block
\else final alternative within current conditional block
\endif end conditional block
Informational
(options: S = show system objects, + = additional detail)
\d[S+] list tables, views, and sequences
\d[S+] NAME describe table, view, sequence, or index
\da[S] [PATTERN] list aggregates
\dA[+] [PATTERN] list access methods
\db[+] [PATTERN] list tablespaces
\dc[S+] [PATTERN] list conversions
\dC[+] [PATTERN] list casts
\dd[S] [PATTERN] show object descriptions not displayed elsewhere
\dD[S+] [PATTERN] list domains
\ddp [PATTERN] list default privileges
\dE[S+] [PATTERN] list foreign tables
\det[+] [PATTERN] list foreign tables
\des[+] [PATTERN] list foreign servers如果想退出当前会话，就直接使用\q命令。
testdb=# \q
postgres@SZX5APLN622241:/var/postgresql/data$当我们想停止数据库服务时，同样需要使用pg_ctl命令。
postgres@SZX5APLN622241:/var/postgresql/data$ pg_ctl -D /var/postgresql/data stop
waiting for server to shut down....2024-05-13 16:29:23.015 CST [2449984] LOG: received fast shutdown request
2024-05-13 16:29:23.017 CST [2449984] LOG: aborting any active transactions
2024-05-13 16:29:23.018 CST [2449984] LOG: background worker "logical replication launcher" (PID 2450001) exited with exit code 1
2024-05-13 16:29:23.022 CST [2449996] LOG: shutting down
2024-05-13 16:29:23.063 CST [2449984] LOG: database system is shut down
done
server stopped数据库服务停止后，psql命令就会失效。
postgres@SZX5APLN622241:/var/postgresql/data$ psql
psql: error: connection to server on socket "/tmp/.s.PGSQL.5442" failed: No such file or directory
Is the server running locally and accepting connections on that socket?同时数据目录中的postmater文件也会被删除。
postgres@SZX5APLN622241:/var/postgresql/data$ ls -rlt postmaster.pid
ls: cannot access 'postmaster.pid': No such file or directory
