PostgreSQL快速入门:PostgreSQL的安装和配置
一、在CentOS上安装PostgreSQL数据库 下面我们就以CentOS系统为例,给大家讲解一下PostgreSQL的安装过程。1.使用yum安装postgresql
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 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |