Promoting a Standby to the Main server

Except for an absolute calamity, there is no reason to promote the hot standby to be the main server. If you need to, the general recovery steps are outlined below. There is only ONE command to do at the backup server to make it the primary server (see bottom half of page).

1. Shut down, turn off, unplug and/or remove the main server from the network so that it is no longer active
2. On the backup server, create the failover trigger file using the primary failover methodology (described in detail below):

   sudo touch /tmp/pg_failover_trigger

3. The hot standby is now your main server.
4. You may need to change the pg_hba.conf file as required to allow workstations to access the database. It is probably okay because it was originally copied from the main server at time of making the standby. You will only need to change the pg_hba.conf file if you added more sub-nets to the main server.
   
5. Do all the work necessary to get Theatre Manager clients to point to a new database server by doing one of:
   • Use Theatre Manager to change the IP address of the database you log into -or-
   • Change the IP address of the hot standby to be the same as the Master main server -or-
   • Change the DNS to point to the new IP address, if that's what you use to connect to the server
6. Log all clients, second generation and classic listeners into the new server IP address
   • Log into the database using Theatre Manager.
   • Quit and restart the Theatre Manager Server
   • Quit and restart any web listeners
7. Fix the reason that the main server died and plan to set up a new hot backup server

Primary Method to promoting the Backup Server to Main Server

If your recovery.conf file in the Postgres data directory was set up according to these instructions, it should have a trigger_file parameter and look like the except below.

# specify the name of a file whose presence should cause streaming replication to # end (i.e. failover). If you create this file, then Postgres will rename 'recovery.conf' # to 'recovery.done' and promote the hot standby to a primary server without a restart. trigger_file = '/tmp/pg_failover_trigger'

Assuming that the path name of the trigger file is /tmp/pg_failover_trigger, then, in Terminal as an admin user, type

sudo touch /tmp/pg_failover_trigger

This will cause Postgres to:

• Notice the file
• Finish off the slave process and recover any log files transferred from the main server that still needs recovery
• Rename the 'recovery.conf' file to 'recovery.done'
• when that happens, it is now the primary server

Alternate Method using pg_ctl

use pg_ctl to promote the server

su - postgres
enter the postgres password
pg_ctl promote

For more information refer to the postgres fail-over documentation

26.3. Failover

If the primary server fails then the standby server should begin failover procedures.
If the standby server fails then no failover need take place. If the standby server can be restarted, even some time later, then the recovery process can also be restarted immediately, taking advantage of restartable recovery. If the standby server cannot be restarted, then a full new standby server instance should be created.
If the primary server fails and the standby server becomes the new primary, and then the old primary restarts, you must have a mechanism for informing the old primary that it is no longer the primary. This is sometimes known as STONITH (Shoot The Other Node In The Head), which is necessary to avoid situations where both systems think they are the primary, which will lead to confusion and ultimately data loss.
Many failover systems use just two systems, the primary and the standby, connected by some kind of heartbeat mechanism to continually verify the connectivity between the two and the viability of the primary. It is also possible to use a third system (called a witness server) to prevent some cases of inappropriate failover, but the additional complexity might not be worthwhile unless it is set up with sufficient care and rigorous testing.
PostgreSQL does not provide the system software required to identify a failure on the primary and notify the standby database server. Many such tools exist and are well integrated with the operating system facilities required for successful failover, such as IP address migration.
Once failover to the standby occurs, there is only a single server in operation. This is known as a degenerate state. The former standby is now the primary, but the former primary is down and might stay down. To return to normal operation, a standby server must be recreated, either on the former primary system when it comes up, or on a third, possibly new, system. The pg_rewind utility can be used to speed up this process on large clusters. Once complete, the primary and standby can be considered to have switched roles. Some people choose to use a third server to provide backup for the new primary until the new standby server is recreated, though clearly this complicates the system configuration and operational processes.
So, switching from primary to standby server can be fast but requires some time to re-prepare the failover cluster. Regular switching from primary to standby is useful, since it allows regular downtime on each system for maintenance. This also serves as a test of the failover mechanism to ensure that it will really work when you need it. Written administration procedures are advised.
To trigger failover of a log-shipping standby server, run pg_ctl promote or create a trigger file with the file name and path specified by the trigger_file setting in recovery.conf. If you're planning to use pg_ctl promote to fail over, trigger_file is not required. If you're setting up the reporting servers that are only used to offload read-only queries from the primary, not for high availability purposes, you don't need to promote it.