南京服务托管满足不同阶段的需求
南京服务器托管租用-行业新闻
Centos下PostgreSQL的安装和配置
发布时间:2017-2-23阅读:1004
PostgreSQL是一个功能强大,开源对象关系型数据库系统。它拥有超过15年的持续开发和业经验证的架构,具有良好的可靠性,数据完整性和正确性。
自从MySQL被Oracle收购以后,PostgreSQL逐渐成为开源关系型数据库的首选。

PostgreSQL是一个功能强大,开源对象关系型数据库系统。它拥有超过15年的持续开发和业经验证的架构,具有良好的可靠性,数据完整性和正确性。 

PostgreSQL能够运行在所有主流操作系统,包括Linux,UNIX(AIX,BSD,HP-UX,SGI IRIX,Mac OS X上的Solaris,Tru64的),和Windows。

1.使用yum安装postgresql 

[pengchengxiang@localhost ~]$ sudo yum install postgresql-server.x86_64 
Loaded plugins: fastestmirror, refresh-packagekit, security 
Setting up Install Process 
Loading mirror speeds from cached hostfile 
 * base: mirrors.btte.net 
 * extras: mirror.bit.edu.cn 
 * updates: mirror.bit.edu.cn 
Resolving Dependencies 
--> Running transaction check 
---> Package postgresql-server.x86_64 0:8.4.20-3.el6_6 will be installed 
--> Processing Dependency: postgresql(x86-64) = 8.4.20-3.el6_6 for package: postgresql-server-8.4.20-3.el6_6.x86_64 
--> Running transaction check 
---> Package postgresql.x86_64 0:8.4.20-3.el6_6 will be installed 
--> Finished Dependency Resolution 
Dependencies Resolved 
================================================================================ 
 Package                 Arch         Version               Repository     Size 
================================================================================ 
Installing: 
 postgresql-server       x86_64       8.4.20-3.el6_6        updates       3.4 M 
Installing for dependencies: 
 postgresql              x86_64       8.4.20-3.el6_6        updates       2.6 M 
Transaction Summary 
================================================================================ 
Install       2 Package(s) 
Total download size: 6.0 M 
Installed size: 28 M 
Is this ok [y/N]: y 
Downloading Packages: 
(1/2): postgresql-8.4.20-3.el6_6.x86_64.rpm              | 2.6 MB     00:02      
(2/2): postgresql-server-8.4.20-3.el6_6.x86_64.rpm       | 3.4 MB     00:06      
-------------------------------------------------------------------------------- 
Total                                           680 kB/s | 6.0 MB     00:09      
Running rpm_check_debug 
Running Transaction Test 
Transaction Test Succeeded 
Running Transaction 
  Installing : postgresql-8.4.20-3.el6_6.x86_64                             1/2  
  Installing : postgresql-server-8.4.20-3.el6_6.x86_64                      2/2  
  Verifying  : postgresql-8.4.20-3.el6_6.x86_64                             1/2  
  Verifying  : postgresql-server-8.4.20-3.el6_6.x86_64                      2/2  
Installed: 
  postgresql-server.x86_64 0:8.4.20-3.el6_6                                      
Dependency Installed: 
  postgresql.x86_64 0:8.4.20-3.el6_6                                             
Complete!

2.初始化postgresql数据库 

[pengchengxiang@localhost ~]$ sudo service postgresql initdb 
Initializing database: [  OK  ]

2.启动postgresql服务 

[pengchengxiang@localhost ~]$ sudo service postgresql start 
Starting postgresql service: [  OK  ]

3.查看postgresql的服务状态 

[pengchengxiang@localhost ~]$ sudo service postgresql status 
postmaster (pid  3496) is running...

问题:如果你在没有进行初始化数据库之前就启东postgrepsql服务,则会报错如下: 

[pengchengxiang@localhost ~]$ sudo service postgresql start 
/var/lib/pgsql/data is missing. Use "service postgresql initdb" to initialize the cluster first. 
[FAILED]

二、连接PostgreSQL数据库 

如果想连接到数据库,需要切换到postgres用户下,然后使用psql连接到数据库中。在该用户下连接数据库,是不需要密码的。 

1.切换的postgres用户,并连接数据库 

[pengchengxiang@localhost ~]$ sudo su - postgres 
-bash-4.1$ psql 
psql (8.4.20) 
Type "help" for help. 
postgres=#

2.列出所有的数据库 

postgres=# \l 
                                  List of databases 
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges    
-----------+----------+----------+-------------+-------------+----------------------- 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |  
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres 
                                                             : postgres=CTc/postgres 
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres 
                                                             : postgres=CTc/postgres 
