preface

After the acquisition of MySQL, there was an alternative, MariaDB, but it was a bit of a struggle. Another open source database has been discovered: PostgreSQL.

Although centos comes with version 9.2, PostgreSQL has been updated to support more content. For example, PostgreSQL has an advantage over MySQL. PostGIS supports spatial data storage and spatial analysis. JSON data types have been built in since PostgreSQL9.3 and JSONB support has been introduced in 9.4, which means PostgreSQL is essentially a combination of a relational database and a NoSQL database.

Although PostgresQL is a relational database, the NoSQL performance of postgresQL has been improved to the point where it exceeds MongoDB. PostgreSQL is ranked fourth on the database Rank chart, which is about the same as MongoDB. PostgreSQL does well in terms of developer love, trust, and community documentation queries.

Centos7 install postgresql10 of the latest version

Update the source

Cloud server system: CentOS 7.2x86_64

Address: www.postgresql.org/download/li…

Here I choose PostgreSQL10,CentOS 7, x86_64, and the corresponding YUM source will appear.

Run the following commands in the centos system:

yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
Copy the code

Install PostgreSQL

Check the PostgreSQL source

What we need to install are these three. postgresql10-devel.x86_64, postgresql10-contrib.x86_64,postgresql10-server.x86_64

 yum install postgresql10-client postgresql10-server postgresql10-contrib postgresql10-devel
Copy the code

Initializing the database

The PostgreSQL installation directory is /usr/pgSQL-10, and the PostgreSQL data directory is /var/lib/pgsqL-/ version number (10).

In this case, if the system starts to allocate enough var space, we can continue. If we don’t allocate enough var space, we need to change the data directory. In this case, we assume enough var space. Start the initialization directly.

/usr/pgsql-10/bin/postgresql-10-setup initdb
Copy the code

Displaying this indicates successful initialization

Start database and set up boot

sudo systemctl start postgresql-10
sudo systemctl enable postgresql-10.service
Copy the code

Log in to PostgreSQL and set the password

Postgresql is installed with user Postgres by default

The input

su - postgres
psql
Copy the code

Accessing the database

The command interface looks like this

Let’s set the password:

Exit: \ q

List all libraries \l

List all users \du

List all tables \d under the library

Support password and remote login

Modifying password Authentication

By default, PostgresQL does not use a password and does not support remote login. We need to modify the configuration file

vi /var/lib/pgsql/10/data/pg_hba.conf
Copy the code

It was supposed to look like this

We need to change

Save the exit

---

Here’s another tidbit about exiting Vim:

StackOverflow proudly announces that it has helped 1.87 million programmers who don’t know how to quit VIM

Here I quote the above excellent answer to help you out of the 1.87 million community.

Modifying Remote Access

vi /var/lib/pgsql/10/data/postgresql.conf
Copy the code

We’ll scroll down and see

Need to change to

