github.com/tuhaihe/gpbackup@v1.0.3/end_to_end/filtered_test.go (about) 1 package end_to_end_test 2 3 import ( 4 "os" 5 6 "github.com/tuhaihe/gp-common-go-libs/iohelper" 7 "github.com/tuhaihe/gp-common-go-libs/testhelper" 8 "github.com/tuhaihe/gpbackup/utils" 9 . "github.com/onsi/ginkgo/v2" 10 . "github.com/onsi/gomega" 11 ) 12 13 var _ = Describe("End to End Filtered tests", func() { 14 BeforeEach(func() { 15 end_to_end_setup() 16 }) 17 AfterEach(func() { 18 end_to_end_teardown() 19 }) 20 21 Describe("Backup include filtering", func() { 22 It("runs gpbackup and gprestore with include-schema backup flag and compression level", func() { 23 timestamp := gpbackup(gpbackupPath, backupHelperPath, 24 "--include-schema", "public", 25 "--compression-level", "2") 26 gprestore(gprestorePath, restoreHelperPath, timestamp, 27 "--redirect-db", "restoredb") 28 29 assertRelationsCreated(restoreConn, 20) 30 assertDataRestored(restoreConn, publicSchemaTupleCounts) 31 assertArtifactsCleaned(restoreConn, timestamp) 32 }) 33 It("runs gpbackup and gprestore with include-table backup flag", func() { 34 skipIfOldBackupVersionBefore("1.4.0") 35 timestamp := gpbackup(gpbackupPath, backupHelperPath, 36 "--include-table", "public.foo", 37 "--include-table", "public.sales", 38 "--include-table", "public.myseq1", 39 "--include-table", "public.myview1") 40 gprestore(gprestorePath, restoreHelperPath, timestamp, 41 "--redirect-db", "restoredb") 42 43 assertRelationsCreated(restoreConn, 16) 44 assertDataRestored(restoreConn, map[string]int{"public.foo": 40000}) 45 }) 46 It("runs gpbackup and gprestore with include-table-file backup flag", func() { 47 skipIfOldBackupVersionBefore("1.4.0") 48 includeFile := iohelper.MustOpenFileForWriting("/tmp/include-tables.txt") 49 utils.MustPrintln(includeFile, "public.sales\npublic.foo\npublic.myseq1\npublic.myview1") 50 timestamp := gpbackup(gpbackupPath, backupHelperPath, 51 "--include-table-file", "/tmp/include-tables.txt") 52 gprestore(gprestorePath, restoreHelperPath, timestamp, 53 "--redirect-db", "restoredb") 54 55 assertRelationsCreated(restoreConn, 16) 56 assertDataRestored(restoreConn, map[string]int{"public.sales": 13, "public.foo": 40000}) 57 58 _ = os.Remove("/tmp/include-tables.txt") 59 }) 60 It("runs gpbackup and gprestore with include-schema-file backup flag", func() { 61 skipIfOldBackupVersionBefore("1.17.0") 62 includeFile := iohelper.MustOpenFileForWriting("/tmp/include-schema.txt") 63 utils.MustPrintln(includeFile, "public") 64 timestamp := gpbackup(gpbackupPath, backupHelperPath, 65 "--include-schema-file", "/tmp/include-schema.txt") 66 gprestore(gprestorePath, restoreHelperPath, timestamp, 67 "--redirect-db", "restoredb") 68 69 assertRelationsCreated(restoreConn, 20) 70 71 _ = os.Remove("/tmp/include-schema.txt") 72 }) 73 It("runs gpbackup with --include-table flag with partitions (non-special chars)", func() { 74 testhelper.AssertQueryRuns(backupConn, 75 `CREATE TABLE public.testparent (id int, rank int, year int, gender 76 char(1), count int ) 77 DISTRIBUTED BY (id) 78 PARTITION BY LIST (gender) 79 ( PARTITION girls VALUES ('F'), 80 PARTITION boys VALUES ('M'), 81 DEFAULT PARTITION other ); 82 `) 83 defer testhelper.AssertQueryRuns(backupConn, 84 `DROP TABLE public.testparent`) 85 86 testhelper.AssertQueryRuns(backupConn, 87 `insert into public.testparent values (1,1,1,'M',1)`) 88 testhelper.AssertQueryRuns(backupConn, 89 `insert into public.testparent values (0,0,0,'F',1)`) 90 91 timestamp := gpbackup(gpbackupPath, backupHelperPath, 92 "--backup-dir", backupDir, 93 "--include-table", `public.testparent_1_prt_girls`, 94 "--leaf-partition-data") 95 gprestore(gprestorePath, restoreHelperPath, timestamp, 96 "--redirect-db", "restoredb", 97 "--backup-dir", backupDir) 98 99 // When running against GPDB 7+, only the root partition and the included leaf partition 100 // will be created due to the new flexible GPDB 7+ partitioning logic. For versions 101 // before GPDB 7, there is only one big DDL for the entire partition table. 102 if true { 103 assertRelationsCreated(restoreConn, 2) 104 } else { 105 assertRelationsCreated(restoreConn, 4) 106 } 107 108 localSchemaTupleCounts := map[string]int{ 109 `public.testparent_1_prt_girls`: 1, 110 `public.testparent`: 1, 111 } 112 assertDataRestored(restoreConn, localSchemaTupleCounts) 113 assertArtifactsCleaned(restoreConn, timestamp) 114 }) 115 It("gpbackup with --include-table does not backup protocols and functions", func() { 116 testhelper.AssertQueryRuns(backupConn, 117 `CREATE TABLE t1(i int)`) 118 defer testhelper.AssertQueryRuns(backupConn, 119 `DROP TABLE public.t1`) 120 testhelper.AssertQueryRuns(backupConn, 121 `CREATE FUNCTION f1() RETURNS int AS 'SELECT 1' LANGUAGE SQL;`) 122 defer testhelper.AssertQueryRuns(backupConn, 123 `DROP FUNCTION public.f1()`) 124 testhelper.AssertQueryRuns(backupConn, 125 `CREATE PROTOCOL p1 (readfunc = f1);`) 126 defer testhelper.AssertQueryRuns(backupConn, 127 `DROP PROTOCOL p1`) 128 129 timestamp := gpbackup(gpbackupPath, backupHelperPath, 130 "--backup-dir", backupDir, "--include-table", "public.t1") 131 132 metadataFileContents := getMetdataFileContents(backupDir, timestamp, "metadata.sql") 133 Expect(string(metadataFileContents)).To(ContainSubstring("t1")) 134 Expect(string(metadataFileContents)).ToNot(ContainSubstring("public.f1()")) 135 Expect(string(metadataFileContents)).ToNot(ContainSubstring("read_from_s3")) 136 Expect(string(metadataFileContents)).ToNot(ContainSubstring("s3_protocol")) 137 }) 138 }) 139 Describe("Restore include filtering", func() { 140 It("runs gpbackup and gprestore with include-schema restore flag", func() { 141 timestamp := gpbackup(gpbackupPath, backupHelperPath, 142 "--backup-dir", backupDir) 143 gprestore(gprestorePath, restoreHelperPath, timestamp, 144 "--redirect-db", "restoredb", 145 "--backup-dir", backupDir, 146 "--include-schema", "schema2") 147 148 assertRelationsCreated(restoreConn, 17) 149 assertDataRestored(restoreConn, schema2TupleCounts) 150 }) 151 It("runs gpbackup and gprestore with include-schema-file restore flag", func() { 152 includeFile := iohelper.MustOpenFileForWriting("/tmp/include-schema.txt") 153 utils.MustPrintln(includeFile, "schema2") 154 utils.MustPrintln(includeFile, "public") 155 timestamp := gpbackup(gpbackupPath, backupHelperPath, 156 "--backup-dir", backupDir) 157 gprestore(gprestorePath, restoreHelperPath, timestamp, 158 "--redirect-db", "restoredb", 159 "--backup-dir", backupDir, 160 "--include-schema-file", "/tmp/include-schema.txt") 161 162 assertRelationsCreated(restoreConn, 37) 163 assertDataRestored(restoreConn, schema2TupleCounts) 164 }) 165 It("runs gpbackup and gprestore with include-table restore flag", func() { 166 timestamp := gpbackup(gpbackupPath, backupHelperPath) 167 gprestore(gprestorePath, restoreHelperPath, timestamp, 168 "--redirect-db", "restoredb", 169 "--include-table", "public.foo", 170 "--include-table", "public.sales", 171 "--include-table", "public.myseq1", 172 "--include-table", "public.myview1") 173 174 assertRelationsCreated(restoreConn, 16) 175 assertDataRestored(restoreConn, map[string]int{ 176 "public.sales": 13, "public.foo": 40000}) 177 }) 178 It("runs gpbackup and gprestore with include-table-file restore flag", func() { 179 includeFile := iohelper.MustOpenFileForWriting("/tmp/include-tables.txt") 180 utils.MustPrintln(includeFile, 181 "public.sales\npublic.foo\npublic.myseq1\npublic.myview1") 182 timestamp := gpbackup(gpbackupPath, backupHelperPath, 183 "--backup-dir", backupDir) 184 gprestore(gprestorePath, restoreHelperPath, timestamp, 185 "--redirect-db", "restoredb", 186 "--backup-dir", backupDir, 187 "--include-table-file", "/tmp/include-tables.txt") 188 189 assertRelationsCreated(restoreConn, 16) 190 assertDataRestored(restoreConn, map[string]int{ 191 "public.sales": 13, "public.foo": 40000}) 192 193 _ = os.Remove("/tmp/include-tables.txt") 194 }) 195 It("runs gpbackup and gprestore with include-table restore flag against a leaf partition", func() { 196 skipIfOldBackupVersionBefore("1.7.2") 197 timestamp := gpbackup(gpbackupPath, backupHelperPath, 198 "--leaf-partition-data") 199 gprestore(gprestorePath, restoreHelperPath, timestamp, 200 "--redirect-db", "restoredb", 201 "--include-table", "public.sales_1_prt_jan17") 202 203 assertRelationsCreated(restoreConn, 13) 204 assertDataRestored(restoreConn, map[string]int{ 205 "public.sales": 1, "public.sales_1_prt_jan17": 1}) 206 }) 207 It("runs gpbackup and gprestore with include-table restore flag which implicitly filters schema restore list", func() { 208 timestamp := gpbackup(gpbackupPath, backupHelperPath, 209 "--backup-dir", backupDir) 210 gprestore(gprestorePath, restoreHelperPath, timestamp, 211 "--redirect-db", "restoredb", 212 "--backup-dir", backupDir, 213 "--include-table", "schema2.foo3") 214 assertRelationsCreated(restoreConn, 1) 215 assertDataRestored(restoreConn, map[string]int{"schema2.foo3": 100}) 216 }) 217 It("runs gprestore with --include-table flag to only restore tables specified ", func() { 218 testhelper.AssertQueryRuns(backupConn, 219 "CREATE TABLE public.table_to_include_with_stats(i int)") 220 defer testhelper.AssertQueryRuns(backupConn, 221 "DROP TABLE public.table_to_include_with_stats") 222 testhelper.AssertQueryRuns(backupConn, 223 "INSERT INTO public.table_to_include_with_stats VALUES (1)") 224 timestamp := gpbackup(gpbackupPath, backupHelperPath, 225 "--backup-dir", backupDir) 226 gprestore(gprestorePath, restoreHelperPath, timestamp, 227 "--redirect-db", "restoredb", 228 "--backup-dir", backupDir, 229 "--include-table", "public.table_to_include_with_stats") 230 231 assertRelationsCreated(restoreConn, 1) 232 233 localSchemaTupleCounts := map[string]int{ 234 `public."table_to_include_with_stats"`: 1, 235 } 236 assertDataRestored(restoreConn, localSchemaTupleCounts) 237 assertArtifactsCleaned(restoreConn, timestamp) 238 }) 239 240 }) 241 Describe("Backup exclude filtering", func() { 242 It("runs gpbackup and gprestore with exclude-schema backup flag", func() { 243 timestamp := gpbackup(gpbackupPath, backupHelperPath, 244 "--exclude-schema", "public") 245 gprestore(gprestorePath, restoreHelperPath, timestamp, 246 "--redirect-db", "restoredb") 247 248 assertRelationsCreated(restoreConn, 17) 249 assertDataRestored(restoreConn, schema2TupleCounts) 250 }) 251 It("runs gpbackup and gprestore with exclude-schema-file backup flag", func() { 252 skipIfOldBackupVersionBefore("1.17.0") 253 excludeFile := iohelper.MustOpenFileForWriting("/tmp/exclude-schema.txt") 254 utils.MustPrintln(excludeFile, "public") 255 timestamp := gpbackup(gpbackupPath, backupHelperPath, 256 "--exclude-schema-file", "/tmp/exclude-schema.txt") 257 gprestore(gprestorePath, restoreHelperPath, timestamp, 258 "--redirect-db", "restoredb") 259 260 assertRelationsCreated(restoreConn, 17) 261 assertDataRestored(restoreConn, schema2TupleCounts) 262 263 _ = os.Remove("/tmp/exclude-schema.txt") 264 }) 265 It("runs gpbackup and gprestore with exclude-table backup flag", func() { 266 skipIfOldBackupVersionBefore("1.4.0") 267 timestamp := gpbackup(gpbackupPath, backupHelperPath, 268 "--exclude-table", "schema2.foo2", 269 "--exclude-table", "schema2.returns", 270 "--exclude-table", "public.myseq2", 271 "--exclude-table", "public.myview2") 272 gprestore(gprestorePath, restoreHelperPath, timestamp, 273 "--redirect-db", "restoredb") 274 275 assertRelationsCreated(restoreConn, TOTAL_RELATIONS_AFTER_EXCLUDE) 276 assertDataRestored(restoreConn, map[string]int{ 277 "schema2.foo3": 100, 278 "public.foo": 40000, 279 "public.holds": 50000, 280 "public.sales": 13}) 281 }) 282 It("runs gpbackup and gprestore with exclude-table-file backup flag", func() { 283 skipIfOldBackupVersionBefore("1.4.0") 284 excludeFile := iohelper.MustOpenFileForWriting("/tmp/exclude-tables.txt") 285 utils.MustPrintln(excludeFile, 286 "schema2.foo2\nschema2.returns\npublic.sales\npublic.myseq2\npublic.myview2") 287 timestamp := gpbackup(gpbackupPath, backupHelperPath, 288 "--exclude-table-file", "/tmp/exclude-tables.txt") 289 gprestore(gprestorePath, restoreHelperPath, timestamp, 290 "--redirect-db", "restoredb") 291 292 assertRelationsCreated(restoreConn, 8) 293 assertDataRestored(restoreConn, map[string]int{ 294 "schema2.foo3": 100, 295 "public.foo": 40000, 296 "public.holds": 50000}) 297 298 _ = os.Remove("/tmp/exclude-tables.txt") 299 }) 300 It("gpbackup with --exclude-table and then runs gprestore when functions and tables depending on each other", func() { 301 skipIfOldBackupVersionBefore("1.19.0") 302 303 testhelper.AssertQueryRuns(backupConn, "CREATE TABLE to_use_for_function (n int);") 304 defer testhelper.AssertQueryRuns(backupConn, "DROP TABLE to_use_for_function;") 305 306 testhelper.AssertQueryRuns(backupConn, "INSERT INTO to_use_for_function values (1);") 307 testhelper.AssertQueryRuns(backupConn, "CREATE FUNCTION func1(val integer) RETURNS integer AS $$ BEGIN RETURN val + (SELECT n FROM to_use_for_function); END; $$ LANGUAGE PLPGSQL;;") 308 defer testhelper.AssertQueryRuns(backupConn, "DROP FUNCTION func1(val integer);") 309 310 testhelper.AssertQueryRuns(backupConn, "CREATE TABLE test_depends_on_function (id integer, claim_id character varying(20) DEFAULT ('WC-'::text || func1(10)::text)) DISTRIBUTED BY (id);") 311 defer testhelper.AssertQueryRuns(backupConn, "DROP TABLE test_depends_on_function;") 312 testhelper.AssertQueryRuns(backupConn, "INSERT INTO test_depends_on_function values (1);") 313 314 timestamp := gpbackup(gpbackupPath, backupHelperPath, 315 "--exclude-table", "public.holds") 316 gprestore(gprestorePath, restoreHelperPath, timestamp, 317 "--redirect-db", "restoredb") 318 319 assertRelationsCreated(restoreConn, TOTAL_RELATIONS-1+2) // -1 for exclude +2 for 2 new tables 320 assertDataRestored(restoreConn, map[string]int{ 321 "public.foo": 40000, 322 "public.sales": 13, 323 "public.to_use_for_function": 1, 324 "public.test_depends_on_function": 1}) 325 assertArtifactsCleaned(restoreConn, timestamp) 326 }) 327 }) 328 Describe("Restore exclude filtering", func() { 329 It("runs gpbackup and gprestore with exclude-schema restore flag", func() { 330 timestamp := gpbackup(gpbackupPath, backupHelperPath) 331 gprestore(gprestorePath, restoreHelperPath, timestamp, 332 "--redirect-db", "restoredb", 333 "--exclude-schema", "public") 334 335 assertRelationsCreated(restoreConn, 17) 336 assertDataRestored(restoreConn, schema2TupleCounts) 337 }) 338 It("runs gpbackup and gprestore with exclude-schema-file restore flag", func() { 339 includeFile := iohelper.MustOpenFileForWriting("/tmp/exclude-schema.txt") 340 utils.MustPrintln(includeFile, "public") 341 timestamp := gpbackup(gpbackupPath, backupHelperPath, 342 "--backup-dir", backupDir) 343 gprestore(gprestorePath, restoreHelperPath, timestamp, 344 "--backup-dir", backupDir, 345 "--redirect-db", "restoredb", 346 "--exclude-schema-file", "/tmp/exclude-schema.txt") 347 348 assertRelationsCreated(restoreConn, 17) 349 assertDataRestored(restoreConn, schema2TupleCounts) 350 351 _ = os.Remove("/tmp/exclude-schema.txt") 352 }) 353 It("runs gpbackup and gprestore with exclude-table restore flag", func() { 354 // Create keyword table to make sure we properly escape it during the exclusion check. 355 testhelper.AssertQueryRuns(backupConn, `CREATE TABLE public."user"(i int)`) 356 defer testhelper.AssertQueryRuns(backupConn, `DROP TABLE public."user"`) 357 358 timestamp := gpbackup(gpbackupPath, backupHelperPath) 359 gprestore(gprestorePath, restoreHelperPath, timestamp, 360 "--redirect-db", "restoredb", 361 "--exclude-table", "schema2.foo2", 362 "--exclude-table", "schema2.returns", 363 "--exclude-table", "public.myseq2", 364 "--exclude-table", "public.myview2", 365 "--exclude-table", "public.user") 366 367 assertRelationsCreated(restoreConn, TOTAL_RELATIONS_AFTER_EXCLUDE) 368 assertDataRestored(restoreConn, map[string]int{ 369 "schema2.foo3": 100, "public.foo": 40000, "public.holds": 50000, "public.sales": 13}) 370 }) 371 It("runs gpbackup and gprestore with exclude-table-file restore flag", func() { 372 // Create keyword table to make sure we properly escape it during the exclusion check. 373 testhelper.AssertQueryRuns(backupConn, `CREATE TABLE public."user"(i int)`) 374 defer testhelper.AssertQueryRuns(backupConn, `DROP TABLE public."user"`) 375 376 includeFile := iohelper.MustOpenFileForWriting("/tmp/exclude-tables.txt") 377 utils.MustPrintln(includeFile, 378 "schema2.foo2\nschema2.returns\npublic.myseq2\npublic.myview2\npublic.user") 379 timestamp := gpbackup(gpbackupPath, backupHelperPath, 380 "--backup-dir", backupDir) 381 gprestore(gprestorePath, restoreHelperPath, timestamp, 382 "--redirect-db", "restoredb", 383 "--backup-dir", backupDir, 384 "--exclude-table-file", "/tmp/exclude-tables.txt") 385 386 assertRelationsCreated(restoreConn, TOTAL_RELATIONS_AFTER_EXCLUDE) 387 assertDataRestored(restoreConn, map[string]int{ 388 "public.sales": 13, 389 "public.foo": 40000}) 390 391 _ = os.Remove("/tmp/exclude-tables.txt") 392 }) 393 }) 394 })