/usr/share/postgresql-common/t/040_upgrade.t is in postgresql-common 190.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 | # Test upgrading from the oldest version to the latest, using the default
# configuration file.
use strict;
use File::Temp qw/tempfile tempdir/;
use POSIX qw/dup2/;
use Time::HiRes qw/usleep/;
use lib 't';
use TestLib;
use PgCommon;
use Test::More tests => (@MAJORS == 1) ? 1 : 107 * 3;
if (@MAJORS == 1) {
pass 'only one major version installed, skipping upgrade tests';
exit 0;
}
foreach my $upgrade_options ('-m dump', '-m upgrade', '-m upgrade --link') {
next if ($ENV{UPGRADE_METHOD} and $upgrade_options !~ /$ENV{UPGRADE_METHOD}$/); # hack to ease debugging individual methods
note ("upgrade method \"$upgrade_options\", $MAJORS[0] -> $MAJORS[-1]");
# create cluster
ok ((system "pg_createcluster $MAJORS[0] upgr >/dev/null") == 0,
"pg_createcluster $MAJORS[0] upgr");
exec_as 'root', "sed -i '/^local.*postgres/ s/\$/\\nlocal all foo trust/' /etc/postgresql/$MAJORS[0]/upgr/pg_hba.conf";
is ((system "pg_ctlcluster $MAJORS[0] upgr start"), 0, 'Starting upgr cluster');
# Create nobody user, test database, and put a table into it
is ((exec_as 'postgres', 'createuser nobody -D -R -s && createdb -O nobody test && createdb -O nobody testnc && createdb -O nobody testro'),
0, 'Create nobody user and test databases');
is ((exec_as 'nobody', 'psql test -c "CREATE TABLE phone (name varchar(255) PRIMARY KEY, tel int NOT NULL)"'),
0, 'create table');
is ((exec_as 'nobody', 'psql test -c "INSERT INTO phone VALUES (\'Alice\', 2)"'), 0, 'insert Alice into phone table');
SKIP: {
skip 'datallowconn = f not supported with pg_upgrade', 1 if $upgrade_options =~ /upgrade/;
is ((exec_as 'postgres', 'psql template1 -c "UPDATE pg_database SET datallowconn = \'f\' WHERE datname = \'testnc\'"'),
0, 'disallow connection to testnc');
}
is ((exec_as 'nobody', 'psql testro -c "CREATE TABLE nums (num int NOT NULL); INSERT INTO nums VALUES (1)"'), 0, 'create table in testro');
SKIP: {
skip 'read-only not supported with pg_upgrade', 2 if $upgrade_options =~ /upgrade/;
is ((exec_as 'postgres', 'psql template1 -c "ALTER DATABASE testro SET default_transaction_read_only TO on"'),
0, 'set testro transaction default to readonly');
is ((exec_as 'nobody', 'psql testro -c "CREATE TABLE test(num int)"'),
1, 'creating table in testro fails');
}
# create a schema and a table with a name that was un-reserved between 8.4 and 9.1
is ((exec_as 'nobody', 'psql test -c "CREATE SCHEMA \"old\""'),
0, 'create schema "old"');
is ((exec_as 'nobody', 'psql test -c "CREATE TABLE \"old\".\"old\" (\"old\" text)"'),
0, 'create table "old.old"');
# create a sequence
is ((exec_as 'nobody', 'psql test -c "CREATE SEQUENCE odd10 INCREMENT BY 2 MINVALUE 1 MAXVALUE 10 CYCLE"'),
0, 'create sequence');
is_program_out 'nobody', 'psql -Atc "SELECT nextval(\'odd10\')" test', 0, "1\n",
'check next sequence value';
is_program_out 'nobody', 'psql -Atc "SELECT nextval(\'odd10\')" test', 0, "3\n",
'check next sequence value';
# create a large object
my ($fh, $filename) = tempfile("lo_import.XXXXXX", TMPDIR => 1, UNLINK => 1);
print $fh "Hello world";
close $fh;
chmod 0644, $filename;
is_program_out 'postgres', "psql -Atc \"SELECT lo_import('$filename', 1234)\"", 0, "1234\n",
'create large object';
# create stored procedures
if ($MAJORS[0] < 9.0) {
is_program_out 'postgres', 'createlang plpgsql test', 0, '', 'createlang plpgsql test';
} else {
pass '>= 9.0 enables PL/pgsql by default';
pass '...';
}
is_program_out 'nobody', 'psql test -c "CREATE FUNCTION inc2(integer) RETURNS integer LANGUAGE plpgsql AS \'BEGIN RETURN \$1 + 2; END;\';"',
0, "CREATE FUNCTION\n", 'CREATE FUNCTION inc2';
SKIP: {
skip 'hardcoded library paths not supported by pg_upgrade', 2 if $upgrade_options =~ /upgrade/;
is_program_out 'postgres', "psql -c \"UPDATE pg_proc SET probin = '$PgCommon::binroot$MAJORS[0]/lib/plpgsql.so' where proname = 'plpgsql_call_handler';\" test",
0, "UPDATE 1\n", 'hardcoding plpgsql lib path';
}
is_program_out 'nobody', 'psql test -c "CREATE FUNCTION inc3(integer) RETURNS integer LANGUAGE plpgsql AS \'BEGIN RETURN \$1 + 3; END;\';"',
0, "CREATE FUNCTION\n", 'create function inc3';
is_program_out 'nobody', 'psql -Atc "SELECT inc2(3)" test', 0, "5\n",
'call function inc2';
is_program_out 'nobody', 'psql -Atc "SELECT inc3(3)" test', 0, "6\n",
'call function inc3';
# create user and group
is_program_out 'postgres', "psql -qc 'CREATE USER foo' template1", 0, '',
'create user foo';
is_program_out 'postgres', "psql -qc 'CREATE GROUP gfoo' template1", 0, '',
'create group gfoo';
# create per-database and per-table ACL
is_program_out 'postgres', "psql -qc 'GRANT CREATE ON DATABASE test TO foo'", 0, '',
'GRANT CREATE ON DATABASE';
is_program_out 'postgres', "psql -qc 'GRANT INSERT ON phone TO foo' test", 0, '',
'GRANT INSERT';
# exercise ACL on old database to ensure they are working
is_program_out 'nobody', 'psql -U foo -qc "CREATE SCHEMA s_foo" test', 0, '',
'CREATE SCHEMA on old cluster (ACL)';
is_program_out 'nobody', 'psql -U foo -qc "INSERT INTO phone VALUES (\'Bob\', 1)" test',
0, '', 'insert Bob into phone table (ACL)';
# set database option
is_program_out 'postgres', 'psql -qc "ALTER DATABASE test SET DateStyle = \'ISO, YMD\'"',
0, '', 'set database parameter';
# create a tablespace
my $tdir = tempdir (CLEANUP => 1);
my ($p_uid, $p_gid) = (getpwnam 'postgres')[2,3];
chown $p_uid, $p_gid, $tdir;
is_program_out 'postgres', "psql -qc \"CREATE TABLESPACE myts LOCATION '$tdir'\"",
0, '', "creating tablespace in $tdir";
is_program_out 'postgres', "psql -qc 'CREATE TABLE tstab (a int) TABLESPACE myts'",
0, '', "creating table in tablespace";
# Check clusters
like_program_out 'nobody', 'pg_lsclusters -h', 0,
qr/^$MAJORS[0]\s+upgr\s+5432 online postgres/;
# Check SELECT in original cluster
my $select_old;
is ((exec_as 'nobody', 'psql -tAc "SELECT * FROM phone ORDER BY name" test', $select_old), 0, 'SELECT in original cluster succeeds');
is ($$select_old, 'Alice|2
Bob|1
', 'check SELECT output in original cluster');
# create inaccessible cwd, to check for confusing error messages
rmdir '/tmp/pgtest';
mkdir '/tmp/pgtest/' or die "Could not create temporary test directory /tmp/pgtest: $!";
chmod 0100, '/tmp/pgtest/';
chdir '/tmp/pgtest';
# Upgrade to latest version
my $outref;
is ((exec_as 0, "(pg_upgradecluster -v $MAJORS[-1] $upgrade_options $MAJORS[0] upgr | sed -e 's/^/STDOUT: /')", $outref, 0), 0, 'pg_upgradecluster succeeds');
like $$outref, qr/Starting target cluster/, 'pg_upgradecluster reported cluster startup';
like $$outref, qr/Success. Please check/, 'pg_upgradecluster reported successful operation';
my @err = grep (!/^STDOUT: /, split (/\n/, $$outref));
if (@err) {
fail 'no error messages during upgrade';
print (join ("\n", @err));
} else {
pass "no error messages during upgrade";
}
# remove inaccessible test cwd
chdir '/';
rmdir '/tmp/pgtest/';
# Check clusters
like_program_out 'nobody', 'pg_lsclusters -h', 0,
qr"$MAJORS[0] +upgr 5433 down postgres /var/lib/postgresql/$MAJORS[0]/upgr +/var/log/postgresql/postgresql-$MAJORS[0]-upgr.log\n$MAJORS[-1] +upgr 5432 online postgres /var/lib/postgresql/$MAJORS[-1]/upgr +/var/log/postgresql/postgresql-$MAJORS[-1]-upgr.log", 'pg_lsclusters output';
# Check that SELECT output is identical
is_program_out 'nobody', 'psql -tAc "SELECT * FROM phone ORDER BY name" test', 0,
$$select_old, 'SELECT output is the same in original and upgraded test';
is_program_out 'nobody', 'psql -tAc "SELECT * FROM nums" testro', 0,
"1\n", 'SELECT output is the same in original and upgraded testro';
# Check sequence value
is_program_out 'nobody', 'psql -Atc "SELECT nextval(\'odd10\')" test', 0, "5\n",
'check next sequence value';
is_program_out 'nobody', 'psql -Atc "SELECT nextval(\'odd10\')" test', 0, "7\n",
'check next sequence value';
is_program_out 'nobody', 'psql -Atc "SELECT nextval(\'odd10\')" test', 0, "9\n",
'check next sequence value';
is_program_out 'nobody', 'psql -Atc "SELECT nextval(\'odd10\')" test', 0, "1\n",
'check next sequence value (wrap)';
# check large objects
is_program_out 'postgres', 'psql -Atc "SET bytea_output = \'escape\'; SELECT data FROM pg_largeobject WHERE loid = 1234"', 0, "Hello world\n",
'check large object';
# check stored procedures
is_program_out 'nobody', 'psql -Atc "SELECT inc2(-3)" test', 0, "-1\n",
'call function inc2';
is_program_out 'nobody', 'psql -Atc "SELECT inc3(1)" test', 0, "4\n",
'call function inc3 (formerly hardcoded path)';
SKIP: {
skip 'upgrading databases with datallowcon = false not supported by pg_upgrade', 2 if $upgrade_options =~ /upgrade/;
# Check connection permissions
my $testnc_conn = $upgrade_options =~ /upgrade/ ? 't' : 'f';
is_program_out 'nobody', 'psql -tAc "SELECT datname, datallowconn FROM pg_database ORDER BY datname" template1', 0,
"postgres|t
template0|f
template1|t
test|t
testnc|$testnc_conn
testro|t
", 'dataallowconn values';
}
# check ACLs
is_program_out 'nobody', 'psql -U foo -qc "CREATE SCHEMA s_bar" test', 0, '',
'CREATE SCHEMA on new cluster (ACL)';
is_program_out 'nobody', 'psql -U foo -qc "INSERT INTO phone VALUES (\'Chris\', 5)" test',
0, '', 'insert Chris into phone table (ACL)';
# check default transaction r/o
is ((exec_as 'nobody', 'psql test -c "CREATE TABLE test(num int)"'),
0, 'creating table in test succeeds');
SKIP: {
skip 'read-only not supported by pg_upgrade', 2 if $upgrade_options =~ /upgrade/;
is ((exec_as 'nobody', 'psql testro -c "CREATE TABLE test(num int)"'),
1, 'creating table in testro fails');
is ((exec_as 'postgres', 'psql testro -c "CREATE TABLE test(num int)"'),
1, 'creating table in testro as superuser fails');
}
is ((exec_as 'nobody', 'psql testro -c "BEGIN READ WRITE; CREATE TABLE test(num int); COMMIT"'),
0, 'creating table in testro succeeds with RW transaction');
# check DB parameter
is_program_out 'postgres', 'psql -Atc "SHOW DateStyle" test', 0, 'ISO, YMD
', 'check database parameter';
SKIP: {
skip "cluster name not supported in $MAJORS[0]", 1 if ($MAJORS[0] < 9.5);
is (PgCommon::get_conf_value ($MAJORS[-1], 'upgr', 'postgresql.conf', 'cluster_name'), "$MAJORS[-1]/upgr", "cluster_name is updated");
}
# check tablespace
is_program_out 'postgres', "psql -Atc 'SELECT spcname FROM pg_tablespace ORDER BY spcname'",
0, "myts\npg_default\npg_global\n", "check tablespace of upgraded table";
is_program_out 'postgres', "psql -Atc \"SELECT spcname FROM pg_class c LEFT JOIN pg_tablespace t ON (c.reltablespace = t.oid) WHERE c.relname = 'tstab'\"",
0, "myts\n", "check tablespace of upgraded table";
# stop servers, clean up
is ((system "pg_dropcluster $MAJORS[0] upgr --stop"), 0, 'Dropping original cluster');
is ((system "pg_ctlcluster $MAJORS[-1] upgr restart"), 0, 'Restarting upgraded cluster');
is_program_out 'nobody', 'psql -Atc "SELECT nextval(\'odd10\')" test', 0, "3\n",
'upgraded cluster still works after removing old one';
is ((system "pg_dropcluster $MAJORS[-1] upgr --stop"), 0, 'Dropping upgraded cluster');
is ((system "rm -rf /var/log/postgresql/pg_upgradecluster-*"), 0, 'Cleaning pg_upgrade log files');
check_clean;
} # foreach method
# vim: filetype=perl
|