Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to use source code to install master-slave POSTGRESQL10.8". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Download POSTGRESQL source code installation package and host configuration

Https://www.postgresql.org/ftp/source/v10.8/

Postgresql-10.8.tar.gz

Virtual machine environment

Node1 192.168.159.4

Node2 192.168.159.5

The operating system is redhat7.6

Database is postgresql10.8

Both nodes are configured with / etc/hosts

Vi / etc/hosts

Node1 192.168.159.4

Node2 192.168.159.5

II. Compilation and installation

(1) create a postgres user

Useradd-m-r-s / bin/bash-u 5432 postgres

(2) install related dependency packages

Yum install gettext gcc make perl python perl-ExtUtils-Embed readline-devel zlib-devel openssl-devel libxml2-devel cmake gcc-c++ libxslt-devel openldap-devel pam-devel python-devel cyrus-sasl-devel libgcrypt-devel libgpg-error-devel libstdc++-devel

(3) configure POSTGRES

Upload unzipped installation package

Cd / opt/

Tar-zxvf postgresql-10.8.tar.gz

Cd / opt/ postgresql-10.8

. / configure-- prefix=/opt/postgresql-10.8-- with-segsize=8-- with-wal-segsize=64-- with-wal-blocksize=16-- with-blocksize=16-- with-libedit-preferred-- with-perl-- with-python-- with-openssl-- with-libxml-- with-libxslt-- enable-thread-safety-- enable-nls=zh_CN

Note that when compiling, do not add the parameter-- enable-profiling-- enable-debug. This parameter will cause unnecessary log generation.

And the growth is extremely fast, which will cause the disk space to fill up quickly, and manual deletion may have some bad effects, so don't match this parameter.

And this parameter description is also used for test and development, and is not suitable for production systems.

Configure editing options:

-- prefix=PREFIX specifies the installation directory. Default is / usr/local/pgsql.

-- bindir= executable file directory, default / PREFIX/bin

-- sysconfdir= configuration file directory, default / PREFIX/etc

-- libdir= library file directory, default / PREFIX/lib

-- includedir= header file directory, default / PREFIX/include

-- datarootdir=DATAROOTDIR sets the read-only shared file directory. Default is / PREFIX/share.

-- mandir= man manual directory, default / DATAROOTDIR/man

-- with-extra-version=STRING appends a STRING string to the version number as a custom version ID

-- with-pgport=NUMBER specifies the default port numbers for both server and client, which defaults to 5432

-- ssl support for with-openssl translation. You need to install OpenSSL package beforehand.

-- with-pam translation PAM support

-- LDAP support for with-ldap translation. You need to install OpenLDAP package beforehand.

-- with-segsize= sets segment size in units of gigabytes (GB). The default is 1GB. Large tables will be divided into multiple files according to segsize.

-- with-blocksize= specifies the block size, which is the basic unit of storage in the table and I 8Kbytes O. Default is 8Kbytes, which usually does not need to be changed. The value range is 1-32KB.

-- with-wal-segsize= sets the segment size of WAL (Write-Ahead Logging) in megabytes (MB). Default is 16MB. Value range is 1-64MB.

-- with-wal-blocksize= specifies the block size of WAL, which is the basic unit of WAL pre-write log storage and I 8KB O, which is in Kbytes. The default size is 1-64KB.

-enable-debug (Compiles all programs and libraries with debugging symbols. This means that you can run the programs in a debugger to analyze problems), which can be used in a production environment when using GCC translators, while other translators can affect performance; mostly for development

Under the enable-profiling GCC editor, all programs and libraries will be translated for performance analysis, and the gmon.out generated when the backend exits will be used for performance analysis; mostly for development

-- enable-coverage code coverage testing, for development only

-- enable-cassert enables the assertion (assertion) check of the server, which is only used for development

-enable-depend (Enables automatic dependency tracking. With this option, the makefiles are set up so that all affected object files will be rebuilt when any header file is changed), for development only

Enable-dtrace translation dynamic tracking tool Dtrace support, currently can not be used in the Linux platform, can be used in FreeBSD, Solaris

-- with-systemd enables systemd support. Version 9.6 or above is required.

If the following yellow output appears in the last few lines, the configuration is correct, otherwise continue to install the dependency package according to the error prompt

Checking thread safety of required library functions... Yes

Checking whether gcc supports-Wl,--as-needed... Yes

Configure: using compiler=gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36)

Configure: using CFLAGS=-Wall-Wmissing-prototypes-Wpointer-arith-Wdeclaration-after-statement-Wendif-labels-Wmissing-format-attribute-Wformat-security-fno-strict-aliasing-fwrapv-fexcess-precision=standard-pg-DLINUX_PROFILE-O2

Configure: using CPPFLAGS=-D_GNU_SOURCE-I/usr/include/libxml2

Configure: using LDFLAGS=-Wl,--as-needed

Configure: creating. / config.status

Config.status: creating GNUmakefile

Config.status: creating src/Makefile.global

