Postgresql快速安装部署文档

PostgreSQL · ruyi · 于 10个月前发布 · 521 次阅读

1.下载和安装

在postgrelsql中文社区网站 (http://postgres.cn/download)可以下载最新版的postgresql,下载后如下直接授权后进行快速安装。

#chmod +x postgresql-9.5.1-1-linux-x64.run
#./postgresql-9.5.1-1-linux-x64.run --help
#useradd daemon

如果没有daemon用户会报如下错误: Error changing group of /tmp/postgresql_installer_f043189227/lib/postgresql to daemon

运行安装程序

#./postgresql-9.5.1-1-linux-x64.run --prefix /usr/local/postgresql --datadir /storage/ssd/postgresql5432/data --serverport 5432

安装完成后超级账号为postgres,系统和db里面都是这个账号 密码也是postgres

2.设置环境变量

#cat /usr/local/postgresql/pg_env.sh
#vim /etc/profile
export PATH=/usr/local/postgresql/bin:$PATH
export PGDATA=/storage/ssd/postgresql5432/data
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=5432
export PGLOCALEDIR=/usr/local/postgresql/share/locale
export MANPATH=$MANPATH:/usr/local/postgresql/share/man

#source /etc/profile
#echo $PGDATA
/storage/ssd/postgresql5432/data

3.启动和关闭

#su - postgres
查看PG状态
$pg_ctl status
pg_ctl: server is running (PID: 13195)
/usr/local/postgresql/bin/postgres

关闭PG
$pg_ctl stop -D  $PGDATA
waiting for server to shut down.... done
server stopped

启动PG
[postgres@a1-dba-test-242-13 /usr/local/postgresql]
$pg_ctl start -D  $PGDATA
server starting

重启PG
$pg_ctl restart -D  $PGDATA
waiting for server to shut down.... done
server stopped
server starting

4.登录数据库

本地登录,直接输入psql,后输入密码即可登录
$psql
Password:
或者:
$psql -h 127.0.0.1 -p 5432 testdb postgres


远程访问,默认是是允许本地连接,没有远程访问权限,访问会报如下错误

$psql -h 10.128.242.13 -p 5432 testdb1  postgres
psql.bin: FATAL:  no pg_hba.conf entry for host "10.128.176.103", user "postgres", database "testdb1", SSL off

修改如下,主要是postgresql.conf和pg_hba.conf两个配置文件控制,修改完需要重启PG:
$vim postgresql.conf
listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all

$vim pg_hba.conf 
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             10.128.242.13/32            trust
host    all             all             10.128.0.0/16        password

5.配置文件优化

$vim postgresql.conf 

连接相关配置项:
listen_addresses = '*'         
port = 5432                             
max_connections = 1000
superuser_reserved_connections = 3
内存相关:
shared_buffers = 2GB
temp_buffers = 128MB
work_mem = 16MB 
work_mem = 8MB                         
maintenance_work_mem = 128MB  

WAL日志:
wal_level = minimal                                                      
fsync = on    
synchronous_commit = on                                            
wal_sync_method = fsync

错误日志:
logging_collector = on
log_rotation_age = 1d                                                     
log_rotation_size = 10MB

6.创建数据库和用户


postgres=# create database blackhole encoding 'utf8';
CREATE DATABASE

查看数据库
postgres=# \l blackhole
                               List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    | Access privileges 
-----------+----------+----------+------------+------------+-------------------
 blackhole | postgres | UTF8     | zh_CN.utf8 | zh_CN.utf8 |

创建用户 
postgres=# create user blackhole_user login password 'xxxxx';
CREATE ROLE 

更新数据库所属用户
blackhole=# alter database blackhole owner to blackhole_user;
ALTER DATABASE
blackhole=# \l blackhole
                                  List of databases
   Name    |     Owner      | Encoding |  Collate   |   Ctype    | Access privileges 
-----------+----------------+----------+------------+------------+-------------------
 blackhole | blackhole_user | UTF8     | zh_CN.utf8 | zh_CN.utf8 | 
(1 row)

创建schema
blackhole=# create schema blackhole_test authorization blackhole_user;
CREATE SCHEMA
blackhole=# \dn
         List of schemas
      Name      |     Owner      
----------------+----------------
 blackhole      | blackhole_user
 blackhole_test | blackhole_user
 public         | postgres
 
 
连接到数据库/切换数据库
blackhole=> \c testdb1
You are now connected to database "testdb1" as user "blackhole_user".

blackhole=# create schema blackhole_test authorization blackhole_user;
CREATE SCHEMA

使用新用户本地登录
$psql -h127.0.0.1 -p 5432 blackhole blackhole_user

使用新用户远程登录
$psql -h 10.128.242.13 -p 5432 blackhole  blackhole_user
Password for user blackhole_user: 
psql.bin: FATAL:  password authentication failed for user "blackhole_user"
[16:03:58postgres@a1-test-dbtest3-176-103 ~$/usr/local/postgresql] 
$psql -h 10.128.242.13 -p 5432 blackhole  blackhole_user
Password for user blackhole_user: 
psql.bin (9.5.1)
Type "help" for help.

blackhole=> \d
            List of relations
 Schema | Name  | Type  |     Owner      
--------+-------+-------+----------------
 public | test1 | table | blackhole_user
(1 row)

blackhole=> \dn
         List of schemas
      Name      |     Owner      
----------------+----------------
 blackhole      | blackhole_user
 blackhole_test | blackhole_user
 public         | postgres
(3 rows)

blackhole=> 

创建只读账号
postgres=# create user blackhole_readonly  login password 'xxxxx';
CREATE ROLE
postgres=# \c blackhole
You are now connected to database "blackhole" as user "postgres".
blackhole=# grant usage on schema public to blackhole_readonly;
GRANT
blackhole=# grant select on all tables in schema public to blackhole_readonly;
GRANT
blackhole=# alter default privileges in schema public grant select on tables to blackhole_readonly;
ALTER DEFAULT PRIVILEGES
blackhole=# 

blackhole=> alter user blackhole_readonly set default_transaction_read_only = on;
ALTER ROLE
blackhole=>

本文由 ruyi 创作,采用 知识共享署名 3.0 中国大陆许可协议 进行许可。 可自由转载、引用,但需署名作者且注明文章出处。


共收到 0 条回复 Postgresql 安装部署
没有找到数据。
回复本帖 (需要登录)