github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/ccl/backupccl/show_test.go (about) 1 // Copyright 2018 The Cockroach Authors. 2 // 3 // Licensed as a CockroachDB Enterprise file under the Cockroach Community 4 // License (the "License"); you may not use this file except in compliance with 5 // the License. You may obtain a copy of the License at 6 // 7 // https://github.com/cockroachdb/cockroach/blob/master/licenses/CCL.txt 8 9 package backupccl_test 10 11 import ( 12 "fmt" 13 "regexp" 14 "strconv" 15 "strings" 16 "testing" 17 18 "github.com/cockroachdb/cockroach/pkg/keys" 19 "github.com/cockroachdb/cockroach/pkg/roachpb" 20 "github.com/cockroachdb/cockroach/pkg/sql/sqlbase" 21 "github.com/cockroachdb/cockroach/pkg/util/leaktest" 22 "github.com/stretchr/testify/require" 23 ) 24 25 func TestShowBackup(t *testing.T) { 26 defer leaktest.AfterTest(t)() 27 28 const numAccounts = 11 29 _, tc, sqlDB, tempDir, cleanupFn := backupRestoreTestSetup(t, singleNode, numAccounts, initNone) 30 kvDB := tc.Server(0).DB() 31 _, _, sqlDBRestore, cleanupEmptyCluster := backupRestoreTestSetupEmpty(t, singleNode, tempDir, initNone) 32 defer cleanupFn() 33 defer cleanupEmptyCluster() 34 35 const full, inc, inc2 = localFoo + "/full", localFoo + "/inc", localFoo + "/inc2" 36 37 beforeTS := sqlDB.QueryStr(t, `SELECT now()::string`)[0][0] 38 sqlDB.Exec(t, fmt.Sprintf(`BACKUP DATABASE data TO $1 AS OF SYSTEM TIME '%s'`, beforeTS), full) 39 40 res := sqlDB.QueryStr(t, `SELECT table_name, start_time::string, end_time::string, rows, is_full_cluster FROM [SHOW BACKUP $1]`, full) 41 require.Equal(t, [][]string{{"bank", "NULL", beforeTS, strconv.Itoa(numAccounts), "false"}}, res) 42 43 // Mess with half the rows. 44 affectedRows, err := sqlDB.Exec(t, 45 `UPDATE data.bank SET id = -1 * id WHERE id > $1`, numAccounts/2, 46 ).RowsAffected() 47 require.NoError(t, err) 48 require.Equal(t, numAccounts/2, int(affectedRows)) 49 50 // Backup the changes by appending to the base and by making a separate 51 // inc backup. 52 incTS := sqlDB.QueryStr(t, `SELECT now()::string`)[0][0] 53 sqlDB.Exec(t, fmt.Sprintf(`BACKUP DATABASE data TO $1 AS OF SYSTEM TIME '%s'`, incTS), full) 54 sqlDB.Exec(t, fmt.Sprintf(`BACKUP DATABASE data TO $1 AS OF SYSTEM TIME '%s' INCREMENTAL FROM $2`, incTS), inc, full) 55 56 // Check the appended base backup. 57 res = sqlDB.QueryStr(t, `SELECT table_name, start_time::string, end_time::string, rows, is_full_cluster FROM [SHOW BACKUP $1]`, full) 58 require.Equal(t, [][]string{ 59 {"bank", "NULL", beforeTS, strconv.Itoa(numAccounts), "false"}, 60 {"bank", beforeTS, incTS, strconv.Itoa(int(affectedRows * 2)), "false"}, 61 }, res) 62 63 // Check the separate inc backup. 64 res = sqlDB.QueryStr(t, `SELECT start_time::string, end_time::string, rows FROM [SHOW BACKUP $1]`, inc) 65 require.Equal(t, [][]string{ 66 {beforeTS, incTS, strconv.Itoa(int(affectedRows * 2))}, 67 }, res) 68 69 // Create two new tables, alphabetically on either side of bank. 70 sqlDB.Exec(t, `CREATE TABLE data.auth (id INT PRIMARY KEY, name STRING)`) 71 sqlDB.Exec(t, `CREATE TABLE data.users (id INT PRIMARY KEY, name STRING)`) 72 sqlDB.Exec(t, `INSERT INTO data.users VALUES (1, 'one'), (2, 'two'), (3, 'three')`) 73 74 // Backup the changes again, by appending to the base and by making a 75 // separate inc backup. 76 inc2TS := sqlDB.QueryStr(t, `SELECT now()::string`)[0][0] 77 sqlDB.Exec(t, fmt.Sprintf(`BACKUP DATABASE data TO $1 AS OF SYSTEM TIME '%s'`, inc2TS), full) 78 sqlDB.Exec(t, fmt.Sprintf(`BACKUP DATABASE data TO $1 AS OF SYSTEM TIME '%s' INCREMENTAL FROM $2, $3`, inc2TS), inc2, full, inc) 79 80 // Check the appended base backup. 81 res = sqlDB.QueryStr(t, `SELECT table_name, start_time::string, end_time::string, rows FROM [SHOW BACKUP $1]`, full) 82 require.Equal(t, [][]string{ 83 {"bank", "NULL", beforeTS, strconv.Itoa(numAccounts)}, 84 {"bank", beforeTS, incTS, strconv.Itoa(int(affectedRows * 2))}, 85 {"bank", incTS, inc2TS, "0"}, 86 {"auth", incTS, inc2TS, "0"}, 87 {"users", incTS, inc2TS, "3"}, 88 }, res) 89 90 // Check the separate inc backup. 91 res = sqlDB.QueryStr(t, `SELECT table_name, start_time::string, end_time::string, rows FROM [SHOW BACKUP $1]`, inc2) 92 require.Equal(t, [][]string{ 93 {"bank", incTS, inc2TS, "0"}, 94 {"auth", incTS, inc2TS, "0"}, 95 {"users", incTS, inc2TS, "3"}, 96 }, res) 97 98 const details = localFoo + "/details" 99 sqlDB.Exec(t, `CREATE TABLE data.details1 (c INT PRIMARY KEY)`) 100 sqlDB.Exec(t, `INSERT INTO data.details1 (SELECT generate_series(1, 100))`) 101 sqlDB.Exec(t, `ALTER TABLE data.details1 SPLIT AT VALUES (1), (42)`) 102 sqlDB.Exec(t, `CREATE TABLE data.details2()`) 103 sqlDB.Exec(t, `BACKUP data.details1, data.details2 TO $1;`, details) 104 105 details1Desc := sqlbase.GetTableDescriptor(tc.Server(0).DB(), keys.SystemSQLCodec, "data", "details1") 106 details2Desc := sqlbase.GetTableDescriptor(tc.Server(0).DB(), keys.SystemSQLCodec, "data", "details2") 107 details1Key := roachpb.Key(sqlbase.MakeIndexKeyPrefix(keys.SystemSQLCodec, details1Desc, details1Desc.PrimaryIndex.ID)) 108 details2Key := roachpb.Key(sqlbase.MakeIndexKeyPrefix(keys.SystemSQLCodec, details2Desc, details2Desc.PrimaryIndex.ID)) 109 110 sqlDBRestore.CheckQueryResults(t, fmt.Sprintf(`SHOW BACKUP RANGES '%s'`, details), [][]string{ 111 {"/Table/56/1", "/Table/56/2", string(details1Key), string(details1Key.PrefixEnd())}, 112 {"/Table/57/1", "/Table/57/2", string(details2Key), string(details2Key.PrefixEnd())}, 113 }) 114 115 var showFiles = fmt.Sprintf(`SELECT start_pretty, end_pretty, size_bytes, rows 116 FROM [SHOW BACKUP FILES '%s']`, details) 117 sqlDBRestore.CheckQueryResults(t, showFiles, [][]string{ 118 {"/Table/56/1/1", "/Table/56/1/42", "369", "41"}, 119 {"/Table/56/1/42", "/Table/56/2", "531", "59"}, 120 }) 121 sstMatcher := regexp.MustCompile(`\d+\.sst`) 122 pathRows := sqlDB.QueryStr(t, `SELECT path FROM [SHOW BACKUP FILES $1]`, details) 123 for _, row := range pathRows { 124 path := row[0] 125 if matched := sstMatcher.MatchString(path); !matched { 126 t.Errorf("malformatted path in SHOW BACKUP FILES: %s", path) 127 } 128 } 129 if len(pathRows) != 2 { 130 t.Fatalf("expected 2 files, but got %d", len(pathRows)) 131 } 132 133 // SCHEMAS: Test the creation statement. 134 var showBackupRows [][]string 135 var expected []string 136 137 // Test that tables, views and sequences are all supported. 138 { 139 viewTableSeq := localFoo + "/tableviewseq" 140 sqlDB.Exec(t, `CREATE TABLE data.tableA (a int primary key, b int, INDEX tableA_b_idx (b ASC))`) 141 sqlDB.Exec(t, `CREATE VIEW data.viewA AS SELECT a from data.tableA`) 142 sqlDB.Exec(t, `CREATE SEQUENCE data.seqA START 1 INCREMENT 2 MAXVALUE 20`) 143 sqlDB.Exec(t, `BACKUP data.tableA, data.viewA, data.seqA TO $1;`, viewTableSeq) 144 145 // Create tables with the same ID as data.tableA to ensure that comments 146 // from different tables in the restoring cluster don't appear. 147 tableA := sqlbase.GetTableDescriptor(kvDB, keys.SystemSQLCodec, "data", "tablea") 148 for i := keys.MinUserDescID; i < int(tableA.ID); i++ { 149 tableName := fmt.Sprintf("foo%d", i) 150 sqlDBRestore.Exec(t, fmt.Sprintf("CREATE TABLE %s ();", tableName)) 151 sqlDBRestore.Exec(t, fmt.Sprintf("COMMENT ON TABLE %s IS 'table comment'", tableName)) 152 } 153 154 expectedCreateTable := `CREATE TABLE tablea ( 155 a INT8 NOT NULL, 156 b INT8 NULL, 157 CONSTRAINT "primary" PRIMARY KEY (a ASC), 158 INDEX tablea_b_idx (b ASC), 159 FAMILY "primary" (a, b) 160 )` 161 expectedCreateView := `CREATE VIEW viewa (a) AS SELECT a FROM data.public.tablea` 162 expectedCreateSeq := `CREATE SEQUENCE seqa MINVALUE 1 MAXVALUE 20 INCREMENT 2 START 1` 163 164 showBackupRows = sqlDBRestore.QueryStr(t, fmt.Sprintf(`SHOW BACKUP SCHEMAS '%s'`, viewTableSeq)) 165 expected = []string{ 166 expectedCreateTable, 167 expectedCreateView, 168 expectedCreateSeq, 169 } 170 for i, row := range showBackupRows { 171 createStmt := row[7] 172 if !eqWhitespace(createStmt, expected[i]) { 173 t.Fatalf("mismatched create statement: %s, want %s", createStmt, expected[i]) 174 } 175 } 176 } 177 178 // Test that foreign keys that reference tables that are in the backup 179 // are included. 180 { 181 includedFK := localFoo + "/includedFK" 182 sqlDB.Exec(t, `CREATE TABLE data.FKSrc (a INT PRIMARY KEY)`) 183 sqlDB.Exec(t, `CREATE TABLE data.FKRefTable (a INT PRIMARY KEY, B INT REFERENCES data.FKSrc(a))`) 184 sqlDB.Exec(t, `CREATE DATABASE data2`) 185 sqlDB.Exec(t, `CREATE TABLE data2.FKRefTable (a INT PRIMARY KEY, B INT REFERENCES data.FKSrc(a))`) 186 sqlDB.Exec(t, `BACKUP data.FKSrc, data.FKRefTable, data2.FKRefTable TO $1;`, includedFK) 187 188 wantSameDB := `CREATE TABLE fkreftable ( 189 a INT8 NOT NULL, 190 b INT8 NULL, 191 CONSTRAINT "primary" PRIMARY KEY (a ASC), 192 CONSTRAINT fk_b_ref_fksrc FOREIGN KEY (b) REFERENCES fksrc(a), 193 INDEX fkreftable_auto_index_fk_b_ref_fksrc (b ASC), 194 FAMILY "primary" (a, b) 195 )` 196 wantDiffDB := `CREATE TABLE fkreftable ( 197 a INT8 NOT NULL, 198 b INT8 NULL, 199 CONSTRAINT "primary" PRIMARY KEY (a ASC), 200 CONSTRAINT fk_b_ref_fksrc FOREIGN KEY (b) REFERENCES data.public.fksrc(a), 201 INDEX fkreftable_auto_index_fk_b_ref_fksrc (b ASC), 202 FAMILY "primary" (a, b) 203 )` 204 205 showBackupRows = sqlDBRestore.QueryStr(t, fmt.Sprintf(`SHOW BACKUP SCHEMAS '%s'`, includedFK)) 206 createStmtSameDB := showBackupRows[1][7] 207 if !eqWhitespace(createStmtSameDB, wantSameDB) { 208 t.Fatalf("mismatched create statement: %s, want %s", createStmtSameDB, wantSameDB) 209 } 210 211 createStmtDiffDB := showBackupRows[2][7] 212 if !eqWhitespace(createStmtDiffDB, wantDiffDB) { 213 t.Fatalf("mismatched create statement: %s, want %s", createStmtDiffDB, wantDiffDB) 214 } 215 } 216 217 // Foreign keys that were not included in the backup are not mentioned in 218 // the create statement. 219 { 220 missingFK := localFoo + "/missingFK" 221 sqlDB.Exec(t, `BACKUP data2.FKRefTable TO $1;`, missingFK) 222 223 want := `CREATE TABLE fkreftable ( 224 a INT8 NOT NULL, 225 b INT8 NULL, 226 CONSTRAINT "primary" PRIMARY KEY (a ASC), 227 INDEX fkreftable_auto_index_fk_b_ref_fksrc (b ASC), 228 FAMILY "primary" (a, b) 229 )` 230 231 showBackupRows = sqlDBRestore.QueryStr(t, fmt.Sprintf(`SHOW BACKUP SCHEMAS '%s'`, missingFK)) 232 createStmt := showBackupRows[0][7] 233 if !eqWhitespace(createStmt, want) { 234 t.Fatalf("mismatched create statement: %s, want %s", createStmt, want) 235 } 236 } 237 238 { 239 full_cluster := localFoo + "/full_cluster" 240 sqlDB.Exec(t, `BACKUP TO $1;`, full_cluster) 241 242 showBackupRows = sqlDBRestore.QueryStr(t, fmt.Sprintf(`SHOW BACKUP '%s'`, full_cluster)) 243 is_full_cluster := showBackupRows[0][6] 244 if !eqWhitespace(is_full_cluster, "true") { 245 t.Fatal("expected show backup to indicate that backup was full cluster") 246 } 247 248 full_cluster_inc := localFoo + "/full_cluster_inc" 249 sqlDB.Exec(t, `BACKUP TO $1 INCREMENTAL FROM $2;`, full_cluster_inc, full_cluster) 250 251 showBackupRows = sqlDBRestore.QueryStr(t, fmt.Sprintf(`SHOW BACKUP '%s'`, full_cluster)) 252 is_full_cluster = showBackupRows[0][6] 253 if !eqWhitespace(is_full_cluster, "true") { 254 t.Fatal("expected show backup to indicate that backup was full cluster") 255 } 256 } 257 258 // Show privileges of descriptors that are backed up. 259 { 260 showPrivs := localFoo + "/show_privs" 261 sqlDB.Exec(t, `CREATE TABLE data.top_secret (id INT PRIMARY KEY, name STRING)`) 262 sqlDB.Exec(t, `CREATE USER agent_bond`) 263 sqlDB.Exec(t, `CREATE USER agent_thomas`) 264 sqlDB.Exec(t, `CREATE USER m`) 265 sqlDB.Exec(t, `CREATE ROLE agents`) 266 sqlDB.Exec(t, `GRANT agents TO agent_bond`) 267 sqlDB.Exec(t, `GRANT agents TO agent_thomas`) 268 sqlDB.Exec(t, `GRANT ALL ON data.top_secret TO m`) 269 sqlDB.Exec(t, `GRANT INSERT on data.top_secret TO agents`) 270 sqlDB.Exec(t, `GRANT SELECT on data.top_secret TO agent_bond`) 271 sqlDB.Exec(t, `GRANT UPDATE on data.top_secret TO agent_bond`) 272 sqlDB.Exec(t, `BACKUP data.top_secret TO $1;`, showPrivs) 273 274 want := `GRANT ALL ON top_secret TO admin; GRANT SELECT, UPDATE ON top_secret TO agent_bond; GRANT INSERT ON top_secret TO agents; GRANT ALL ON top_secret TO m; GRANT ALL ON top_secret TO root; ` 275 276 showBackupRows := sqlDBRestore.QueryStr(t, fmt.Sprintf(`SHOW BACKUP '%s' WITH privileges`, showPrivs)) 277 privs := showBackupRows[0][7] 278 if !eqWhitespace(privs, want) { 279 t.Fatalf("mismatched privileges: %s, want %s", privs, want) 280 } 281 } 282 } 283 284 func eqWhitespace(a, b string) bool { 285 return strings.Replace(a, "\t", "", -1) == strings.Replace(b, "\t", "", -1) 286 }