PostgreSQL快速入门：PostgreSQL的安装和配置

一、在CentOS上安装PostgreSQL数据库 

下面我们就以CentOS系统为例，给大家讲解一下PostgreSQL的安装过程。 

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运行完后，内存就释放了。 

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