acytoo

postgresql 基础操作

#安装

Debian 安装 postgresql：

sudo apt install postgresql

或者下载源码自行编译。

#初始化

如果是使用 apt 安装，不需要手动初始化，请跳过此步骤

首先先要切换到 postgres 用户，数据很重要，应该使用单独的用户操作数据库，而且最好改个名。postgres 是默认的用户名。

su
su - postgres
initdb --locale=en_US.UTF-8 -E UTF8 -D /var/lib/postgres/data
#使用

切换到 postgres 用户

su
su - postgres

连接本地数据库

psql [OPTION]... [DBNAME [USERNAME]]

默认会连接到 postgres 这个自带的数据库，如果删除了这个数据库，要指定连接的数据库名字，例如psql mydatabase

当然也可以使用 sudo 命令

sudo -u postgres psql

连接数据库后，会进入 postgresql 的命令行。下面是一些常用命令，使用\d?查看帮助

\h                        # 查看帮助文档
\l                        # 查看所有的数据库
\c postgres               # 连接到postgres数据库
\d                        # 查看这个数据库中所有的关系
\d region_table           # 查看某个表中的所有列名
\q                        # 退出postgresql命令行

给数据库创建一个使用密码登录的账户，方便编程对接：

CREATE USER someone WITH PASSWORD '123456';

上面这行命令创建了一个叫 someone 的账户，密码是 123456 。注意，密码一定要用单引号包起来。

查看用户名:在 psql 命令行里执行\du

授予新用户创建数据库的权限：

ALTER USER someone CREATEDB;

授予新用户创建数据库的权限：

ALTER USER someone LOGIN;

接下来修改 postgresql 配置，允许使用密码登录：

sudo nano /etc/postgresql/13/main/pg_hba.conf

找到 local all all peer 这一行，把最后的 peer 改为 md5，这样就允许本机所有除了 postgres 以外的账户使用密码登录。

重启一下：

sudo systemctl restart postgresql

postgresql默认只允许本机访问，如果允许外部访问：

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

找到listen_addresses = 'localhost'，改为listen_addresses = '*'
这样就把端口对外开放，接下来允许用户远程登录：

sudo nano /etc/postgresql/13/main/pg_hba.conf

加上

host all someone 0.0.0.0/0 md5

其中。0.0.0.0/0是允许的 ipv4 地址范围。

postgres 是 postgresql 的管理员账户，平时最好不要用权限这么高的用户操作数据库，因此下面使用使用 someone 账户操作：

psql -U someone -W postgres

输入密码后就以 someone 账户连接到 postgres 数据库。 -U 指示用户名，-W明确需要密码登录，testdb 是要连接的数据库名字，而不是密码：psql 会提示输入密码，而不是明文输入到命令中。

创建数据库：

CREATE DATABASE testdb;

赋予其他用户操作某个数据库的权限：

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO someone;

如果上面的命令没有正常工作，只能 drop 数据库，用新用户再建一遍。查了怎么 transfer ownership，太麻烦了……

修改某个表名：

ALTER TABLE table_name RENAME TO new_table_name;

修改某个表中的某个列名：

ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;

修改自增序列的起始值：

ALTER SEQUENCE tablename_columnname_seq RESTART WITH anynumber;

查看某张表中的数据条数:

SELECT COUNT(*) FROM table;

模糊查询:

SELECT * FROM table WHERE column_name LIKE 'some%';
SELECT * FROM table WHERE column_name ILIKE 'SOMEthi_g';

上面的例子中，LIKE 区分大小写，ILIKE 是 PostgreSQL 增加的，不区分大小写的模糊查询。% 可以匹配任意长度的字符串，_匹配任意单个字符。 需要注意的是，虽然可以使用%thing这样来匹配，但是左模糊 LIKE 匹配效率尤其低。

备份数据 PostgreSQL 有三种备份方法，详情见官方文档

  • SQL dump
  • File system level backup
  • Continuous archiving

简单对比一下：SQL dump 是建议使用的方式：因为 dump 可以“热备份”，即备份时，不需要关掉服务。而且，可以从低版本向高版本备份，例如从 PostgreSQL 13 dump 的数据库，可以在 PostgreSQL 14 上回复。同时，SQL dump 还可以跨平台备份，例如从 32 位机器上备份，在 64 位机器上恢复。

文件系统层的备份，也有优点：可以一次备份整个 database cluster, 只要确定环境相同，把数据库存储的文件转移到相同的环境中，一次可以备份多个数据库。

Continuous archiving 还没看，先记录下 dump。

dump 方法：

pg_dump dbname > outfile

相关文章：