Postgresql 入门（1）

本文最后附上安装过程中遇到的问题，可以参考。

一、安装postgresql

打开postgresql 中文社区，参考其安装过程，傻瓜式操作。

其中需要装有 bzip2(解压命令)、gcc、gcc-c++、(这些是编译使用)

# 一般在编译前，还需要这些库：
yum install -y gcc gcc-c++
yum install docbook-dtds docbook-style-xsl fop libxslt -yyum install readline-devel.x86_64  zlib-devel.x86_64 -y
yum install -y ncurses-devel

下面是postgresql社区提供的解压指令：解压编译即可安装好postgresql了。

wget https://ftp.postgresql.org/pub/source/v12.2/postgresql-12.2.tar.bz2
tar xjvf postgresql*.bz2 #解压至一个目录
cd potgresql-12.2# 这几步操作需要费些时间，耐心等待
./configure --prefix=/opt/pgsql #拟安装至/opt/pgsql
make world
make install-worldadduser postgres #增加新用户，系统提示要给定新用户密码
mkdir /opt/pgsql/data #创建数据库目录
chown -R postgres:postgres /opt/pgsql/data
su - postgres #使用postgres帐号操作
/opt/pgsql/bin/initdb -D /opt/pgsql/data #初始化数据库
/opt/pgsql/bin/pg_ctl -D /opt/pgsql/data -l logfile start #启动数据库
/opt/pgsql/bin/createdb genericdb #假定数据库名为gerericdb)
/opt/pgsql/bin/psql genericdb # (进入数据库内部)

这里已经完成了基本安装，接下来使用上面创建的postgre用户操作数据库了。

如果中途./configure失败：

error1：configure: error: C preprocessor "/lib/cpp" fails sanity checkyum install kernel-headers

二、使用前的一些操作：

安装一些辅助工具

yum install mlocate # 这个工具可以帮我们查找psql：locate psql | grep /bin
updatedb
# 如果没有updatedb更新后台数据库，直接输入查找命令：locate，还是未能找到命令。
# 原因是Linux不是实时更新它的后台数据库，所以我们并不能马上执行。
locate inittab

执行 locate psql | grep /bin，查到的第一个就是我们需要的

编辑.bash_profile文件或.bashrc文件，添加命令别名

alias psql=/user/local/mysoft/pgsql/bin/psql

切换到Linux用户postgres，然后执行psql：

[root@localhost postgres]# su root #首先切换到postgres
[root@localhost postgres]# su - postgres #首先切换到postgres
[postgres@localhost ~]$ psql
psql (12.2)
Type "help" for help.postgres=# 

此时就在数据库postgres中了。使用 psql -d [databasename] 可登录其他数据库。注意：执行psql命令时，必须确认已配置postgresql的环境变量。

基本使用

更多使用参考菜鸟教程

创建用户

postgres=# create user adam with password '123456';
CREATE ROLE
postgres=#

需要注意： 本文使用的username均是根据自己的喜欢命名
　1. 要以英文分号结尾
2.密码需要引号包裹
3.用户名首字母不能大写

创建数据库

postgres=# create database testdb owner adam;
CREATE DATABASE

将数据库得权限，全部赋给某个用户

# 将dbtest所有权限赋值给username
postgres=# grant all on database testdb to adam; 
GRANT

查看版本

postgres=# select version();version                                                 
---------------------------------------------------------------------------------------------------------PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

查看数据库大小

postgres=# SELECT pg_size_pretty(pg_database_size('testdb')) As fulldbsize; fulldbsize 
------------7809 kB
(1 row)

查看所有的数据库的大小：

postgres=# select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database; datname  |  size   
-----------+---------postgres  | 7953 kBtemplate1 | 7809 kBtemplate0 | 7809 kBtestdb    | 7809 kB
(4 rows)

查看各数据库数据创建时间：

select datname,(pg_stat_file(format('%s/%s/PG_VERSION',
case
when spcname='pg_default' then 'base'
else 'pg_tblspc/'||t2.oid||'/PG_11_201804061/'
end,
t1.oid))).*
from pg_database t1,pg_tablespace t2 where t1.dattablespace=t2.oid;

按占空间大小，顺序查看所有表的大小

select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;

按占空间大小，顺序查看索引大小

select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc;

以你的普通Linux用户名，在数据库中创建同名的用户和数据库，如xxf，然后就可以本机直接连接到数据库xxf了。

[postgres@localhost ~]$ psql -d testdb
psql (12.2)
Type "help" for help.testdb=# grant all privileges on database testdb to adam;
GRANT

配置远程访问

PATH=$PATH:$HOME/.local/bin:$HOME/binexport PATHexport PGPORT=8432
export PGHOME=/user/local/mysoft/pgsql
export PGDATA=/user/local/mysoft/pgsql/data
export PATH=$PGHOME/bin:$PATH
export MANPATH=$PGHOME/share/man:$MANPATH
export LANG=en_US.UTF-8
export DATE='date +"%Y%m%d%H%M"'
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgresPATH=$PATH:$HOME/.local/bin:$HOME/bin:$PGHOME/bin
export PATH

执行指令使之生效 source /home/postgres/.bash_profile

执行下面指令之后就可以外网navicat访问了

[root@localhost etc]# firewall-cmd --zone=public --add-port=5432/tcp --permanent
success
[root@localhost etc]# firewall-cmd --reload
success

配置navicat连接

配置原创访问和外网访问的指令之后，可以远程连接了。

connections on Unix domain socket “/usr/mysoft/pgsql/data/.s.PGSQL.5432”?

[postgres@localhost ~]$ psql -U postgres -d postgres
psql: error: could not connect to server: could not connect to server: No such file or directoryIs the server running locally and acceptingconnections on Unix domain socket "/usr/mysoft/pgsql/data/.s.PGSQL.5432"?
[postgres@localhost ~]$ ln -s /tmp/.s.PGSQL.5432 /usr/mysoft/pgsql/data/.s.PGSQL.5432 #创建软连接
[postgres@localhost ~]$ ./postrestart.sh 
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
[postgres@localhost ~]$ psql
psql (12.2)
Type "help" for help.# 如果上面还不成功 可以修改 pg_hba.conf文件，在local all postgres trust 中添加 local all postgres trust
# 使postgres可以访问。此处的postgres用户并非Linux系统的postgres用户，而是export PGUSER=postgres中的用户
[root@localhost etc]# vi /usr/mysoft/pgsql/data/pg_hba.conf # "local" is for Unix domain socket connections only
local   all             all                                     trust
local   all             postgres                                trust[postgres@localhost ~]$ ./postrestart.sh 

PG::Error: ERROR: permission denied to create database

使用普用户创建数据库时候
PG::Error: ERROR: permission denied to create database解决方式：
使用postgres 登录
sudo -u postgres psqlpostgres=# ALTER ROLE xxxx CREATEROLE CREATEDB;xxxx: 普通用户名称赋予超级权限
ALTER ROLE xxxx CREATEROLE SUPERUSER;[postgresql官网](http://www.postgresql.org/docs/9.2/static/sql-alterrole.html)