Task: Back Up and Restore a Hub Database: TLS Communication

• Introduction
• Permissions Needed
• Preliminaries
• Backing Up
• Restoring
• When You Upgrade CodeSonar
• Links

Introduction

We recommend using streaming replication ("warm standby") to back up your hub in preference to using pg_dump, which can be very slow (especially if the hub is in use). This backup method involves establishing a standby server that continuously receives changes from the hub and updates accordingly. Restoring the hub from this kind of backup involves creating a new hub, swapping in the warm standby database directory, and then promoting it to be the new primary database.

One big advantage of this approach is that its time and space costs are much lower than those for pg_dump. The pg_dump approach is linear in the size of the entire hub every time you do it, so if you did daily backups, they might each take most of a day. With this approach, you get backups more frequently than daily, but at a fraction of the cost.

For full information about continuous archiving, see the PostgreSQL documentation.

In this section (and throughout this manual), $CSONAR indicates the CodeSonar installation directory.

• If you have defined environment variable CSONAR to the location of the CodeSonar installation directory, you can use $CSONAR directly in your command lines. On Windows systems, use %CSONAR% in place of $CSONAR.
• If you don't want to use environment variables, replace $CSONAR with the path to your CodeSonar installation directory before using the command lines.

Permissions Needed

The CodeSonar command lines in this task require the following permissions.

