How to do it...

Use the following steps to replicate a PostGIS database:

1. Create directories for the primary and standby database clusters by executing the following commands:

      > mkdir postgis_cookbook/db
      > mkdir postgis_cookbook/db/primary
      > mkdir postgis_cookbook/db/standby

2. Initialize the database clusters with initdb as follows, defining the user me as the owner of the database:

      > cd postgis_cookbook/db
      > initdb --encoding=utf8 --locale=en_US.utf-8 -U me -D primary
      > initdb --encoding=utf8 --locale=en_US.utf-8 -U me -D standby

3. You may opt for avoiding the --locale=en_US.utf-8 option if an error occurs; in that case, the system will adopt the default locale on your computer.
4. Create directories for the archives of the primary and standby database clusters by executing the following commands:

      > mkdir postgis_cookbook/db/primary/archive
      > mkdir postgis_cookbook/db/standby/archive

5. Open the pg_hba.conf authentication file of the primary cluster with your preferred editing application.

 

6. If you're running PostgreSQL 9.0, add the following text to the end of pg_hba.conf:

For PostgreSQL 9.1 or a later version, the configuration lines are already part of the pg_hba.conf file. You just need to remove the comment character (#) from the beginning of each matching line.

7. Edit the primary cluster's postgresql.conf configuration file to set the streaming replication parameters. Search for each parameter, uncomment and replace the assigned value to the following:

      port = 5433
      wal_level = hot_standby 
      max_wal_senders = 5
      wal_keep_segments = 32
      archive_mode = on
      archive_command = 'copy "%p" 
      "C:\\postgis_cookbook\\db\\primary\\archive\\%f"' # for Windows

A relative location could also be used:

      archive_command = 'copy "%p" "archive\\%f" "%p"'

When using Linux or macOS type instead:

      archive_command = 'cp %p archive\/%f'

8. Start PostgreSQL on the primary database cluster by executing the following command:

      > pg_ctl start -D primary -l primary\postgres.log

9. Create a base backup of the primary database cluster and copy it to the standby database cluster. Before performing the backup, create an exclusion list file for xcopy (Windows only) by executing the following command:

      > notepad exclude.txt

10. Add the following to exclude.txt:

      postmaster.pid
      pg_xlog

11. Run the base backup and copy the directory contents from the primary to the standby database cluster, as follows:

      > psql -p 5433 -U me -c "SELECT pg_start_backup('base_backup', true)"
      > xcopy primary\* standby\ /e /exclude:primary\exclude.txt
      > psql -p 5433 -U me -c "SELECT pg_stop_backup()"

12. Make the following changes to the standby cluster's postgresql.conf configuration file uncommenting these parameters and adjusting the values:

      port = 5434
      hot_standby = on
      archive_command = 'copy "%p" 
      "C:\\postgis_cookbook\\db\\standby\\archive\\%f"' # for Windows

A relative location could also be used:

      archive_command = 'copy ".\\archive\\%f" "%p"'

When using Linux or macOS type instead:

      archive_command = 'cp %p archive\/%f'

13. Create the recovery.conf configuration file in the standby cluster directory by executing the following command for Windows:

      > notepad standby\recovery.conf

For Linux or macOS:

      > nano standby\recovery.conf

14. Enter the following in the recovery.conf configuration file and save the changes:

      standby_mode = 'on'
      primary_conninfo = 'port=5433 user=me'
      restore_command = 'copy 
      "C:\\postgis_cookbook\\db\\standby\\archive\\%f" "%p"'

Or a relative location could be used also:

      restore_command = 'copy ".\\archive\\%f" "%p"'

For Linux or macOS use:

      restore_command = 'cp %p \archive\/%f"'

15. Start PostgreSQL on the standby database cluster by executing the following command:

      > pg_ctl start -U me -D standby -l standby\postgres.log

16. Run some simple tests to make sure the replication is working.
17. Create the test database and the test table on the primary database server by executing the following commands:

      > psql -p 5433 -U me
      postgres=# CREATE DATABASE test;
      postgres=# \c test
      test=# CREATE TABLE test AS SELECT 1 AS id, 'one'::text AS value;

18. Connect to the standby database server by executing the following command:

      > psql -p 5434 -U me

19. See if the test database is present by executing the following command:

      postgres=# \l  

20. Connect to the test database and get the list of tables by executing the following command:

      postgres=# \c test  

21. Get the records, if any, in the test table by executing the following commands:

Congratulations! The streaming replication works.