PostgreSQL基本运维

前言

本文简单罗列了PostgreSQL的基本运维命令。以Ubuntu下使用为主。

PG基础

一个系统上可以同时运行多个postgres进程， 只要他们使用不同的数据区和不同的端口号。
postgres 启动时需要知道数据区的位置，该位置必须通过-D选项或PGDATA 环境变量指定；没有缺省值。通常，-D或PGDATA 都直接指向由initdb创建的数据区。

基本数据类型：

安装

sudo apt update
sudo apt install postgresql postgresql-contrib
1
2

检查安装是否成功:

su - postgres
psql -c "SELECT version();"
1
2

输出：

postgres@w530:~$ psql -c "SELECT version();"
                                                                version                                                                 
----------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.18 (Ubuntu 12.18-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit
(1 row)
1
2
3
4
5

默认用户为postgres，密码为空，通过\password命令可以设置密码。

配置文件

两个主要的配置文件，路径/etc/postgresql/12/main/：

• pg_hba.conf：配置对数据库的访问权限。
• postgresql.conf：配置PostgreSQL数据库服务器的相应的参数。
  修改postgresql.conf，允许远程连接：

listen_addresses = ‘*’

service postgresql restart
开启防火墙：
sudo ufw allow proto tcp from 192.168.1.0/24 to any port 5432

初始化

initdb -D /usr/local/pgsql/data

启停PostgreSQL

启停PostgreSQL
最重要的参数是-D, 指定数据区

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ -l logfile start/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ -l logfile restart/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ -l logfile stop
1

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ -l logfile start
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ -l logfile restart
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ -l logfile stop
1
2
3

/u01/pgsql_20160101/bin/postgres -D /u01/mini-rds/pgsql

pg_controldata data_path 显示Database信息

连接PostgreSQL

postgre的默认端口为5432

psql -p 3320 -d postgres    //-d 指定数据库名称
psql -U username -d databasename -h 127.0.0.1
psql -U username -h 127.0.0.1 -p 5432 postgres
psql -U username -d databasename -h 127.0.0.1psql -U username -h 127.0.0.1 -p 5432 postgres
1
2
3
4

采用\c切换数据库

创建数据库

本地创建: createdb databasename -O username -E UTF8
–e指定ip创建,小二申请的rds postgreSQL需要登录到跳板机后执行。
如:
createdb demodb -p 5432 -h 127.0.0.1 -U username -E UTF8 -e

createdb databasename -O username -E UTF8 –e
1

指定ip创建,小二申请的rds postgreSQL需要登录到跳板机后执行。

createdb demodb -p 5432 -h 127.0.0.1 -U username -E UTF8 -e
1

查看数据库

\l即可

创建用户

createuser username –P

或者用psql：

psql -p 5432 -d postgres -q -c “create user ku superuser replication login encrypted password ‘8ctPv7jcIdpeqavv’”

查看用户

\du

给用户授权

GRANT ALL PRIVILEGES ON DATABASE testDB TO testUser;

删除用户

dropuser username

创建Schema和Table

CREATE SCHEMA custom;
ALTER SCHEMA custom OWNER TO custom;
CREATE TABLE custom.test (id integer not null);

删除数据库

在liunx命令行下删除数据库：
dropdb -U postgres databasename在psql命令行下删除数据库【关键字必须大写】psql#DROP DATABASE demodb
dropdb -U postgres databasename
在psql命令行下删除数据库【关键字必须大写】
psql#DROP DATABASE demodb

修改用户密码

ALTER USER username WITH PASSWORD ‘passwd’;

创建模式
CREATE SCHEMA myschema;

删除模式
DROP SCHEMA myschema;
如果要删除模式及其所有对象，请使用级联删除：
DROP SCHEMA myschema CASCADE;

d命令

1. 如果\d命令什么都不带，将列出当前数据库中的所有表。

2. \d后面跟一个表名，表示显示这个表的结构定义。如 \d userinfo

3. \d后面可以跟一通配符"*“或”?"

4. \d+命令，该命令将显示比\d命令更详细的信息

5. \db显示所有表空间

6. 列出数据库所有角色或用户\du或\dg

创建模式
CREATE SCHEMA myschema;

删除模式
DROP SCHEMA myschema;如果要删除模式及其所有对象，请使用级联删除：DROP SCHEMA myschema CASCADE;

PostgreSQL资源大全

概念书籍

《PostgreSQL Introduction and Concepts》

开发书籍

《PostgreSQL开发必备参考手册》
《PostgreSQL Server Programming》

管理类书籍

《PostgreSQL 9 Administration Cookbook》
《PostgreSQL 9.0 High Performance》
《从小工到专家》

内核类书籍

http://www.postgresql.org/developer/backend/
http://wiki.postgresql.org/wiki/Backend_flowchart
《 PostgreSQL 内核分析》

官方手册

http://www.postgresql.org/docs/

中文手册

http://www.postgres.cn/document

PostgreSQL, 内核, Greenplum 圈子
https://yq.aliyun.com/groups/29
https://yq.aliyun.com/groups/12
https://yq.aliyun.com/groups/13

云栖问答

https://yq.aliyun.com/ask

代码

http://doxygen.postgresql.org/
https://commitfest.postgresql.org/
http://git.postgresql.org

PostgreSQL JDBC 驱动

http://jdbc.postgresql.org
http://jdbc.postgresql.org/development/privateapi/

PostgreSQL ODBC 驱动

http://www.postgresql.org/ftp/odbc/versions/src/

PostgreSQL 扩展插件

http://pgfoundry.org
http://pgxn.org/

GUI工具(pgAdmin)
http://www.pgadmin.org/

安全漏洞
http://cve.mitre.org/cgi-bin/cvekey.cgi?keyword=postgresql

中文社区
http://www.postgres.cn

全球PG社区峰会，事件
https://wiki.postgresql.org/wiki/Events

国外FAQ社区, BLOG
http://stackoverflow.com/questions/tagged/postgresql
http://momjian.us
http://www.pgexperts.com
http://blog.2ndquadrant.com/en

TODO
http://wiki.postgresql.org/wiki/Todo

PostGIS
http://www.postgis.org/
http://workshops.opengeo.org/postgis-intro/
http://www.opengeospatial.org/

分布式 PostgreSQL (Greenplum, HAWQ, Actian, Redshift, Asterdata, Postgres-XL, citus, deepgreen)

https://github.com/greenplum-db/gpdb
http://hawq.apache.org/
http://aws.amazon.com/redshift/
http://www.actian.com/products/big-data-analytics-platforms-hadoop/matrix-mpp-analytics-databases/
http://www.postgres-xl.org/
https://github.com/postgres-x2/postgres-x2
http://git.postgresql.org/gitweb/?p=postgres-xl.git;a=summary
https://wiki.postgresql.org/wiki/PL/Proxy
http://git.postgresql.org/gitweb/?p=plproxy.git;a=summary

并行计算 PostgreSQL (CPU, GPU)

http://vitessedata.com/
https://wiki.postgresql.org/wiki/PGStrom
https://github.com/pg-strom/devel

机器学习

http://madlib.incubator.apache.org/
https://cran.r-project.org/web/packages/PivotalR/index.html

个人站点

http://blog.163.com/digoal%40126

在线培训视频

PostgreSQL 数据库性能优化 从入门到进阶
PostgreSQL 数据库管理 从入门到进阶
PostgreSQL 2 DAY DBA
https://yq.aliyun.com/edu/lesson/45
http://www.tudou.com/home/digoal/playlist