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

This article is about how PostgreSQL builds a master/backup environment. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

Install deployment database software

Installation deployment or a few words, using the 9.5 version of the source code installation, source package is very small, on the tens of megabytes.

1)decompression

tar -zxvf postgresql-9.5.0.tar.gz

2)Switch to the unzipped directory and try to compile ready

cd postgresql-9.5.0

./ configure -prefix /usr/local/pgsql

This process is likely to have problems, such as the error below.

configure: error: zlib library not found

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

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

Use --without-zlib to disable zlib support.

Similar errors are readline, the actual situation zlib package and readline package are there.

One thing to note here:

The redhat package is called readline-devel ubuntu and readline-dev is subdivided into libreadline5-dev and libreadline6-dev.

So we need to install readline-devel and zlib-devel packages to get it done, not just doubt, and then turn on the--without-zlib option.

The next steps are simple.

3)Start compiling installation

These two processes will take a relatively long time, about a few minutes, much faster than MySQL source code compilation.

make

make install

4)create users and groups

useradd postgres

mkdir -p /data/pgsql9.5

chown -R postgres:postgres /data/pgsql9.5

su - postgres

5) Initialize deployment

/usr/local/pgsql/bin/initdb -D /data/pgsql9.5

At this point, the database software deployment is done, where we have only done functionality, not involving performance-level tuning and optimization.

2

3

Configuration Master Library

The environment used is two servers

192.168.179.128 Main Library

192.168.253.134 www.example.com

1) Create a replication role

CREATE ROLE replica login replication encrypted password 'replica';

2) Configure the access rights file gp_hba.conf

Add a record so that the backup library can be accessed. Restart is required after modification.

host replication replica 192.168.253.134/24 trust

Because it is a cross-network segment, I added an additional gateway record

host replication replica 192.168.179.1/24 trust

3) Modify the parameter configuration file postgresql.conf

Modify the following parameter settings, port or keep the default 5432

listen_addresses = '*"

port = 5432

wal_level = hot_standby

max_wal_senders = 2

wal_keep_segments = 32

wal_sender_timeout =60s

max_connections =100

Once these steps are complete, remember to reboot PG to make the configuration take effect

4) Restart PG

$ /usr/local/pgsql/bin/pg_ctl -D /data/pgsql9.5 -l logfile restart

3

3

configuration backup

Backup requires the same steps to deploy database software, as described in Part 1.

At this time, there is no initialization data on the backup database. We simulate the client's way to access it. There may be the following errors.

$ psql -Ureplica -h292.168.179.128 -p5432 --password

Password for user replica:

psql: FATAL: no pg_hba.conf entry for host "192.168.179.1", user "replica", database "replica"

1) Restore data using pg_basebackup

Don't worry, we can use pg_basebackup or command line to do backup recovery

$ pg_basebackup -F p --progress -D /data/pgsql9.5 -h 192.168.179.128 -p 5432 -U replica --password

Password:

22484/22484 kB (100%), 1/1 tablespace

NOTICE: WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup

2) Configuration Recovery Configuration recovery.conf

This step is key and is similar to the archive parameters in Oracle or the change master settings in MySQL.

The recovery.conf file can be obtained from the template:

cp /usr/local/pgsql/share/recovery.conf.sample /data/pgsql9.5/recovery.conf

The contents of the recovery.conf file are modified as follows:

standby_mode = on

primary_conninfo = 'host=192.168.179.128 port=5432 user=replica password=replica'

recovery_target_timeline = 'latest'

trigger_file = '/data/pgsql9.5/trigger_activestb'

3) Modify the configuration of parameter file postgresql.conf

The contents of the postgresql.conf file are modified as follows. The configuration is quite different from the main library, so you need to pay attention to it.

listen_addresses = '*'

port = 5432

wal_level = minimal

max_wal_senders = 0

wal_keep_segments = 0

max_connections = 1000

synchronous_commit = off

synchronous_standby_names = ''

hot_standby = on

max_standby_streaming_delay = 30

wal_receiver_status_interval = 1s

hot_standby_feedback = on

4) Start PG backup library

$ /usr/local/pgsql/bin/pg_ctl -D /data/pgsql9.5 -l logfile start

5) View replication status

You can view the replication status on the main database side. Refer to pg_stat_replication view. In the process of viewing, this view field is large and looks a little messy. We can use a method similar to MySQL \G to view it, that is,\x expansion mode.

postgres=# \x

Expanded display is on.

postgres=# select * from pg_stat_replication;

-[ RECORD 1 ]----+------------------------------

pid | 20539

usesysid | 16384

usename | replica

application_name | walreceiver

client_addr | 192.168.179.1

client_hostname |

client_port | 49374

backend_start | 2018-03-25 05:19:15.215181+08

backend_xmin | 1756

state | streaming

sent_location | 0/302F600

write_location | 0/302F600

flush_location | 0/302F600

replay_location | 0/302F600

sync_priority | 0

sync_state | async

Thank you for reading! About "PostgreSQL how to build the main and standby environment" this article is shared here, I hope the above content can be of some help to everyone, so that everyone can learn more knowledge, if you think the article is good, you can share it to let more people see it!

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.