NOTE: Please note that this does NOT work on Windows! Backups only work on Unix machines.
NOTE: We need at least postgres 9.1 to make this back up work.
NOTE: You must be admin to restart the server. And you must be user postgres to make edits to the configuration files. All edits assume your base directory is /var/lib/pgsql/
Hot Standby is the name for the capability to run queries on a database that is currently performing archive recovery. Log Shipping replication allows you to create one or more standby nodes that are replicas of the primary node (or master node). Standby nodes can then be used for read-only query access.
To enable hot standby, make the following changes to the postgres.conf on the master:
Add the following changes to pg_hba.conf on the master:
Save these changes and restart the server. It is now time to configure the slave server.
First stop the postgres server on the slave and create a backup of the existing server. I do this by moving the entire data directory to old.
Now we need to create a new file, called recovery.conf. In essence this file contains the information of the master server:
NOTE: We need at least postgres 9.1 to make this back up work.
NOTE: You must be admin to restart the server. And you must be user postgres to make edits to the configuration files. All edits assume your base directory is /var/lib/pgsql/
Hot Standby is the name for the capability to run queries on a database that is currently performing archive recovery. Log Shipping replication allows you to create one or more standby nodes that are replicas of the primary node (or master node). Standby nodes can then be used for read-only query access.
To enable hot standby, make the following changes to the postgres.conf on the master:
wal_level = hot_standby checkpoint_segments = 8 max_wal_senders = 3 wal_keep_segments = 8Please note that the segment size of the write ahead log (wal) is a fixed 16MB. Should the server undergo huge stress, it could be that the streaming process cannot keep up with WAL, in which case, some data might be lost. To prevent this, increase the size of checkpoint_segments and wal_keep_segments.
Add the following changes to pg_hba.conf on the master:
host replication postgres 192.168.2.37/32 trustWhere 192.168.2.37 is the slave. The above line is saying that we would like the postgres user on host 192.168.2.37 to gain replication access to this master server.
Save these changes and restart the server. It is now time to configure the slave server.
First stop the postgres server on the slave and create a backup of the existing server. I do this by moving the entire data directory to old.
mv 9.2 9.2.oldNow that we have an empty data directory, copy all the contents from the master database with the pg_basebackup command. Note: this only works on versions of postgres greater than or equal to 9.1
pg_basebackup -D 9.2/data --host=10.1.1.7 -v -P -xlog -U postgresWhere 10.1.1.7 is your master server. This in essence copies the entire database over to the slave. Now we need to make some edits. If your access for the master and the slave are the same, you do not need to edit pg_hba.conf. Edit postgresql.conf:
hot_standby = onThis tells postgres that the server should act as a standby server.
Now we need to create a new file, called recovery.conf. In essence this file contains the information of the master server:
standby_mode = 'on' primary_conninfo = 'host=10.1.1.7 port=5432 user=postgres password=postgres'Now start the slave server. You must verify that the server is in recovery; this can be done by opening an sql pane and executing the query:
select pg_is_in_recovery();