Postgresql数据库安装与基本操作

安装

最好使用新版Postgresql 12版本，12版本是向上兼容，不向下兼容。如果考虑兼容问题，可以进行10版本的安装。
安装命令：

# 添加 Postgresql 源到系统源
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# 添加签名密钥
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# 更新软件包列表
# 在此之前可以先将软件源更换为国内的软件源
sudo apt-get update

# 安装最新版本的 PostgreSQL
# 如果想要指定版本的话，使用 postgresql-12 类似的命令 postgresql-版本号:
sudo apt-get install postgresql

数据库的简单配置

创建数据库用户和数据库

Postgresql默认会在Linux系统下创建用户postgres密码随机，所以安装完成后就要为postgres用户修改密码。
psql命令只能在Postgresql用户下执行，所以要使用postgres用户执行psql命令。

sudo -u postgres psql

进入postgresql控制台，执行命令修改密码：

# 修改默认管理员用户密码
ALTER USER postgres WITH PASSWORD '123456'; 
# 添加用户
create user test01 with password '123456';
# 为test01用户创建数据库
create database mydatabase owner test01;
# 将数据库的管理权授予用户
grant all privileges on database mydatabase to test01;
# 退出
\q

修改Linux用户poatgres的密码：

sudo passwd postgres
## 123456

修改配置文件允许远程登录

vim /etc/postgresql/版本号/main/postgresql.conf
#listen_addresses = 'localhost' 改为 listen_addresses = '*'

vim /etc/postgresql/版本号/main/pg_hba.conf
在文档末尾加上以下内容
host all all 0.0.0.0 0.0.0.0 md5

# 重启Postgresql，加载配置文件
service postgresql restart

使用新增用户登录

因为没有在Linux系统中创建test01用户，需要使用以下命令走网络连接登录：

# psql -U 用户名 -h ip地址 -W 要登录的数据库
psql -U test01 -h 127.0.0.1 -W mydatabase

使用管理员账户登录

sudo -u postgres psql

重置数据表

TRUNCATE TABLE table_name RESTART IDENTITY;

数据库备份

需要切换到postgres 用户下

su postgres
# 输入密码
cd ~
# 切换到postgres用户目录下

# 备份命令
pg_dump -h 127.0.0.1 -U test01 -d mydatabase -f mydatabase_copy.sql

数据库还原

需要切换到postgres 用户下

su postgres
# 输入密码
cd ~
# 切换到postgres用户目录下

注意：先把sql文件copy到postgres用户目录下

# 还原命令
psql -h 127.0.0.1 -U test01 -d mydatabase -f mydatabase_copy.sql

基本操作命令

sudo -u postgres psql

 \h #查看所有的sql关键字
 \? #命令行操作的帮助
 \d #查看当前schema 中所有的表
 \q #退出pg命令行
 \d #schema.table 查看表的结构
 \x #横纵显示切换 
 \dT+ #显示扩展类型相关属性及描述
 \dx #显示已安装的扩展插件
 \l #列出所有的数据库
 \timing #显示执行时间 
 \c database_name #切换数据库 
 set search to schema #切换schema 
 explain sql #解释或分析sql执行过程