PostgreSQL快速入門：PostgreSQL的安裝和配置

jopen 8年前發布 | 55K 次閱讀 PostgreSQL 數據庫服務器

<p style="padding-bottom:0px;background-color:transparent;margin-top:0px;word-wrap:break-word;white-space:pre;margin-bottom:0px;color:windowtext;font-size:6pt;vertical-align:baseline;padding-top:0px;-webkit-user-select:text;-webkit-user-drag:none;-webkit-tap-highlight-color:transparent;" class="Paragraph SCX256515079" xml:lang="ZH-CN"><span style="padding-bottom:0px;line-height:15px;margin:0px;padding-left:0px;padding-right:0px;font-family:'Calibri Light', sans-serif;white-space:normal;color:windowtext;font-size:10pt;font-weight:bold;padding-top:0px;-webkit-user-select:text;-webkit-user-drag:none;-webkit-tap-highlight-color:transparent;" class="TextRun SCX256515079" xml:lang="ZH-CN">一、在CentOS上安裝PostgreSQL數據庫</span> </p> </div>

<p style="padding-bottom:0px;background-color:transparent;margin-top:0px;word-wrap:break-word;white-space:pre;margin-bottom:0px;color:windowtext;font-size:6pt;vertical-align:baseline;padding-top:0px;-webkit-user-select:text;-webkit-user-drag:none;-webkit-tap-highlight-color:transparent;" class="Paragraph SCX256515079" xml:lang="ZH-CN"><span style="padding-bottom:0px;line-height:15px;margin:0px;padding-left:0px;padding-right:0px;font-family:'Calibri Light', sans-serif;white-space:normal;color:windowtext;font-size:10pt;padding-top:0px;-webkit-user-select:text;-webkit-user-drag:none;-webkit-tap-highlight-color:transparent;" class="TextRun SCX256515079" xml:lang="ZH-CN">下面我們就以CentOS系統為例，給大家講解一下PostgreSQL的安裝過程。</span> </p> </div>

[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!</pre>

</div>

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

</div>

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

</div>

<p style="padding-bottom:0px;background-color:transparent;margin-top:0px;word-wrap:break-word;white-space:pre;margin-bottom:0px;color:windowtext;font-size:6pt;vertical-align:baseline;padding-top:0px;-webkit-user-select:text;-webkit-user-drag:none;-webkit-tap-highlight-color:transparent;" class="Paragraph SCX256515079" xml:lang="ZH-CN"><span style="padding-bottom:0px;line-height:15px;margin:0px;padding-left:0px;padding-right:0px;font-family:'Calibri Light', sans-serif;white-space:normal;color:windowtext;font-size:10pt;font-weight:bold;padding-top:0px;-webkit-user-select:text;-webkit-user-drag:none;-webkit-tap-highlight-color:transparent;" class="TextRun SCX256515079" xml:lang="ZH-CN">3.查看postgresql的服務狀態</span> </p> </div>

[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運行完后，內存就釋放了。

# actively intend to use prepared transactions. 
#work_mem = 1MB                         # min 64kB

來自： http://blog.csdn.net/p106786860/article/details/49877483

本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1451809091933.html

PostgreSQL 數據庫服務器

PostgreSQL快速入門：PostgreSQL的安裝和配置

相關經驗

相關資訊

相關文檔

目錄

PostgreSQL快速入門：PostgreSQL的安裝和配置

Dependencies Resolved

Package Arch Version Repository Size

Transaction Summary

相關經驗

相關資訊

相關文檔

目錄