postgresql数据库部署

运维开发技术交流群欢迎大家加入一起学习（QQ:722381733）

一、postgresql数据库部署

　　1、前往postgresql安装包的目录（这里我部署的是10.5的版本）

[root@web1 ~]# cd /package/

[root@web1 package]# ls

apache-tomcat-8.5..tar.gz  jdk-8u131-linux-x64.tar.gz  postgresql-10.5.tar.gz  redis-3.2..tar.gz

[root@web1 package]#

　　2、解压缩包

[root@web1 package]# tar xf postgresql-10.5.tar.gz

[root@web1 package]# ls

apache-tomcat-8.5..tar.gz  jdk-8u131-linux-x64.tar.gz  postgresql-10.5  postgresql-10.5.tar.gz  redis-3.2..tar.gz

[root@web1 package]#

　　3、进入解压后生成的目录文件进行编译

[root@web1 postgresql-10.5]# ./configure --prefix=/usr/local/pgsql-10.5

checking build system type... x86_64-pc-linux-gnu

checking host system type... x86_64-pc-linux-gnu

checking which template to use... linux

checking whether NLS is wanted... no

checking for default port number...

checking for block size... 8kB

checking for segment size... 1GB

checking for WAL block size... 8kB

checking for WAL segment size... 16MB

　　4、提示如下信息是缺少包文件

checking for library containing readline... no

configure: error: readline library not found

If you have readline already installed, see config.log for details on the

failure.  It is possible the compiler isn't looking in the proper directory.

Use --without-readline to disable readline support.

　　5、安装相应的包文件文件

[root@web1 postgresql-10.5]#yum install ncurses* readline* zlib* -y

######################################

[root@web1 postgresql-10.5]# rpm -aq ncurses* readline* zlib*

readline-devel-6.2-.el7.x86_64

zlib-static-1.2.-.el7.x86_64

ncurses-libs-5.9-14.20130511.el7_4.x86_64

ncurses-devel-5.9-14.20130511.el7_4.x86_64

readline-static-6.2-.el7.x86_64

zlib-devel-1.2.-.el7.x86_64

readline-6.2-.el7.x86_64

ncurses-base-5.9-14.20130511.el7_4.noarch

ncurses-5.9-14.20130511.el7_4.x86_64

zlib-1.2.-.el7.x86_64

[root@web1 postgresql-10.5]#

　　6、安装完成后重新编译下

[root@web1 postgresql-10.5]# ./configure --prefix=/usr/local/pgsql-10.5

checking build system type... x86_64-pc-linux-gnu

checking host system type... x86_64-pc-linux-gnu

checking which template to use... linux

checking whether NLS is wanted... no

checking for default port number...

checking for block size... 8kB

checking for segment size... 1GB

checking for WAL block size... 8kB

checking for WAL segment size... 16MB

checking for gcc... gcc

　　7、解析文件（在postgresql-10.5目录执行：make install），最后输出结果没提示异常【error】便可开始安装了

rm -f pg_regress.o && ln -s ../../../src/test/regress/pg_regress.o .

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql-10.5/lib',--enable-new-dtags  -lpgcommon -lpgport -lpthread -lz -lreadline -lrt -lcrypt -ldl

make[]: Leaving directory `/package/postgresql-10.5/src/test/isolation'

make -C test/perl all

make[]: Entering directory `/package/postgresql-10.5/src/test/perl'

make[]: Nothing to be done for `all'.

make[]: Leaving directory `/package/postgresql-10.5/src/test/perl'

make[]: Leaving directory `/package/postgresql-10.5/src'

make -C config all

make[]: Entering directory `/package/postgresql-10.5/config'

make[]: Nothing to be done for `all'.

make[]: Leaving directory `/package/postgresql-10.5/config'

All of PostgreSQL successfully made. Ready to install.

　　8、开始安装（在postgresql-10.5目录执行：make install），最后输出结果没提示异常【error】便表示安装成功

/bin/install -c -m  Makefile.global '/usr/local/pgsql-10.5/lib/pgxs/src/Makefile.global'

