Shulou(Shulou.com)06/03 Report--

How do I install, configure and use PostgreSQL in CentOS? In view of this problem, today the editor summarizes this article about PostgreSQL, which can be used as a reference for interested friends. I hope it will be helpful to you.

1. Environment

Operating system version: CentOS Linux release 8.0.1905 (Core)

PostgreSQL version: 10.6

Host:

Test1 192.168.1.11 test2 192.168.1.12 test3 192.168.1.13

2. Install and initialize PostgreSQL on 3 machines

[root@test1] # yum install postgresql-server-y [root@test1 ~] # postgresql-setup initdbWARNING: using obsoleted argument syntax, try-- helpWARNING: arguments transformed to: postgresql-setup-- initdb-- unit postgresql * Initializing database in'/ var/lib/pgsql/data' * Initialized, logs are in / var/lib/pgsql/initdb_postgresql.log

3. Configuration of the main library

Edit the main library configuration file

[root@test1 ~] # vim / var/lib/pgsql/data/postgresql.conflisten_addresses = '192.168.1.11'wal_log_hints = onarchive_mode = onarchive_command =' cp% p / var/lib/pgsql/pg_archive/%f'

Configure authentication file

[root@test1 ~] # vim / var/lib/pgsql/data/pg_hba.conf# add three lines host replication replica 192.168.1.11 md5host replication replica 32 md5host replication replica 192.168.1.12 md5host replication replica 192.168.1.13 md5

Create a pg_archive directory

[root@test1 ~] # mkdir-p / var/lib/pgsql/pg_ Archive [root @ test1 ~] # chown postgres:postgres / var/lib/pgsql/pg_archive

Configure recovery.conf

[root@test1 ~] # vim / var/lib/pgsql/data/recovery.bakstandby_mode = onprimary_conninfo = 'host=192.168.1.11 port=5432 user=replica password=replica'recovery_target_timeline =' latest' [root@test1 ~] # chown postgres:postgres / var/lib/pgsql/data/recovery.bak

Create a new pgpass file

[postgres@test1 ~] $vim ~ / .pgpass192.168.1.11: 5432:replication:replica:replica192.168.1.12:5432:replication:replica:replica192.168.1.13:5432:replication:replica:replica [root@test1 ~] # chown 600 / var/lib/pgsql/.pgpass

Start the database and shut down the service

[root@test1 ~] # systemctl start postgresql [root@test1 ~] # systemctl stop firewalld.service

Create a synchronization user

[root@test3 ~] # su-postgres [postgres@test3 ~] $psql psql Type "help" for help.postgres=# create role replica login replication encrypted password 'replica';CREATE ROLEpostgres=#\ Q [postgres@test1 ~] $

4. Configure two slave libraries

Copy the backup from the main library

[root@test2] # rm-rf / var/lib/pgsql/data/* [root@test2 ~] # pg_basebackup-h 192.168.1.11-p 5432-U replica-F p-P-D / var/lib/pgsql/data/Password: replica22797/22797 kB, 1 tablespace [root@test2] # chown postgres:postgres-R / var/lib/pgsql/data

Rename recovery profile

[root@test2 ~] # mv / var/lib/pgsql/data/recovery.bak / var/lib/pgsql/data/recovery.conf

Create a new pgpass file

[root@test2 ~] # su-postgres [postgres@test2 ~] $vim ~ / .pgpass192.168.1.11: 5432:replication:replica:replica192.168.1.12:5432:replication:replica:replica192.168.1.13:5432:replication:replica:replica [root@test2 ~] # chown 600 / var/lib/pgsql/.pgpass

Create a pg_archive directory

[root@test2 ~] # mkdir-p / var/lib/pgsql/pg_ Archive [root @ test2 ~] # chown postgres:postgres / var/lib/pgsql/pg_archive

Modify the listening ip address and start the service

[root@test2 ~] # vim / var/lib/pgsql/data/postgresql.conflisten_addresses = '192.168.1.12' [root@test2 ~] # systemctl start postgresql [root@test2 ~] # systemctl stop firewalld.service

Do it again in test3

5. Test the master-slave synchronization status

View synchronization nodes in the main library

[postgres@test1] $psql psql Type "help" for help.postgres=# select * from pg_stat_replication

Create a test library, and then check whether the two slave libraries are synchronized

Operate in the main library

Postgres=# CREATE DATABASE test_db;CREATE DATABASE

View slave library

Test2 is synchronized

Test3 is synchronized

The construction is complete

6. Master-slave switching

Simulate the failure of the master library, switch to the slave library, and then set the original master library as the slave library

View synchronization status

The main library is in in production state

[root@test1 ~] # pg_controldata / var/lib/pgsql/data/pg_control version number: 1002Catalog version number: 201707211Database system identifier: 6782563721072319907Database cluster state: in productionpg_control last modified: Fri 17 Jan 10:39:41 PM CST

Test2 slave library in in archive recovery

[root@test2 ~] # pg_controldata / var/lib/pgsql/data/pg_control version number: 1002Catalog version number: 201707211Database system identifier: 6782563721072319907Database cluster state: in archive recoverypg_control last modified: Fri 17 Jan 10:39:44 PM CST

Test3 slave library in in archive recovery

[root@test3 ~] # pg_controldata / var/lib/pgsql/data/pg_control version number: 1002Catalog version number: 201707211Database system identifier: 6782563721072319907Database cluster state: in archive recoverypg_control last modified: Fri 17 Jan 10:39:47 PM CST

Simulation of main library failure

[root@test1 ~] # systemctl stop postgresql.service

Promote test2 to the main library, check the test2 status, this is test2 to become the main library, you can write

[root@test2] # su-postgres-c "pg_ctl promote" waiting for server to promote.... Doneserver promoted [root@test2 ~] # pg_controldata / var/lib/pgsql/data/pg_control version number: 1002Catalog version number: 201707211Database system identifier: 6782563721072319907Database cluster state: in productionpg_control last modified: Fri 17 Jan 10:48:12 PM CST

Point test3 to the new main library, modify the host in recovery, and then restart

[root@test3 ~] # vim / var/lib/pgsql/data/recovery.conf standby_mode = onprimary_conninfo = 'host=192.168.1.12 port=5432 user=replica password=replica'recovery_target_timeline =' latest' [root@test3 ~] # systemctl restart postgresql.service

At this point, test2 can see test3 coming from the library.

Change the old test1 master library into a slave library and point to test2

[root@test1 ~] # mv / var/lib/pgsql/data/recovery.bak / var/lib/pgsql/data/recovery.conf [root@test1 ~] # vim / var/lib/pgsql/data/recovery.confstandby_mode = onprimary_conninfo = 'host=192.168.1.12 port=5432 user=replica password=replica'recovery_target_timeline =' latest' [root@test1 ~] # systemctl start postgresql.service

Go to test2 to check the nodes. Test1 is connected.

The state of test1 has also become in archive recovery.

[root@test1 ~] # pg_controldata / var/lib/pgsql/data/pg_control version number: 1002Catalog version number: 201707211Database system identifier: 6782563721072319907Database cluster state: in archive recoverypg_control last modified: Fri 17 Jan 10:54:51 PM CST

Handover complete

The above is the introduction of installing configuration and using PostgreSQL in CentOS, the content is more comprehensive, the editor believes that there may be some knowledge points that we may see or use in our daily work. I hope you can learn more from this article.

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.