Easy setup replication on PostgreSQL

image
The need arose to quickly and simply as possible to organize the data replication from database server to the backup server. A simple and intuitive way on the Internet and was not found, this had parts to assemble information, which became this article.

task. The source data


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

Custom main database server


In the section "the Role of input" using PgAdmin create user (role) repl rights "Can create streaming replication and backups". Add in the pg_hba or through a "Configuration server" in PgAdmin create a string that allows the user repl connection to the database.

the host replication repl 192.168.1.2/32 trust

In postgresql.conf made the following changes:
the the the the the
Original value Change Description
#max_wal_senders = 1 max_wal_senders = 2 the Number of standby servers that can connect to the main server
#wal_keep_segments = 32 wal_keep_segments = 32 (you can put a 256) How to store segments. The number should be chosen such that the backup server had time to pick up and handle. I put 256 to the day wal files was not erased
#wal_level = hot_standby wal_level = hot_standby hot_standby adds information required to run read-only queries on a standby server
#checkpoint_segments = 3 checkpoint_segments = 16 you Can increase the number of segments in WAL-log

Custom backup server


Stop service Postgresql 9.2 on the backup server and clean up the data folder that is created when you install PostgreSQL, for example D:\database. After this, run backup from the command line:

"C:\PostgreSQL\bin\pg_basebackup.exe" --host=192.168.1.1 --port=5432 --username=repl -D "D:\database"

Custom postgresql.conf D:\database
the the
Original value Change Description
#hot_standby = off hot_standby = on Allow read-only queries to the database during the restore process

A custom recovery.conf D:\database
the the the the
Value Description
standby_mode = 'on' Enable recovery mode and to act as a backup server (slave)
primary_conninfo = 'host=192.168.1.1 port=5432 user=repl' the Parameters to connect to the main server
trigger_file = 'D:\\database\\end_trig' If you create a file with the name end_trig in the specified folder, the server will exit and replication will become a regular server

Start the Postgresql service on the backup server. Should start without errors. Check the replication job: changes in a table on the primary server, and check — if they reflected on the backup.

configuring the archive logs (if needed)


In postgresql.conf on the master server, include the following:
the
archive_mode = on archive_command = 'copy "%p" "e:\\Backup\\%f"'

Folder e:\Backup will fall the archives of the logs. (Note, they can score all the disk space, you need to configure the cleanup at the overflow)

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

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

Actions in case of failures


If the failed backup server, then stop it the Postgres service and performed all actions as "Custom backup server".
If the failed primary server, it is necessary to translate the backup server to normal operation: it is necessary to create a file end_trig in the folder as specified in the recovery.conf trigger_file = 'D:\\database\\end_trig' b do vacuum and reindex the database.

notes


Replication can be configured as from Windows XP to Windows 7, and Vice versa.
For Windows 7 and Wndows XP in pg_hba different settings, because in XP there is no Protocol for ip6, then the line should be commented out.
Article based on information from habrahabr.ru

Комментарии

Отправить комментарий

Популярные сообщения из этого блога

Why I left Google Zurich

the Backstory The question I often hear from different people and now I will try to answer it. Probably for anybody not a secret, but still: how does Google? They recruit the best engineers pay them large enough (even for Switzerland) salary and create an amazing environment for work, free food, massage at the office, the phantasmagoric recreation area , childrens slide down directly into the dining room, etc. This is very cool, like to write journalists, but it doesn't matter. Well, how many times will you walk down the hall on the hill and is it important? Free food has led to the fact that the majority of Googlers fat pounds five after a job, thus denying yourself the pleasure to go to lunch every day in a new restaurant. What's more determines how good you feel at work, free food, massage, design zones, the rest or the work that you do? I'm sure that's really important what you do every day and how this activity brings joy. Google hires the b
Далее...

2000 3000 icons ready — become a sponsor! (the table of orders)

Изображение
Not so long ago on habré was top that led you to our website 17 thousand visitors for two days (11,828+5,100 on October 21, 2010). With new year 2009 this is not the first wave, and the last icons will be drawn in the first quarter of 2012. Best reseller hosting providers . Not enough more 1000 icons for best email hosting . Are you willing to offer metaphors, of which we still have not enough? Then follow me... Gebrettert from 17K inferior reddit 49k (but for four days), but still is a decent part of the order of 200k visitors who have downloaded a set. Initially I just wanted to beat set Yusuke , then not counted, and 2000 icons and there was only 16m in size. Now I just want to make the largest (and having the maximum number of unique metaphors) set of icons in the world . Well, a few minnamoro like these 2 for wordpress admin . Today drawn 2040 icon, cut 2000 more smoothly. I want to draw flags and more file type icons, but still pieces of 40
Далее...

New web-interface for statistics and listen to the calls for IP PBX Asterisk

Изображение
the Idea of writing web-interface for statistics and listen to the calls for IP PBX Asterisk is not left me for several years now. The solutions found in Internet, are not satisfied on certain criteria — somewhere lacked functionality, some of them are not pleasing to the eye. And so, armed with technology stack and riding warhorse provided by ServerClub I went. The result of my journey has become a new interface , with charts, graphs and the ability to download and listen to the calls. I will not further bore you with words, here are a couple of screenshots: And under the cut are waiting for you a video guide on the interface, necessary settings and detailed description of the available functionality. the Interface. That post didn't look like a bed sheet from the screenshots, I made a little slideshow where you can see the interface. Description. The already existing plans. Currently implemented the following functionality: Incoming calls:
Далее...