/bin/install -c -m  Makefile.port '/usr/local/pgsql-10.5/lib/pgxs/src/Makefile.port'

/bin/install -c -m  ./Makefile.shlib '/usr/local/pgsql-10.5/lib/pgxs/src/Makefile.shlib'

/bin/install -c -m  ./nls-global.mk '/usr/local/pgsql-10.5/lib/pgxs/src/nls-global.mk'

make[]: Leaving directory `/package/postgresql-10.5/src'

make -C config install

make[]: Entering directory `/package/postgresql-10.5/config'

/bin/mkdir -p '/usr/local/pgsql-10.5/lib/pgxs/config'

/bin/install -c -m  ./install-sh '/usr/local/pgsql-10.5/lib/pgxs/config/install-sh'

/bin/install -c -m  ./missing '/usr/local/pgsql-10.5/lib/pgxs/config/missing'

make[]: Leaving directory `/package/postgresql-10.5/config'

PostgreSQL installation complete.

　　9、安装完成后可以看到在/usr/local目录上成了pgsql-10.5目录

[root@web1 postgresql-10.5]# ll /usr/local/ |grep pgsql-10.5

drwxr-xr-x   root root    May  : pgsql-10.5

[root@web1 postgresql-10.5]#

　　10、创建用户及设置/usr/local/pgsql-10.5目录文件属主组

[root@web1 postgresql-10.5]# useradd postgres

[root@web1 postgresql-10.5]# chown -R postgres.postgres /usr/local/pgsql-10.5/

[root@web1 postgresql-10.5]# ll /usr/local/ |grep pgsql-10.5

drwxr-xr-x   postgres postgres    May  : pgsql-10.5

[root@web1 postgresql-10.5]#

　　11、给/usr/local/pgsql-10.5目录设置软连接（方便查看和管理吧....）

[root@web1 postgresql-10.5]# ln -s /usr/local/pgsql-10.5/ /usr/local/pgsql

[root@web1 postgresql-10.5]# ll /usr/local/pgsql

lrwxrwxrwx  root root  May  : /usr/local/pgsql -> /usr/local/pgsql-10.5/

[root@web1 postgresql-10.5]#

　　12、进入刚创建的postgres用户

[root@web1 postgresql-10.5]# su postgres

[postgres@web1 postgresql-10.5]$

　　13、设置环境变量（注意路径）

[postgres@web1 ~]$ vim ~/.bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

        . ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH=$PATH:/usr/local/pgsql-10.5/bin

PGDATA=/usr/local/pgsql-10.5/data

export PGDATA

export PATH

　　14、当前生效

[postgres@web1 ~]$ source ~/.bash_profile

[postgres@web1 ~]$

　　15、测试

[postgres@web1 ~]$ which psql

/usr/local/pgsql-10.5/bin/psql

[postgres@web1 ~]$ psql -V

psql (PostgreSQL) 10.5

　　16、初始化数据库（initdb --help查看讲情），指定库文件路径

[postgres@web1 ~]$ initdb /usr/local/pgsql-10.5/data

The files belonging to this database system will be owned by user "postgres".

This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".

The default database encoding has accordingly been set to "UTF8".

The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /usr/local/pgsql-10.5/data ... ok

creating subdirectories ... ok

selecting default max_connections ...

selecting default shared_buffers ... 128MB

selecting dynamic shared memory implementation ... posix

creating configuration files ... ok

running bootstrap script ... ok

performing post-bootstrap initialization ... ok

syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections

You can change this by editing pg_hba.conf or using the option -A, or

--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /usr/local/pgsql-10.5/data -l logfile start

[postgres@web1 ~]$

***在此就已经部署成功了，现在要做的就是修改下配置文件，优化下***

　　17、前往文件安装所在地，修改pg_hba.conf配置文件

[postgres@web1 data]$ cp pg_hba.conf pg_hba.conf.`date +%F`

[postgres@web1 data]$ vim pg_hba.conf

#######################################

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only

local   all             all                                     trust

# IPv4 local connections:

#host    all             all             127.0.0.1/            trust

host    all             all             0.0.0.0/               md5     #所有ip都可以通过密码连接

# IPv6 local connections:

host    all             all             ::/                 trust

# Allow replication connections from localhost, by a user with the

# replication privilege.

local   replication     all                                     trust

host    replication     all             127.0.0.1/            trust
######################################

　　18、前往文件安装所在地，修改postgresql.conf配置文件

[postgres@web1 data]$ cd /usr/local/pgsql/data/

[postgres@web1 data]$ cp postgresql.conf postgresql.conf.`date +%F`

[postgres@web1 data]$ vim postgresql.conf

#########################################

# - Connection Settings -

listen_addresses = '*'  #为了方便，监听所有# what IP address(es) to listen on;

                                        # comma-separated list of addresses;

                                        # defaults to 'localhost'; use '*' for all

                                        # (change requires restart)

#port =                             # (change requires restart)

max_connections =                    # (change requires restart)

#superuser_reserved_connections =      # (change requires restart)

#unix_socket_directories = '/tmp'       # comma-separated list of directories

                                        # (change requires restart)

#unix_socket_group = ''                 # (change requires restart)

#unix_socket_permissions =          # begin with  to use octal notation

#########################################

　　19、创建log目录用于存放开机日志，及启动数据库

[postgres@web1 data]$ mkdir /usr/local/pgsql/log

[postgres@web1 data]$ pg_ctl start -l /usr/local/pgsql-10.5/log/pg_server.log

waiting for server to start.... done

server started

[postgres@web1 data]$

　　20、检查是否启动，可以看日志

[postgres@web1 data]$ lsof -i:

COMMAND    PID     USER   FD   TYPE DEVICE SIZE/OFF NODE NAME

postgres  postgres    3u  IPv4        0t0  TCP *:postgres (LISTEN)

postgres  postgres    4u  IPv6        0t0  TCP *:postgres (LISTEN)

[postgres@web1 data]$ netstat -lnutp|grep postgres

(Not all processes could be identified, non-owned process info

 will not be shown, you would have to be root to see it all.)

tcp               0.0.0.0:            0.0.0.0:*               LISTEN      /postgres

tcp6              :::                 :::*                    LISTEN      /postgres

[postgres@web1 data]$ ps -ef|grep postgres

root          : pts/    :: su postgres

postgres     : pts/    :: bash

postgres         : pts/    :: /usr/local/pgsql-10.5/bin/postgres

postgres     : ?        :: postgres: checkpointer process

postgres     : ?        :: postgres: writer process

postgres     : ?        :: postgres: wal writer process

postgres     : ?        :: postgres: autovacuum launcher process

postgres     : ?        :: postgres: stats collector process

postgres     : ?        :: postgres: bgworker: logical replication launcher

postgres     : pts/    :: ps -ef

postgres     : pts/    :: grep --color=auto postgres

[postgres@web1 data]$

　　21、进入库的一些小操作

[postgres@web1 data]$ psql    #进入库

psql (10.5)

Type "help" for help.

postgres=# \password    #设置密码

Enter new password:

Enter it again:

postgres=# CREATE DATABASE name;       #创建库

CREATE DATABASE

postgres=# \du    #查看用户

                                   List of roles

 Role name |                         Attributes                         | Member of

-----------+------------------------------------------------------------+-----------

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=#

postgres=# \l    #查看数据库列表

                                  List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges

-----------+----------+----------+-------------+-------------+-----------------------

 name      | postgres | UTF8     | en_US.UTF- | en_US.UTF- |

 postgres  | postgres | UTF8     | en_US.UTF- | en_US.UTF- |

 template0 | postgres | UTF8     | en_US.UTF- | en_US.UTF- | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF- | en_US.UTF- | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

( rows)

postgres=#

postgres=# create user test superuser password '';    #创建用户及密码

CREATE ROLE

postgres=#

*****到此就大致部署完了，如有什么问题以及不足之处，欢迎读客在线批评****

巴特西

postgresql数据库部署

最新文章

热门文章