Easy setup of replication in PostgreSQL

There was a need to quickly and as easily as possible organize the replication of data from the database server to the backup server. There was no simple and understandable way in the open spaces of the Web, so I had to collect information in parts, which became this article.

Solved problem. Initial data

So, we have a database server, which clients work with, and a backup server, to which it is necessary to configure replication from the main database.
In my case, PostgreSQL 9.2.1 is used, which is installed on both servers and supports streaming replication. Suppose that the database on the main server is deployed and is working, the backup server is only installed, but PostgreSQL is not configured. For example, take the IP address 192.168.1.1 for the address of the main server, the IP address 192.168.1.2 - for the backup address.

We configure the main database server

In the “Login Roles” section, through PgAdmin we create a user (role) repl with the rights “Can create streaming replication and backup copies”. We add in pg_hba or through “Server Configuration” in PgAdmin we create a string allowing the repl user to connect to the database.

host replication repl 192.168.1.2/32 trust

The following changes were made to the postgresql.conf file:

Initial value	Change the value	Description
#max_wal_senders = 1	max_wal_senders = 2	Number of backup servers that can connect to the primary server
#wal_keep_segments = 32	wal_keep_segments = 32 (you can put 256)	How many segments to store. It is necessary to choose the number of such that the backup server has time to take everything and handle. I set 256 so that the wal-files will not be erased in 24 hours.
#wal_level = hot_standby	wal_level = hot_standby	hot_standby adds the information needed to run only read requests to the backup server
#checkpoint_segments = 3	checkpoint_segments = 16	You can increase the number of segments in the WAL log

Configuring a backup server

We stop the Postgresql 9.2 service on the backup server and clear the folder with the data created during the installation of PostgreSQL, for example, D: \ database. After that, run backup from the command line:
')
"C:\PostgreSQL\bin\pg_basebackup.exe" --host=192.168.1.1 --port=5432 --username=repl -D "D:\database"

We configure postgresql.conf in D: \ database

Initial value	Change the value	Description
#hot_standby = off	hot_standby = on	Allow read-only requests to the DBMS during the recovery process

Configure recovery.conf in D: \ database

Value	Description
standby_mode = 'on'	Enable recovery mode and work as a backup server (slave)
primary_conninfo = 'host = 192.168.1.1 port = 5432 user = repl'	Parameters for connecting to the main server
trigger_file = 'D: \\ database \\ end_trig'	If we create a file named end_trig in the specified folder, the server will exit replication mode and become a normal server.

We start the Postgresql service on a reserve server. Should start without errors. We check replication work: we make changes in the table on the main server, and we check if they are reflected in the backup.

Setting up logging logs (if needed)

In the postgresql.conf file on the main server we include the following:

archive_mode = on

archive_command = 'copy "% p" "e: \\ Backup \\% f"'

In the folder e: \ Backup archives will fall logs. (Attention, they can clog up all the disk space, you need to configure the cleanup when overflowed)

To use the log archives, you need to copy the logs to the folder from which we will recover and add a line to recovery.conf

restore_command = 'copy "e:\\Backup\\%f" "%p"'

Failure actions

If the backup server is out of order, then we stop the Postgres service on it and perform all actions as in the section “Configuring the backup server”.
If the primary server is out of order, you need to put the backup server into normal operation: to do this, you need to create the end_trig file in the folder as indicated in recovery.conf trigger_file = 'D: \\ database \\ end_trig' b to make the vacuum and reindex databases.

Notes

Replications can be configured from Windows XP to Windows 7, and vice versa.
For Windows 7 and Wndows XP, pg_hba has different settings, because in XP there is no ip6 protocol, the line with it should be commented out.