(3 rows)

3.退出数据库 

postgres=# \q 
-bash-4.1$

三、PostgreSQL数据库目录 

默认安装上,PostgreSQL的数据库目录在/var/lib/pgsql/data目录。 

-bash-4.1$ ls -l /var/lib/pgsql/data/ 
total 80 
drwx------. 5 postgres postgres  4096 Nov 16 23:55 base 
drwx------. 2 postgres postgres  4096 Nov 16 23:55 global 
drwx------. 2 postgres postgres  4096 Nov 16 23:55 pg_clog 
-rw-------. 1 postgres postgres  3411 Nov 16 23:55 pg_hba.conf 
-rw-------. 1 postgres postgres  1631 Nov 16 23:55 pg_ident.conf 
drwx------. 2 postgres postgres  4096 Nov 17 00:00 pg_log 
drwx------. 4 postgres postgres  4096 Nov 16 23:55 pg_multixact 
drwx------. 2 postgres postgres  4096 Nov 17 00:02 pg_stat_tmp 
drwx------. 2 postgres postgres  4096 Nov 16 23:55 pg_subtrans 
drwx------. 2 postgres postgres  4096 Nov 16 23:55 pg_tblspc 
drwx------. 2 postgres postgres  4096 Nov 16 23:55 pg_twophase 
-rw-------. 1 postgres postgres     4 Nov 16 23:55 PG_VERSION 
drwx------. 3 postgres postgres  4096 Nov 16 23:55 pg_xlog 
-rw-------. 1 postgres postgres 16886 Nov 16 23:55 postgresql.conf 
-rw-------. 1 postgres postgres    57 Nov 16 23:55 postmaster.opts 
-rw-------. 1 postgres postgres    45 Nov 16 23:55 postmaster.pid

四、PostgrepSQL的简单配置 

PostgreSQL数据库的配置主要是通过修改数据目录下的postgresql.conf文件来实现的。 

1.修改监听的ip和端口 

使用postgresql用户连接数据库后,进入到/var/lib/pgsql/data目录下,编辑postgresql.conf文件: 

# - Connection Settings - 
#listen_addresses = '*'                 # what IP address(es) to listen on; 
                                        # comma-separated list of addresses; 
                                        # defaults to 'localhost', '*' = all 
                                        # (change requires restart) 
#port = 5432                            # (change requires restart)

修改这两个参数之后,需要重启之后才能生效 

[pengchengxiang@localhost ~]$ sudo service postgresql restart 
Stopping postgresql service: [  OK  ] 
Starting postgresql service: [  OK  ]

2.修改数据库log相关的参数 

日志收集,一般是打开的 

# This is used when logging to stderr: 
logging_collector = on                  # Enable capturing of stderr and csvlog 
                                        # into log files. Required to be on for 
          ,                               # csvlogs. 
                                        # (change requires restart)

日志目录,一般使用默认值 

# These are only used if logging_collector is on: 
log_directory = 'pg_log'                # directory where log files are written, 
                                        # can be absolute or relative to PGDATA

只保留一天的日志,进行循环覆盖 

log_filename = 'postgresql-%a.log'      # log file name pattern, 
                                        # can include strftime() escapes 
log_truncate_on_rotation = on           # If on, an existing log file of the 
                                        # same name as the new log file will be 
                                        # truncated rather than appended to. 
                                        # But such truncation only occurs on 
                                        # time-driven rotation, not on restarts 
                                        # or size-driven rotation.  Default is 
                                        # off, meaning append to existing files 
                                        # in all cases. 
log_rotation_age = 1d                   # Automatic rotation of logfiles will 
                                        # happen after that time.  0 disables. 
log_rotation_size = 0                   # Automatic rotation of logfiles will

3.内存参数的配置 

共享内存的大小,用于共享数据块。如果你的机器上有足够的内存,可以把这个参数改的大一些,这样数据库就可以缓存更多的数据块,当读取数据时,就可以从共享内存中读,而不需要再从文件上去读取。 

# - Memory - 
shared_buffers = 32MB                   # min 128kB 
                                        # (change requires restart)

单个SQL执行时,排序、hash json所用的内存,SQL运行完后,内存就释放了。 

本文来源:http://www.hh-data.com/Shownews_567.html
WWW.HH-DATA.COM,南京服务器托管第一品牌  TEL:400-025-9-467  QQ:21390019
本文由华昊数据整理发布,修订2017-2-23

Baidu
 
QQ:21390019
QQ:925493756
点击这里给我发消息
点击这里给我发消息
服务热线
025-58633651
 
友情链接:南京服务器托管 | 南京服务器租用 | 南京服务器手机版