Task: Back Up and Restore a Hub Database: Unencrypted

JavaScript is not currently enabled, but is required for full CodeSonar manual search and browse functionality.

If you are viewing this file in your hub's Web GUI, enable JavaScript in your browser: you will also need it for GUI functionality.

If you opened this file directly from disk, your browser may be directly suppressing JavaScript functionality: certain browsers perform this suppression on local files (but not files delivered by web servers) for security reasons.

If you access the manual through the hub's Web GUI, the functionality will not be suppressed because the hub is a web server.
Alternatively, your browser may allow you to explicitly disable the security setting that suppresses functionality. See the CodeSonar FAQ for more information.

CodeSonar^® 9.0p0 Hot Tips

CONFIDENTIAL

CodeSecure Inc

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

Use this method to back up your hub database and then restore it at a later time.

If you want to move the backed-up database to a hub on a different architecture, or restore into a different version of CodeSonar, a different process is required. See Relocating the Hub (Different Machine).

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.

hub shutdown and info commands	If special user Anonymous has G_HUB_INFO and G_HUB_SHUTDOWN permissions, you will not need to provide credentials to authenticate and authorize these commands. Otherwise, you will need to provide suitable hub user account credentials when you execute the corresponding command lines or load the corresponding hub command URLs. See Hub Authentication: Authenticated codesonar Subcommands for more information.
HTTP Settings	Special user Anonymous is always treated as if it does not have G_ADMINISTER_HTTP_SETTINGS permission, even if its assigned roles would otherwise confer it. Therefore, you will always need to sign in to the hub GUI as a user with G_ADMINISTER_HTTP_SETTINGS permission in order to change HTTP settings. See RBAC: Role-Permissions: Special User Permission Adjustments for more information.
SQL Console	If special user Anonymous has G_SQL_CONSOLE permission, you will not need to provide credentials to use the hub GUI SQL console. Otherwise, you will need to sign in to the hub GUI as a user with G_SQL_CONSOLE permission in order to perform the SQL steps. User Administrator should typically have G_SQL_CONSOLE permission.

Preliminaries

Before you start, do the following.

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.
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.
Select a secure password for the special database user you will be creating to manage the backup.@
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 in Task: Back Up and Restore a Hub Database: TLS Communication.
- Otherwise, use the instructions on this page.

The following instructions are based on the following scenario.

Hub	machine name	hubmachine
	interface	[::]
	port	7340
	directory	hubpath/to/my_hubdir
	home page	http://hubmachine:7340
Standby server	name	standbymachine
Standby server	backup directory	sbpath/to/standbydir

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.

If the hub is not already running, start it now.
If the CodeSonar Web GUI is not already open, open it now.
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.
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.
Click Update.
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
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.
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.
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.
  In place of replicatorpw, use the secure password you chose at the beginning of this task (replicatorpw is not a secure password and is just shown as an example).
```
CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'replicatorpw';
```
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.
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.
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
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.
```
# replication setup
host    replication    replicator    all    md5
```
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.
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
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.
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.

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	$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	$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.

You will be prompted for a password.

Enter the password for the replicator user.
The command will continue to run until the base backup has been completed.

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.

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.

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:

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.
Try the postgres command line again.

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.

In the hub SQL console, enter the following query in the text field.
```
SELECT * FROM pg_stat_replication;      
```
Click Submit.
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.

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.
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.)
On standbymachine, terminate the postgres command that is performing replication.
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.
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.)
Move the existing hub database away.
mv recpath/to/newhub_dir/pgdata pgdata.old

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

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
Restart the hub.
codesonar hub-start recpath/to/newhub_dir [::]:7340
The hub will start up using the restored database.
Navigate to http://restoremachine:7340/ to verify that the restoration worked as expected.
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.
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
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.

Suspend archiving by terminating the postgres command that is performing replication.
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

Upgrade the CodeSonar installation on both hubmachine and standbymachine.
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.