To search for the text you want to find in Vim, you can use /***, n indicates next page, n indicates up page

Restart the postgresql

systemctl restart postgresql-10
Copy the code

Login postgresql

Postgresql is installed with user Postgres by default

The input

su - postgres
psql
Copy the code

Accessing the database

At this point, you can verify the password

Remotely connecting to a database

We use Navicat Premium 12 to verify the connection

Now we can see the data

PostgreSQL primary and secondary stream replication deployment

Introduction to the

Postgres introduced master slave stream replication after 9.0. Stream replication is the synchronization of data from the master server over TCP streams. This way, if the data on the primary server is lost, there is a backup on the secondary server.

Stream replication allows you to keep updates from the slave server, in contrast to file-log-based shipping. The slave server connects to the master server and the resulting stream of WAL records is sent to the slave server without waiting for the master server to finish writing WAL files.

PostgreSQL stream replication is asynchronous by default. There is a small delay between committing a transaction on the master server and changing visibility on the slave server, which is much smaller than file log-based shipping and usually takes 1 second. If the primary server suddenly crashes, a small amount of data may be lost.

Synchronous replication must wait for both primary and secondary servers to write WAL before committing a transaction. This increases the response time of the transaction to some extent.

Configuring synchronous replication requires only one additional configuration step: synchronous_standby_NAMES must be set to a non-null value. Synchronous_commit must also be set to on.

Asynchronous stream replication is deployed here.

The systems and environments of the nodes where the primary and secondary servers reside must be the same. The PostgreSQL version should also be consistent, otherwise there may be problems.

Install the deployment

Assume that PostgreSQL is installed on both 192.168.20.93 and 192.168.20.94.

For details about the installation and deployment, see the preceding steps

2.1 Primary Server

The primary server is 192.168.20.93

1. Create a database user to perform primary/secondary synchronization. Create user replica and grant login and replication permissions.

postgres# CREATE ROLE replica login replication encrypted password 'replica';

Copy the code

2. Modify pg_hba.conf to allow the replica user to synchronize data.

Add two lines to pg_hba.conf:

Host all All 192.168.20.94/32 TrustAllow 94 to connect to the primary serverHost replication Replica 192.168.20.94/32 MD5Allow 94 to use the Replica user for replication

Copy the code

Thus, the Replica user is set up to make stream replication requests from 192.168.20.93.

Note: The second field must be replication

4. Modify the postgresql. Conf

listen_addresses = The '*'   Listen on all IP addresses
archive_mode = on  # allow archiving
archive_command = 'cp %p /opt/pgsql/pg_archive/%f'  Use this command to archive logfile segment
wal_level = hot_standby 
max_wal_senders = 32 Wal_keep_segments = 256 # Set the maximum number of xlogs retained by stream replicationWal_sender_timeout = 60s # Set the timeout period for the stream replication host to send data max_connections = 100The max_connections value of the slave library must be greater than that of the master library

Copy the code

After configuring the two files, restart the server.

systemctl restart postgresql-10

Copy the code

3. Test whether 94 can connect to database 93. Run the following command on 94:

PSQL -h 192.168.20.93 -u postgresCopy the code

See if I can get into the database. If yes, it is normal.

2.2 Secondary Server

1. Copy data from the primary node to the secondary node

su - postgres
rm -rf /var/lib/pgsql/10/data/*   Delete all data from the data directoryPg_basebackup -h 192.168.20.93 -u replica -x stream -pCopy data from 93 to 94 (base backup)

Copy the code

2. Configuration recovery. Conf

Copy/usr/PGSQL – 9.4 / share/recovery. Conf. Sample to/opt/PGSQL/data/recovery. Conf

cp /usr/pgsql-10/share/recovery.conf.sample /usr/pgsql-10/share/recovery.conf

Copy the code

Modify recovery. Conf

standby_mode = on    # indicates that the node is from the server
primary_conninfo = 'the host = 192.168.20.93 port = 5432 user = up password = up'  # Master server information and connected users
recovery_target_timeline = 'latest'

Copy the code

3. Configure postgresql. Conf

Hot_standby = on wal_level = hot_standby max_connections = 1000 Also used for data query max_standby_streaming_delay = 30s# Maximum latency for data stream backup
wal_receiver_status_interval = 10s How often does the slave state report to the master? Of course, every slave data copy reports the status to the master
hot_standby_feedback = on If there is wrong data replication, whether to report back to the master

Copy the code

After the configuration, restart the secondary server

systemctl restart postgresql-10

Copy the code

3. Check whether the deployment is successful

Execute on the primary node:

select client_addr,sync_state from pg_stat_replication;

Copy the code

The results are as follows:

postgres=# select client_addr,sync_state from pg_stat_replication;Client_addr | sync_state -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- 192.168.20.94 | async (1 row)Copy the code

Note 94 is receiving streams from the server, and is asynchronous stream replication.

In addition, you can separately in the main, from the node to run on ps aux | grep postgres to view the process:

On main server (93) :

Postgres 262270 0.0 0.0 337844 2832? Ss 10:14:00 POSTgres: Wal Sender Process Replica 192.168.20.94(13059) Streaming 0/A002A88Copy the code

You can see that there is a WAL Sender process.

From server (94) :

Postgres 569868 0.0 0.0 384604 2960? Ss 10:14 0:02 postgres: wal receiver process streaming 0/A002B60Copy the code

You can see that there is a Wal Receiver process.

The PostgreSQL primary and secondary stream replication is installed and deployed.

Data is inserted or deleted on the master server, and changes are seen on the slave server. You can only query from the server, not insert or delete.

Copyright: Mu Shuwei

github.com/sanshengshu…

Personal blog: www.mushuwei.cn/