PostgresSQL数据库的基本使用——新手入门

目录

介绍

postgresql是一个老牌的数据库,它的文档postgresql manuals中包含更多的内容。

PostgresSQL数据库的基本使用——新手入门

PostgreSQL的用户到底是这么回事?新建用户怎样才能用密码登陆?

部署启动

在CentOS上部署和用以容器的方式启动。

1. 在CentOS上使用

安装:

yum install -y  postgresql-server

启动前初始化:

postgresql-setup initdb

启动:

systemctl start postgresql

需要以postgre用户身份登陆管理:

su - postgres
psql

安装非默认版本的PostgreSQL,以9.6为例,如果安装其它版本将下面连接中的9.6换成对应版本号:

yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
yum install postgresql96
yum install postgresql96-server
export PATH=$PATH:/usr/pgsql-9.6/bin/
postgresql96-setup initdb
systemctl start postgresql-9.6
su - postgres 
psql
CREATE USER kong; CREATE DATABASE kong OWNER kong;
alter user kong with encrypted password '123456';
\q

2. 在容器中启动

下载镜像:

docker pull docker.io/postgres:latest

查看镜像docker.io/postgres可以知道,容器的entrypoint是镜像中的脚本docker-entrypoint.sh。 该脚本运行时会创建用户,并执行目录/docker-entrypoint-initdb.d中的.sh、.sql和.sql.gz文件。

可以把sql文件打包到镜像中或者挂载到/docker-entrypoint-initdb.d目录中,postgre启动运行时自动加载运行:

$cat Dockerfile
FROM postgres:latest
ADD ./your.sql  /docker-entrypoint-initdb.d

$docker build -t mypostgres:latest .

启动postgres:

docker run -idt \
	-e POSTGRES_PASSWORD="alice" \
	-e POSTGRES_USER="alice" \
	-e POSTGRES_DB="alice"  \
	-p 5432:5432  \
	mypostgres:latest

3. 在 Mac 中使用

安装 postgres

在 Mac 上用 brew 安装:

安装完成后显示操作提示:

To migrate existing data from a previous major version of PostgreSQL run:
  brew postgresql-upgrade-database

[email protected] is keg-only, which means it was not symlinked into /usr/local,
because this is an alternate version of another formula.

If you need to have [email protected] first in your PATH run:
  echo 'export PATH="/usr/local/opt/[email protected]/bin:$PATH"' >> ~/.zshrc

For compilers to find [email protected] you may need to set:
  export LDFLAGS="-L/usr/local/opt/[email protected]/lib"
  export CPPFLAGS="-I/usr/local/opt/[email protected]/include"

For pkg-config to find [email protected] you may need to set:
  export PKG_CONFIG_PATH="/usr/local/opt/[email protected]/lib/pkgconfig"


To have launchd start [email protected] now and restart at login:
  brew services start [email protected]
Or, if you don't want/need a background service you can just run:
  pg_ctl -D /usr/local/var/[email protected] start

设置环境变量:

echo 'export PATH="/usr/local/opt/[email protected]/bin:$PATH"' >> ~/.zshrc

验证版本:

$ postgres -V
postgres (PostgreSQL) 11.6

命令行工具的单独安装

如果只是要从本地访问 postgres,可以只安装命令行工具:

$ brew install pgcli
...
If you need to have libpq first in your PATH run:
  echo 'export PATH="/usr/local/opt/libpq/bin:$PATH"' >> ~/.zshrc

For compilers to find libpq you may need to set:
  export LDFLAGS="-L/usr/local/opt/libpq/lib"
  export CPPFLAGS="-I/usr/local/opt/libpq/include"

For pkg-config to find libpq you may need to set:
  export PKG_CONFIG_PATH="/usr/local/opt/libpq/lib/pkgconfig"

启动 postgres

启动 postgres:

$ brew services start [email protected]
==> Successfully started `[email protected]` (label: [email protected])

查看状态:

$ brew services list |grep postgres
[email protected] started lijiao /Users/lijiao/Library/LaunchAgents/[email protected]

默认数据库文件路径:

$ ls /usr/local/var/[email protected]
PG_VERSION           pg_ident.conf        pg_snapshots         pg_wal
base                 pg_logical           pg_stat              pg_xact
global               pg_multixact         pg_stat_tmp          postgresql.auto.conf
pg_commit_ts         pg_notify            pg_subtrans          postgresql.conf
pg_dynshmem          pg_replslot          pg_tblspc            postmaster.opts
pg_hba.conf          pg_serial            pg_twophase          postmaster.pid

第一次登陆

本地登陆 postgres:

$ psql postgres
psql (11.6)
Type "help" for help.

postgres=#

默认创建的 role(用户):

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 lijiao    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

所在的系统的当前用户会被自动创建为 postgres 的超级用户,所以在本地可以直接用 psql postgres 登陆。

创建其它用户

创建一个新用户:

create user postgresdemo with password 'password123';

在本地用新用户登陆(注意指定 -h 127.0.0.1 -p 5432):

$ psql -h 127.0.0.1 -p 5432 -U postgresdemo
Password:
psql (11.6)
Type "help" for help.

postgres=>

本地登陆时,可能无需密码就成功了,远程登陆时可能密码正确也无法登陆,这是 postgres 的认证配置导致的:

$ cat /usr/local/var/[email protected]/pg_hba.conf |grep all
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