Config.status: creating src/include/pg_config.h

Config.status: creating src/include/pg_config_ext.h

Config.status: creating src/interfaces/ecpg/include/ecpg_config.h

Config.status: linking src/backend/port/tas/dummy.s to src/backend/port/tas.s

Config.status: linking src/backend/port/dynloader/linux.c to src/backend/port/dynloader.c

Config.status: linking src/backend/port/posix_sema.c to src/backend/port/pg_sema.c

Config.status: linking src/backend/port/sysv_shmem.c to src/backend/port/pg_shmem.c

Config.status: linking src/backend/port/dynloader/linux.h to src/include/dynloader.h

Config.status: linking src/include/port/linux.h to src/include/pg_config_os.h

Config.status: linking src/makefiles/Makefile.linux to src/Makefile.port

Or type echo $right away? Just get an output of 0.

(4) compilation

Make & & make install

The following yellow output appears in the last few lines to be configured correctly

Make [2]: leave the directory "/ opt/postgresql-10.8/src/test/perl"

/ usr/bin/mkdir-p'/ opt/postgresql-10.8/lib/pgxs/src'

/ usr/bin/install-c-m 644 Makefile.global' / opt/postgresql-10.8/lib/pgxs/src/Makefile.global'

/ usr/bin/install-c-m 644 Makefile.port' / opt/postgresql-10.8/lib/pgxs/src/Makefile.port'

/ usr/bin/install-c-m 644. / Makefile.shlib' / opt/postgresql-10.8/lib/pgxs/src/Makefile.shlib'

/ usr/bin/install-c-m 644. / nls-global.mk' / opt/postgresql-10.8/lib/pgxs/src/nls-global.mk'

Make [1]: leave the directory "/ opt/postgresql-10.8/src"

Make-C config install

Make [1]: enter the directory "/ opt/postgresql-10.8/config"

/ usr/bin/mkdir-p'/ opt/postgresql-10.8/lib/pgxs/config'

/ usr/bin/install-c-m 755. / install-sh' / opt/postgresql-10.8/lib/pgxs/config/install-sh'

/ usr/bin/install-c-m 755. / missing' / opt/postgresql-10.8/lib/pgxs/config/missing'

Make [1]: leave the directory "/ opt/postgresql-10.8/config"

PostgreSQL installation complete.

Or type echo $right away? Just get an output of 0.

(5) installation

Make world & & make install-world

The following yellow output appears in the last few lines to be configured correctly

/ usr/bin/mkdir-p'/ opt/postgresql-10.8/lib/pgxs/src'

/ usr/bin/install-c-m 644 Makefile.global' / opt/postgresql-10.8/lib/pgxs/src/Makefile.global'

/ usr/bin/install-c-m 644 Makefile.port' / opt/postgresql-10.8/lib/pgxs/src/Makefile.port'

/ usr/bin/install-c-m 644. / Makefile.shlib' / opt/postgresql-10.8/lib/pgxs/src/Makefile.shlib'

/ usr/bin/install-c-m 644. / nls-global.mk' / opt/postgresql-10.8/lib/pgxs/src/nls-global.mk'

Make [1]: leave the directory "/ opt/postgresql-10.8/src"

Make-C config install

Make [1]: enter the directory "/ opt/postgresql-10.8/config"

/ usr/bin/mkdir-p'/ opt/postgresql-10.8/lib/pgxs/config'

/ usr/bin/install-c-m 755. / install-sh' / opt/postgresql-10.8/lib/pgxs/config/install-sh'

/ usr/bin/install-c-m 755. / missing' / opt/postgresql-10.8/lib/pgxs/config/missing'

Make [1]: leave the directory "/ opt/postgresql-10.8/config"

PostgreSQL installation complete.

Make: leave the directory "/ opt/postgresql-10.8"

Or type echo $right away? Just get an output of 0.

(6) create relevant directories and configure environment variables

Mkdir-p / home/postgresql10.8/serverlog

Mkdir-p / home/postgresql10.8/pg/pgdata

Su-postgres

Vi .bash _ profile (delete all the original, copy and paste the following yellow parts directly)

# .bash _ profile

# Get the aliases and functions

If [- f ~ / .bashrc]; then

. ~ / .bashrc

Fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

Export PATH

# postgres

# PostgreSQL Port

PGPORT=5432

# PostgreSQL data directory

PGDATA=/home/postgresql10.8/pg/pgdata

Export PGPORT PGDATA

# the language used

Export LANG=zh_CN.utf8

# PostgreSQL installation directory

Export PGHOME=/home/postgresql10.8/pg/pgdata

# PostgreSQL connection Library File

Export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH

Export DATE= `date + "% Y%m%d%H%M" `

# add the command line of PostgreSQL to the PATH environment variable

Export PATH=$PGHOME/bin:$PATH

# man manual for PostgreSQL

Export MANPATH=$PGHOME/share/man:$MANPATH

# default user for PostgreSQL

Export PGUSER=postgres

# PostgreSQL default host address

