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