postgresql小记 源码安装

1 Requirements

安装以下的软件包

The following software packages are required for building PostgreSQL:

rpm -q  make gcc gzip readline readline-devel zlib zlib-devel

查看版本

make --version

which make tar gzip

磁盘空间需求

disk space

source tree:    100 MB

installation directory:    20MB

empty database cluster:    35MB

databases:    5倍文件

2 Getting The Source

二进制包

Binary Package

Linux downloads (Red Hat family)

postgresql-client            libraries and client binaries

postgresql-server            core database server

postgresql-contrib        additional supplied modules

postgresql-devel            libraries and headers for C language development

pgadmin4                    pgAdmin 4 graphical administration utility

或者

源码包

Source code

The PostgreSQL 10.4 sources can be obtained from the download section of our website: https://www.postgresql.org/download/. You should get a file named postgresql-10.4.tar.gz

gunzip postgresql-10.4.tar.gz

tar xf postgresql-10.4.tar

tar -xzvf

3 Installation Procedure安装软件

安装方式1：使用发行版二进制包的安装，配置本地yum源即可

yum install postgresql

yum install postgresql-server    

yum install postgresql-contrib

yum install postgresql-devel

查看安装文件路径

rpm -qal |grep postgresql

/etc/rc.d/init.d/postgresql

查看home路径

cat /etc/rc.d/init.d/postgresql | grep -n PGDATA

[root@db pgsql]# cat /etc/rc.d/init.d/postgresql | grep -n PG

14:# PGVERSION is the full package version, e.g., 8.4.0

16:PGVERSION=8.4.20

17:# PGMAJORVERSION is major version, e.g., 8.4 (this should match PG_VERSION)

18:PGMAJORVERSION=`echo "$PGVERSION" | sed 's/^\([0-9]*\.[0-9]*\).*$/\1/'`

43:PGENGINE=/usr/bin

44:PGPORT=5432

45:PGDATA=/var/lib/pgsql/data

46:PGLOG=/var/lib/pgsql/pgstartup.log

48:PG_OOM_ADJ=-17

53:export PGDATA

安装方式2：源码安装步骤

首先卸载掉原来的pg，如果已经安装的话

rpm -e postgresql postgresql-server postgresql-contrib postgresql-devel

gunzip postgresql-10.4.tar.gz

tar xf postgresql-10.4.tar

useradd pg

passwd pg

chown -R pg:pg postgresql-10.4

chmod 775 postgresql-10.4

mkdir -p /opt/postgres

chown pg:pg /opt/postgres

chmod 775 /opt/postgres

su - pg

1) Configuration

./configure --prefix=/opt/postgres

2) Build

make

All of PostgreSQL successfully made. Ready to install.

or

make world

PostgreSQL, contrib, and documentation successfully made. Ready to install.

3) Regression Tests(optional)

make check

4) Installing the Files

make install

or

make install -docs

make install -world

Uninstallation

make uninstall

https://www.postgresql.org/docs/10/static/install-procedure.html

4 Post-Installation Setup安装后的步骤

su - pg

vi .bash_profile

export LD_LIBRARY_PATH=/opt/postgres/lib

export PGHOME=/opt/postgres/

export PGDATA=/opt/postgres/data

export PATH=$PGHOME/bin:$PATH

Creating a Database Cluster

Before you can do anything, you must initialize a database storage area on disk. We call this a database cluster. (The SQL standard uses the term catalog cluster.) A database cluster is a collection of databases that is managed by a single instance of a running database server. After initialization, a database cluster will contain a database named postgres, which is meant as a default database for use by utilities, users and third party applications. The database server itself does not require the postgres database to exist, but many external utility programs assume it exists. Another database created within each cluster during initialization is called template1. As the name suggests, this will be used as a template for subsequently created databases; it should not be used for actual work.

$ initdb -D /opt/postgres/data

如果配置了PGDATA可忽略 -D

Starting the Database Server

Before anyone can access the database, you must start the database server. The database server program is called postgres. The postgres program must know where to find the data it is supposed to use. This is done with the -D option. Thus, the simplest way to start the server is:

$ pg_ctl start -l logfile

will start the server in the background and put the output into the named log file

如果配置了PGDATA可忽略 -D

pg_ctl stop

连接数据库

1 修改数据库默认参数配置

vi $PGDATA/postgresql.conf

listen_addresses = '*'

port = 5432

max_connections=2000

#for memory

shared_buffers = 128MB   #total_memory*25%

checkpoint_completion_target = 0.8

# for log  

log_min_messages = warning  #default

log_directory = 'pg_log'  #default

logging_collector=on

log_destination = 'csvlog'

log_statement = 'ddl'

log_hostname = on   

#for archivelog

fsync = on    #default

synchronous_commit = on  #default

wal_level = archive

archive_mode = on

archive_command = 'copy  "%p" /backup/archivelog/%f'

注：%p 要用双引号括起来处理安装目录中有空格的情况。

手动创建归档存放路径 /backup/archivelog/ 否则无法归档。

2 修改网络访问控制

vi $PGDATA/pg_hba.conf

增加一行

host    all             all             0.0.0.0/0              md5

允许任何一个客户端使用正确的用户名和密码访问

重新加载，使配置生效。

pg_ctl reload

注意：

trust，本地可以使用psql -U postgres直接登录服务器；

peer，本地可以使用psql -h 127.0.0.1 -d postgres -U postgres直接登录服务器 psql -h 127.0.0.1 -d postgres -U pg

md5， 本机不适用密码登录数据库

在pg_hba.conf条目约从上到下有效性递减。如：第一条同意某主机访问，第二条禁止某主机访问，则该主机可以访问。

> psql -h 192.168.6.12 -d postgres -U pg -p 5432

设置PostgreSQL开机自启动

su - root

1) PostgreSQL的开机自启动脚本位于PostgreSQL源码目录的contrib/start-scripts路径下

linux文件即为linux系统上的启动脚本

2) 修改linux文件属性，添加X属性

chmod a+x linux

3) 复制linux文件到/etc/init.d目录下，更名为postgresql

cp linux /etc/init.d/postgresql

4) 修改/etc/init.d/postgresql文件中的环境变量

# Installation prefix

prefix=/opt/postgres/

# Data directory

PGDATA="/opt/postgres/data"

# Who to run the postmaster as, usually "postgres".  (NOT "root")

PGUSER=pg

5) 启动服务 postgresql

service postgresql start

6) 设置postgresql服务开机自启动

chkconfig --add postgresql

chkconfig --list PostgreSQL