Export PGHOST=127.0.0.1

# default database name

Export PGDATABASE=postgres

# define log storage directory

PGLOG= "$PGDATA/serverlog"

Source .bash _ profile

(7) initialize the database

# execute database initialization script

Root user login

Chown-R postgres.postgres / home/postgresql10.8

Su-postgres

/ opt/postgresql-10.8/bin/initdb-- encoding=utf8-D / home/postgresql10.8/pg/pgdata/

Warning: trust authentication has been initiated for local connections.

You can change it by editing pg_hba.conf or your next time

Use the-An or-- auth-local and-- auth-host options when initdb. Success. You can now start the database server using: starting the database

Su-postgres

/ opt/postgresql-10.8/bin/pg_ctl-D'/ home/postgresql10.8/pg/pgdata/'-l logfile start

(8) copy of relevant commands

Root user

Mkdir / home/postgresql10.8/pg/pgdata/bin

Cp / opt/postgresql-10.8/bin/* / home/postgresql10.8/pg/pgdata/bin

Chown-R postgres.postgres / home/postgresql10.8/pg/pgdata/bin

Third, postgresql master-slave building

1. Configuration of main library

(1) create a user replica copied by the user

Su-postgres

Psql

CREATE ROLE replica login replication encrypted password 'replica'

(2) modify the pg_hba.conf file and specify replica to log in to the network (add on the last line)

Vi / home/postgresql10.8/pg/pgdata/pg_hba.conf

Host replication replica 192.168.159.0/24 md5

Host all replica 192.168.159.0/24 md5

Host all all 192.168.159.0/24 md5

Host all all 0.0.0.0/0 md5

(3) the main library configuration file modifies the following items, while others remain unchanged.

Vi / home/postgresql10.8/pg/pgdata/postgresql.conf

Listen_addresses ='*'

Wal_level = hot_standby # hot standby mode

Max_wal_senders= 10 # can set up a maximum of several stream copy links, as many as there are from.

Wal_keep_segments = 100 # important configuration

Wal_send_timeout = 60s

Max_connections = 3000 # the max_connections of the slave library is greater than the master library

Archive_mode = on # allow archiving

Archive_command ='cp% p / home/postgresql10.8/pg/archivedir/%f' # set according to the actual situation

Checkpoint_timeout = 30min

Max_wal_size = 2GB

Min_wal_size = 1GB

Mkdir / home/postgresql10.8/pg/archivedir

2. From the library environment

(1) empty the data folder directory of the database.

Rm-rf / home/postgresql10.8/pg/*

(2) run on the standby database

/ opt/postgresql-10.8/bin/pg_basebackup-F p-- progress-D / home/postgresql10.8/pg/pgdata-h 192.168.159.4-p 5432-U replica-- password

Enter password replica

!!! Note that after the replication is completed, all the files in the data directory must be re-authorized in the standby library.

Chown-R postgres.postgres / home/postgresql10.8/pg/pgdata

(3) create recovery.conf file

Cp / opt/postgresql-10.8/share/recovery.conf.sample / home/postgresql10.8/pg/pgdata/recovery.conf

Vi / home/postgresql10.8/pg/pgdata/recovery.conf

Standby_mode = on

Primary_conninfo = 'host=192.168.159.4 port=5432 user=replica password=replica'

Recovery_target_timeline = 'latest'

Trigger_file ='/ home/postgresql10.8/pg/pgdata/trigger.kenyon'

(4) configure postgresql.conf file

Vi / home/postgresql10.8/pg/pgdata/postgresql.conf

Listen_addresses ='*'

Wal_level = hot_standby

Max_connections = 1000 # generally, the maximum slave link is larger than the master link.

Hot_standby = on # indicates that this machine is not only used for data archiving, but also for query

Max_standby_streaming_delay = 30s

Wal_receiver_status_interval = 10s # how often do you report the slave status to the master?

Hot_standby_feedback = on # if there is incorrect data replication, whether to make an example to the master

(5) start the backup database

Su-postgres

/ opt/postgresql-10.8/bin/pg_ctl-D'/ home/postgresql10.8/pg/pgdata/'-l logfile start

If it cannot be started, copy the file postmaster.opts from the main library to the standby library as follows:

Scp / home/postgresql10.8/pg/pgdata/postmaster.opts 192.168.159.5:/home/postgresql10.8/pg/pgdata/

Chown-R postgres.postgres / home/postgresql10.8/pg/pgdata/

Cd / home/postgresql10.8/pg/

Chmod 700 pgdata/

3. Verify the master-slave function

Main database query

Su-postgres

Psql

Postgres=# select client_addr,sync_state from pg_stat_replication

Client_addr | sync_state

-+-

192.168.159.5 | async

(1 row)

Pay attention to one problem, the production library should pay attention to the time zone.

Find the configuration file postgresql.conf

Where the parameters

Timezone = 'PRC'

PRC represents the Shanghai time zone.

This is the end of the content of "how to install master-slave POSTGRESQL10.8 with source code". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.