Tco - Copying DBs and More

There is a set of new additions to tco I’d like to introduce. Most notably, copying a DB via tco is now available.

As always, feedback including feature requests and complaints are welcome. Expect tco db-copy to still have some rough edges, though.

Installation and update instructions can be found in the first blog post.

Copying a DB with tco db-copy

tco db-copy can be used to easily copy databases.

Main difference to previous implementations is:

  • Installation names are understood (in addition to host names).
  • Owner for target database is discovered automatically.
  • Databases are created automatically, or reused if empty.
  • If SOURCE and TARGET are identical, copying is done on the server directly.
  • There are unit and integration tests.

Synopsis

Slightly simplified, the syntax looks like this:

tco db-copy <SOURCE> [TARGET]

SORUCE: <INSTALLATION|HOST>[/DB_NAME]
TARGET: [OWNER@][INSTALLATION|HOST][/DB_NAME]

TARGET defaults to localhost.

When an INSTALLATION is given as TARGET, and the target DB doesn’t already exists, the owner is set automatically based on the installation. See also --owner-mode.

As always, tco db-copy --help shows all available options.

Examples

Copy DB of installation example to localhost:

tco db-copy example

TARGET defaults to localhost.

Copy DB of example to localhost giving it the custom DB name of nice_example_clean_copy:

tco db-copy example /nice_example_clean_copy

Replace DB of exampletest with a copy of example:

tco db-copy example exampletest

Copy DB of example onto the DB server of exampletest and call it nice_example_new:

tco db-copy example exampletest/nice_example_new

Owner is set based on the target installation, exampletest.

To connect to the resulting DB, use the same syntax with db-connect:

tco db-connect exampletest/nice_example_new

Many commands have short aliases (shown in the –help output). The previous example can also use tco cp (alias for tco db-copy) and tco db (alias for tco db-connect).

Copy:

tco cp example exampletest/nice_example_new

Connect:

tco db exampletest/nice_example_new

Copy DB of example on the same server:

$ tco example example/nice_example_copy

Copying is done on the server directly, without transferring any data off the server. This is slightly slower than CREATE DATABASE <TARGET> WITH TEMPLATE <SOURCE> but can be done while the installation is running.

Replace history DB of exampletest with a copy of the one from example:

tco db-copy -t history example exampletest

Then connect to target DB:

tco db-connect -t history exampletest

Copy DB of example to server db6.prod copying the owner:

tco db-copy --owner-mode copy example db6.prod/nice_example

Anywhere you can use an installation name, you can also specify a host name. When using a host name as target, it is sometimes necessary to specify a owner explicitly:

tco db-copy example nice_example@db6.prod/nice_example

What’s Next?

Once tco db-copy has seen some more testing, I’ll deprecate some of the older scripts and update https://docs.tocco.ch.

Some additional features currently under consideration:

  • --replace to replace / rename the target DB.
  • tco db-move for moving a DB (renaming the old DB) rather than copying.
  • Optimized transfer protocol for history DBs which allows using the DB before copying is completed.
  • Show DB size and duration (similar to the old copy_db script).

Show information about DB with tco db-info

tco db-copy stores some metainformation on the target DB as comment visible (far to the right) when using the \l+ command in psql:

\l+
...
 nice_master                    | nice_master            | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |                       | 742 MB  | pg_default | {"creator":"peter.gerber","creation_time":"2000-01-01T00:00:00+00:00","source":"db5.stage.tocco.cust.vshn.net/nice_master_clean"}
 nice_master_history            | nice_master            | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |                       | 179 MB  | pg_default |
...

This information plus the DB location and size are also shown by the new tco db-info:

$ tco db-info master
Database main:
        server: db5.stage.tocco.cust.vshn.net
        name:   nice_master
        size:   0.7 GiB
        DB source / copied from:
            from: db5.stage.tocco.cust.vshn.net/nice_master_clean
            at:   2000-01-01 01:00:00 +01:00
            by:   peter.gerber

Database history:
        server: db5.stage.tocco.cust.vshn.net
        name:   nice_master_history
        size:   0.2 GiB

Full syntax is tco db-info <INSTALLATION[/DB_NAME]|DB_SERVER/DB_NAME>. See --help for details.

Last login with tco sessions

tco sessions shows active sessions and it has been extended by a Latest Login section. This can be useful, among other things, to see if running an experiment or maintenance work will interfere with any active users.

Show sessions / logins for installation example

$ tco sessions example
Active sessions:

IP               SESSION REFRESH  NAME                            USER NAME
111.22.233.44     2 min  1 s ago  Jane Doe                        jdoe@example.com
55.66.177.188     0 min 11 s ago  John Smith                      jsmith@example.net

Found 18 active session(s).

Latest logins:

             LOGIN TIME           NAME                            USER NAME
Overall      0 d  0 h 14 min ago  Jane Doe                        jdoe@example.com

Tocco        4 d 21 h  2 min ago  Support Tocco AG                imueller@tocco.ch
non-Tocco    0 d  0 h 14 min ago  Jane Doe                        jdoe@example.com

Jane and John have active sessions. Last login from a @tocco.ch user was 4 days ago, and last login by an non-@tocco.ch user 14 minutes ago

There is also a --verbose option showing some more details:

$ tco sessions --verbose example

tco elasticsearch

Various command have been added to interact with Elasticsearch.

Important:

Most of commands bellow require that you are in our office network. There is an IP restriction in place dropping traffic from the public internet. See --help output of the corresponding command for a workaround.

Use tco elasticsearch --help to show all Elasticsearch-related commands. This commands also have a helpful --help.

Examples

List various details about available clusters and nodes. Also shows some example curl commands.

$ tco elasticsearch info

List indexes, and their health, size, number of replicas, etc.:

$ tco elasticsearch index-stats

Show various information related to cluster and node health:

$ tco elasticsearch health

Execute a GET requests to given endpoint

tco elasticsearch raw --node es1.prod.tocco.cust.vshn.net '/nice-tocco/_search?q="peter gerber"&size=5'