PostgreSQL cluster solution hot standby installation and testing

These two days are busy studying postgresql. The basic usage is similar to that of other databases. I will not explain it myself. Today I want to record the postgresql cluster solution hot standby.

Set the Cluster Environment myself:

Three computers: one master database and two slave Databases

1. Install Postgresql9

Tar-zxvf postgresql-9.1.3.tar.gz # Extract

Cd postgresql-9.1.3

./Configure -- prefix/home/proxy_pg/pgsql # configure the postgresql installation directory

Install the basic libraries (gcc, readline, zlib, and) # You don't have to worry about it. The prompt "make" appears.

Make # compile

Make install # install
 

2. Add an ipvs account

Useradd S # Add an account

Passwd postgres # And then enter the password

# Enter the postgres user

Su ipvs
 

3. initialize the primary database

Mkdir data # create a data folder where you want to store data

Bin/initdb-D ../data/# initialize the database

Modify data/postgresql. conf

Port = 5433 # You can change what you want

Wal_level = hot_standby

Max_wal_senders = 30

Modify data/pg_cmd.conf

Host replication all 0.0.0.0 0.0.0.0 trust
 

4. Start the master database

Bin/pg_ctl start-D ../data/
 

5. Check whether the database is successfully started.

./Psql-d postgres

Psql (9.1.3)

Type "help" for help.

 

Postgres =## indicates that the startup is successful.
 

6. Basic backup

Basic Process: Execute pg_start_backup () on the master database server, copy the data directory, and execute pg_stop_backup ().

 

./Psql-d postgres

S = # select pg_start_backup ('');

# After this method, all requests will not be refreshed to the disk after the log is written. Until the pg_stop_backup () function is executed.

# Copy the following data directory and copy it to the sub-database through scp

Cp-R data/data_bac
 

7. Create a slave database (standby)

# Use scp to copy the data_bac directory to the slave database (you can also use other methods)

Scp-r data_bac/postgres@192.168.30.199:/home/proxy_pg/pgsql/

# Log on to the slave database server and go to the copied data_bac directory.

Cd data_bac

# Modify ipvs. conf

Port = 5433 # change to the port you want

Hot_standby = on

# Add the primary database information (ip address, port, and user) connected to the recovery. conf configuration)

Standby_mode = 'on'

Primary_conninfo = 'host = 192.168.30.150 port = 5433 user = ipvs'

 

# Deleting a pid File

Rm postmaster. pid
 

8. Start the slave Database

Bin/pg_ctl start-D ../data_bac/
 

9. Stop the basic backup of the primary database

S = # select pg_stop_backup ();

Note that steps 8 and 9 can be reversed, because after the backup is stopped, when the slave database starts, it will automatically connect to the master database and pull the transaction logs of the server after the basic backup, then, repeat the transaction log.
 

10. Test

# For ease of viewing the database, I installed pgAdminII

After creating a table in the primary database and inserting three pieces of data, observe the following:

# Create a database named lengzijian

./Createdb lengzijian-p 5433 # All master and slave databases will create the lengzijian Database

# Create a table and insert fields. This is not demonstrated here.
 

11. Conclusion

Here we have just made a preliminary cluster installation. There are many postgresql cluster solutions. I will demonstrate all the solutions and then check each solution, if you have a good experiment or solution, do not share it with me.