postgresql使用教程

1、在线安装，默认最新版本，注意先update源，我这安装的是9.5；

sudo apt-get install postgresql
安装完成后，默认会：

（1）创建名为"postgres"的Linux用户

（2）创建名为"postgres"、不带密码的默认数据库账号作为数据库管理员

（3）创建名为"postgres"的表

安装完成后的一些默认信息如下：

config /etc/postgresql/9.5/main 
data /var/lib/postgresql/9.5/main 
locale en_US.UTF-8 
socket /var/run/postgresql 
port 5432

源码安装：

···
方式一：
wget https://ftp.postgresql.org/pub/source/v10.11/postgresql-10.11.tar.gz
yum install readline*
yum install xml2*
yum install libxml2 libxml2-devel
yum install libxslt libxslt-devel

useradd postgres
mkdir /opt/postgres
chown -R postgres:postgres /opt/postgres/
vi /etc/profile
export PATH=/opt/postgres/bin:$PATHexportPGHOME=/opt/postgresexportPGDATA=/opt/postgres/data/exportL{D}_{L}IBRAR{Y}_{P}ATH=$LD_LIBRARY_PATH:$PGHOME/lib/exportPATH=$PGHOME/bin:$PATH:$HOME/bin
source /etc/profile

方式二：
wget https://ftp.postgresql.org/pub/source/v10.11/postgresql-10.11.tar.gz
tar zxvf postgresql-10.11.tar.gz
cd postgresql-10.11
./configure --prefix=/opt/postgres/ --with-python --with-libxml --with-libxslt
make && make install
su postgres
#所有配置文件尽量指定到/etc/postgres/文件下
···

ubuntu 创建新用户

创建新用户：
sudo useradd -r -m -s /bin/bash liny(username)

在 Ubuntu18.04 中，不会在创建用户的时候自动提示设置密码。需要手动执行：
sudo passwd liny 
来设置新用户的密码
-r：建立系统账号
-m：自动建立用户的登入目录
-s：指定用户登入后所使用的shell

输入ls /home/，可以看到用户目录被成功创建了

修改用户权限

这里采用修改/etc/sudoers文件的方法分配用户权限。因为此文件只有r权限，在改动前需要增加w权限，改动后，再去掉w权限。

sudo chmod +w /etc/sudoers
sudo vim /etc/sudoers
# 添加下图的配置语句，并且保存修改
sudo chmod +w /etc/sudoers
sudo chmod -w /etc/sudoers

删除用户

删除用户的操作分为 3 步：
执行userdel：
sudo userdel dongyuanxin_2016150127
删除用户目录：
sudo rm -rf /home/dongyuanxin_2016150127
删除用户权限相关配置：
删除或者注释掉/etc/sudoers中关于要删除用户的配置，否则无法再次创建同名用户。

启动

# 目录/etc/init.d/
postgresql   start/stop/restart/status
或者:
systemctl start/restart postgresql@10-main.service
# 如果启动不成功，使用以下命令查找原因
systemctl status postgresql@10-main.service
# 根据提示原因，解决对应问题

psql常用启动方法：

systemctl status/start/restart/stop/ postgresql@10-main.service

配置文件：

目录：/etc/postgresql/10/main
基础配置文件：postgresql.conf
远程连接配置文件：pg_hba.conf

启动方式三

 1. 切换root用户:sudo su
 2. 切换postgres用户,进入su postgres
 3. 进入postgresql的bin目录
 4. ./或者绝对路径/pg_ctl start/restart/stop/  -D 绝对路径/数据库data目录

常见的操作指令：

查看状态
sudo /etc/init.d/postgresql status
 
启动
sudo /etc/init.d/postgresql start
 
停止
sudo /etc/init.d/postgresql stop
 
 重启
sudo /etc/init.d/postgresql restart
创建一个新的数据库用户root，并制定为超级用户：
sudo -u postgres createuser --superuser root
设置root用户的密码：
sudo -u postgres psql
\password root
\q
或者：
CREATE USER dbname WITH PASSWORD '密码';

创建一个数据库 mydb：
sudo -u postgres createdb -O root test
或者进入postgres数据库，使用sql语句创建：

CREATE DATABASE mydb OWNER dbname;
赋予所有权限：
GRANT ALL PRIVILEGES ON DATABASE mydb to dbname;


# 为数据库改名：

alter database mydb rename to mynewdb;
7、登录数据库：
psql -U root -d test -h 127.0.0.1 -p 5432
-U 指定用户，-d 指定数据库，-h 指定服务器，-p 指定端口。
8、常用控制台命令:
\h：查看SQL命令的解释，比如\h select。
\?：查看psql命令列表。
\l：列出所有数据库。
\c [database_name]：连接其他数据库。
\d：列出当前数据库的所有表格。
\d [table_name]：列出某一张表格的结构。
\du：列出所有用户。
\e：打开文本编辑器。
\conninfo：列出当前数据库和连接的信息。
9、数据库操作：
数据库操作为普通的 SQL，不过有 PostgreSQL 自己的语法，详细见文档说明。

创建表：
create table users (
 id serial primary key,
 username varchar(20),
 password varchar(20)
);
插入数据：
insert into users(username, password) values('admin', 'admin');
查询数据
select * from users;
10、安装图形界面客户端PgAdmin,有了这货 谁还用sql建表,毕竟时间就是生命：
sudo apt-get install pgadmin3

解决锁表问题:

方式一:
检索出死锁进程的ID
   SELECT * FROM pg_stat_activity where wait_event_type = 'Lock';
如果查询到了pid，表示有死锁进程，则需要杀掉解锁进程
   select pg_terminate_backend('pid')
方式二:
查询是否锁表了
  select oid from pg_class where relname=tablename
  select pid from pg_locks where relation=oid
如果查询到了结果，表示该表被锁 则需要释放锁定
  select pg_cancel_backend(oid)