backup

Syntax:

    shardmanctl [common_options] backup --datadir directory [--maxtasks number_of_tasks] [--use-ssh]
   

Backs up a Shardman cluster.

For more details, see the section called “Backing up a Shardman Cluster”

cleanup

Syntax:

shardmanctl [common_options] cleanup [-p|--processrepgroups] --after-node-operation|--after-rebalance

Performs cleanup after the nodes add or rebalance command.

config update credentials

Syntax:

shardmanctl [common_options] config update credentials [-u | --user] [-p | --password] [-k | --ssl-key] [-c | --ssl-cert] [-w|--wait time_duration] [--force] [-y | --yes] [--from-primary-cluster]

Updates password or certificate/key of a user to connect to a Shardman cluster. It only updates the authentication type that was specified by the user (scram-sha-256, ssl) and not the type itself.

cluster repfactor set

Syntax:

shardmanctl [common_options] cluster repfactor set --value new_repfactor

Sets the replication factor for the manual-topology mode.

cluster start

Syntax:

shardmanctl [common_options] cluster start

Starts all PostgreSQL server instances.

cluster stop

Syntax:

shardmanctl [common_options] cluster stop [-y|--yes]

Stops all PostgreSQL server instances.

cluster standby enable

Syntax:

shardmanctl [common_options cluster standby enable [-y | --yes][--no-wait]

Switches the cluster to the standby mode.

cluster standby disable

Syntax:

shardmanctl [common_options cluster standby disable [-y | --yes][--no-wait]

Checks that all cluster instances are restored to the same checkpoint and then switches the cluster from the standby mode back to the main. Once the standby mode is disabled, all cluster instances read the shardman/<cluster_name>/data/last-replay-lsn/<shard_name>/<node_address> etcd key, that checks whether all instances are restored to the same syncpoint.

Note that if there are dictionary errors after the cluster is up, you can fix it with the shardmanctl config update fdw command.

cluster standby catchup

Syntax:

shardmanctl [common_options] cluster standby catchup
[-s|--shard shard_name]
[-n|--node node_names --soutce-datadir directory]
[--remote-port port]
[--remote-user username]
[--ssh-key path]
[-j|--jobs count]
[--log-directory path]
[--log-to-console]

Copies data from the primary cluster instance to the standby cluster instance if one of the standby cluster instances lags behind. It is run on the standby cluster node and uses StandbySettings from sdmspec.

cluster topology

Syntax:

shardmanctl [common_options] cluster topology -f|--format table|json|text

Displays the cluster topology.

daemon check

Syntax:

shardmanctl [common_options] daemon check -n|--nodes node_name:port

Checks shardmand on nodes.

forall

Syntax:

shardmanctl [common_options] forall --sql query[ --sql query[ --sql query ...]] [--twophase]

Executes an SQL statement on all replication groups in a Shardman cluster.

getconnstr

Syntax:

shardmanctl [common_options] getconnstr --all

Gets the libpq connection string for connecting to a cluster as administrator.

init

Syntax:

    shardmanctl [common_options] init [-y|--yes] [-f|--spec-file spec_file_name]|spec_text
   

Registers a new Shardman cluster in the etcd store or reinitializes the existing cluster defining a new cluster configuration and removing all data and nodes.

intcheck

Syntax:

    shardmanctl [common_options] intcheck [-s|--system] [-u|--user] [-c|--catalog] [-o|--output]  [-n|--node node]
   

Runs pg_integrity_check on all nodes of a Shardman cluster or on a selected one node.

load

Syntax:

     shardmanctl [common_options] load [ -b | --batch-size lines_limit] [ --destination-fields fields_list]
     [ --distributed-keys key_type_list] [ -D | --delimiter character]
     [--null_marker string] [ -e | --escape character] [ -f | --file input_file]
     [ -F | --format text|csv ] [ -j | --jobs task_total] [ -q | --quote character]
     [ --reject-file filename] [ --schema filename] [ --source file|postgres]
     [ --source-connstr connect_string] [ --source-fields fields_list] [ --source-table source_table]
     [ -t | --table destination_table]
   

Loads data to a Shardman cluster.

nodes add

Syntax:

shardmanctl [common_options] nodes add -n|--nodes node_names [--no-rebalance]

Adds nodes to a Shardman cluster.

nodes rm

Syntax:

shardmanctl [common_options] nodes rm -n|--nodes node_names

Removes nodes from a Shardman cluster.

probackup

Syntax:

shardmanctl [common_options] probackup
       [init|archive-command|backup|checkdb|delete|merge|restore|set-config|show|validate|show-config]
       [--log-to-console][--help]
       [subcommand_options]

Creates a backup of a Shardman cluster and restores the Shardman cluster from a backup using pg_probackup.

shardmanctl [common_options] probackup init
-B|--backup-path path
-E|--etcd-path path
[--remote-port port]
[--remote-user username]
[--ssh-key path]
[-t|--timeout seconds]
[-m|--maxtasks number_of_tasks]
[--storage-type mount|remote|S3]
[--s3-config-only]
[--s3-config-path path]
[--s3-host S3_host]
[--s3-port S3_port]
[--s3-access-key S3_access_key]
[--s3-secret-key S3_secret_key]
[--s3-bucket S3_bucket]
[--s3-region S3_region]
[--s3-buffer-size size]
[--s3-retries number_of_retries]
[--s3-timeout time]
[--s3-https]
[-y|--yes]

shardmanctl [common_options] probackup archive-command [add|rm]
        -B|--backup-path path
        [-j|--jobs count]
        [--compress]
        [--compress-algorithm algorithm]
        [--compress-level level]
        [--batch-size batch_size]
        [--storage-type mount|remote|S3]
        [--remote-port port]
        [--remote-user username]
        [-s|--shard shard-name]
        [--s3-config-path path]
        [-y|--yes]
    

shardmanctl [common_options] probackup backup -B|--backup-path path
        -E|--etcd-path path
        -b|--backup-mode MODE
        [-j|--jobs count]
        [--compress-algorithm algorithm]
        [--compress-level level]
        [--batch-size batch_size]
        [--storage-type mount|remote|S3]
        [--remote-port port]
        [--remote-user username]
        [--ssh-key path]
        [-t|--timeout seconds]
        [-m|--maxtasks number_of_tasks]
        [--log-directory path]
        [--s3-config-path path]
        [--no-validate]
        [--skip-block-validation]
        [--log-to-console]
        [--retention-redundancy]
        [--retention-window]
        [--wal-depth]
        [--delete-wal]
        [--delete-expired]
        [--merge-expired]
        [-y | --yes]
        [--lock-lifetime]
    

shardmanctl [common_options] probackup checkdb
[--amcheck [--skip-block-validation] [--heapallindexed]] [--shard shard]
[-m|--maxtasks number_of_tasks]

shardmanctl [common_options] probackup delete -B|--backup-path path
        [-i|--backup-id backup_id]
        [-j|--jobs count]
        [-m|--maxtasks number_of_tasks]
        [--storage-type mount|remote|S3]
        [--s3-config-path path]
        [--delete-wal]
        [-y|--yes]
        [--retention-redundancy]
        [--retention-window]
        [--wal-depth]
        [--delete-expired]
        [--merge-expired]

shardmanctl [common_options] probackup merge -B|--backup-path path
        -i|--backup-id backup_id
        [-j|--jobs count]
        [-m|--maxtasks number_of_tasks]
        [--no-validate]
        [--no-sync]
        [-y|--yes]

shardmanctl [common_options] probackup restore
        -B|--backup-path path
        -i|--backup-id id
        -j|--jobs count
        [--recovery-target-time timestamp]
        [-I|--recovery-mode incremental_mode]
        [-t|--timeout seconds]
        [-m|--maxtasks number_of_tasks]
        [--metadata-only] [--schema-only] [--shard shard]
        [--no-validate]
        [--skip-block-validation]
        [--s3-config-path path]
        [--storage-type mount|remote|S3]
        [--wal-limit number_of_wal_segments]
        [--log-directory path]
        [--data-validate]
    

shardmanctl [common_options] probackup show
        -B|--backup-path path
        [-f|--format table|json]
        [--archive ]
        [-i|--backup-id backup-id]
        [--instance instance]
        [--storage-type mount|remote|S3]
        [--s3-config-path path]
    

shardmanctl [common_options] probackup show-config
    -B backup_path
    [--format=text|json]
    [--no-scale-units]
    -s|--shard shard_name
    [--s3-config-path path]
    [--storage-type mount|remote|S3]

shardmanctl [common_options] probackup validate
        -B|--backup-path path
        -i|--backup-id id
        [-t|--timeout seconds]
        [-m|--maxtasks number_of_tasks]
        [--log-to-console]
        [--storage-type mount|remote|S3]
        [--s3-config-path path]
        [--log-directory path]
        [--remote-port port]
        [--remote-user username]
    

shardmanctl [common_options] probackup set-config
        [--archive-timeout int]
        [-B | --backup-path string]
        [-m |--maxtasks int]
        [--remote-port int]
        [--remote-user string]
        [--retention-redundancy int]
        [--retention-window int]
        [--wal-depth int]
        [--s3-config-path string]
        [-s |--shard string]
        [--storage-type string]

rebalance

Syntax:

shardmanctl [common_options] rebalance [-f|--force]

Rebalances sharded tables.

recover

Syntax:

shardmanctl [common_options] recover [--info file] [--dumpfile file] [--shard shard] [--metadata-only][--schema-only] [--timeout seconds]

Restores a Shardman cluster from a backup created by the backup command.

For more details, see the section called “Restoring a Shardman Cluster”

restart

Syntax:

     shardmanctl [common_options] restart [-y|--yes] [--no-wait]
   

Restarts a Shardman cluster.

restart rolling

Syntax:

     shardmanctl [common_options] restart rolling [-y|--yes] [--restore-masters] [--upgrade]
   

Consecutively restarts a Shardman cluster with minimum downtime. It restarts shardmand, consecutively restarts all standbys, switches the primary to one of the standbys, and restarts the former primary instance.

shard add

Syntax:

shardmanctl [common_options] shard -s|--shard shard_name add -n|--nodes node_names [--no-wait]

Adds a replica to a shard.

shard master set

Syntax:

shardmanctl [common_options] shard -s|--shard shard_name master set [--yes | -y] [-n| node node_names]

Sets the precedence for a certain primary server for a specified shard.

shard master reset

Syntax:

shardmanctl [common_options] shard -s|--shard shard_name master reset [--yes | -y] -n| node node_names

Resets the parameters of the master with precedence for the shard.

shard add

Syntax:

shardmanctl [common_options] shard -s|--shard shard_name reset [--yes | -y][--new-primary | -p]

Resets nodes of a replication group if they are in a state of hanging.

shard rm

Syntax:

     shardmanctl [common_options] shard -s|--shard shard_name rm -n|--nodes node_names [-f|--force]
   

Removes a replica from a shard.

shard switch

Syntax:

     shardmanctl [common_options] shard -s|--shard shard_name switch [--new-primary node_names]
   

Switches the primary node.

shard start

Syntax:

     shardmanctl [common_options] shard -s |--shard shard_name start [--no-wait] [-n|--node node_name]
   

Starts the shard.

shard stop

Syntax:

     shardmanctl [common_options] shard -s |--shard shard_name stop [-n|--node node_name]
   

Stops the shard.

shard replicas reinit

Syntax:

     shardmanctl [common_options] shard -s|--shard shard_name replicas reinit [-n|--node node_names] [-y|--yes] [--no-wait]

Resets replicas of a specific shard.

For more details, see the section called “Reinitializing Replicas”

nodes start

Syntax:

     shardmanctl [common_options] nodes start -n|--nodes node_names [--no-wait]
   

Starts the nodes.

nodes restart

Syntax:

     shardmanctl [common_options] nodes restart -n|--nodes node_names [--no-wait]
   

Restarts the nodes.

nodes stop

Syntax:

     shardmanctl [common_options] nodes stop -n|--nodes node_names [--no-wait]
   

Stops the nodes.

status

Syntax:

     shardmanctl [common_options] status [-f|--format table|json] [--filter store|metadata|shardmand|rg|master|dictionary|all|restart_required_params] [-s|--sort node|rg|status]
   

Reports on the health status of Shardman cluster subsystems.

status transactions

Syntax:

     shardmanctl [common_options] status transactions [-r|--repgroup replication_group_name
     

Shows distributed transactions that Shardman built-in monitoring tools failed to resolve.

store dump

Syntax:

     shardmanctl [common_options] store dump [-f|--file filename]
   

Dumps current cluster specifications from the store.

store restore

Syntax:

     shardmanctl [common_options] store restore [--delete-old-keys][-f|--file filename][-y|--yes]
   

Allows to safely restore the etcd cluster from the dump. To do this, shardmand must be disabled on every shard. Also, it only works for the cold backup.

store lock

Syntax:

     shardmanctl [common_options] store lock [-f|--format text|json]
   

Shows the current cluster meta lock information.

store get

Syntax:

    shardmanctl [common_options] store get [[-a|--alias aliasname]|[-k|--key keyname] [-f|--file filename]]
   

Gets the specified key from the store.

store keys

Syntax:

    shardmanctl [common_options] store keys
   

Gets all keys with the current cluster prefix from the store.

For more details, see the section called “Getting the Cluster and Ladle Key Names For the Current Cluster”.

store set

Syntax:

    shardmanctl [common_options] store set [[-a|--alias aliasname]|[-k|--key keyname]] [-f|--file filename]
   

Creates or rewrites a key in the store.

tables sharded info

Syntax:

    shardmanctl [common_options] tables sharded info [-t|--table table_name]
   

Gets information about a sharded table.

tables sharded list

Syntax:

    shardmanctl [common_options] tables sharded list
   

Gets the list of all sharded tables.

tables sharded norebalance

Syntax:

    shardmanctl [common_options] tables sharded norebalance
   

Gets the list of sharded tables with automatic rebalancing disabled.

tables sharded partmove

Syntax:

    shardmanctl [common_options] tables sharded partmove [-t|--table table_name] [-s|--shard shard_name] [-p|--partnum number]
   

Moves the specified partition of a sharded table to a new shard.

tables sharded rebalance

Syntax:

    shardmanctl [common_options] tables sharded rebalance [-t|--table table_name]
   

Enables and runs automatic data rebalancing for the selected sharded table.

config get

Syntax:

shardmanctl [common_options] config get [-c | --choose-revision] [-r | --revision ] [-f | --file]

Outputs the current full cluster specification or a configuration of the specified revision.

config revisions rm

Syntax:

shardmanctl [common_options] config revisions rm [-r | --revision ] [-y | --yes]

Deletes a specified configuration revision from history.

config update

Syntax:

shardmanctl [common_options] config update [[-f|--file stolon_spec_file|shardman_spec_file]|spec_text [-p|--patch][-w|--wait]] [--force] [-y | --yes] [--from-primary-cluster]

Updates the stolon or full Shardman configuration.

config rollback

Syntax:

shardmanctl [common_options] config rollback [-r | --revision] [-w|--wait time_duration] [--force] [-y|--yes]

Makes a rollback of Shardman to one of the previous states. When rolling back to the config revision that has max_connections, max_prepared_transactions, or max_worker_processes parameters, the replicas are reinitialized.

config revisions

Syntax:

shardmanctl [common_options] config revisions [-f|--format text|json]

Outputs the revision history of the Shardman cluster configuration. It has the following information for each revision:

config revisions set

Syntax:

shardmanctl [common_options] config revisions set [--keep-config-revisions]

Allows setting the length of the configuration revision history. This length cannot be lower than 5, in which case it is automatically set to 5. For Shardman clusters where the configuration revision history was not collected yet, the length is automatically set to 20.

config update ip

Syntax:

shardmanctl [common_options] config update ip [-u|ip_1=ip_2,hostname_1=hostname_2][-y|--yes]

Updates the specified node IPs in the cluster.

set

Syntax:

shardmanctl [common_options] set pgParam1=value1 [pgParam2=value2 [...]] [-y|--yes] [-w|--wait time_duration] [-f|--force]

Sets the values of the specified Shardman cluster database parameters.

upgrade

Syntax:

shardmanctl [common_options] upgrade

Upgrades the shardman database extension and updates pg_foreign_server options.

bench init

Syntax:

shardmanctl [common_options] bench init [--schema-type single|simple|shardman|custom]
[--schema-file file_name] [-s|--scale scale_value]  [-n|--no-vacuum]
[-F|--fillfactor fillfactor_value]

Initializes the benchmark schema via pgbench. Schema can be custom or predefined. Creates tpc-b schema tables and fills them.

bench run

Syntax:

shardmanctl [common_options] bench run [--schema-type single|simple|shardman|custom]
[-f|--file file_name] [-c|--client client_value]  [-C|--connect] [--full-output]
[-j|--jobs jobs_value][-T|--time seconds][-t|--transactions transactions_value]
[-s|--scale scale_factor] [ -P | --progress seconds] [ -R | --rate rate] [ -M | --protocol querymode]

Runs the initialized benchmark via pgbench. Can use the default pgbench script or a custom script from a file.

bench cleanup

Syntax:

shardmanctl [common_options] bench cleanup

Cleans up schema database after benchmarks. Drops tpc-b tables.

bench generate

Syntax:

shardmanctl [common_options] bench generate [-c|--config file_name] [-o|--output-file file_name]

Gets the benchmark configuration from a file and generates a bash script to create a schema optimized for Shardman and run the benchmark using pgbench. The configuration file must be in yaml format.

script

Syntax:

shardmanctl [common_options] script -s|--shard shard_name][[-f|--file file_name][--sql query]]

Executes non-transactional commands from a file or from the command-line on the specified shards.

psql

Syntax:

shardmanctl [common_options] psql -s|--shard shard_name

Connects to the first available primary node if no options are specified.

daemon set

Syntax:

shardmanctl [common_options] daemon set [--session-log-level debug | info | warn | error] [--session-log-format json|text] [--session-log-nodes]

Allows updating the log parameters “on the fly”.

history

Syntax:

shardmanctl [common_options] history [--reverse | -r] [-f|--format json|text] [-l|--limit number_of_commands]

Shows history of the commands that updated the cluster. By default, they are sorted from the most recent to the oldest ones.

Adding Nodes to a Shardman Cluster

To add nodes to a Shardman cluster, run the following command:

shardmanctl [common_options] nodes add -n|--nodes node_names

You must specify the -n (--nodes) option to pass the comma-separated list of nodes to be added. Nodes can be referred by their hostname or IP address. Hostnames must be correctly resolved on all nodes.

If nodes add command fails during execution, use the cleanup --after-node-operation command to fix possible cluster configuration issues.

Performing Cleanup

By default, cleanup operates in the report-only mode, that is, the following command will only show actions to be done during actual cleanup:

    shardmanctl [common_options] cleanup --after-node-operation|--after-rebalance
   

To perform the actual cleanup, run the following command:

    shardmanctl [common_options] cleanup -p|--processrepgroups --after-node-operation|--after-rebalance
   

Displaying the Cluster Topology

cluster topology displays the current cluster topology. The default is the table mode. All cluster nodes will be grouped by the replication groups they belong to. For each node, its status will be displayed.

    shardmanctl [common_options] cluster topology -f|--format table|json|text
   

Checking shardmand Service on Nodes

daemon check not only checks that shardmand service is running on specified nodes, but also assures those services are configured for the same cluster as shardmanctl:

    shardmanctl [common_options] daemon check -n|--nodes node_names
   

Removing Nodes from a Shardman cluster

To remove nodes from a Shardman cluster, run the following command:

shardmanctl [common_options] nodes rm -n|--nodes node_names

Specify the -n (--nodes) option to pass the comma-separated list of nodes to be removed.Recreates all partitions of sharded tables

To remove all nodes in a cluster and not care about the data, just reinitialize the cluster. If a removed replication group contains local (non-sharded and non-global) tables, the data is silently lost after the replication group removal.

Getting the Standby Cluster Status

Here is a sample status output from shardmanctl with OK and Error statuses for the cluster in the standby mode. It also shows what syncpoint each DB instance was restored to, and whether it is required to wait for recovery to the next syncpoint. For the standby mode, this is what the output looks like (and not what was filtered with --filter option):

        $ shardmanctl status
        
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                                                                        == STANDBY CLUSTER STATUS ==                                                                                                                        │
├─────────────────────────────┬──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─────────────────────────────────────────────────────────┬─────────────────────────────────────────────────────────┤
│            STATUS           │                                                      MESSAGE                                                     │                           NODE                          │                    REPLICATION GROUP                    │
├─────────────────────────────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼─────────────────────────────────────────────────────────┼─────────────────────────────────────────────────────────┤
│           WARNING           │ Instance recovered at an older syncpoint with CSN 1754916674425273000; last replay LSN is 0/1189F0A0, last found │                          shrn1r                         │                         shard-1                         │
│                             │                                            syncpoint LSN is 0/11CA0588                                           │                                                         │                                                         │
├─────────────────────────────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼─────────────────────────────────────────────────────────┼─────────────────────────────────────────────────────────┤
│              OK             │                          Instance recovered at a syncpoint with CSN 1754916734406196000                          │                          shrn2r                         │                         shard-2                         │
├─────────────────────────────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼─────────────────────────────────────────────────────────┼─────────────────────────────────────────────────────────┤
│           WARNING           │ Instance recovered at an older syncpoint with CSN 1754916674425273000; last replay LSN is 0/157A3618, last found │                          shrn3r                         │                         shard-3                         │
│                             │                                            syncpoint LSN is 0/233CEB70                                           │                                                         │                                                         │
├─────────────────────────────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼─────────────────────────────────────────────────────────┼─────────────────────────────────────────────────────────┤
│           WARNING           │ Instance recovered at an older syncpoint with CSN 1754916674425273000; last replay LSN is 0/16C743C8, last found │                          shrn4r                         │                         shard-4                         │
│                             │                                            syncpoint LSN is 0/2616C078                                           │                                                         │                                                         │
├─────────────────────────────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼─────────────────────────────────────────────────────────┼─────────────────────────────────────────────────────────┤
│           WARNING           │ Instance recovered at an older syncpoint with CSN 1754916674425273000; last replay LSN is 0/1AB9A798, last found │                          shrn5r                         │                         shard-5                         │
│                             │                                            syncpoint LSN is 0/2DF8F348                                           │                                                         │                                                         │
├─────────────────────────────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼─────────────────────────────────────────────────────────┼─────────────────────────────────────────────────────────┤
│              OK             │                          Instance recovered at a syncpoint with CSN 1754916734406196000                          │                          shrn6r                         │                         shard-6                         │
└─────────────────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────────────────────────────────────────────────┴─────────────────────────────────────────────────────────┘


          
        

For json format:

shardmanctl status --format json
[
 {
  "Title": "STANDBY CLUSTER STATUS",
  "Messages": [
   {
    "Status": "WARNING",
    "Message": "Instance recovered using catchup, need to wait for recovery to the next syncpoint",
    "Node": "shrn1r",
    "Shard": "shard-1"
   },
   {
    "Status": "OK",
    "Message": "Instance recovered at a syncpoint with CSN 1754917034419818000",
    "Node": "shrn2r",
    "Shard": "shard-2"
   },
   {
    "Status": "OK",
    "Message": "Instance recovered at a syncpoint with CSN 1754917034419818000",
    "Node": "shrn3r",
    "Shard": "shard-3"
   },
   {
    "Status": "OK",
    "Message": "Instance recovered at a syncpoint with CSN 1754917034419818000",
    "Node": "shrn4r",
    "Shard": "shard-4"
   },
   {
    "Status": "OK",
    "Message": "Instance recovered at a syncpoint with CSN 1754917034419818000",
    "Node": "shrn5r",
    "Shard": "shard-5"
   },
   {
    "Status": "OK",
    "Message": "Instance recovered at a syncpoint with CSN 1754917034419818000",
    "Node": "shrn6r",
    "Shard": "shard-6"
   }
  ]
 }
]

Getting the Status of Cluster Subsystems

To get a report on the health status of Shardman cluster in a table format for metadata and store subsystems sorted by replication group, run the following command:

    shardmanctl [common_options] status --filter=metadata,store --sort=rg
   

To get the report in JSON format, use -f|--format=json option (omitted above since table format is used by default). Each detected issue is reported as an Unknown, Warning, Error or Fatal error status. The tool can also report an Operational error, which means there was an issue during the cluster health check. When the command encounters a Fatal or Operational error, it stops further diagnostics. For example, an inconsistency in the store metadata does not allow correct cluster operations and must be handled first.

Outputting the List of Unresolved Distributed Transactions

To view the list of distributed transactions that Shardman built-in monitoring tools failed to resolve, run the following command:

    shardmanctl [common_options] status transactions -r|--repgroup replication_group_name

Each output transaction consists of tx_id (transaction ID), coordinator_id, creation_time and description (error or transaction status). To display the list of transactions for a specific replication group, use the -r|--repgroup option (for all replication groups by default). In case there are no such transactions, returns null value in JSON.

Dumping All Keys from the Store to Debug Error Configuration

After facing an error while using Shardman cluster, to fill in an exhaustive report, it is convinient to dump all specifications that could produce such an error with the following command:

    shardmanctl [common_options] store dump -f|--file filename
   

Some harmless errors may be shown, but they will not interrupt dumping. If you do not specify the filename, dump will be sent to stdout and may pollute your terminal.

Getting the Current stolon Specification

To get the current stolon specification, which is normally a part of cluster key in the store, use the following command:

    shardmanctl [common_options] store get -a|--alias stolonspec -f|--file filename
   

If the cluster key is corrupted itself, stolon specification will not be shown either. Instead of using the alias, you may also find out the full cluster data key name (by listing all keys with store keys command), use store get to retrieve it and find the stolon part there. Mind that while using the last option, shardman.config_uuid parameter will not be deleted, which may result in a conflict in later use of this data; for manipulation with stolon specification, it is recommended to use shardmanctl store get -a stolonspec command.

Getting the Cluster and Ladle Key Names For the Current Cluster

To get all key names in the store at once, run the following command:

    shardmanctl [common_options] store keys
   

It can only be shown in JSON format. It will also print alias names for keys that have them (excluding stolonspec and spec, since they are parts of other keys)

Output Current Cluster Meta Lock Information

You can view information about current cluster meta locks that acquired by any command:

    shardmanctl [common_options] store lock -f|--format json
   

To get the report in JSON format, use -f|--format=json option (omitted above since text format is used by default). In case the lock does not exists returns Lock not found

Setting a New Spec for the Cluster

To set a new spec part of the cluster specification, run the following command:

    shardmanctl [common_options] store set --alias=spec --file=spec.json
   

Since spec is a part of cluster data key, it cannot be set with --key. If the provided file is not a valid JSON, the new spec part will not be set.

Backing up a Shardman Cluster

Requirements for backing up and restoring a Shardman cluster using the basebackup command are listed in Section 2.6.1.1.

To backup a Shardman cluster, you can run the following command:

    shardmanctl [common_options] backup --datadir directory [--use-ssh]
   

You must pass the directory to write the output to through the --datadir option. You can limit the number of running concurrent tasks (pg_receivewal or pg_basebackup commands) by passing the limit through the --maxtasks option.

If --use-ssh is specified shardmanctl recover command will use scp command to restore data. It allows to use backup repository on the local host.

Registering a Shardman Cluster

To register a Shardman cluster in the etcd store, run the following command:

    shardmanctl [common_options] init [-y|--yes] [-f|--spec-file spec_file_name]|spec_text
   

You must provide the string with the cluster specification. You can do it as follows:

Restoring a Shardman Cluster

shardmanctl can perform either full restore, metadata-only or schema-only restore of a Shardman cluster from a backup created by the backup command.

To perform full restore, you can run the following command:

    shardmanctl [common_options] recover --info file
   

Pass the file to load information about the backup from through the --info option. In most cases, set this option to point to the backup_info file in the backup directory or to its modified copy.

If you encounter issues with an etcd instance, it makes sense to perform metadata-only restore. To do this, you can run the following command:

   shardmanctl [common_options] recover --dumpfile file --metadata-only
   

You must pass the file to load the etcd metadata dump from through the --dumpfile option.

If you need to restore only schema information, like: tables, roles and etc. you should specify --schema-only option.

For all kinds of restore, you can specify --timeout for the tool to exit with error after waiting until the cluster is ready or the recovery is complete for the specified number of seconds.

You can specify --shard parameter for restoring only on the single shard.

Before running the recover command, specify DataRestoreCommand and RestoreCommand in the backup_info file. DataRestoreCommand fetches the base backup and restores it to the stolon data directory. RestoreCommand fetches the WAL file and saves it to stolon pg_wal directory. These commands can use the following substitutions:

stolon keeper thread runs both commands on each node in the cluster. Therefore:

These examples show how to specify RestoreCommand and DataRestoreCommand:

Backing up a Shardman Cluster Using probackup Command

Requirements for backing up and restoring a Shardman cluster using the probackup command are listed in Section 2.6.3.1.

For example, following these requirements, on the backup host:

  groupadd postgres
  useradd -m -N -g postgres -r -d /var/lib/postgresql -s /bin/bash
 

Then add SSH keys to provide passwordless SSH connection between the backup host and Shardman cluster hosts. Then on the backup host:

  apt-get install pg-probackup shardman-utils
  mkdir -p directory
  chown postgres:postgres directory -R
  shardmanctl [common_options] probackup init --backup-path=directory --etcd-path=directory/etcd --remote-user=postgres --remote-port=22
  shardmanctl [common_options] probackup archive-command --backup-path=directory --remote-user=postgres --remote-port=22
  

If all the requirements are met, then run the backup subcommand for the cluster backup:

shardmanctl [common_options] probackup backup --backup-path=directory --etcd-path=directory --backup-mode=MODE
  

You must pass the directories through the --backup-path and --etcd-path options and backup mode through --backup-mode. Full and delta backups are available with FULL, DELTA, PTRACK and PAGE values. Also it is possible to specify backup compression options through --compress-level flags, as well as specify --remote-port and --remote-user flags. You can limit the number of running concurrent tasks when doing backup by passing the limit through the --maxtasks flag.

By default, copying data via SSH is used to create a backup. To copy data to a mounted partition instead, use the --storage-type option with the mount value. This value will be automatically used in the restore process.

You can also copy data to an S3-compatible object storage. To do this, use the --storage-type option with the S3 value. When this value is used, it is required to specify the directory for pg_probackup logs. You can do it either by specifying --log-directory for each command or set the environment variable SDM_LOG_DIRECTORY, for example:

export SDM_LOG_DIRECTORY=/backup/logs

If you are going to perform backup/restore only for an S3-compatible object storage, you can also set an environment variable instead of specifying --storage-type in each probackup command:

export SDM_STORAGE_TYPE=S3
                

Restoring a Shardman Cluster using probackup command

shardmanctl in probackup mode can perform either full restore, metadata-only or schema-only restore of a Shardman cluster from a backup created by the probackup backup command.

To perform full or partial restore, firstly you must select needed backup to restore from. To show list of available backups run the following command:

shardmanctl [common_options] probackup show --backup-path=path --format=format [--archive ] [-i|--backup-id backup-id] [--instance instance]

The output should be a list of backups with their IDs in a table or JSON format. Then pick the needed backup ID and run the probackup restore command.

shardmanctl [common_options] probackup restore --backup-path=path --backup-id=id

Pass the path to the repo through the --backup-path option and backup ID througt --backup-id flag.

If you encounter issues with an etcd instance, it makes sense to perform metadata-only restore. To do this, you can run the following command:

shardmanctl [common_options] probackup restore --backup-path=path --backup-id=id --metadata-only

If you need to restore only schema information, like: tables, roles and etc. you should specify --schema-only option.

For both kinds of restore, you can specify --timeout for the tool to exit with error after waiting until the cluster is ready or the recovery is complete for the specified number of seconds.

You can specify --shard parameter for restoring only on the single shard.

Also you can specify --recovery-target-time option for Point-in-Time Recovery. In this case Shardman finds the closest syncpoint to specified timestamp and suggests restoring on the found LSN. You can also specify --wal-limit to limit the number of WAL segments to be processed.

Reinitializing Replicas

If replicas are in an incorrect state, you can reset them using the shardmanctl command:

shardmanctl [common_options] shard --shard=shard_name replicas reinit

This command determines the nodes on which replicas of the specified shard are running and sends a request to shardmand on these nodes. After receiving this request, shardmand clears the postgres data directory and restarts the keeper thread that is responsible for managing the replica. After that, the replicas are restarted and begin to receive data from the corresponding primary.

Initializing the Cluster

To initialize a Shardman cluster that has the cluster0 name, uses an etcd cluster consisting of n1,n2 and n3 nodes listening on port 2379, ensure proper settings in the spec file sdmspec.json and run:

$ shardmanctl --store-endpoints http://n1:2379,http://n2:2379,http://n3:2379 init -f sdmspec.json

Getting the Cluster Connection String

To get the connection string for a Shardman cluster that has the cluster0 name, uses an etcd cluster consisting of n1,n2 and n3 nodes listening on port 2379, run:

 $ shardmanctl --store-endpoints http://n1:2379,http://n2:2379,http://n3:2379 getconnstr
 
 dbname=postgres host=n1,n4,n2,n1,n1,n2,n4,n3 password=yourpasswordhere port=5432,5433,5432,5433,5432,5433,5432,5433 user=postgres
 
 

To add replicas to getconnstr, use --all.

Getting the Cluster Status

Here is a sample status output from shardmanctl with OK and Error statuses:

        $ shardmanctl status --filter store,shardmand,rg --sort=node
        
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                            == STORE STATUS ==                                            │
├────────────┬─────────────────────────────────────────────┬───────────────────────┬───────────────────────┤
│   STATUS   │                   MESSAGE                   │   REPLICATION GROUP   │          NODE         │
├────────────┼─────────────────────────────────────────────┼───────────────────────┼───────────────────────┤
│     OK     │ etcd store is OK                            │                       │                       │
└────────────┴─────────────────────────────────────────────┴───────────────────────┴───────────────────────┘
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                          == SHARDMAND STATUS ==                                           │
├────────────┬─────────────────────────────────────────────┬───────────────────────┬───────────────────────┤
│   STATUS   │                   MESSAGE                   │   REPLICATION GROUP   │          NODE         │
├────────────┼─────────────────────────────────────────────┼───────────────────────┼───────────────────────┤
│     OK     │ shardmand on node 56d819b4e9e4 is OK        │                       │      56d819b4e9e4     │
├────────────┼─────────────────────────────────────────────┼───────────────────────┼───────────────────────┤
│     OK     │ shardmand on node 6d0aabd50acc is OK        │                       │      6d0aabd50acc     │
└────────────┴─────────────────────────────────────────────┴───────────────────────┴───────────────────────┘
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                       == REPLICATION GROUP STATUS ==                                      │
├────────────┬──────────────────────────────────────────────┬───────────────────────┬───────────────────────┤
│   STATUS   │                    MESSAGE                   │   REPLICATION GROUP   │          NODE         │
├────────────┼──────────────────────────────────────────────┼───────────────────────┼───────────────────────┤
│     OK     │ Replication group clover-1-56d819b4e9e4 is   │ clover-1-56d819b4e9e4 │                       │
│            │ OK                                           │                       │                       │
├────────────┼──────────────────────────────────────────────┼───────────────────────┼───────────────────────┤
│            │ Replication connection is down for slave     │                       │                       │
│    Error   │ 6d0aabd50acc:5442 in replication group       │ clover-1-6d0aabd50acc │   6d0aabd50acc:5442   │
│            │ clover-1-6d0aabd50acc                        │                       │                       │
└────────────┴──────────────────────────────────────────────┴───────────────────────┴───────────────────────┘
┌─────────────────────────────────────────────────────────────────────────────────────────┐

│                           == RESTART REQUIRED PARAMS STATUS ==                          │

├──────────┬──────────────────────────────────────┬───────────────────┬───────────────────┤

│  STATUS  │                MESSAGE               │ REPLICATION GROUP │        NODE       │

├──────────┼──────────────────────────────────────┼───────────────────┼───────────────────┤

│    OK    │     No pending restart parameters    │      shard-1      │       shrn1       │

├──────────┼──────────────────────────────────────┼───────────────────┼───────────────────┤

│    OK    │     No pending restart parameters    │      shard-2      │       shrn4       │

└──────────┴──────────────────────────────────────┴───────────────────┴───────────────────┘
          
        

Rewriting stolon Specification

First, get the list of available keys in the store using the following command:

      $ shardmanctl store keys
        
{
 "Key": "shardman/cluster0/data/cluster",
 "Alias": "cluster"
}{
 "Key": "shardman/cluster0/data/shardmand/56d819b4e9e4"
}{
...
 "Key": "shardman/cluster0/stolon/remoteLogs/6d0aabd50acc/clover-1-6d0aabd50acc/keeper_1/error"
}
        
      

Get stolon configuration from the store and save it in the stolonspec.json file with the command

      $ shardmanctl store get -a stolonspec -f stolonspec.json
    

Apply the necessary changes to the file and upload the new specification using shardmanctl config update. Mind that shardman.config_uuid parameter is deleted with shardmanctl store get -a stolonspec and not with shardmanctl store get -k full/path/to/clusterspec; using spec with existing shardman.config_uuid will result in a conflict.

To double-check, you can get the cluster key with new StolonSpec by the full key name (which was shown earlier with store keys command):

      $ shardmanctl store get -k shardman/cluster0/data/cluster
        
{
  "FormatVersion": 1,
  "Spec": {
    "PgSuAuthMethod": "md5",
    "PgSuPassword": "12345",
    "PgSuUsername": "postgres",
    "PgReplAuthMethod": "md5",
    "PgReplPassword": "12345",
    "PgReplUsername": "repluser",
    "ShardSpec": {
      ...
}
        
      

Adding Nodes to the Cluster

To add n1,n2, n3 and n4 nodes to the cluster, run:

$ shardmanctl --store-endpoints http://n1:2379,http://n2:2379,http://n3:2379 nodes add -n n1,n2,n3,n4

Removing Nodes from the Cluster

To remove n1 and n2 nodes from the cluster0 cluster, run:

      $ shardmanctl --store-endpoints http://n1:2379,http://n2:2379,http://n3:2379 nodes rm -n n1,n2
    

If cross placement policy is used, then the clovers that contain them will be deleted along with the nodes.

Executing a Query on All Replication Groups

To execute the select version() query on all replication groups, run:

 $ shardmanctl --store-endpoints http://n1:2379,http://n2:2379,http://n3:2379 forall --sql 'select version()'
 
 Node 1 says:
 [PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit]
 Node 4 says:
 [PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit]
 Node 3 says:
 [PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit]
 Node 2 says:
 [PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit]
 
 

Performing Rebalance

To rebalance sharded tables in the cluster0 cluster, run:

 $ shardmanctl --store-endpoints http://n1:2379,http://n2:2379,http://n3:2379 rebalance
 

Updating PostgreSQL Configuration Settings

To set the max_connections parameter to 200 in the cluster, create the spec file (for instance, ~/stolon.json) with the following contents:

 {
   "pgParameters": {
     "max_connections": "200"
   }
 }
 

Then run:

 $ shardmanctl --store-endpoints http://n1:2379,http://n2:2379,http://n3:2379 config update -p -f ~/stolon.json
 

Since changing max_connections requires a restart, DBMS instances are restarted by this command.

Performing Backup and Recovery

To create a backup of the cluster0 cluster using etcd at etcdserver listening on port 2379 and store it in the local directory /var/backup/shardman, run:

$ shardmanctl --store-endpoints http://etcdserver:2379 backup --datadir=/var/backup/shardman --use-ssh

Assume that you are performing a recovery from a backup to the cluster0 cluster using etcd at etcdserver listening on port 2379 and you take the backup description from the /var/backup/shardman/backup_info file. Edit the /var/backup/shardman/backup_info file, set DataRestoreCommand , RestoreCommand as necessary and run:

$ shardmanctl --store-endpoints http://etcdserver:2379 recover --info /var/backup/shardman/backup_info

For metadata-only restore, run:

$ shardmanctl --store-endpoints http://etcdserver:2379 recover --metadata-only --dumpfile /var/backup/shardman/etcd_dump

For schema-only restore, run:

$ shardmanctl --store-endpoints http://etcdserver:2379 recover --schema-only --dumpfile /var/backup/shardman/etcd_dump

For single shard restore, run:

$ shardmanctl --store-endpoints http://etcdserver:2379 recover  --info /var/backup/shardman/backup_info --shard shard_1

Performing Backup and Recovery with probackup Command

To create a backup of the cluster0 cluster using etcd at etcdserver listening on port 2379 and store it in the local directory /var/backup/shardman, first initialize the backups repository with the init subcommand:

$ shardmanctl --store-endpoints http://etcdserver:2379 probackup init --backup-path=/var/backup/shardman --etcd-path=/var/backup/etcd_dump

Then add and enable archive_command with the archive-command subcommand:

$ shardmanctl --store-endpoints http://etcdserver:2379 probackup archive-command add --backup-path=/var/backup/shardman

If the repository is successfully initialized and archive-command successfully added, create a FULL backup with the backup subcommand:

$ shardmanctl --store-endpoints http://etcdserver:2379 probackup backup --backup-path=/var/backup/shardman --etcd-path=/var/backup/etcd_dump --backup-mode=FULL --compress --compress-algorithm=zlib --compress-level=5

To create DELTA, PTRACK or PAGE backup, run the backup subcommand with DELTA, PTRACK or PAGE value of the --backup-mode option:

$ shardmanctl --store-endpoints http://etcdserver:2379 probackup backup --backup-path=/var/backup/shardman --etcd-path=/var/backup/etcd_dump --backup-mode=DELTA  --compress --compress-algorithm=zlib --compress-level=5

To show the created backup ID, run show subcommand:

$ shardmanctl --store-endpoints http://etcdserver:2379 probackup show --backup-path=/var/backup/shardman --format=table

┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                                                      == BACKUP ID 'S88FRO'                                                                                                     │
│                                                                                                               ==                                                                                                               │
├───────────────────────────────────┬───────────────────────────────────┬───────────────────────────────────┬────────────┬────────────┬────────────┬────────────┬────────────┬────────────┬────────────┬────────────┬────────────┤
│              INSTANCE             │                HOST               │           RECOVERY TIME           │    MODE    │  WAL MODE  │     TLI    │    DATA    │     WAL    │   Z-RATIO  │  START LSN │  STOP LSN  │   STATUS   │
├───────────────────────────────────┼───────────────────────────────────┼───────────────────────────────────┼────────────┼────────────┼────────────┼────────────┼────────────┼────────────┼────────────┼────────────┼────────────┤
│              shard-1              │               n1                  │       2024-02-02 14:19:05+00      │    FULL    │   ARCHIVE  │     1/0    │  42.37MiB  │    16MiB   │    1.00    │  0/C000028 │  0/D0018B0 │     OK     │
├───────────────────────────────────┼───────────────────────────────────┼───────────────────────────────────┼────────────┼────────────┼────────────┼────────────┼────────────┼────────────┼────────────┼────────────┼────────────┤
│              shard-2              │               n2                  │       2024-02-02 14:19:05+00      │    FULL    │   ARCHIVE  │     1/0    │  42.38MiB  │    16MiB   │    1.00    │  0/C000028 │  0/D001E00 │     OK     │
└───────────────────────────────────┴───────────────────────────────────┴───────────────────────────────────┴────────────┴────────────┴────────────┴────────────┴────────────┴────────────┴────────────┴────────────┴────────────┘

In PTRACK backup mode, Shardman tracks page changes on the fly. Continuous archiving is not necessary for it to operate. Each time a relation page is updated, this page is marked in a special PTRACK bitmap. Tracking implies some minor overhead on the database server operation, but speeds up incremental backups significantly.

If you are going to use PTRACK backups, complete the following additional steps:

To validate the created backup, run validate subcommand:

$ shardmanctl --store-endpoints http://etcdserver:2379 probackup validate --backup-path=/var/backup/shardman --backup-id=RFP1FI

Assume that you are performing a recovery from a backup to the cluster0 cluster using etcd at etcdserver listening on port 2379 and you take the backup ID from the show command:

$ shardmanctl --store-endpoints http://etcdserver:2379 probackup restore --backup-path=/var/backup/shardman --backup-id=RFP1FI
  

Finally we need to enable archive_command back.

$ shardmanctl --store-endpoints http://etcdserver:2379 probackup archive-command add --backup-path=/var/backup/shardman

For metadata-only restore, run:

$ shardmanctl --store-endpoints http://etcdserver:2379 probackup restore --metadata-only --backup-path=/var/backup/shardman --backup-id=RFP1FI

For metadata-only restore, run:

$ shardmanctl --store-endpoints http://etcdserver:2379 probackup restore --schema-only --backup-path=/var/backup/shardman --backup-id=RFP1FI

For single shard restore, run:

$ shardmanctl --store-endpoints http://etcdserver:2379 probackup restore --backup-path=/var/backup/shardman --backup-id=RFP1FI --shard shard_1

For Point-in-Time Recovery, run:

$ shardmanctl --store-endpoints http://etcdserver:2379 probackup restore --metadata-only --backup-path=/var/backup/shardman --backup-id=RFP1FI --recovery-target-time='2006-01-02 15:04:05' -s

Loading Data from a Text File

To load data into a Shardman cluster, run the following command:

$ shardmanctl --store-endpoints http://etcdserver:2379 load --file=/var/load/data.tsv --table=mytable --source file --format text -j 8
    

In this example, data is loaded from the /var/load/data.tsv data file (tab-delimited) into the table mytable in 8 parallel threads. You can use schema.table as the table name.

Loading data from PostgreSQL table

To load data into a Shardman cluster from a PostgreSQL table, run the following command:

$ shardmanctl --store-endpoints http://etcdserver:2379 load -t desttable --source postgres --source-connstr "dbname=db host=srchost port=srcport user=login password=passwd" --source-table sourcetable -j 8
    

In this example, data is loaded from the table sourcetable into the desttable table in 8 parallel threads. You can use schema.table as table names.

Loading Data with a Schema from PostgreSQL

To load data with a schema into Shardman cluster from PostgreSQL, run the following command:

$ shardmanctl --store-endpoints http://etcdserver:2379 load --schema load_schema.yaml
    

The file load_schema.yaml has the folowing format:

version: "1.0"
migrate:
  connstr: "dbname=workdb host=workhost port=workport user=workuser password=workpassword"
  jobs: 8
  batch: 1000
  options:
    - create_schema
    - create_table
    - create_index
    - create_sequence
    - create_foreign_key
    - create_role
    - copy_ownership
    - copy_grants
    - truncate_table
    - skip_no_pkey_tables
    - skip_create_index_error
    - skip_create_extension_error
    - skip_load_errors
    - skip_create_foreign_key_error
    - skip_create_role_error
    - skip_copy_grants_error
    - skip_copy_ownership_error
  schemas:
    - name: public
      all: false
      tables:
        - name: tab1
          type: sharded
          partitions: 6
          distributedby: id
          priority: 3
        - name: tab2
          type: global
        - name: tab3
          type: sharded
          partitions: 6
          distributedby: field_id
          colocatewith: tab1
        - name: table4
            type: global
            source: schema.view
            source_pk: field_id
        - name: table5
            type: global
            source: schema.func(arg)
            source_pk: field_id
    - name: schema2
      all: false
      default_type: sharded
      default_partitions: 6
      tables:
        - name: table1
          distributedby: field_id
          priority: 2
        - name: table2
          type: global
        - name: table3
          source: schema.view
          distributedby: field_id
          priority: 3
        - name: table4
          distributedby: field_id
          source: schema.func(arg)
        - name: table5
          source: schema."complex.""table.name"
          distributedby: field_id
    - name: schema3
      all: true
      skip_tables: [table1, table2, table3]
  roles:
    - name: test_user1
      password: test_password
    - name: test_user2
    

The migrate.jobs value defines the number of parallel data loader processes.

The migrate.batch value is the number of rows in one batch (recommended value is 1000).

The migrate.schemas section defines an array of source database schemas that you are working with. All other schemas will be skipped.

If the all value is set to true, then all tables from the current schema will be migrated (with global type by default). If a table is listed in the migrate.schemas.tables array, then the target table type must be explicitly specified for it. Two types of tables are currently supported: global and sharded. Global tables are loaded first, then sharded tables and at the end sharded tables with the colocatedwith parameter. The order of loading tables of the same type can be changed using priority option.

The migrate.schemas.skip_tables section defines an array of table names that will be skipped when the schema is loaded even if the all parameter is set to true.

For sharded tables, the following attributes must be set: distributedby (specifies the name of the column to use for the table partitioning) and partitions (number of partitions that will be created for this table). Optionally, for sharded tables colocatewith attribute can be set (name of the table to colocate with). Shardman will try to place partitions of the created table with the same partition key on the same nodes as the corresponding partitions of the table specified by colocatewith.

You can specify the table default_type option for a schema: global or sharded (default: global). For the sharded type you can also specify the default_partitions option (default: 20). If you set default_type to sharded, you need to specify the distributedby option for each table.

The source option for a table should include the schema and table source: schema.source. The source can be a table, view or function. For example: public.table, public.view, public.func(arg). If you set the source view or function for a global table, you should specify source_pk to set the primary key for this table. If source is not specified or contains the name of a table, you can also specify source_pk to create a primary key or override the existing one.

The priority option for table determines the order in which the tables of the same type are loaded. Tables with higher priority are loaded earlier. Default priority value is 0.

The migrate.roles section defines an array of role names and passwords that will be copied from the source database if create_role is specified.

The schema supports the following options:

Initialization and Running Benchmarks

To initialize a benchmark via shardmanctl using pgbench with the shardman schema, scale=1000, partitions=40, run:

$ shardmanctl bench init --schema-type=shardman --scale=1000 --partitions=40

To run an initialized benchmark for the same shardman schema, number of jobs=4, number of clients=10, duration in seconds=60 and full pgbench output, use:

$ shardmanctl bench run --schema-type=shardman --jobs=4 --client=10 --time=60 --full-output 

To initialize a benchmark with the custom schema from file schema.psql with scale=1000 run:

$ shardmanctl bench init --schema-type=custom --schema-file=schema.psql --scale=1000

To run an initialized benchmark with the custom schema and custom transaction script from script.psql with the number of jobs=4, number of clients=10, duration in seconds=60, use:

$ shardmanctl bench run --schema-type=custom --file=script.psql --jobs=4 --client=10 --time=60 

To clean up a PostgreSQL database of tpc-b tables, use:

$ shardmanctl bench cleanup

Benchmark Generation Scripts

To generate a benchmark sequence via shardmanctl from the config file=cfg.yaml and output the result to file=script.sh, run:

$ shardmanctl bench generate --config=cfg.yaml --output-file=script.sh

Configuration file example:

    benches:
    - schema_type: single
      init_flags: "-s 3"
      run_flags: "-n -P 10 -c 10 -j 4 -T 10"
    - schema_type: simple
      init_flags: "-s 4"
      run_flags: "-n -P 10 -c 20 -j 4 -T 10"
      partitions: 100
    - schema_type: shardman
      init_flags: "-s 5"
      run_flags: "-n -P 10 -c 20 -j 4 -T 10"
    - schema_type: custom
      init_flags: "-s 6"
      schema_file: "schema.psql"