中文网站位置

跳转链接： http://www.postgres.cn/v2/download

安装命令一览

源码安装指导
Linux平台编译安装的快捷参考(Centos平台/Pg12.2为例)：
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-world
adduser 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	# (进入数据库内部)

详细操作过程：

1. 建用户

[root@VM_0_3_centos /]# useradd -d /postgres -m postgres
[root@VM_0_3_centos /]# chmod 777 postgres
[root@VM_0_3_centos /]# yum install lrzsz

2. postgres用户下载代码并解压缩

su - postgres
wget https://ftp.postgresql.org/pub/source/v12.2/postgresql-12.2.tar.gz
tar -xzvf postgresql-12.2.tar.gz

3. 拟安装至/postgres/pgsql

[postgres@VM-16-15-centos postgresql-12.5]$ ./configure --prefix=/postgres/pgsql

报错：

configure: error: readline library not found
If you have readline already installed, see config.log for details on the
failure.  It is possible the compiler isn't looking in the proper directory.
Use --without-readline to disable readline support.

root用户安装依赖：

[root@VM_0_3_centos /]# yum install perl-ExtUtils-Embed
[root@VM_0_3_centos /]# yum install readline-devel
[root@VM_0_3_centos /]# yum -y install zlib-devel
[root@VM_0_3_centos bin]# yum install gcc-c++

此时执行命令成功！

4. 编译

make world
make install-world

5. 设置并启动

[postgres@VM-16-15-centos postgresql-12.5]$ adduser postgres
adduser: user 'postgres' already exists
[postgres@VM-16-15-centos ~]$ mkdir /postgres/pgsql/data
[postgres@VM-16-15-centos pgsql]$ ll
total 20
drwxrwxr-x 2 postgres postgres 4096 Apr  2 11:10 bin
drwxrwxr-x 2 postgres postgres 4096 Apr  2 11:13 data
drwxrwxr-x 6 postgres postgres 4096 Apr  2 11:10 include
drwxrwxr-x 4 postgres postgres 4096 Apr  2 11:10 lib
drwxrwxr-x 8 postgres postgres 4096 Apr  2 11:10 share
/postgres/pgsql/bin/initdb -D /postgres/pgsql/data #初始化数据库
/postgres/pgsql/bin/pg_ctl -D /postgres/pgsql/data -l logfile start #启动数据库

/postgres/pgsql/bin/createdb genericdb #假定数据库名为gerericdb)
/postgres/pgsql/bin/psql genericdb	# (进入数据库内部)

6. 配置数据库

listen_addresses = '*'            # what IP address(es) to listen on;
port = 5432                    # (change requires restart)
max_connections = 300         # (change requires restart)
logging_collector = on           # Enable capturing of stderr and csvlog
log_directory = '/postgres/pglog'  # directory where log files are written,             
log_filename = 'postgresql-%u.log'   # log file name pattern,
log_file_mode = 0644                    #0640 or 0644
log_truncate_on_rotation = on
log_line_prefix = '%m%%%u%%%d'

相同路径下，配置配置pg_hba.conf,md5验证密码，trust不验证密码

# IPv4 local connections:
host    all             all            127.0.0.1/32            md5
host    all             all            0.0.0.0/0               md5
# IPv6 local connections:
host    all             all            ::1/128                 md5

启动及关闭数据库

[postgres@VM_0_3_centos pgdata]$ cd /postgres/pgsql/bin/
[postgres@VM_0_3_centos bin]$ pg_ctl start
[postgres@VM_0_3_centos bin]$ pg_ctl stop  -m fast

7. 未设置postgres环境变量，需要进bin目录执行psql命令

[postgres@VM-16-15-centos bin]$ ./psql -d postgres -U postgres
psql (12.5)
Type "help" for help.

postgres=# 

pg_ctl命令不管用，需要设置PGDATA 环境变量

[postgres@VM-16-15-centos bin]$ ./pg_ctl restart -m fast
pg_ctl: no database directory specified and environment variable PGDATA unset
Try "pg_ctl --help" for more information.

[postgres@sitdb1 ~]$ vi ~/.bash_profile

添加如下参数
PGHOME=/postgres/pgsql/bin
PGDATA=/postgres/pgsql/data
export  PGDATA
修改如下项
PATH=$PGHOME:$PATH:$HOME/bin
export  PATH
生效
[postgres@sitdb1 ~]$ source ~/.bash_profile


[postgres@VM_0_3_centos pgsql]$ vi ~/.bash_profile
[postgres@VM_0_3_centos pgsql]$ echo $PATH
/opt/java/jdk1.8.0_161/bin:/opt/java/jdk1.8.0_161/jre/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/postgres/.local/bin:/home/postgres/bin
[postgres@VM_0_3_centos pgsql]$ source ~/.bash_profile
[postgres@VM_0_3_centos pgsql]$ echo $PATH
/postgres/pgsql/bin:/postgres/.local/bin:/postgres/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/postgres/bin

此时可以任意位置使用bin下的命令了

建库、表空间及用户

1.创建表空间和数据库实例
注：表空间必须不在PGDATA目录中

[postgres@VM_0_3_centos pg_log]$ cd /postgres/pgdb/tablespace/

创建表空间数据文件

[postgres@VM_0_3_centos tablespace]$ mkdir ts_ibank

2. 使用PG管理员连接默认数据库postgres创建表空间

[postgres@VM_0_3_centos tablespace]$ psql -d postgres -U postgres
psql (9.5.7)
Type "help" for help.
postgres=# create tablespace ts_ibank location '/postgres/pgdb/tablespace/ts_ibank';
CREATE TABLESPACE
postgres=# select spcname from pg_tablespace;
  spcname   
------------
 pg_default
 pg_global
 ts_ibank
(3 rows)
postgres=# set default_tablespace = ts_ibank;
SET

如果psql报错symbol lookup error:psql:undefined symbol:PQconnectdbParams

是没找到动态链接库,指定下
vim ~/.bashrc
添加
export LD_LIBRARY_PATH=/postgres/pgsql/lib

3. 退出超级用户，创建数据库实例，并指定表空间

postgres=# \q
[postgres@VM_0_3_centos tablespace]$ createdb ibank -D ts_ibank;

4. 以超级用户访问ibank数据库，创建user和对应的schema

[postgres@VM_0_3_centos tablespace]$ psql -d ibank
psql (9.5.7)
Type "help" for help.
ibank=# create role ibank login password 'ibank';
CREATE ROLE
ibank=# create schema ibank authorization ibank;
CREATE SCHEMA
ibank=#

5. 创建只读用户view，授权可以访问ibank用户的表，创建完成

ibank=# create role view login password 'view';
CREATE ROLE
ibank=# grant connect on database ibank to view;
GRANT
ibank=# grant usage on schema ibank to view;
GRANT
ibank=# grant select on all tables in schema ibank to view;
GRANT

pg数据库的数据文件路径为/postgres/pgsql/data pg数据库的表空间路径为 /postgres/pgdb/tablespace

8. 常用命令
   | 命令 | 解释 |
   |–|--|
   | create user test with password ‘123456’
   drop user test | 创建与删除用户 |
   | create database “testdb” with owner=”test ”; | 建数据库 |
   | drop database testdb | 删数据库 |
   | \c dbname| 切换数据库 |
   | \l | 列举数据库 |
   | \dt | 列举表 |
   | \d tblname | 查看表结构 |