src/bin/scripts/t/020_createdb.pl

   1
   2 # Copyright (c) 2021-2025, PostgreSQL Global Development Group
   3
   4 use strict;
   5 use warnings FATAL => 'all';
   6
   7 use PostgreSQL::Test::Cluster;
   8 use PostgreSQL::Test::Utils;
   9 use Test::More;
  10
  11 program_help_ok('createdb');
  12 program_version_ok('createdb');
  13 program_options_handling_ok('createdb');
  14
  15 my $node = PostgreSQL::Test::Cluster->new('main');
  16 $node->init;
  17 $node->start;
  18
  19 $node->issues_sql_like(
  20         [ 'createdb', 'foobar1' ],
  21         qr/statement: CREATE DATABASE foobar1/,
  22         'SQL CREATE DATABASE run');
  23 $node->issues_sql_like(
  24         [
  25                 'createdb',
  26                 '--locale' => 'C',
  27                 '--encoding' => 'LATIN1',
  28                 '--template' => 'template0',
  29                 'foobar2',
  30         ],
  31         qr/statement: CREATE DATABASE foobar2 ENCODING 'LATIN1'/,
  32         'create database with encoding');
  33
  34 if ($ENV{with_icu} eq 'yes')
  35 {
  36         # This fails because template0 uses libc provider and has no ICU
  37         # locale set.  It would succeed if template0 used the icu
  38         # provider.  XXX Maybe split into multiple tests?
  39         $node->command_fails(
  40                 [
  41                         'createdb',
  42                         '--template' => 'template0',
  43                         '--encoding' => 'UTF8',
  44                         '--locale-provider' => 'icu',
  45                         'foobar4',
  46                 ],
  47                 'create database with ICU fails without ICU locale specified');
  48
  49         $node->issues_sql_like(
  50                 [
  51                         'createdb',
  52                         '--template' => 'template0',
  53                         '--encoding' => 'UTF8',
  54                         '--locale-provider' => 'icu',
  55                         '--locale' => 'C',
  56                         '--icu-locale' => 'en',
  57                         'foobar5',
  58                 ],
  59                 qr/statement: CREATE DATABASE foobar5 .* LOCALE_PROVIDER icu ICU_LOCALE 'en'/,
  60                 'create database with ICU locale specified');
  61
  62         $node->command_fails(
  63                 [
  64                         'createdb',
  65                         '--template' => 'template0',
  66                         '--encoding' => 'UTF8',
  67                         '--locale-provider' => 'icu',
  68                         '--icu-locale' => '@colNumeric=lower',
  69                         'foobarX',
  70                 ],
  71                 'fails for invalid ICU locale');
  72
  73         $node->command_fails_like(
  74                 [
  75                         'createdb',
  76                         '--template' => 'template0',
  77                         '--locale-provider' => 'icu',
  78                         '--encoding' => 'SQL_ASCII',
  79                         'foobarX',
  80                 ],
  81                 qr/ERROR:  encoding "SQL_ASCII" is not supported with ICU provider/,
  82                 'fails for encoding not supported by ICU');
  83
  84         # additional node, which uses the icu provider
  85         my $node2 = PostgreSQL::Test::Cluster->new('icu');
  86         $node2->init(extra => [ '--locale-provider=icu', '--icu-locale=en' ]);
  87         $node2->start;
  88
  89         $node2->command_ok(
  90                 [
  91                         'createdb',
  92                         '--template' => 'template0',
  93                         '--locale-provider' => 'libc',
  94                         'foobar55',
  95                 ],
  96                 'create database with libc provider from template database with icu provider'
  97         );
  98
  99         $node2->command_ok(
 100                 [
 101                         'createdb',
 102                         '--template' => 'template0',
 103                         '--icu-locale' => 'en-US',
 104                         'foobar56',
 105                 ],
 106                 'create database with icu locale from template database with icu provider'
 107         );
 108
 109         $node2->command_ok(
 110                 [
 111                         'createdb',
 112                         '--template' => 'template0',
 113                         '--locale-provider' => 'icu',
 114                         '--locale' => 'en',
 115                         '--lc-collate' => 'C',
 116                         '--lc-ctype' => 'C',
 117                         'foobar57',
 118                 ],
 119                 'create database with locale as ICU locale');
 120 }
 121 else
 122 {
 123         $node->command_fails(
 124                 [
 125                         'createdb',
 126                         '--template' => 'template0',
 127                         '--locale-provider' => 'icu',
 128                         'foobar4',
 129                 ],
 130                 'create database with ICU fails since no ICU support');
 131 }
 132
 133 $node->command_fails(
 134         [
 135                 'createdb',
 136                 '--template' => 'template0',
 137                 '--locale-provider' => 'builtin',
 138                 'tbuiltin1',
 139         ],
 140         'create database with provider "builtin" fails without --locale');
 141
 142 $node->command_ok(
 143         [
 144                 'createdb',
 145                 '--template' => 'template0',
 146                 '--locale-provider' => 'builtin',
 147                 '--locale' => 'C',
 148                 'tbuiltin2',
 149         ],
 150         'create database with provider "builtin" and locale "C"');
 151
 152 $node->command_ok(
 153         [
 154                 'createdb',
 155                 '--template' => 'template0',
 156                 '--locale-provider' => 'builtin',
 157                 '--locale' => 'C',
 158                 '--lc-collate' => 'C',
 159                 'tbuiltin3',
 160         ],
 161         'create database with provider "builtin" and LC_COLLATE=C');
 162
 163 $node->command_ok(
 164         [
 165                 'createdb',
 166                 '--template' => 'template0',
 167                 '--locale-provider' => 'builtin',
 168                 '--locale' => 'C',
 169                 '--lc-ctype' => 'C',
 170                 'tbuiltin4',
 171         ],
 172         'create database with provider "builtin" and LC_CTYPE=C');
 173
 174 $node->command_ok(
 175         [
 176                 'createdb',
 177                 '--template' => 'template0',
 178                 '--locale-provider' => 'builtin',
 179                 '--lc-collate' => 'C',
 180                 '--lc-ctype' => 'C',
 181                 '--encoding' => 'UTF-8',
 182                 '--builtin-locale' => 'C.UTF8',
 183                 'tbuiltin5',
 184         ],
 185         'create database with --builtin-locale C.UTF-8 and -E UTF-8');
 186
 187 $node->command_fails(
 188         [
 189                 'createdb',
 190                 '--template' => 'template0',
 191                 '--locale-provider' => 'builtin',
 192                 '--lc-collate' => 'C',
 193                 '--lc-ctype' => 'C',
 194                 '--encoding' => 'LATIN1',
 195                 '--builtin-locale' => 'C.UTF-8',
 196                 'tbuiltin6',
 197         ],
 198         'create database with --builtin-locale C.UTF-8 and -E LATIN1');
 199
 200 $node->command_fails(
 201         [
 202                 'createdb',
 203                 '--template' => 'template0',
 204                 '--locale-provider' => 'builtin',
 205                 '--locale' => 'C',
 206                 '--icu-locale' => 'en',
 207                 'tbuiltin7',
 208         ],
 209         'create database with provider "builtin" and ICU_LOCALE="en"');
 210
 211 $node->command_fails(
 212         [
 213                 'createdb',
 214                 '--template' => 'template0',
 215                 '--locale-provider' => 'builtin',
 216                 '--locale' => 'C',
 217                 '--icu-rules' => '""',
 218                 'tbuiltin8',
 219         ],
 220         'create database with provider "builtin" and ICU_RULES=""');
 221
 222 $node->command_fails(
 223         [
 224                 'createdb',
 225                 '--template' => 'template1',
 226                 '--locale-provider' => 'builtin',
 227                 '--locale' => 'C',
 228                 'tbuiltin9',
 229         ],
 230         'create database with provider "builtin" not matching template');
 231
 232 $node->command_fails([ 'createdb', 'foobar1' ],
 233         'fails if database already exists');
 234
 235 $node->command_fails(
 236         [
 237                 'createdb',
 238                 '--template' => 'template0',
 239                 '--locale-provider' => 'xyz',
 240                 'foobarX',
 241         ],
 242         'fails for invalid locale provider');
 243
 244 # Check use of templates with shared dependencies copied from the template.
 245 my ($ret, $stdout, $stderr) = $node->psql(
 246         'foobar2',
 247         'CREATE ROLE role_foobar;
 248 CREATE TABLE tab_foobar (id int);
 249 ALTER TABLE tab_foobar owner to role_foobar;
 250 CREATE POLICY pol_foobar ON tab_foobar FOR ALL TO role_foobar;');
 251 $node->issues_sql_like(
 252         [ 'createdb', '--locale' => 'C', '--template' => 'foobar2', 'foobar3' ],
 253         qr/statement: CREATE DATABASE foobar3 TEMPLATE foobar2 LOCALE 'C'/,
 254         'create database with template');
 255 ($ret, $stdout, $stderr) = $node->psql(
 256         'foobar3',
 257         "SELECT pg_describe_object(classid, objid, objsubid) AS obj,
 258        pg_describe_object(refclassid, refobjid, 0) AS refobj
 259    FROM pg_shdepend s JOIN pg_database d ON (d.oid = s.dbid)
 260    WHERE d.datname = 'foobar3' ORDER BY obj;", on_error_die => 1);
 261 chomp($stdout);
 262 like(
 263         $stdout,
 264         qr/^policy pol_foobar on table tab_foobar\|role role_foobar
 265 table tab_foobar\|role role_foobar$/,
 266         'shared dependencies copied over to target database');
 267
 268 # Check quote handling with incorrect option values.
 269 $node->command_checks_all(
 270         [ 'createdb', '--encoding', "foo'; SELECT '1", 'foobar2' ],
 271         1,
 272         [qr/^$/],
 273         [qr/^createdb: error: "foo'; SELECT '1" is not a valid encoding name/s],
 274         'createdb with incorrect --encoding');
 275 $node->command_checks_all(
 276         [ 'createdb', '--lc-collate', "foo'; SELECT '1", 'foobar2' ],
 277         1,
 278         [qr/^$/],
 279         [
 280                 qr/^createdb: error: database creation failed: ERROR:  invalid LC_COLLATE locale name|^createdb: error: database creation failed: ERROR:  new collation \(foo'; SELECT '1\) is incompatible with the collation of the template database/s,
 281         ],
 282         'createdb with incorrect --lc-collate');
 283 $node->command_checks_all(
 284         [ 'createdb', '--lc-ctype', "foo'; SELECT '1", 'foobar2' ],
 285         1,
 286         [qr/^$/],
 287         [
 288                 qr/^createdb: error: database creation failed: ERROR:  invalid LC_CTYPE locale name|^createdb: error: database creation failed: ERROR:  new LC_CTYPE \(foo'; SELECT '1\) is incompatible with the LC_CTYPE of the template database/s,
 289         ],
 290         'createdb with incorrect --lc-ctype');
 291
 292 $node->command_checks_all(
 293         [ 'createdb', '--strategy', "foo", 'foobar2' ],
 294         1,
 295         [qr/^$/],
 296         [
 297                 qr/^createdb: error: database creation failed: ERROR:  invalid create database strategy "foo"/s,
 298         ],
 299         'createdb with incorrect --strategy');
 300
 301 # Check database creation strategy
 302 $node->issues_sql_like(
 303         [
 304                 'createdb',
 305                 '--template' => 'foobar2',
 306                 '--strategy' => 'wal_log',
 307                 'foobar6',
 308         ],
 309         qr/statement: CREATE DATABASE foobar6 STRATEGY wal_log TEMPLATE foobar2/,
 310         'create database with WAL_LOG strategy');
 311
 312 $node->issues_sql_like(
 313         [
 314                 'createdb',
 315                 '--template' => 'foobar2',
 316                 '--strategy' => 'WAL_LOG',
 317                 'foobar6s',
 318         ],
 319         qr/statement: CREATE DATABASE foobar6s STRATEGY "WAL_LOG" TEMPLATE foobar2/,
 320         'create database with WAL_LOG strategy');
 321
 322 $node->issues_sql_like(
 323         [
 324                 'createdb',
 325                 '--template' => 'foobar2',
 326                 '--strategy' => 'file_copy',
 327                 'foobar7',
 328         ],
 329         qr/statement: CREATE DATABASE foobar7 STRATEGY file_copy TEMPLATE foobar2/,
 330         'create database with FILE_COPY strategy');
 331
 332 $node->issues_sql_like(
 333         [
 334                 'createdb',
 335                 '--template' => 'foobar2',
 336                 '--strategy' => 'FILE_COPY',
 337                 'foobar7s',
 338         ],
 339         qr/statement: CREATE DATABASE foobar7s STRATEGY "FILE_COPY" TEMPLATE foobar2/,
 340         'create database with FILE_COPY strategy');
 341
 342 # Create database owned by role_foobar.
 343 $node->issues_sql_like(
 344         [
 345                 'createdb',
 346                 '--template' => 'foobar2',
 347                 '--owner' => 'role_foobar',
 348                 'foobar8',
 349         ],
 350         qr/statement: CREATE DATABASE foobar8 OWNER role_foobar TEMPLATE foobar2/,
 351         'create database with owner role_foobar');
 352 ($ret, $stdout, $stderr) =
 353   $node->psql('foobar2', 'DROP OWNED BY role_foobar;', on_error_die => 1,);
 354 ok($ret == 0, "DROP OWNED BY role_foobar");
 355 ($ret, $stdout, $stderr) =
 356   $node->psql('foobar2', 'DROP DATABASE foobar8;', on_error_die => 1,);
 357 ok($ret == 0, "DROP DATABASE foobar8");
 358
 359 done_testing();