`tco` - Copy Only What’s Needed

tco has recently been updated to allow skipping tables rarely used during development. Another change will be rolled out shortly which will make skipping various tables the default when copying to localhost. This will help safe space and time.

In an initial release, the following tables will be skipped:

nice_email_archive
nice_email_archive_to_address
nice_email_archive_to_email_attachment
nice_email_archive_to_recipient
nice_entity_history
nice_log_entry
nice_log_entry_var
nice_notification
nice_progress_var
nice_system_activity
nice_task_execution
nice_task_progress

tco will clearly indicate when tables are skipped:

$ tco cp auto master
localhost target detected: creating faster, partial, dev-only DB copy.
Creating ⚠️  partial copy ⚠️ . Will skip content of 12 tables.

Use `--show-excluded-tables` to show skipped tables. Use `--protocol
generic` to create full copy.

tco will also indicate when a partial copy is the source of a tco db-copy command:

$ tco cp /nice_master master

source:
    location: localhost/nice_master
    DB size:  0.4 GiB
    tainted: true ⚠️ (source is partial copy)
target:
    location: db5.stage.tocco.cust.vshn.net/nice_master

The same information (and more) is also shown by tco db-info:

$ tco db-info odaszh
Database main:
    server:        db1.prod.tocco.cust.vshn.net
    name:          nice_odaszh
    last modified: 2024-12-26 11:21:16 (  0 d  0 h  0 min)
    size:          7.7 GiB
    DB source / copied from:
        from:    localhost/nice_odaszh
        at:      2024-09-19 10:51:33 +02:00
        by:      jane.doe
        tainted: true ⚠️ (partial DB copy)

Database history:
    server:        db1.prod.tocco.cust.vshn.net
    name:          nice_odaszh_history
    last modified: <timeout>
    size:          7.0 GiB

See also --protocol and --exclude-table in tco cp --help output.

Skipping some of these tables (by default) for test systems is also being considered.

Thank you Stefan for the idea.

Renaming test / testold / testnew

tco has gained a new db-rename subcommand to easy renaming between the different test systems:

$ tco db-rename bbgtest bbgtestold
$ tco db-rename bbgtestnew bbgtest

This may seem rather simple but here is what how this was handled until now:

ssh ${db_server} psql postgres -c "\"ALTER DATABASE nice_${customer}test RENAME TO nice_${customer}testold\""
ssh ${db_server} psql nice_${customer}testold -c "\"REASSIGN OWNED BY nice_${customer}test TO nice_${customer}testold\""

ssh ${db_server} psql postgres -c "\"ALTER DATABASE nice_${customer}test_history RENAME TO nice_${customer}testold_history\""
ssh ${db_server} psql nice_${customer}testold_history -c "\"REASSIGN OWNED BY nice_${customer}test TO nice_${customer}testold\""

ssh ${db_server} psql postgres -c "\"ALTER DATABASE nice_${customer}testnew RENAME TO nice_${customer}test\""
ssh ${db_server} psql nice_${customer}test -c "\"REASSIGN OWNED BY nice_${customer}testnew TO nice_${customer}test\""

ssh ${db_server} psql postgres -c "\"ALTER DATABASE nice_${customer}testnew_history RENAME TO nice_${customer}test_history\""
ssh ${db_server} psql nice_${customer}test_history -c "\"REASSIGN OWNED BY nice_${customer}testnew TO nice_${customer}test\""

Warning

REASSIGN OWNED will change the owner of all objects in the DB connected to matching BY ${ROLE_NAME} and the owner of all DBs matching. Be careful when there are other DBs that have ${ROLE_NAME} as owner!

tco db-rename will do all of the above, including avoiding the pitfall mentioned in the warning, and can handle cases such as when the target DB already exists (and is empty).

Documentation will be updated to use the new command shortly, once testing has been completed.

A big thank you to Lino for pointing out that this was cumbersome and error prone.

Running SQL

This is not exactly something new but rather something others may find useful as well. tco can be used to run SQL queries stored in local files. I, for insance, have a collection of SQL scripts in ~/sql/ and then just use tco db to run them against whatever DB I need.

Create an SQL file locally:

cat > ~/sql/tables_by_size <<EOF
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
  pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
  pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY pg_catalog.pg_table_size(c.oid) DESC, 1, 2;
EOF

Run it against a DB:

$ tco db master ~/sql/tables_by_size
 Schema |                              Name                               |   Type   |    Owner    |    Size    |                                                   Description
 -------+-----------------------------------------------------------------+----------+-------------+------------+------------------------------------------------------------------------------------------------------------------
 public | nice_log_entry                                                  | table    | nice_master | 1068 MB    |
 public | nice_log_entry_var                                              | table    | nice_master | 360 MB     |
 public | nice_system_activity                                            | table    | nice_master | 67 MB      |
 public | nice_calendar_event                                             | table    | nice_master | 26 MB      |
 public | nice_incoming_payment                                           | table    | nice_master | 16 MB      |
 public | nice_reservation_registration                                   | table    | nice_master | 12 MB      |
 public | nice_task_execution                                             | table    | nice_master | 11 MB      |
...

SQL is executed by psql:

Any number of SQL queries can be passed at the same time.
Use of psql meta-commands like \x auto or \timing on in the SQL script is allowed.

See psql(5) Meta-Commands
Passing arguments to psql is supported, for instance, pass -x (extended output) to psql like this: tco db <installation> <file> -- -x.

See psql(5) OPTIONS

Example:

$ cat >~/sql/binaries <<EOF
\echo All binaries:
select current_database() as db, count(*), pg_size_pretty(coalesce(sum(size), 0)) as size from _nice_binary;

\echo Unused binaries:
select current_database() as db, count(*), pg_size_pretty(coalesce(sum(size), 0)) as size from _nice_binary where reference_count = 0;
EOF

$ tco db iffp ~/sql/binaries
All binaries:
    db     | count  | size
-----------+--------+-------
 nice_iffp | 225045 | 48 GB
(1 row)

Unused binaries:
    db     | count |  size
-----------+-------+---------
 nice_iffp | 23439 | 3945 MB
(1 row)

Updating `tco`

Debian / Ubuntu users will receive this updates shortly. (If not, ensure to setup the Tocco Debian Repository).

Other Linux distros and and OSes follow the instructions in the tco README file and update tco frequently.

tco - Copy Only What’s Needed

Renaming test / testold / testnew

Running SQL

Updating tco

`tco` - Copy Only What’s Needed

Updating `tco`