• G_HUB_INFO (needed for codesonar hub-info).
• G_HUB_SHUTDOWN (needed for codesonar hub-stop).
• G_SQL_CONSOLE (needed to use the hub SQL Console).
• G_ADMINISTER_HTTP_SETTINGS (needed to change the hub's HTTP settings).
• (Security for codesonar hub-start is managed by controlling file system access to the hub directory, not through RBAC permissions.)

The hub's default role-permission settings have the following consequences.

Preliminaries

Before you start, do the following.

1. Identify a reliable machine to host your standby server.
   • It must have the same architecture and operating system as the machine that runs the hub.
   • It is preferable for this to be a separate machine to the one on which the hub runs. If you use the same machine for both, a serious problem on that machine could destroy both the hub and the backup.
2. If CodeSonar is not installed on the standby server machine, install it now. It must be the same CodeSonar version as the one that is running the hub. @
3. Determine whether or not your standby server will use TLS to communicate with the hub.
   The hub-standby communication will use TLS if your hub uses TLS for database communication.
   • If your hub is already configured this way, or if you would like to change it to this configuration so that communication between the hub and the standby is encrypted, use the instructions on this page.
   • Otherwise, use the instructions in Task: Back Up and Restore a Hub Database: Unencrypted Communication.

The following instructions are based on the following scenario.

As with all backup strategies, do a dry run to test your backup and restore procedures before going live.

Backing Up

To back up, you will establish the standby server. This requires actions on both hubmachine and standbymachine.

A. Prepare the Hub for Backup

All of these operations are performed on the hub and its database.

• Command lines are executed on hubmachine.
• GUI operations are performed in the hub's Web GUI, whose homepage is http://hubmachine:7340.
• Files that are edited and created are located on hubmachine.

To prepare the hub for backup, do the following.

1. If the hub is not already running, start it now.
2. If the CodeSonar Web GUI is not already open, open it now.
3. Navigate to the HTTP settings tab:
   1. Click the Settings icon in the page header.
      (You may be prompted for hub user account credentials.)
   2. Click the HTTP tab header to switch to the HTTP tab.
4. Select the Allow satellite hubs? checkbox, if it is not already selected.
   You will not be starting a satellite hub, but this is the most straightforward way to ensure that the hub database is listening on all interfaces.
5. Click Update.
6. If you have not already configured the hub to use TLS for database communication, do so now.
   1. Click to select Use TLS for database communication?.
   2. Click Update.
7. Shut down and then restart the hub so that this change takes effect.
   
   codesonar hub-stop hubpath/to/my_hubdir
   codesonar hub-start hubpath/to/my_hubdir [::]:7340
8. Determine the interface and port on which the hub database is listening.
   1. Access the hub info command URL in your browser: http://hubmachine:7340/command/info/.
      (You may be prompted for hub user account credentials.)
   2. Find the Database line in the command output. It will look something like the following.
      Database: postgresql://<db_interface>:<db_port>/cshub
   3. Make a note of <db_interface> and <db_port> so you can use them in the following steps.
9. Use the hub SQL console to find out what version of PostgreSQL your hub is running. You can use this information to search the online PostgreSQL documentation for version-specific details if you require them at any stage.
   1. Access the SQL console URL in your browser: http://hubmachine:7340/sql.html (or https://hubmachine:7340/sql.html).
   2. Enter the following query in the text field.

      select version();
      

   3. Click Submit.
   4. Look at the bottom of the page to see your query result. There will be a table with one entry that describes the hub PostgreSQL version.
   5. Make a note of the version number in case you need it later.
10. In the hub SQL console, create a special user to manage the backup.
    1. Enter the following query in the text field to create a new PostgreSQL database user named replicator.

       CREATE USER replicator REPLICATION LOGIN PASSWORD NULL

    2. Click Submit.
       There will be no confirmation message if you were successful, although there will be an error message if there was a problem.
    3. Enter the following query in the text field to retrieve all the PostgreSQL database user names so you can confirm your addition was successful.
       Note that these are not related to hub user accounts. Usually there is only one PostgreSQL database user for the hub database, but you have just added a second one.

       SELECT u.usename AS "User Name" FROM pg_catalog.pg_user u; 
       

    4. Click Submit.
    5. Look at the bottom of the page to see the query result.
       You should see a table with a single User Name column and two rows: one for cshubuser and one for replicator. This indicates that you successfully added the replicator user.
11. On hubmachine, generate TLS certificates.
    1. Generate a key-certificate pair.
       openssl req -batch -newkey rsa:4096 -extensions client_extensions -nodes -sha256 \
       -keyout replicator.key -out replicator.csr -subj "/CN=replicator" \
       -config $CSONAR/codesonar/py/hub/openssl.conf
    2. Sign the certificate.
       openssl x509 -req -in replicator.csr -days 9999 -CA hubpath/to/my_hubdir/pgdata/client_auth.crt\
       -CAkey hubpath/to/my_hubdir/pgdata/client_auth.key -set_serial 04 -out replicator.crt
12. On hubmachine, make a backup copy of the postgresql.conf file for your hub database.
    
    cp hubpath/to/my_hubdir/pgdata/postgresql.conf hubpath/to/my_hubdir/pgdata/postgresql.conf.bk
    The backup copy will be useful if you need to restore the database configuration to its previous state at some point in the future.
13. Edit hubpath/to/my_hubdir/pgdata/postgresql.conf to include the following settings.
    Make sure your settings are not commented out (that is, that there is not a # character at the start of the line).

    max_wal_senders = 4
    wal_keep_size = 160
    wal_sender_timeout = 60s 
    max_replication_slots = 4
    wal_level = replica
    

    If you are using CodeSonar 6.1 or older, add wal_keep_segments = 10 and remove wal_keep_size = 160
14. On hubmachine, edit hubpath/to/my_hubdir/pgdata/pg_hba.conf to add the following lines at the end of the file. Note: separate the values with tabs.

    # Codesonar: START local access
    hostssl  cshub   cshubuser      ::1/128 cert clientcert=verify-full
    # Codesonar: END local access
    # Codesonar: START shared access
    hostssl  cshub   cshubuser      ::/0    cert clientcert=verify-full
    hostssl  cshub   cshubuser      0.0.0.0/0      cert clientcert=verify-full
    hostssl  cshub   cshubuser      ::/0    cert clientcert=verify-full
    # Codesonar: END shared access
    #
    # Replicator SSL: START
    hostssl replication replicator 0.0.0.0/0       cert clientcert=verify-full
    hostssl replication replicator ::/0     cert clientcert=verify-full
    hostssl cshub replicator       ::/0     cert clientcert=verify-full
    # Replicator SSL: END
    

15. Create a new empty file hubpath/to/my_hubdir/pgdata/pg_hba.conf.csignore.
    
    • This instructs CodeSonar not to overwrite your changes to pg_hba.conf.
    • In some it may also cause the Allow satellite hubs? checkbox on the HTTP Settings tab to be deactivated, so that it cannot be deselected.
16. Shut down and then restart the hub so that your changes to postgresql.conf take effect.
    
    codesonar hub-stop hubpath/to/my_hubdir
    codesonar hub-start hubpath/to/my_hubdir [::]:7340
17. In the hub SQL console, instruct the hub database to reread pg_hba.conf.
    1. Enter the following query in the text field.
       

       SELECT pg_reload_conf();        
       

    2. Click Submit.
    3. Look at the bottom of the page to see the query result.
       You should see a table with a single row and a single column ("pg_reload_conf"), containing the value True.
18. In the hub SQL console, create a physical replication slot for the backup to use.
    1. Enter the following query in the text field to create a new physical replication slot named standby1.
       

       SELECT * from pg_create_physical_replication_slot('standby1');
       

    2. Click Submit.
    3. Look at the bottom of the page to see the query result.
       You should see a table with a single row describing your new standby1 slot.

B. Set up the standby server

All of these operations are performed on standbymachine.

1. Copy the following TLS certificates from hubmachine to standbymachine. Copy them to the parent directory of the standby directory: for example, copy root.crt to sbpath/to/root.crt, where the standby directory is sbpath/to/standbydir.
   • replicator.crt, replicator.key
     (as generated earlier)
   • hubpath/to/my_hubdir/root.crt
2. Run the following command to take a base backup of the hub database and store it in sbpath/to/standbydir. This directory does not have to already exist, but its parent directory does.
   Replace <db_port> with the hub database port number that you determined earlier.

   Windows	set PGSSLCERT=sbpath\to\replicator.crt set PGSSLKEY=sbpath\to\replicator.key $CSONAR\third-party\postgresql\inst\bin\pg_basebackup.exe ^ -D sbpath\to\standbydir -R -S standby1 -h hubmachine -p <db_port> -U replicator -X s
   Other Systems	PGSSLCERT=sbpath/to/replicator.crt PGSSLKEY=sbpath/to/replicator.key $CSONAR/third-party/postgresql/inst/bin/pg_basebackup \ -D sbpath/to/standbydir -R -S standby1 -h hubmachine -p <db_port> -U replicator -X s

   The above reflects the command structure for the current version of CodeSonar. If you have a different version of CodeSonar that uses a different version of PostgreSQL, use the version number you obtained in part A to select version-specific pg_basebackup documentation.

   -D sbpath/to/standbydir	Store the backup in directory sbpath/to/standbydir. This directory does not have to already exist. If it does exist, it must be empty.
   -R	Create standby.signal and append connection settings to sbpath/to/standbydir/postgresql.auto.conf.
   -S standby1	Use the standby1 replication slot that you created earlier.
   -h hubmachine	The primary database is hosted on hubmachine.
   -p <db_port>	The primary database is listening on port <db_port>.
   -U replicator	Connect to the primary database as the special replicator user that you created earlier.
   -X s	Stream the transaction log while the backup is created.

   The command will continue to run until the base backup has been completed.

3. Once the command finishes, inspect the contents of the standby database directory.

   Windows	dir sbpath\to\standbydir
   Other Systems	ls sbpath/to/standbydir

   It should contain a number of database files and directories.
4. Inspect the contents of postgresql.auto.conf.

   Windows	type sbpath\to\standbydir\postgresql.auto.conf
   Other Systems	cat sbpath/to/standbydir/postgresql.auto.conf

   Its contents should reflect the information you provided in your pg_basebackup command.
5. You now have a base backup file, but are not currently streaming changes from the primary database to your backup.
   To stream changes, run the following command.

   Windows	$CSONAR\third-party\postgresql\inst\bin\postgres.exe -D sbpath\to\standbydir
   Other Systems	$CSONAR/third-party/postgresql/inst/bin/postgres -D sbpath/to/standbydir

   If postgres issues an error message saying that the port number is unavailable:
   1. Edit file sbpath/to/standbydir/postgresql.conf to change the setting of port to a port number that is not currently in use on standbymachine.
   2. Try the postgres command line again.
6. Leave this command running for as long as you wish to continue streaming changes. You may prefer to run it in the background.

Monitoring Replication: Hub Side

To confirm that the hub is streaming data, you can use an SQL query.

1. In the hub SQL console, enter the following query in the text field.
   

   SELECT * FROM pg_stat_replication;      
   

2. Click Submit.
3. Look at the bottom of the page to see the query result. There should be a table with one row and a number of columns.
   • "state" should be streaming.
   • "sync_state" should be async.
   • "sent_location", "write_location", and "flush" location should be the same value; "replay_location" may be slightly behind this value.

Monitoring Replication: Standby Server Side

To confirm that the standby server is receiving the streamed data, inspect the timestamps and file sizes in the sbpath/to/standbydir directory. Note that if there is no hub activity, the only changes will be periodic updates to sbpath/to/standbydir/pg_logical/reporigin_checkpoint.

Restore From Backup

Restoring your database will involve starting a brand new hub, swapping in the warm standby database directory, and then promoting it to be the new primary database.
Carry out the hubmachine steps as the system user or service account that is usually responsible for starting the hub.

1. Select a machine to run the restored hub.
   • It can be hubmachine, but is not required to be.
   • If it is a different machine, it must have the same architecture and operating system as hubmachine, and have the same version of CodeSonar installed.
   • The remainder of these instructions will refer to the machine running the restored hub as restoremachine.
2. On hubmachine, shut down the hub if it is running.
   codesonar hub-stop hubpath/to/my_hubdir
   (You may be prompted for hub user account credentials.)
3. On standbymachine, terminate the postgres command that is performing replication.
4. On restoremachine, start a brand new hub.
   • Use the same port as your previous hub.
     (This minimizes reconfiguration requirements.)
   • Specify the same Administrator password as was used on the previous hub.
     (This also minimizes reconfiguration requirements.)
   • Do not use the same hub directory.
     (It is fine to use the same hub directory name, but it must be a new directory.)
   codesonar hub-start recpath/to/newhub_dir [::]:7340
   The remainder of these instructions will use recpath/to/newhub_dir as the hub directory for the restored hub, and 7340 for its port.
5. Once the new hub has started, shut it down.
   
   codesonar hub-stop recpath/to/newhub_dir
   (You may be prompted for hub user account credentials.)
6. Move the existing hub database away.
   mv recpath/to/newhub_dir/pgdata pgdata.old
7. Copy and rename your standby directory to pgdata.

   Windows	Use the remote file transfer method recommended by your local system administrator to perform the copy: original path name and machine: sbpath/to/standbydir on standbymachine destination path name and machine: recpath/to/newhub_dir/pgdata on recoverymachine
   Other Systems	scp -r standbymachine:sbpath/to/standbydir recpath/to/newhub_dir/pgdata

8. Rename the database standby.signal file to standby.signal.disabled.
   mv recpath/to/newhub_dir/pgdata/standby.signal recpath/to/newhub_dir/pgdata/standby.signal.disabled
9. Restart the hub.
   codesonar hub-start recpath/to/newhub_dir [::]:7340
   The hub will start up using the restored database.
10. Navigate to http://restoremachine:7340/ to verify that the restoration worked as expected.
11. In the hub SQL console http://restoremachine:7340/sql.html, create the standby1 physical replication slot so that you can use it when you reestablish the standby.
    This is the only hub-side step you need to repeat from part A: the remaining hub preparation has been backed up and restored.
    1. Enter the following query in the text field to create a new physical replication slot named standby1.
       

       SELECT * from pg_create_physical_replication_slot('standby1');
       

    2. Click Submit.
    3. Look at the bottom of the page to see the query result.
       You should see a table with a single row describing your new standby1 slot.
12. On standbymachine, move the old standby directory aside. You will be starting over with a new one.
    For example:
    mv sbpath/to/standbydir sbpath/to/standbydir20191210
13. Reestablish the standby by working through all the steps of B. Set up the standby server, above.
    (If the hub machine has changed, remember to account for that in your command lines.)

When You Upgrade CodeSonar

Upgrading a CodeSonar hub that is backed up using streaming replication involves some additional steps before and after the hub upgrade.
Carry out all these steps as the system user or service account that is usually responsible for starting the hub.

Before Upgrading the Hub

Perform the following steps on standbymachine to suspend archiving and move your existing archive so that you can establish a new one after upgrading the hub.

1. Suspend archiving by terminating the postgres command that is performing replication.
2. Move your old standby directory aside so that you can create a fresh one after upgrading. This will ensure that all entries in the upgraded standby are consistent with the upgraded hub format. For example:
   mv sbpath/to/standbydir sbpath/to/standbydir20191210

Upgrading

1. Upgrade the CodeSonar installation on both hubmachine and standbymachine.
2. Follow the instructions in Upgrading an Existing Hub: Upgrade the Hub to upgrade the hub.

After Upgrading the Hub

After upgrading the hub, reestablish the standby by working through all the steps of B. Set up the standby server, above.

Links

• Typical CodeSonar Tasks

  In particular, you may be interested in:

  • Make a hub listen on all interfaces
  • ssh tunnel to a hub
• The CodeSonar Hub