默认对本地全部信任(trust),没有配置其它来源访问。

用下面的配置允许 postgresdemo 用户从任何地址访问所有数据库,通过密码认证:

# TYPE  DATABASE   USER          ADDRESS      METHOD
  host  all        postgresdemo  0.0.0.0/0    password

添加配置后需要重启 postgresql,详细说明见: Postgres 新建用户怎样才能用密码登陆?

创建数据库

创建数据库并授权给 postgresdemo:

create database postgresdemo;
grant all on database  postgresdemo to postgresdemo;

如果要限制该数据库的访问方式,可以在 pg_hba.conf 添加类似配置:

# TYPE  DATABASE        USER            ADDRESS      METHOD
  host  postgresdemo    postgresdemo    0.0.0.0/0    password

数据库操作:

\list: lists all the databases in Postgres
\connect: connect to a specific database
\dt: list the tables in the currently connected database

配置

在CentOS中,postgre默认使用的数据目录是/var/lib/pgsql/9.6/,配置文件是/var/lib/pgsql/9.6/data/postgresql.confSetting Parameters

配置服务监听地址

Postgre默认监听localhost:5432,相关参数为listen_addressesport

listen_addresses='localhost,10.10.64.58'
port=5432

postgres用户创建/删除

这个需要认真说下,很多人在这踩坑。

Postgre的默认用户是postgres,需要在运行postgres的机器上,切换为系统的postgres用户,然后才能通过psql直接进入:

# su - postgres
Last login: Fri Sep 28 15:23:41 CST 2018 on pts/2
-bash-4.2$ psql
psql (9.2.24)
Type "help" for help.

postgres=#

创建新用户,进入postgres之后,使用create创建:

create user tony with password '123';

创建之后是不是就可以登陆了? 不是!新用户怎样才能用密码登陆?

查看用户使用du命令,删除用户使用drop user USERNAME

命令行psql

Postgresql的client命令是psql,通过psql --help可以查看具体用法。

在mac上可以用brew安装psql:

 brew install pgcli
 echo 'export PATH="/usr/local/opt/libpq/bin:$PATH"' >> ~/.bash_profile
 source ~/.bash_profile

登录数据库:

psql -U <用户名>  <数据库>

Connection options:
  -h, --host=HOSTNAME      database server host or socket directory (default: "local socket")
  -p, --port=PORT          database server port (default: "5432")
  -U, --username=USERNAME  database user name (default: "root")
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)

例如:

psql -h 172.19.133.100  -p 40001 -U kong

退出命令为\q,其它控制台命令:

\h:查看SQL命令的解释,比如\h select。
\?:查看psql命令列表。
\l:列出所有数据库。
\c [database_name]:连接其他数据库。
\d:列出当前数据库的所有表格。
\d [table_name]:列出某一张表格的结构。
\du:列出所有用户。
\e:打开文本编辑器。
\conninfo:列出当前数据库和连接的信息。

SQL语句

收录一些常用的SQL语句。

SQL语句——role

创建role,postgres的中的roleuser的包含更多的内容,user是可以login的role。

CREATE ROLE name;
DROP ROLE name;

所有的role信息存放在pg_roles表中:

SELECT rolname FROM pg_roles;

为了方便,pg支持了下面的命令:

createuser name
dropuser name

默认会有一个名为postgressuperuser

create role的语法:

CREATE ROLE name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

可以设置的role属性:

login privilege:         CREATE ROLE name LOGIN;
superuser status:        CREATE ROLE name SUPERUSER;
database creation:       CREATE ROLE name CREATEDB;
role creation:           CREATE ROLE name CREATEROLE;
initiating replication:  CREATE ROLE name REPLICATION LOGIN;
password:                CREATE ROLE name PASSWORD 'string';

使用alter role修改role的属性:

ALTER ROLE name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'

ALTER ROLE name RENAME TO new_name

ALTER ROLE { name | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER ROLE { name | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER ROLE { name | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
ALTER ROLE { name | ALL } [ IN DATABASE database_name ] RESET ALL

SQL语句——database

连接postgres数据库时必须指定一个数据库,第一个库是初始化时用postgresql-setup initdb命令创建的:

The first database is always created by the initdb command when the data storage area is initialized. 

Postgres支持数据库模版,数据库可以从模版创建,模版修改了,所有从这个模版创建的数据库都会随之修改。

数据库的创建语法:

CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ CONNECTION LIMIT [=] connlimit ] ]

创建数据库:

create database secured;

修改数据库:

ALTER DATABASE mydb SET geqo TO off;
ALTER DATABASE dbname RESET varname

删除数据库:

DROP DATABASE name;

赋予role操作database的权限,grant语句:

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

执行脚本

参考

  1. postgresql manuals
  2. alter role
  3. create role
  4. sql commands
  5. PostgreSQL的用户到底是这么回事?新用户怎样才能用密码登陆?
  6. Getting Started with PostgreSQL on Mac OSX

推荐阅读

赞助商广告

Copyright @2011-2019 All rights reserved. 转载请添加原文连接,合作请加微信lijiaocn或者发送邮件: [email protected],备注网站合作

友情链接:  李佶澳的博客  小鸟笔记  软件手册  编程手册  运营手册  爱马影视  网络课程  奇技淫巧  课程文档  精选文章  发现知识星球  百度搜索 谷歌搜索