diff options
Diffstat (limited to 't')
50 files changed, 6630 insertions, 0 deletions
diff --git a/t/001_packages.t b/t/001_packages.t new file mode 100644 index 0000000..9d67839 --- /dev/null +++ b/t/001_packages.t @@ -0,0 +1,66 @@ +# Check that the necessary packages are installed + +use strict; + +use lib 't'; +use TestLib; +use POSIX qw/setlocale LC_ALL LC_MESSAGES/; + +use Test::More tests => $PgCommon::rpm ? (8*@MAJORS) : (11 + 7*@MAJORS); + +note "PostgreSQL versions installed: @MAJORS\n"; + +if ($PgCommon::rpm) { + foreach my $v (@MAJORS) { + my $vv = $v; + $vv =~ s/\.//; + + ok ((rpm_installed "postgresql$vv"), "postgresql$vv installed"); + ok ((rpm_installed "postgresql$vv-libs"), "postgresql$vv-libs installed"); + ok ((rpm_installed "postgresql$vv-server"), "postgresql$vv-server installed"); + ok ((rpm_installed "postgresql$vv-contrib"), "postgresql$vv-contrib installed"); + ok ((rpm_installed "postgresql$vv-plperl"), "postgresql$vv-plperl installed"); + ok ((rpm_installed "postgresql$vv-plpython"), "postgresql$vv-plpython installed"); + ok ((rpm_installed "postgresql$vv-pltcl"), "postgresql$vv-pltcl installed"); + ok ((rpm_installed "postgresql$vv-devel"), "postgresql$vv-devel installed"); + } + exit; +} + +foreach my $v (@MAJORS) { + ok ((deb_installed "postgresql-$v"), "postgresql-$v installed"); + ok ((deb_installed "postgresql-plpython-$v"), "postgresql-plpython-$v installed"); + if ($v >= '9.1') { + ok ((deb_installed "postgresql-plpython3-$v"), "postgresql-plpython3-$v installed"); + } else { + pass "no Python 3 package for version $v"; + } + ok ((deb_installed "postgresql-plperl-$v"), "postgresql-plperl-$v installed"); + ok ((deb_installed "postgresql-pltcl-$v"), "postgresql-pltcl-$v installed"); + ok ((deb_installed "postgresql-server-dev-$v"), "postgresql-server-dev-$v installed"); + SKIP: { + skip "No postgresql-contrib-$v package for version $v", 1 if ($v >= 10); + ok ((deb_installed "postgresql-contrib-$v"), "postgresql-contrib-$v installed"); + } +} + +ok ((deb_installed 'libecpg-dev'), 'libecpg-dev installed'); +ok ((deb_installed 'procps'), 'procps installed'); +ok ((deb_installed 'netcat-openbsd'), 'netcat-openbsd installed'); + +ok ((deb_installed 'hunspell-en-us'), 'hunspell-en-us installed'); + +# check installed locales to fail tests early if they are missing +ok ((setlocale(LC_MESSAGES, '') =~ /utf8|UTF-8/), 'system has a default UTF-8 locale'); +ok (setlocale (LC_ALL, "ru_RU"), 'locale ru_RU exists'); +ok (setlocale (LC_ALL, "ru_RU.UTF-8"), 'locale ru_RU.UTF-8 exists'); + +my $key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'; +my $pem_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'; +ok ((getgrnam('ssl-cert'))[3] =~ /postgres/, + 'user postgres in the UNIX group ssl-cert'); +ok (-e $key_file, "$key_file exists"); +is (exec_as ('postgres', "cat $key_file > /dev/null"), 0, "$key_file is readable for postgres"); +ok (-e $pem_file, "$pem_file exists"); + +# vim: filetype=perl diff --git a/t/002_existing_clusters.t b/t/002_existing_clusters.t new file mode 100644 index 0000000..93cc782 --- /dev/null +++ b/t/002_existing_clusters.t @@ -0,0 +1,11 @@ +# Check that no clusters and postgres processes are present for this test. + +use strict; +use Test::More tests => 8; + +use lib 't'; +use TestLib; + +check_clean; + +# vim: filetype=perl diff --git a/t/003_package_checks.t b/t/003_package_checks.t new file mode 100644 index 0000000..e2cc04e --- /dev/null +++ b/t/003_package_checks.t @@ -0,0 +1,23 @@ +# Check properties of the installed packages/binaries + +use strict; + +use lib 't'; +use TestLib; +use PgCommon qw/$binroot/; + +use Test::More tests => (@MAJORS) * 4; + +# Debian/Ubuntu packages are linked against libedit. If your psql binaries are +# linked against libreadline, set PG_READLINE=1 when running this testsuite. +$ENV{PG_READLINE} = 1 if ($PgCommon::rpm); +my ($want_lib, $avoid_lib) = $ENV{PG_READLINE} ? qw(libreadline libedit) : qw(libedit libreadline); + +foreach my $v (@MAJORS) { + like_program_out (0, "ldd $binroot$v/bin/psql", 0, qr/$want_lib\.so\./, + "psql is linked against $want_lib"); + unlike_program_out (0, "ldd $binroot$v/bin/psql", 0, qr/$avoid_lib\.so\./, + "psql is not linked against $avoid_lib"); +} + +# vim: filetype=perl diff --git a/t/005_PgCommon.t b/t/005_PgCommon.t new file mode 100644 index 0000000..22dcca8 --- /dev/null +++ b/t/005_PgCommon.t @@ -0,0 +1,293 @@ +# Check PgCommon library functions. + +use strict; + +use File::Temp qw/tempdir/; + +use lib '.'; +use PgCommon; + +use lib 't'; +use TestLib; + +use Test::More tests => 24; + +my $tdir = tempdir (CLEANUP => 1); +$PgCommon::confroot = $tdir; + +# test read_pg_hba with valid file +open P, ">$tdir/pg_hba.conf" or die "Could not create $tdir/pg_hba.conf: $!"; +print P <<EOF; +# comment +local all postgres ident sameuser + +# TYPE DATABASE USER CIDR-ADDRESS METHOD +local foo nobody trust +local foo nobody crypt +local foo nobody,joe krb5 +local foo,bar nobody ident +local all +foogrp password +host \@inc all 127.0.0.1/32 md5 +hostssl all \@inc 192.168.0.0 255.255.0.0 pam +hostnossl all all 192.168.0.0 255.255.0.0 reject +EOF +close P; + +my @expected_records = ( + { 'type' => 'local', 'db' => 'all', 'user' => 'postgres', 'method' => 'ident sameuser' }, + { 'type' => 'local', 'db' => 'foo', 'user' => 'nobody', 'method' => 'trust' }, + { 'type' => 'local', 'db' => 'foo', 'user' => 'nobody', 'method' => 'crypt' }, + { 'type' => 'local', 'db' => 'foo', 'user' => 'nobody,joe', 'method' => 'krb5' }, + { 'type' => 'local', 'db' => 'foo,bar', 'user' => 'nobody', 'method' => 'ident' }, + { 'type' => 'local', 'db' => 'all', 'user' => '+foogrp', 'method' => 'password' }, + { 'type' => 'host', 'db' => '@inc', 'user' => 'all', 'method' => 'md5', 'ip' => '127.0.0.1', 'mask' => '32'}, + { 'type' => 'hostssl', 'db' => 'all', 'user' => '@inc', 'method' => 'pam', 'ip' => '192.168.0.0', 'mask' => '255.255.0.0'}, + { 'type' => 'hostnossl', 'db' => 'all', 'user' => 'all', 'method' => 'reject', 'ip' => '192.168.0.0', 'mask' => '255.255.0.0'}, +); + +my @hba = read_pg_hba "$tdir/pg_hba.conf"; +foreach my $entry (@hba) { + next if $$entry{'type'} eq 'comment'; + if ($#expected_records < 0) { + fail '@expected_records is already empty'; + next; + } + my $expected = shift @expected_records; + my $parsedstr = ''; + my $expectedstr = ''; + foreach my $k (keys %$expected) { + $parsedstr .= $k . ':\'' . $$entry{$k} . '\' '; + $expectedstr .= $k . ':\'' . $$expected{$k} . '\' '; + if ($$expected{$k} ne $$entry{$k}) { + fail "mismatch: $expectedstr ne $parsedstr"; + last; + } + } + pass 'correctly parsed line \'' . $$entry{'line'} . "'"; +} + +ok (($#expected_records == -1), '@expected_records has correct number of entries'); + +# test read_pg_hba with invalid file +my $invalid_hba = <<EOF; +foo all all md5 +local all all foo +host all all foo +host all all 127.0.0.1/32 foo +host all all md5 +host all all 127.0.0.1/32 0.0.0.0 md5 +host all all 127.0.0.1 md5 +EOF +open P, ">$tdir/pg_hba.conf" or die "Could not create $tdir/pg_hba_invalid.conf: $!"; +print P $invalid_hba; +close P; + +@hba = read_pg_hba "$tdir/pg_hba.conf"; +is (scalar (split "\n", $invalid_hba), $#hba+1, 'returned read_pg_hba array has correct number of records'); +foreach my $entry (@hba) { + is $$entry{'type'}, undef, 'line \'' . $$entry{'line'} . '\' parsed as invalid'; +} + +# test read_conf_file() +my %conf = PgCommon::read_conf_file '/nonexisting'; +is_deeply \%conf, {}, 'read_conf_file returns empty dict for nonexisting file'; + +mkdir "$tdir/8.4"; +mkdir "$tdir/8.4/test" or die "mkdir: $!"; +mkdir "$tdir/conf.d" or die "mkdir: $!"; +my $c = "$tdir/8.4/test/foo.conf"; +open F, ">$c" or die "Could not create $c: $!"; +print F <<EOF; +# test configuration file + +# Commented_Int = 12 +# commented_str='foobar' + +#intval = 1 +Intval = 42 +cintval=1 # blabla +floatval = 1.5e+3 +strval 'hello' +strval2 'world' +cstrval = 'bye' # comment +emptystr = '' +cemptystr = '' # moo! +#testpath = '/bin/bad' +testpath = '/bin/test' +QuoteStr = 'test ! -f \\'/tmp/%f\\' && echo \\'yes\\'' +EOF +close F; +%conf = PgCommon::read_conf_file "$c"; +is_deeply (\%conf, { + 'intval' => 42, + 'cintval' => 1, + 'floatval' => '1.5e+3', + 'strval' => 'hello', + 'strval2' => 'world', + 'cstrval' => 'bye', + 'testpath' => '/bin/test', + 'emptystr' => '', + 'cemptystr' => '', + 'quotestr' => "test ! -f '/tmp/%f' && echo 'yes'" + }, 'read_conf_file() parsing'); + +# test read_conf_file() with include directives +open F, ">$tdir/8.4/test/condinc.conf" or die "Could not create $tdir/condinc.conf: $!"; +print F "condint = 42\n"; +close F; + +open F, ">$tdir/bar.conf" or die "Could not create $tdir/bar.conf: $!"; +print F <<EOF; +# test configuration file + +# Commented_Int = 24 +# commented_str = 'notme' + +intval = -1 +include '8.4/test/foo.conf' # foo +include_dir 'conf.d' # bar +strval = 'howdy' +include_if_exists '/nonexisting.conf' +include_if_exists = '8.4/test/condinc.conf' +EOF +close F; + +open F, ">$tdir/conf.d/sub.conf" or die "Could not create $tdir/conf.d/sub.conf: $!"; +print F <<EOF; +subvalue = 1 +include = '../relative.conf' +EOF +close F; + +open F, ">$tdir/relative.conf" or die "Could not create $tdir/relative.conf: $!"; +print F <<EOF; +relativevalue = 1 +include '$tdir/absolute.conf' +EOF +close F; + +open F, ">$tdir/absolute.conf" or die "Could not create $tdir/absolute.conf: $!"; +print F <<EOF; +absolutevalue = 1 +EOF +close F; + +%conf = PgCommon::read_conf_file "$tdir/bar.conf"; +is_deeply (\%conf, { + 'intval' => 42, + 'cintval' => 1, + 'floatval' => '1.5e+3', + 'strval' => 'howdy', + 'strval2' => 'world', + 'cstrval' => 'bye', + 'testpath' => '/bin/test', + 'emptystr' => '', + 'cemptystr' => '', + 'quotestr' => "test ! -f '/tmp/%f' && echo 'yes'", + 'condint' => 42, + 'subvalue' => 1, + 'relativevalue' => 1, + 'absolutevalue' => 1, + }, 'read_conf_file() parsing with include directives'); + + +# test set_conf_value() +PgCommon::set_conf_value '8.4', 'test', 'foo.conf', 'commented_int', '24'; +PgCommon::set_conf_value '8.4', 'test', 'foo.conf', 'commented_str', 'new foo'; +PgCommon::set_conf_value '8.4', 'test', 'foo.conf', 'intval', '39'; +PgCommon::set_conf_value '8.4', 'test', 'foo.conf', 'cintval', '5'; +PgCommon::set_conf_value '8.4', 'test', 'foo.conf', 'strval', 'Howdy'; +PgCommon::set_conf_value '8.4', 'test', 'foo.conf', 'newval', 'NEW!'; +PgCommon::set_conf_value '8.4', 'test', 'foo.conf', 'testpath', '/bin/new'; +PgCommon::set_conf_value '8.4', 'test', 'foo.conf', 'include_dir', 'conf.d'; + +open F, "$c"; +my $conf; +read F, $conf, 1024; +close F; +is ($conf, <<EOF, 'set_conf_value'); +# test configuration file + +Commented_Int = 24 +commented_str='new foo' + +#intval = 1 +Intval = 39 +cintval=5 # blabla +floatval = 1.5e+3 +strval Howdy +strval2 'world' +cstrval = 'bye' # comment +emptystr = '' +cemptystr = '' # moo! +#testpath = '/bin/bad' +testpath = '/bin/new' +QuoteStr = 'test ! -f \\'/tmp/%f\\' && echo \\'yes\\'' +newval = 'NEW!' +include_dir = 'conf.d' +EOF + +# test disable_conf_value() +PgCommon::disable_conf_value '8.4', 'test', 'foo.conf', 'intval', 'ints are out of fashion'; +PgCommon::disable_conf_value '8.4', 'test', 'foo.conf', 'cstrval', 'not used any more'; +PgCommon::disable_conf_value '8.4', 'test', 'foo.conf', 'nonexisting', 'NotMe'; +PgCommon::disable_conf_value '8.4', 'test', 'foo.conf', 'testpath', 'now 2 comments'; + +open F, "$c"; +read F, $conf, 1024; +close F; +is ($conf, <<EOF, 'disable_conf_value'); +# test configuration file + +Commented_Int = 24 +commented_str='new foo' + +#intval = 1 +#Intval = 39 #ints are out of fashion +cintval=5 # blabla +floatval = 1.5e+3 +strval Howdy +strval2 'world' +#cstrval = 'bye' # comment #not used any more +emptystr = '' +cemptystr = '' # moo! +#testpath = '/bin/bad' +#testpath = '/bin/new' #now 2 comments +QuoteStr = 'test ! -f \\'/tmp/%f\\' && echo \\'yes\\'' +newval = 'NEW!' +include_dir = 'conf.d' +EOF + +# test replace_conf_value() +PgCommon::replace_conf_value '8.4', 'test', 'foo.conf', 'strval', + 'renamedstrval', 'newstrval', 'goodbye'; +PgCommon::replace_conf_value '8.4', 'test', 'foo.conf', 'nonexisting', + 'renamednonexisting', 'newnonexisting', 'XXX'; + +open F, "$c"; +read F, $conf, 1024; +close F; +is ($conf, <<EOF, 'replace_conf_value'); +# test configuration file + +Commented_Int = 24 +commented_str='new foo' + +#intval = 1 +#Intval = 39 #ints are out of fashion +cintval=5 # blabla +floatval = 1.5e+3 +#strval Howdy #renamedstrval +newstrval = goodbye +strval2 'world' +#cstrval = 'bye' # comment #not used any more +emptystr = '' +cemptystr = '' # moo! +#testpath = '/bin/bad' +#testpath = '/bin/new' #now 2 comments +QuoteStr = 'test ! -f \\'/tmp/%f\\' && echo \\'yes\\'' +newval = 'NEW!' +include_dir = 'conf.d' +EOF + +# vim: filetype=perl diff --git a/t/006_next_free_port.t b/t/006_next_free_port.t new file mode 100644 index 0000000..cb6c2f0 --- /dev/null +++ b/t/006_next_free_port.t @@ -0,0 +1,49 @@ +# Check PgCommon's next_free_port() + +use strict; + +use lib '.'; +use PgCommon; + +use lib 't'; +use TestLib; + +use Test::More tests => 5; + +# test next_free_port(). We are intentionally using nc as an external tool, +# using perl would replicate what next_free_port is doing, and that would +# be a pointless test. +use IPC::Open2; +use Time::HiRes qw(usleep); +my @pids; +# no ports open +is (next_free_port, 5432, 'next_free_port is 5432'); + +# open a localhost ipv4 socket +push @pids, open2(\*CHLD_OUT, \*CHLD_IN, qw(nc -4 -l 127.0.0.1 5432)); +usleep 2*$delay; +is (next_free_port, 5433, 'next_free_port detects localhost ipv4 socket'); +# open a wildcard ipv4 socket +push @pids, open2(\*CHLD_OUT, \*CHLD_IN, qw(nc -4 -l 5433)); +usleep $delay; +is (next_free_port, 5434, 'next_free_port detects wildcard ipv4 socket'); + +SKIP: { + $^V =~ /^v(\d+\.\d+)/; # parse perl version + skip "perl <= 5.10 does not have proper IPv6 support", 2 if ($1 <= 5.10); + skip "skipping IPv6 tests", 2 if ($ENV{SKIP_IPV6}); + + # open a localhost ipv6 socket + push @pids, open2(\*CHLD_OUT, \*CHLD_IN, qw(nc -6 -l ::1 5434)); + usleep $delay; + is (next_free_port, 5435, 'next_free_port detects localhost ipv6 socket'); + # open a wildcard ipv6 socket + push @pids, open2(\*CHLD_OUT, \*CHLD_IN, qw(nc -6 -l 5435)); + usleep $delay; + is (next_free_port, 5436, 'next_free_port detects wildcard ipv6 socket'); +} + +# clean up +kill 15, @pids; + +# vim: filetype=perl diff --git a/t/007_pg_conftool.t b/t/007_pg_conftool.t new file mode 100644 index 0000000..e8465ef --- /dev/null +++ b/t/007_pg_conftool.t @@ -0,0 +1,85 @@ +# Test pg_conftool + +use strict; +use warnings; + +use Test::More tests => 41; +use File::Temp qw/tempdir/; +use lib '.'; +use PgCommon; +use lib 't'; +use TestLib; + +my $tdir = tempdir (CLEANUP => 1); +$ENV{'PG_CLUSTER_CONF_ROOT'} = $tdir; + +open F, "> $tdir/different.conf"; +print F "a = '5'\n"; +print F "#b = '6'\n"; +close F; + +note 'test without cluster'; +is_program_out 0, "pg_conftool show all", 1, "Error: No default cluster found\n"; +is_program_out 0, "pg_conftool foo.conf show all", 1, "Error: No default cluster found\n"; +is_program_out 0, "pg_conftool $tdir/different.conf show all", 0, "a = 5\n"; +is_program_out 0, "pg_conftool 9.7 main show all", 1, "Error: Cluster 9.7 main does not exist\n"; + +my $version = $MAJORS[-1]; +die "Tests past this point need PostgreSQL installed" unless ($version); +mkdir "$tdir/$version"; +mkdir "$tdir/$version/main"; + +open F, "> $tdir/$version/main/postgresql.conf"; +print F "a = '1'\n"; +print F "#b = '2'\n"; +close F; + +open F, "> $tdir/$version/main/other.conf"; +print F "a = '3'\n"; +print F "#b = '4'\n"; +close F; + +sub pgconf { + undef $/; + open F, "$tdir/$version/main/postgresql.conf"; + my $f = <F>; + close F; + return $f; +} + +sub differentconf { + undef $/; + open F, "$tdir/different.conf"; + my $f = <F>; + close F; + return $f; +} + +note 'test show'; +is_program_out 0, "pg_conftool show all", 0, "a = 1\n"; +is_program_out 0, "pg_conftool other.conf show all", 0, "a = 3\n"; +is_program_out 0, "pg_conftool $tdir/different.conf show all", 0, "a = 5\n"; +is_program_out 0, "pg_conftool $version main show all", 0, "a = 1\n"; +is_program_out 0, "pg_conftool $version main other.conf show all", 0, "a = 3\n"; +is_program_out 0, "pg_conftool show a", 0, "a = 1\n"; +is_program_out 0, "pg_conftool -s show a", 0, "1\n"; + +note 'test set'; +is_program_out 0, "pg_conftool set c 7", 0, ""; +undef $/; # slurp mode +is pgconf, "a = '1'\n#b = '2'\nc = 7\n", "file contains new setting"; +is_program_out 0, "pg_conftool set a 8", 0, ""; +is pgconf, "a = 8\n#b = '2'\nc = 7\n", "file contains updated setting"; +is_program_out 0, "pg_conftool $tdir/different.conf set a 9", 0, ""; +is differentconf, "a = 9\n#b = '6'\n", "file with path contains updated setting"; + +note 'test remove'; +is_program_out 0, "pg_conftool remove a", 0, ""; +is pgconf, "#a = 8\n#b = '2'\nc = 7\n", "setting removed from file"; +is_program_out 0, "pg_conftool $tdir/different.conf remove a", 0, ""; +is differentconf, "#a = 9\n#b = '6'\n", "setting removed from file with path"; + +note 'test edit'; +$ENV{EDITOR} = 'cat'; +is_program_out 0, "pg_conftool edit", 0, "#a = 8\n#b = '2'\nc = 7\n"; +is_program_out 0, "pg_conftool $tdir/different.conf edit", 0, "#a = 9\n#b = '6'\n"; diff --git a/t/010_defaultport_cluster.t b/t/010_defaultport_cluster.t new file mode 100644 index 0000000..557390f --- /dev/null +++ b/t/010_defaultport_cluster.t @@ -0,0 +1,33 @@ +# We try to call psql with --version and then on localhost. Since there are no +# clusters, we expect an error message that the connection to port 5432 is +# refused. This checks that pg_wrapper correctly picks the default port and +# uses the highest available version. + +use strict; +use Test::More tests => 14; + +use lib 't'; +use TestLib; + +like_program_out 0, 'psql --version', 0, qr/^psql \(PostgreSQL\) $ALL_MAJORS[-1]/, + 'pg_wrapper selects highest available version number'; + +like_program_out 0, 'env LC_MESSAGES=C psql -h 127.0.0.1 -l', 2, qr/could not connect/, + 'connecting to localhost fails with no clusters'; + +# We check if PGCLUSTER, --cluster, and native psql options are evaluated with +# correct priority. (This is related to the checks in t/090_multicluster.t, but +# easier to do here because no clusters are running.) + +like_program_out 0, "env LC_MESSAGES=C PGCLUSTER=$MAJORS[-1]/127.0.0.2:5431 psql -l", + 2, qr/could not connect.*127.0.0.2.*on port 5431/s, 'pg_wrapper uses host and port from PGCLUSTER'; +like_program_out 0, "env LC_MESSAGES=C PGCLUSTER=$MAJORS[-1]/127.0.0.2:5431 psql --cluster $MAJORS[-1]/127.0.0.3:5430 -l", + 2, qr/could not connect.*127.0.0.3.*on port 5430/s, 'pg_wrapper uses --cluster from the command line'; +like_program_out 0, "env LC_MESSAGES=C PGCLUSTER=$MAJORS[-1]/127.0.0.2:5431 psql -h 127.0.0.3 -l", + 2, qr/could not connect.*127.0.0.3.*on port 5432/s, 'pg_wrapper ignores PGCLUSTER with -h on the command line'; +like_program_out 0, "env LC_MESSAGES=C PGCLUSTER=$MAJORS[-1]/127.0.0.2:5431 psql --host 127.0.0.3 -l", + 2, qr/could not connect.*127.0.0.3.*on port 5432/s, 'pg_wrapper ignores PGCLUSTER with --host on the command line'; +like_program_out 0, "env LC_MESSAGES=C PGCLUSTER=$MAJORS[-1]/127.0.0.2:5431 PGHOST=127.0.0.3 psql -l", + 2, qr/could not connect.*127.0.0.3.*on port 5432/s, 'pg_wrapper ignores PGCLUSTER if PGHOST is set'; + +# vim: filetype=perl diff --git a/t/015_start_stop.t b/t/015_start_stop.t new file mode 100644 index 0000000..3ec43a5 --- /dev/null +++ b/t/015_start_stop.t @@ -0,0 +1,174 @@ +use strict; + +use lib 't'; +use TestLib; +use PgCommon; + +use Test::More tests => 71 * @MAJORS; + +my $systemd = (-d "/run/systemd/system" and not $ENV{_SYSTEMCTL_SKIP_REDIRECT}); +note $systemd ? "We are running systemd" : "We are not running systemd"; + +# check cluster status +# arguments: <version> <pg_ctlcluster exit status> <systemctl exit status> <text to print> +sub check_status { + my ($v, $ctlstatus, $scstatus, $text) = @_; + program_ok (0, "pg_ctlcluster $v main status", $ctlstatus, "cluster $v main $text"); + if ($systemd) { + program_ok (0, "systemctl status postgresql\@$v-main", $scstatus, "service postgresql\@$v-main $text"); + } else { + pass ''; + } +} + +sub check_major { + my $v = $_[0]; + my $ctlstopped = $v >= 9.2 ? 3 : 1; # pg_ctl status "not running" changed in 9.2 + note "Running tests for $v"; + + note "Cluster does not exist yet"; ############################### + check_status $v, 1, 3, "does not exist"; + + # try to start postgresql + if ($systemd) { + program_ok (0, "systemctl start postgresql"); + } else { + program_ok (0, "/etc/init.d/postgresql start"); + } + check_status $v, 1, 3, "does not exist"; + + # try to start specific cluster + if ($systemd) { + program_ok (0, "systemctl start postgresql\@$v-main", 1); + } else { + program_ok (0, "/etc/init.d/postgresql start $v"); + } + check_status $v, 1, 3, "does not exist"; + + note "Start/stop postgresql using system tools"; ############################### + + # create cluster + program_ok (0, "pg_createcluster $v main"); + check_status $v, $ctlstopped, 3, "is stopped"; + + # start postgresql + if ($systemd) { + program_ok (0, "systemctl start postgresql"); + } else { + program_ok (0, "/etc/init.d/postgresql start"); + } + check_status $v, 0, 0, "is running"; + + # start postgresql again + if ($systemd) { + program_ok (0, "systemctl start postgresql"); + } else { + program_ok (0, "/etc/init.d/postgresql start"); + } + check_status $v, 0, 0, "is already running"; + + # stop postgresql + if ($systemd) { + program_ok (0, "systemctl stop postgresql"); + sleep 6; # FIXME: systemctl stop postgresql is not yet synchronous (#759725) + } else { + program_ok (0, "/etc/init.d/postgresql stop"); + } + check_status $v, $ctlstopped, 3, "is stopped"; + + # stop postgresql again + if ($systemd) { + program_ok (0, "systemctl stop postgresql"); + } else { + program_ok (0, "/etc/init.d/postgresql stop"); + } + check_status $v, $ctlstopped, 3, "is already stopped"; + + note "Start/stop specific cluster using system tools"; ############################### + + # start cluster using system tools + if ($systemd) { + program_ok (0, "systemctl start postgresql\@$v-main"); + } else { + program_ok (0, "/etc/init.d/postgresql start $v"); + } + check_status $v, 0, 0, "is running"; + + # try start cluster again + if ($systemd) { + program_ok (0, "systemctl start postgresql\@$v-main"); + } else { + program_ok (0, "/etc/init.d/postgresql start $v"); + } + check_status $v, 0, 0, "is running"; + + # restart cluster + if ($systemd) { + program_ok (0, "systemctl restart postgresql\@$v-main"); + } else { + program_ok (0, "/etc/init.d/postgresql restart $v"); + } + check_status $v, 0, 0, "is running"; + + # stop cluster + if ($systemd) { + program_ok (0, "systemctl stop postgresql\@$v-main"); + } else { + program_ok (0, "/etc/init.d/postgresql stop $v"); + } + check_status $v, $ctlstopped, 3, "is stopped"; + + # try to stop cluster again + if ($systemd) { + program_ok (0, "systemctl stop postgresql\@$v-main"); + } else { + program_ok (0, "/etc/init.d/postgresql stop $v"); + } + check_status $v, $ctlstopped, 3, "is already stopped"; + + # drop cluster + program_ok (0, "pg_dropcluster $v main"); + check_status $v, 1, 3, "does not exist"; + + note "Start/stop specific cluster using pg_*cluster"; ############################### + + # try to start cluster + program_ok (0, "pg_ctlcluster $v main start", 1); + check_status $v, 1, 3, "does not exist"; + + # create cluster and start it + program_ok (0, "pg_createcluster $v main --start"); + check_status $v, 0, 0, "is running"; + + # try to start cluster again + my $exitagain = $systemd ? 0 : 2; + program_ok (0, "pg_ctlcluster $v main start", $exitagain); + check_status $v, 0, 0, "is already running"; + + # restart cluster + program_ok (0, "pg_ctlcluster $v main restart"); + check_status $v, 0, 0, "is running"; + + # stop cluster + program_ok (0, "pg_ctlcluster $v main stop"); + check_status $v, $ctlstopped, 3, "is stopped"; + + # try to stop cluster again + program_ok (0, "pg_ctlcluster $v main stop", 2); + check_status $v, $ctlstopped, 3, "is already stopped"; + + # start cluster + program_ok (0, "pg_ctlcluster $v main start"); + check_status $v, 0, 0, "is running"; + + # stop server, clean up, check for leftovers + program_ok (0, "pg_dropcluster $v main --stop"); + + check_clean; +} + +foreach (@MAJORS) { + check_major $_; +} + +# vim: filetype=perl diff --git a/t/020_create_sql_remove.t b/t/020_create_sql_remove.t new file mode 100644 index 0000000..e44c8be --- /dev/null +++ b/t/020_create_sql_remove.t @@ -0,0 +1,412 @@ +# We create a cluster, execute some basic SQL commands, drop it again, and +# check that we did not leave anything behind. + +use strict; + +use File::Temp qw(tempdir); +use POSIX qw/dup2/; +use Time::HiRes qw/usleep/; + +use lib 't'; +use TestLib; +use PgCommon; + +use Test::More tests => 144 * @MAJORS; + +$ENV{_SYSTEMCTL_SKIP_REDIRECT} = 1; # FIXME: testsuite is hanging otherwise + +sub check_major { + my $v = $_[0]; + note "Running tests for $v"; + + # create cluster + my $xlogdir = tempdir("/tmp/$v.xlog.XXXXXX", CLEANUP => 1); + rmdir $xlogdir; # recreated by initdb + if ($v > 8.2) { + my $start_command = ($v >= 11 and not $PgCommon::rpm) ? "pg_ctlcluster" : "pg_ctl"; # CLUSTER_START_COMMAND supported in initdb + like_program_out 'root', "pg_createcluster $v main --start -- -X $xlogdir", 0, qr/$start_command/, + "pg_createcluster $v main"; + } else { # 8.2 does not have -X yet + like_program_out 'root', "pg_createcluster $v main --start", 0, qr/pg_ctl/, + "pg_createcluster $v main"; + system "mv /var/lib/postgresql/$v/main/pg_xlog $xlogdir"; + system "ln -s $xlogdir /var/lib/postgresql/$v/main/pg_xlog"; + } + + # check that a /var/run/postgresql/ pid file is created + my @contents = ('.s.PGSQL.5432', '.s.PGSQL.5432.lock', "$v-main.pid", "$v-main.pg_stat_tmp"); + pop @contents if ($v < 8.4); # remove pg_stat_tmp + unless ($PgCommon::rpm and $v < 9.4) { + ok_dir '/var/run/postgresql/', [@contents], + 'Socket and pid file are in /var/run/postgresql/'; + } else { + ok_dir '/var/run/postgresql/', [grep {/main/} @contents], 'Pid File is in /var/run/postgresql/'; + } + + # check that the xlog/wal symlink was created + my $first_xlog = $v >= 9.0 ? "000000010000000000000001" : "000000010000000000000000"; + ok_dir $xlogdir, [$first_xlog, "archive_status"], + "xlog/wal directory $xlogdir was created"; + + # verify that exactly one postgres master is running + my @pm_pids = pidof ('postgres'); + is $#pm_pids, 0, 'Exactly one postgres master process running'; + + # check environment + my %safe_env = qw/LC_ALL 1 LC_CTYPE 1 LANG 1 PWD 1 PGLOCALEDIR 1 PGSYSCONFDIR 1 PG_GRANDPARENT_PID 1 PG_OOM_ADJUST_FILE 1 PG_OOM_ADJUST_VALUE 1 SHLVL 1 PGDATA 1 _ 1/; + my %env = pid_env 'postgres', $pm_pids[0]; + foreach (keys %env) { + fail "postgres has unsafe environment variable $_" unless exists $safe_env{$_}; + } + + # activate external_pid_file + PgCommon::set_conf_value $v, 'main', 'postgresql.conf', 'external_pid_file', ''; + + # add variable to environment file, restart, check if it's there + open E, ">>/etc/postgresql/$v/main/environment" or + die 'could not open environment file for appending'; + print E "PGEXTRAVAR1 = 1 # short one\nPGEXTRAVAR2='foo bar '\n\n# comment"; + close E; + is_program_out 0, "pg_ctlcluster $v main restart", 0, '', + 'cluster restarts with new environment file'; + + @pm_pids = pidof ('postgres'); + is $#pm_pids, 0, 'Exactly one postgres master process running'; + %env = pid_env 'postgres', $pm_pids[0]; + is $env{'PGEXTRAVAR1'}, '1', 'correct value of PGEXTRAVAR1 in environment'; + is $env{'PGEXTRAVAR2'}, 'foo bar ', 'correct value of PGEXTRAVAR2 in environment'; + + # Now there should not be an external PID file any more, since we set it + # explicitly + unless ($PgCommon::rpm and $v < 9.4) { + ok_dir '/var/run/postgresql', [grep {! /pid/} @contents], + 'Socket and stats dir, but not PID file in /var/run/postgresql/'; + } else { + ok_dir '/var/run/postgresql', ["$v-main.pg_stat_tmp"], 'Only stats dir in /var/run/postgresql/'; + } + + # verify that the correct client version is selected + like_program_out 'postgres', 'createdb --version', 0, qr/^createdb \(PostgreSQL\) $v/, + 'pg_wrapper selects version number of cluster'; + + # we always want to use the latest version of "psql", though. + like_program_out 'postgres', 'psql --version', 0, qr/^psql \(PostgreSQL\) $ALL_MAJORS[-1]/, + 'pg_wrapper selects version number of cluster'; + + my $default_log = "/var/log/postgresql/postgresql-$v-main.log"; + + # verify that the cluster is displayed + my $ls = `pg_lsclusters -h`; + $ls =~ s/\s+/ /g; + $ls =~ s/\s*$//; + is $ls, "$v main 5432 online postgres /var/lib/postgresql/$v/main $default_log", + 'pg_lscluster reports online cluster on port 5432'; + + # verify that the log file is actually used + ok !-z $default_log, 'log file is actually used'; + + # verify configuration file permissions + my $postgres_uid = (getpwnam 'postgres')[2]; + my @st = stat "/etc/postgresql"; + is $st[4], $postgres_uid, '/etc/postgresql is owned by user "postgres"'; + my @st = stat "/etc/postgresql/$v"; + is $st[4], $postgres_uid, 'version configuration directory file is owned by user "postgres"'; + my @st = stat "/etc/postgresql/$v/main"; + is $st[4], $postgres_uid, 'configuration directory file is owned by user "postgres"'; + + # verify data file permissions + my @st = stat "/var/lib/postgresql/$v"; + is $st[4], $postgres_uid, 'version data directory file is owned by user "postgres"'; + my @st = stat "/var/lib/postgresql/$v/main"; + is $st[4], $postgres_uid, 'data directory file is owned by user "postgres"'; + + # verify log file permissions + my @logstat = stat $default_log; + is $logstat[2], 0100640, 'log file has 0640 permissions'; + is $logstat[4], $postgres_uid, 'log file is owned by user "postgres"'; + is $logstat[5], (getgrnam 'adm')[2], 'log file is owned by group "adm"'; + + # check default log file configuration; when not specifying -l with + # pg_createcluster, we should not have a 'log' symlink + ok !-e "/etc/postgresql/$v/main/log", 'no log symlink by default'; + ok !-z $default_log, "$default_log is the default log if log symlink is missing"; + like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/^$v\s+main.*$default_log\n$/; + + # verify that log symlink works + is ((exec_as 'root', "pg_ctlcluster $v main stop"), 0, 'stopping cluster'); + open L, ">$default_log"; close L; # empty default log file + my $p = (PgCommon::cluster_data_directory $v, 'main') . '/mylog'; + symlink $p, "/etc/postgresql/$v/main/log"; + is ((exec_as 'root', "pg_ctlcluster $v main start"), 0, + 'restarting cluster with nondefault log symlink'); + ok !-z $p, "log target is used as log file"; + ok -z $default_log, "default log is not used"; + like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/^$v\s+main.*$p\n$/; + is ((exec_as 'root', "pg_ctlcluster $v main stop"), 0, 'stopping cluster'); + open L, ">$p"; close L; # empty log file + + # verify that explicitly configured log file trumps log symlink + PgCommon::set_conf_value ($v, 'main', 'postgresql.conf', + ($v >= '8.3' ? 'logging_collector' : 'redirect_stderr'), 'on'); + PgCommon::set_conf_value $v, 'main', 'postgresql.conf', 'log_filename', "$v#main.log"; + is ((exec_as 'root', "pg_ctlcluster $v main start"), 0, + 'restarting cluster with explicitly configured log file'); + ok -z $default_log, "default log is not used"; + ok !-z $p, "log symlink target is used for startup message"; + my $pg_log = $v >= 10 ? 'log' : 'pg_log'; # log directory in PGDATA changed in PG 10 + my @l = glob ((PgCommon::cluster_data_directory $v, 'main') . "/$pg_log/$v#main.log*"); + is $#l, 0, 'exactly one log file'; + ok (-e $l[0] && ! -z $l[0], 'custom log is actually used'); + SKIP: { skip "no logging_collector in $v", 2 if ($v < 8.3); + like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/^$v\s+main.*$v#main.log\n$/; + } + + # clean up + PgCommon::disable_conf_value ($v, 'main', 'postgresql.conf', + ($v >= '8.3' ? 'logging_collector' : 'redirect_stderr'), ''); + PgCommon::disable_conf_value $v, 'main', 'postgresql.conf', 'log_filename', ''; + unlink "/etc/postgresql/$v/main/log"; + + # check that log creation does not escalate privileges + program_ok 'root', "pg_ctlcluster $v main stop", 0, 'stopping cluster'; + unlink $default_log; + symlink "/etc/postgres-hack", $default_log; + program_ok 'root', "pg_ctlcluster $v main start", 1, 'starting cluster with rouge /var/log/postgresql symlink fails'; + ok !-f "/etc/postgres-hack", "/etc/postgres-hack was not created"; + unlink $default_log; + program_ok 'root', "pg_ctlcluster $v main start", 0, 'restarting cluster'; + + # verify that processes do not have an associated terminal + unlike_program_out 0, 'ps -o tty -U postgres h', 0, qr/tty|pts/, + 'postgres processes do not have an associated terminal'; + + # verify that SSL is enabled (which should work for user postgres in a + # default installation) + my $ssl = config_bool (PgCommon::get_conf_value $v, 'main', 'postgresql.conf', 'ssl'); + if ($PgCommon::rpm) { + is $ssl, undef, 'SSL is disabled'; + } else { + is $ssl, 1, 'SSL is enabled'; + } + + # Create user nobody, a database 'nobodydb' for him, check the database list + my $outref; + is ((exec_as 'nobody', 'psql -l 2>/dev/null', $outref), 2, 'psql -l fails for nobody'); + is ((exec_as 'postgres', 'createuser nobody -D -R -S'), 0, 'createuser nobody'); + is ((exec_as 'postgres', 'createdb -O nobody nobodydb'), 0, 'createdb nobodydb'); + is ((exec_as 'nobody', 'psql -ltA|grep "|" | cut -f1-3 -d"|"', $outref), 0, 'psql -ltA succeeds for nobody'); + is ($$outref, 'nobodydb|nobody|UTF8 +postgres|postgres|UTF8 +template0|postgres|UTF8 +template1|postgres|UTF8 +', 'psql -ltA output'); + + # Then fill nobodydb with some data. + is ((exec_as 'nobody', 'psql nobodydb -c "create table phone (name varchar(255) PRIMARY KEY, tel int NOT NULL)" 2>/dev/null'), + 0, 'SQL command: create table'); + is ((exec_as 'nobody', 'psql nobodydb -c "insert into phone values (\'Bob\', 1)"'), 0, 'SQL command: insert into table values'); + is ((exec_as 'nobody', 'psql nobodydb -c "insert into phone values (\'Alice\', 2)"'), 0, 'SQL command: insert into table values'); + is ((exec_as 'nobody', 'psql nobodydb -c "insert into phone values (\'Bob\', 3)"'), 1, 'primary key violation'); + + # Check table contents + is_program_out 'nobody', 'psql -tAc "select * from phone order by name" nobodydb', 0, + 'Alice|2 +Bob|1 +', 'SQL command output: select -tA'; + is_program_out 'nobody', 'psql -txc "select * from phone where name = \'Alice\'" nobodydb', 0, + 'name | Alice +tel | 2 + +', 'SQL command output: select -tx'; + is_program_out 'nobody', 'psql -tAxc "select * from phone where name = \'Alice\'" nobodydb', 0, + 'name|Alice +tel|2 +', 'SQL command output: select -tAx'; + + sub create_extension ($) { + return "psql -qc 'CREATE EXTENSION $_[0]' nobodydb" if ($v >= 9.1); + return "createlang $_[0] nobodydb"; + } + + # Check PL/Perl untrusted + my $fn_cmd = 'CREATE FUNCTION read_file() RETURNS text AS \'open F, \\"/etc/passwd\\"; \\$buf = <F>; close F; return \\$buf;\' LANGUAGE plperl'; + is ((exec_as 'nobody', create_extension('plperlu')), 1, 'CREATE EXTENSION plperlu fails for user nobody'); + is_program_out 'postgres', create_extension('plperlu'), 0, '', 'CREATE EXTENSION plperlu succeeds for user postgres'; + is ((exec_as 'nobody', "psql nobodydb -qc \"${fn_cmd}u;\""), 1, 'creating PL/PerlU function as user nobody fails'); + is ((exec_as 'postgres', "psql nobodydb -qc \"${fn_cmd};\""), 1, 'creating unsafe PL/Perl function as user postgres fails'); + is_program_out 'postgres', "psql nobodydb -qc \"${fn_cmd}u;\"", 0, '', 'creating PL/PerlU function as user postgres succeeds'; + like_program_out 'nobody', 'psql nobodydb -Atc "select read_file()"', + 0, qr/^root:/, 'calling PL/PerlU function'; + + # Check PL/Perl trusted + my $pluser = ($v >= '8.3') ? 'nobody' : 'postgres'; # pg_pltemplate allows non-superusers to install trusted languages in 8.3+ + is_program_out $pluser, create_extension('plperl'), 0, '', "CREATE EXTENSION plperl succeeds for user $pluser"; + is ((exec_as 'nobody', "psql nobodydb -qc \"${fn_cmd};\""), 1, 'creating unsafe PL/Perl function as user nobody fails'); + is_program_out 'nobody', 'psql nobodydb -qc "CREATE FUNCTION remove_vowels(text) RETURNS text AS \'\\$_[0] =~ s/[aeiou]/_/ig; return \\$_[0];\' LANGUAGE plperl;"', + 0, '', 'creating PL/Perl function as user nobody succeeds'; + is_program_out 'nobody', 'psql nobodydb -Atc "select remove_vowels(\'foobArish\')"', + 0, "f__b_r_sh\n", 'calling PL/Perl function'; + + # Check PL/Python (untrusted) + is_program_out 'postgres', create_extension('plpythonu'), 0, '', 'CREATE EXTENSION plpythonu succeeds for user postgres'; + is_program_out 'postgres', 'psql nobodydb -qc "CREATE FUNCTION capitalize(text) RETURNS text AS \'import sys; return args[0].capitalize() + sys.version[0]\' LANGUAGE plpythonu;"', + 0, '', 'creating PL/Python function as user postgres succeeds'; + is_program_out 'nobody', 'psql nobodydb -Atc "select capitalize(\'foo\')"', + 0, "Foo2\n", 'calling PL/Python function'; + + # Check PL/Python3 (untrusted) + if ($v >= '9.1' and not $PgCommon::rpm) { + is_program_out 'postgres', create_extension('plpython3u'), 0, '', 'CREATE EXTENSION plpython3u succeeds for user postgres'; + is_program_out 'postgres', 'psql nobodydb -qc "CREATE FUNCTION capitalize3(text) RETURNS text AS \'import sys; return args[0].capitalize() + sys.version[0]\' LANGUAGE plpython3u;"', + 0, '', 'creating PL/Python3 function as user postgres succeeds'; + is_program_out 'nobody', 'psql nobodydb -Atc "select capitalize3(\'foo\')"', + 0, "Foo3\n", 'calling PL/Python function'; + } else { + pass "Skipping PL/Python3 test for version $v..."; + pass '...'; + pass '...'; + pass '...'; + pass '...'; + pass '...'; + } + + # Check PL/Tcl (trusted/untrusted) + is_program_out 'postgres', create_extension('pltcl'), 0, '', 'CREATE EXTENSION pltcl succeeds for user postgres'; + is_program_out 'postgres', create_extension('pltclu'), 0, '', 'CREATE EXTENSION pltclu succeeds for user postgres'; + is_program_out 'nobody', 'psql nobodydb -qc "CREATE FUNCTION tcl_max(integer, integer) RETURNS integer AS \'if {\\$1 > \\$2} {return \\$1}; return \\$2\' LANGUAGE pltcl STRICT;"', + 0, '', 'creating PL/Tcl function as user nobody succeeds'; + is_program_out 'postgres', 'psql nobodydb -qc "CREATE FUNCTION tcl_max_u(integer, integer) RETURNS integer AS \'if {\\$1 > \\$2} {return \\$1}; return \\$2\' LANGUAGE pltclu STRICT;"', + 0, '', 'creating PL/TclU function as user postgres succeeds'; + is_program_out 'nobody', 'psql nobodydb -Atc "select tcl_max(3,4)"', 0, + "4\n", 'calling PL/Tcl function'; + is_program_out 'nobody', 'psql nobodydb -Atc "select tcl_max_u(5,4)"', 0, + "5\n", 'calling PL/TclU function'; + + # fake rotated logs to check that they are cleaned up properly + open L, ">$default_log.1" or die "could not open fake rotated log file"; + print L "old log .1\n"; + close L; + open L, ">$default_log.2" or die "could not open fake rotated log file"; + print L "old log .2\n"; + close L; + if (system "gzip -9 $default_log.2") { + die "could not gzip fake rotated log"; + } + + # Check that old-style pgdata symbolic link still works (p-common 0.90+ + # does not create them any more, but they still need to work for existing + # installations) + is ((exec_as 'root', "pg_ctlcluster $v main stop"), 0, 'stopping cluster'); + my $datadir = PgCommon::get_conf_value $v, 'main', 'postgresql.conf', 'data_directory'; + symlink $datadir, "/etc/postgresql/$v/main/pgdata"; + + # data_directory should trump the pgdata symlink + PgCommon::set_conf_value $v, 'main', 'postgresql.conf', 'data_directory', '/nonexisting'; + like_program_out 0, "pg_ctlcluster $v main start", 1, + qr/\/nonexisting is not accessible/, + 'cluster fails to start with invalid data_directory and valid pgdata symlink'; + + # if only pgdata symlink is present, it is authoritative + PgCommon::disable_conf_value $v, 'main', 'postgresql.conf', 'data_directory', 'disabled for test'; + is_program_out 0, "pg_ctlcluster $v main start", 0, '', + 'cluster restarts with pgdata symlink'; + + # check properties of backend processes + pipe RH, WH; + my $psql = fork; + if (!$psql) { + close WH; + my @pw = getpwnam 'nobody'; + change_ugid $pw[2], $pw[3]; + open(STDIN, "<& RH"); + dup2(POSIX::open('/dev/null', POSIX::O_WRONLY), 1); + exec 'psql', 'nobodydb' or die "could not exec psql process: $!"; + } + close RH; + select WH; $| = 1; # make unbuffered + + my $master_pid = `ps --user postgres hu | grep 'bin/postgres.*-D' | grep -v grep | awk '{print \$2}'`; + chomp $master_pid; + + my $client_pid; + while (!$client_pid) { + usleep $delay; + $client_pid = `ps --user postgres hu | grep 'postgres.*: nobody nobodydb' | grep -v grep | awk '{print \$2}'`; + ($client_pid) = ($client_pid =~ /(\d+)/); # untaint + } + + # OOM score adjustment under Linux: postmaster gets bigger shields for >= + # 9.0, but client backends stay at default; this might not work in + # containers with restricted privileges, so skip the check there + my $adj; + my $detect_virt = system 'systemd-detect-virt --container --quiet'; # from systemd + open F, "/proc/$master_pid/oom_score_adj"; + $adj = <F>; + chomp $adj; + close F; + if ($v >= '9.0' and not $PgCommon::rpm) { + SKIP: { + skip 'skipping postmaster OOM killer adjustment in container', 1 if $detect_virt == 0; + cmp_ok $adj, '<=', -500, 'postgres master has OOM killer protection'; + } + } else { + is $adj, 0, 'postgres master has no OOM adjustment'; + } + + open F, "/proc/$client_pid/oom_score_adj"; + $adj = <F>; + chomp $adj; + close F; + is $adj, 0, 'postgres client backend has no OOM adjustment'; + + # test process title update + like_program_out 0, "ps h $client_pid", 0, qr/ idle\s*$/, 'process title is idle'; + print WH "BEGIN;\n"; + usleep $delay; + like_program_out 0, "ps h $client_pid", 0, qr/idle in transaction/, 'process title is idle in transaction'; + print WH "SELECT pg_sleep(2); COMMIT;\n"; + usleep $delay; + like_program_out 0, "ps h $client_pid", 0, qr/SELECT/, 'process title is SELECT'; + + close WH; + waitpid $psql, 0; + + # Drop database and user again. + usleep $delay; + is ((exec_as 'nobody', 'dropdb nobodydb', $outref, 0), 0, 'dropdb nobodydb', ); + is ((exec_as 'postgres', 'dropuser nobody', $outref, 0), 0, 'dropuser nobody'); + + # log file gets re-created by pg_ctlcluster + is ((exec_as 0, "pg_ctlcluster $v main stop"), 0, 'stopping cluster'); + unlink $default_log; + is ((exec_as 0, "pg_ctlcluster $v main start"), 0, 'starting cluster as postgres works without a log file'); + ok (-e $default_log && ! -z $default_log, 'log file got recreated and used'); + + # create tablespaces + my $spc1 = tempdir("/tmp/$v.spc1.XXXXXX", CLEANUP => 1); + my $spc2 = tempdir("/tmp/$v.spc2.XXXXXX", CLEANUP => 1); + is (mkdir ("$spc2/PG_99_fakedirectory"), 1, 'creating a directory in spc2'); + chown $postgres_uid, 0, $spc1, $spc2, "$spc2/PG_99_fakedirectory"; + is_program_out 'postgres', "psql -qc \"CREATE TABLESPACE spc1 LOCATION '$spc1'\"", 0, '', 'creating tablespace spc1'; + is_program_out 'postgres', "psql -qc 'CREATE TABLE tbl1 (x int) TABLESPACE spc1'", 0, '', 'creating a table in spc1'; + SKIP: { + skip "Non-empty tablespaces not supported before 9.0", 4 if ($v < 9.0); + is_program_out 'postgres', "psql -qc \"CREATE TABLESPACE spc2 LOCATION '$spc2'\"", 0, '', 'creating tablespace spc2'; + is_program_out 'postgres', "psql -qc 'CREATE TABLE tbl2 (x int) TABLESPACE spc2'", 0, '', 'creating a table in spc2'; + } + + # stop server, clean up, check for leftovers + ok ((system "pg_dropcluster $v main --stop") == 0, + 'pg_dropcluster removes cluster'); + + is (-e $xlogdir, undef, "xlog/wal directory $xlogdir was deleted"); + ok_dir $spc1, [], "tablespace spc1 was emptied"; + ok_dir $spc2, [qw(PG_99_fakedirectory)], "tablespace spc2 was emptied"; + check_clean; +} + +foreach (@MAJORS) { + check_major $_; +} + +# vim: filetype=perl diff --git a/t/021_pg_renamecluster.t b/t/021_pg_renamecluster.t new file mode 100644 index 0000000..9917c0a --- /dev/null +++ b/t/021_pg_renamecluster.t @@ -0,0 +1,43 @@ +use strict; + +use lib 't'; +use TestLib; +use PgCommon; + +use Test::More tests => 20; + +my $v = $MAJORS[-1]; + +# create cluster +ok ((system "pg_createcluster $v main --start >/dev/null") == 0, + "pg_createcluster $v main"); + +# test pg_renamecluster with a running cluster +program_ok (0, "pg_renamecluster $v main donau"); +is_program_out 'postgres', 'psql -tAc "show data_directory"', 0, + "/var/lib/postgresql/$v/donau\n", 'cluster is running and data_directory was moved'; +is ((PgCommon::get_conf_value $v, 'donau', 'postgresql.conf', 'hba_file'), + "/etc/postgresql/$v/donau/pg_hba.conf", 'pg_hba.conf location updated'); +is ((PgCommon::get_conf_value $v, 'donau', 'postgresql.conf', 'ident_file'), + "/etc/postgresql/$v/donau/pg_ident.conf", 'pg_ident.conf location updated'); +is ((PgCommon::get_conf_value $v, 'donau', 'postgresql.conf', 'external_pid_file'), + "/var/run/postgresql/$v-donau.pid", 'external_pid_file location updated'); +ok (-f "/var/run/postgresql/$v-donau.pid", 'external_pid_file exists'); +SKIP: { + skip "no stats_temp_directory in $v", 2 if ($v < 8.4); + is ((PgCommon::get_conf_value $v, 'donau', 'postgresql.conf', 'stats_temp_directory'), + "/var/run/postgresql/$v-donau.pg_stat_tmp", 'stats_temp_directory location updated'); + ok (-d "/var/run/postgresql/$v-donau.pg_stat_tmp", 'stats_temp_directory exists'); +} +SKIP: { + skip "cluster name not supported in $v", 1 if ($v < 9.5); + is (PgCommon::get_conf_value ($v, 'donau', 'postgresql.conf', 'cluster_name'), "$v/donau", "cluster_name is updated"); +} + +# stop server, clean up, check for leftovers +ok ((system "pg_dropcluster $v donau --stop") == 0, + 'pg_dropcluster removes cluster'); + +check_clean; + +# vim: filetype=perl diff --git a/t/022_recovery.t b/t/022_recovery.t new file mode 100644 index 0000000..b70a8b5 --- /dev/null +++ b/t/022_recovery.t @@ -0,0 +1,54 @@ +# We create a cluster, stop it ungracefully, and check if recovery works. + +use strict; + +use POSIX qw/dup2/; +use Time::HiRes qw/usleep/; + +use lib 't'; +use TestLib; +use PgCommon; + +use Test::More tests => 17 * @MAJORS; + +sub check_major { + my $v = $_[0]; + note "Running tests for $v"; + + # create cluster + program_ok (0, "pg_createcluster $v main --start >/dev/null"); + + # try an immediate shutdown and restart + program_ok (0, "pg_ctlcluster $v main stop -m i"); + program_ok (0, "pg_ctlcluster $v main start"); + my $c = 0; # fallback for when pg_isready is missing (PG < 9.3) + while (system ("pg_isready -q 2>&1") >> 8 == 1 and $c++ < 15) { + sleep(1); + } + program_ok ('postgres', "psql -c ''"); + + # try again with an write-protected file + program_ok (0, "pg_ctlcluster $v main stop -m i"); + open F, ">/var/lib/postgresql/$v/main/foo"; + print F "moo\n"; + close F; + ok ((chmod 0444, "/var/lib/postgresql/$v/main/foo"), + "create write-protected file in data directory"); + program_ok (0, "pg_ctlcluster $v main start"); + $c = 0; + while (system ("pg_isready -q 2>&1") >> 8 == 1 and $c++ < 15) { + sleep(1); + } + program_ok ('postgres', "psql -c ''"); + + program_ok (0, "pg_dropcluster $v main --stop", 0, + 'pg_dropcluster removes cluster'); + + check_clean; +} + +foreach (@MAJORS) { + check_major $_; +} + +# vim: filetype=perl diff --git a/t/025_logging.t b/t/025_logging.t new file mode 100644 index 0000000..fb4a004 --- /dev/null +++ b/t/025_logging.t @@ -0,0 +1,96 @@ +# Test various logging-related things + +use strict; + +use lib 't'; +use TestLib; +use PgCommon; +use Time::HiRes qw/usleep/; + +use Test::More tests => 55 * @MAJORS; + +my $syslog_works = 0; + +sub check_logging ($$) +{ + my ($text, $msg) = @_; + my $ls = `pg_lsclusters -h`; + $ls =~ s/\s+/ /g; + $ls =~ s/\s*$//; + like $ls, $text, $msg; +} + +sub check_major { + my $v = $_[0]; + note "Running tests for $v"; + my $pgdata = "/var/lib/postgresql/$v/main"; + + # create cluster + ok ((system "pg_createcluster $v main --start >/dev/null") == 0, + "pg_createcluster $v main"); + + # default log setup + my $default_log = "/var/log/postgresql/postgresql-$v-main.log"; + check_logging qr($v main 5432 online postgres $pgdata $default_log), "pg_lscluster reports logfile $default_log"; + like_program_out 'postgres', "psql -qc \"'foobar_${v}_$$'\"", 1, qr/syntax error.*foobar_${v}_$$/, 'log an error'; + like_program_out 'postgres', "grep --binary-files=text foobar_${v}_$$ $default_log", 0, qr/syntax error.*foobar_${v}_$$/, 'error appears in logfile'; + + # syslog + is_program_out 0, "pg_conftool $v main set log_destination syslog", 0, "", "set log_destination syslog"; + is_program_out 0, "pg_ctlcluster $v main reload", 0, "", "$v main reload"; + is_program_out 'postgres', "psql -Atc \"show log_destination\"", 0, "syslog\n", 'log_destination is syslog'; + check_logging qr($v main 5432 online postgres $pgdata syslog), "pg_lscluster reports syslog"; + SKIP: { + skip "/var/log/syslog not available", 2 unless ($syslog_works); + usleep $delay; + like_program_out 0, "grep --binary-files=text 'postgres.*parameter \"log_destination\" changed to \"syslog\"' /var/log/syslog", 0, qr/log_destination/, 'error appears in /var/log/syslog'; + } + + # turn logging_collector on, csvlog + my $pg_log = $v >= 10 ? 'log' : 'pg_log'; # log directory in PGDATA changed in PG 10 + SKIP: { + skip "No logging collector in 8.2", 30 if ($v <= 8.2); + is_program_out 0, "pg_conftool $v main set logging_collector on", 0, "", "set logging_collector on"; + is_program_out 0, "pg_conftool $v main set log_destination csvlog", 0, "", "set log_destination csvlog"; + is_program_out 0, "pg_ctlcluster $v main restart", 0, "", "$v main restart"; + is_program_out 'postgres', "psql -Atc \"show logging_collector\"", 0, "on\n", 'logging_collector is on'; + is_program_out 'postgres', "psql -Atc \"show log_destination\"", 0, "csvlog\n", 'log_destination is csvlog'; + check_logging qr($v main 5432 online postgres $pgdata $pg_log/.*\.csv), "pg_lscluster reports csvlog"; + like_program_out 'postgres', "psql -qc \"'barbaz_${v}_$$'\"", 1, qr/syntax error.*barbaz_${v}_$$/, 'log an error'; + like_program_out 'postgres', "grep --binary-files=text barbaz_${v}_$$ $pgdata/$pg_log/*.csv", 0, qr/syntax error.*barbaz_${v}_$$/, "error appears in $pg_log/*.csv"; + + # stderr,syslog,csvlog + is_program_out 0, "pg_conftool $v main set log_destination stderr,syslog,csvlog", 0, "", "set log_destination stderr,syslog,csvlog"; + is_program_out 0, "pg_ctlcluster $v main reload", 0, "", "$v main reload"; + is_program_out 'postgres', "psql -Atc \"show log_destination\"", 0, "stderr,syslog,csvlog\n", 'log_destination is stderr,syslog,csvlog'; + check_logging qr($v main 5432 online postgres $pgdata $pg_log/.*\.log,syslog,$pg_log/.*\.csv), "pg_lscluster reports stderr,syslog,csvlog"; + like_program_out 'postgres', "psql -qc \"'moo_${v}_$$'\"", 1, qr/syntax error.*moo_${v}_$$/, 'log an error'; + like_program_out 'postgres', "grep --binary-files=text moo_${v}_$$ $pgdata/$pg_log/*.log", 0, qr/syntax error.*moo_${v}_$$/, "error appears in $pg_log/*.log"; + SKIP: { + skip "/var/log/syslog not available", 2 unless ($syslog_works); + usleep $delay; + like_program_out 0, "grep --binary-files=text 'postgres.*moo_${v}_$$' /var/log/syslog", 0, qr/moo_${v}_$$/, 'error appears in /var/log/syslog'; + } + like_program_out 'postgres', "grep --binary-files=text moo_${v}_$$ $pgdata/$pg_log/*.csv", 0, qr/syntax error.*moo_${v}_$$/, "error appears in $pg_log/*.csv"; + } + + # stop server, clean up, check for leftovers + is_program_out 0, "pg_dropcluster $v main --stop", 0, "", 'pg_dropcluster removes cluster'; + + check_clean; +} + +system "logger -t '$0' 'test-logging-$$'"; +usleep $delay; +if (system ("grep --binary-files=text -q 'test-logging-$$' /var/log/syslog 2> /dev/null") == 0) { + note 'Logging to /var/log/syslog works'; + $syslog_works = 1; +} else { + note 'Logging to /var/log/syslog does not work, skipping some syslog tests'; +} + +foreach (@MAJORS) { + check_major $_; +} + +# vim: filetype=perl diff --git a/t/030_errors.t b/t/030_errors.t new file mode 100644 index 0000000..de306b4 --- /dev/null +++ b/t/030_errors.t @@ -0,0 +1,319 @@ +# Check all kinds of error conditions. + +use strict; + +require File::Temp; + +use lib 't'; +use TestLib; +use Test::More tests => 147; +use PgCommon; + +my $version = $MAJORS[-1]; + +my $socketdir = '/tmp/postgresql-testsuite/'; +my ($pg_uid, $pg_gid) = (getpwnam 'postgres')[2,3]; + +# create a pid file with content $1 and return its path +sub create_pidfile { + my $fname = "/var/lib/postgresql/$version/main/postmaster.pid"; + open F, ">$fname" or die "open: $!"; + print F $_[0]; + close F; + chown $pg_uid, $pg_gid, $fname or die "chown: $!"; + chmod 0700, $fname or die "chmod: $!"; + return $fname; +} + +sub check_nonexisting_cluster_error { + my $outref; + my $result = exec_as 0, $_[0], $outref; + is $result, 1, "'$_[0]' fails"; + like $$outref, qr/(invalid version|does not exist)/i, "$_[0] gives error message about nonexisting cluster"; + unlike $$outref, qr/invalid symbolic link/i, "$_[0] does not print 'invalid symbolic link' gibberish"; +} + +# create cluster +ok ((system "pg_createcluster --socketdir '$socketdir' $version main >/dev/null") == 0, + "pg_createcluster --socketdir"); +like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/$version\s*main.*5432.*down/, 'cluster was created'; + +is ((get_cluster_port $version, 'main'), 5432, 'Port of created cluster is 5432'); + +# creating cluster with the same name should fail +like_program_out 'root', "pg_createcluster --socketdir '$socketdir' $version main", 1, qr/already exists/, + "pg_createcluster on existing cluster"; +# and the original one still exists +like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/$version\s*main.*5432.*down/, 'original cluster still exists'; + +# attempt to create clusters with an invalid port +like_program_out 0, "pg_createcluster $version test -p foo", 1, + qr/invalid.*number expected/, + 'pg_createcluster -p checks that port option is numeric'; +like_program_out 0, "pg_createcluster $version test -p 42", 1, + qr/must be a positive integer between/, + 'pg_createcluster -p checks valid port range'; + +# chown cluster to an invalid user to test error +my $badid = 98; +(system "chown -R $badid /var/lib/postgresql/$version/main") == 0 or die "chown failed: $!"; +is ((system "pg_ctlcluster $version main start 2>/dev/null"), 256, + 'pg_ctlcluster fails on invalid cluster owner uid'); +(system "chown -R postgres:$badid /var/lib/postgresql/$version/main") == 0 or die "chown failed: $!"; +is ((system "pg_ctlcluster $version main start 2>/dev/null"), 256, + 'pg_ctlcluster as root fails on invalid cluster owner gid'); +is ((exec_as 'postgres', "pg_ctlcluster $version main start"), 1, + 'pg_ctlcluster as postgres fails on invalid cluster owner gid'); +(system "chown -R postgres:postgres /var/lib/postgresql/$version/main") == 0 or die "chown failed: $!"; +is ((system "pg_ctlcluster $version main start"), 0, + 'pg_ctlcluster succeeds on valid cluster owner uid/gid'); + +# check socket +my @contents = ('.s.PGSQL.5432', '.s.PGSQL.5432.lock', "$version-main.pid", "$version-main.pg_stat_tmp"); +pop @contents if ($version < 8.4); # remove pg_stat_tmp +ok_dir '/var/run/postgresql', [grep {/main/} @contents], 'No sockets in /var/run/postgresql'; +ok_dir $socketdir, ['.s.PGSQL.5432', '.s.PGSQL.5432.lock'], "Socket is in $socketdir"; + +# stop cluster, check sockets +ok ((system "pg_ctlcluster $version main stop") == 0, + 'cluster stops with custom unix_socket_dir'); +ok_dir $socketdir, [], "No sockets in $socketdir after stopping cluster"; + +# remove default socket dir and check that the socket defaults to +# /var/run/postgresql +open F, "+</etc/postgresql/$version/main/postgresql.conf" or + die "could not open postgresql.conf for r/w: $!"; +my @lines = <F>; +seek F, 0, 0 or die "seek: $!"; +truncate F, 0; +@lines = grep !/^unix_socket_dir/, @lines; # <= 9.2: "_directory", >= 9.3: "_directories" +print F @lines; +close F; + +ok ((system "pg_ctlcluster $version main start") == 0, + 'cluster starts after removing unix_socket_dir'); +if ($PgCommon::rpm) { + ok ((grep { $_ eq '.s.PGSQL.5432' } @{TestLib::dircontent('/tmp')}) == 1, 'Socket is in /tmp'); +} else { + ok_dir '/var/run/postgresql', [@contents], + 'Socket is in default dir /var/run/postgresql'; +} +ok_dir $socketdir, [], "No sockets in $socketdir"; + +# server should not stop with corrupt file +rename "/var/lib/postgresql/$version/main/postmaster.pid", + "/var/lib/postgresql/$version/main/postmaster.pid.orig" or die "rename: $!"; +create_pidfile 'foo'; +is_program_out 'postgres', "pg_ctlcluster $version main stop", 1, + "Error: pid file is invalid, please manually kill the stale server process.\n", + 'pg_ctlcluster fails with corrupted PID file'; +like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/online/, 'cluster is still online'; + +# restore PID file +(system "cp /var/lib/postgresql/$version/main/postmaster.pid.orig /var/lib/postgresql/$version/main/postmaster.pid") == 0 or die "cp: $!"; +is ((exec_as 'postgres', "pg_ctlcluster $version main stop"), 0, + 'pg_ctlcluster succeeds with restored PID file'); +like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/down/, 'cluster is down'; + +# stop stopped server +is_program_out 'postgres', "pg_ctlcluster $version main stop", 2, + "Cluster is not running.\n", 'pg_ctlcluster stop fails on stopped cluster'; + +# simulate crashed server +rename "/var/lib/postgresql/$version/main/postmaster.pid.orig", + "/var/lib/postgresql/$version/main/postmaster.pid" or die "rename: $!"; +is_program_out 'postgres', "pg_ctlcluster $version main start", 0, + "Removed stale pid file.\n", 'pg_ctlcluster succeeds with already existing PID file'; +like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/online/, 'cluster is online'; +is ((exec_as 'postgres', "pg_ctlcluster $version main stop"), 0, + 'pg_ctlcluster stop succeeds'); +like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/down/, 'cluster is down'; +ok (! -e "/var/lib/postgresql/$version/main/postmaster.pid", 'no pid file left'); + +# trying to stop a stopped server cleans up corrupt and stale pid files +my $pf = create_pidfile 'foo'; +is_program_out 'postgres', "pg_ctlcluster $version main stop", 2, + "Removed stale pid file.\nCluster is not running.\n", + 'pg_ctlcluster stop succeeds with corrupted PID file'; +ok (! -e $pf, 'pid file was cleaned up'); + +create_pidfile 'foo'; +is_program_out 'postgres', "pg_ctlcluster --force $version main stop", 2, + "Removed stale pid file.\nCluster is not running.\n", + 'pg_ctlcluster --force stop succeeds with corrupted PID file'; +ok (! -e $pf, 'pid file was cleaned up'); + +create_pidfile '99998'; +is_program_out 'postgres', "pg_ctlcluster $version main stop", 2, + "Removed stale pid file.\nCluster is not running.\n", + 'pg_ctlcluster stop succeeds with stale PID file'; +ok (! -e $pf, 'pid file was cleaned up'); + +create_pidfile '99998'; +is_program_out 'postgres', "pg_ctlcluster --force $version main stop", 2, + "Removed stale pid file.\nCluster is not running.\n", + 'pg_ctlcluster --force stop succeeds with stale PID file'; +ok (! -e $pf, 'pid file was cleaned up'); + +create_pidfile ''; +is_program_out 'postgres', "pg_ctlcluster --force $version main stop", 2, + "Removed stale pid file.\nCluster is not running.\n", + 'pg_ctlcluster stop succeeds with empty PID file'; +ok (! -e $pf, 'pid file was cleaned up'); + +# corrupt PID file while server is down +create_pidfile 'foo'; +is_program_out 'postgres', "pg_ctlcluster $version main start", 0, + "Removed stale pid file.\n", 'pg_ctlcluster succeeds with corrupted PID file'; +like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/online/, 'cluster is online'; + +# start running server +is_program_out 'postgres', "pg_ctlcluster $version main start", 2, + "Cluster is already running.\n", 'pg_ctlcluster start fails on running cluster'; +is ((exec_as 'postgres', "pg_ctlcluster $version main stop"), 0, 'pg_ctlcluster stop'); + +# backup pg_hba.conf +rename "/etc/postgresql/$version/main/pg_hba.conf", + "/etc/postgresql/$version/main/pg_hba.conf.orig" or die "rename: $!"; + +# test check for invalid pg_hba.conf +open F, ">/etc/postgresql/$version/main/pg_hba.conf" or die "could not create pg_hba.conf: $!"; +print F "foo\n"; +close F; +chmod 0644, "/etc/postgresql/$version/main/pg_hba.conf" or die "chmod: $!"; + +if ($version < '8.4') { + like_program_out 'postgres', "pg_ctlcluster $version main start", 0, + qr/WARNING.*connection to the database failed.*pg_hba.conf/is, + 'pg_ctlcluster start warns about invalid pg_hba.conf'; + is_program_out 'postgres', "pg_ctlcluster $version main stop", 0, '', 'stopping cluster'; +} else { + like_program_out 'postgres', "pg_ctlcluster $version main start", 1, + qr/FATAL.*pg_hba.conf/is, + 'pg_ctlcluster start fails on invalid pg_hba.conf'; + is_program_out 'postgres', "pg_ctlcluster $version main stop", 2, + "Cluster is not running.\n", 'stopping cluster'; +} + +# test check for pg_hba.conf with removed passwordless local superuser access +open F, ">/etc/postgresql/$version/main/pg_hba.conf" or die "could not create pg_hba.conf: $!"; +print F "local all all md5\n"; +close F; +chmod 0644, "/etc/postgresql/$version/main/pg_hba.conf" or die "chmod: $!"; + +like_program_out 'postgres', "pg_ctlcluster $version main start", 0, + qr/WARNING.*connection to the database failed.*postgres/is, + 'pg_ctlcluster start warns about absence of passwordless superuser connection'; +is_program_out 'postgres', "pg_ctlcluster $version main stop", 0, '', 'stopping cluster'; + +# restore pg_hba.conf +unlink "/etc/postgresql/$version/main/pg_hba.conf"; +rename "/etc/postgresql/$version/main/pg_hba.conf.orig", + "/etc/postgresql/$version/main/pg_hba.conf" or die "rename: $!"; + +# leftover files must not create confusion +open F, '>/etc/postgresql/postgresql.conf'; +print F "data_directory = '/nonexisting'\n"; +close F; +my @c = get_version_clusters $version; +is_deeply (\@c, ['main'], + 'leftover /etc/postgresql/postgresql.conf is not regarded as a cluster'); +unlink '/etc/postgresql/postgresql.conf'; + +# fails by default due to access restrictions +# remove cluster and directory; this should work as user "postgres" +is_program_out 'postgres', "pg_dropcluster $version main", 0, '', + , "pg_dropcluster works as user postgres"; + +# graceful handling of absent data dir (might not be mounted) +ok ((system "pg_createcluster $version main >/dev/null") == 0, + "pg_createcluster succeeds"); +rename "/var/lib/postgresql/$version", "/var/lib/postgresql/$version.orig" or die "rename: $!"; +my $outref; +is ((exec_as 0, "pg_ctlcluster $version main start", $outref, 1), 1, + 'pg_ctlcluster fails on nonexisting /var/lib/postgresql'); +like $$outref, qr/^Error:.*\/var\/lib\/postgresql.*not accessible.*$/, 'proper error message for nonexisting /var/lib/postgresql'; + +rename "/var/lib/postgresql/$version.orig", "/var/lib/postgresql/$version" or die "rename: $!"; +is_program_out 'postgres', "pg_ctlcluster $version main start", 0, '', + 'pg_ctlcluster start succeeds again with reappeared /var/lib/postgresql'; +is_program_out 'postgres', "pg_ctlcluster $version main stop", 0, '', 'stopping cluster'; + +# pg_ctlcluster checks colliding ports +ok ((system "pg_createcluster $version other >/dev/null") == 0, + "pg_createcluster other"); +set_cluster_port $version, 'other', '5432'; +is ((exec_as 'postgres', "pg_ctlcluster $version main start"), 0, + 'pg_ctlcluster: main cluster on conflicting port starts'); + +# clusters can run side by side on different socket directories +set_cluster_socketdir $version, 'other', $socketdir; +PgCommon::set_conf_value $version, 'other', 'postgresql.conf', + 'listen_addresses', ''; # otherwise they will conflict on TCP socket +is ((exec_as 'postgres', "pg_ctlcluster $version other start"), 0, + 'pg_ctlcluster: other cluster starts on conflicting port, but different socket dirs'); +is ((exec_as 'postgres', "pg_ctlcluster $version other stop"), 0); + +# ... but will give an error when running on the same port +set_cluster_socketdir $version, 'other', ($PgCommon::rpm and $version < 9.4) ? '/tmp' : '/var/run/postgresql'; +like_program_out 'postgres', "pg_ctlcluster $version other start", 1, + qr/Port conflict:.*port 5432/, + 'pg_ctlcluster other cluster fails on conflicting port and same socket dir'; +is_program_out 'postgres', "pg_ctlcluster $version main stop", 0, '', + 'stopping main cluster'; +is ((exec_as 'postgres', "pg_ctlcluster $version other start"), 0, + 'pg_ctlcluster: other cluster on conflicting port starts after main is down'); +ok ((system "pg_dropcluster $version other --stop") == 0, + 'pg_dropcluster other'); + +# clean up +ok ((system "pg_dropcluster $version main") == 0, + 'pg_dropcluster'); +ok_dir $socketdir, [], 'No sockets any more'; +rmdir $socketdir or die "rmdir: $!"; + +# ensure sane error messages for nonexisting clusters +check_nonexisting_cluster_error 'pg_lsclusters 4.5 foo'; +check_nonexisting_cluster_error 'psql --cluster 4.5/foo'; +check_nonexisting_cluster_error "psql --cluster $MAJORS[0]/foo"; +check_nonexisting_cluster_error "pg_dropcluster 4.5 foo"; +check_nonexisting_cluster_error "pg_dropcluster $MAJORS[0] foo"; +check_nonexisting_cluster_error "pg_upgradecluster 4.5 foo"; +check_nonexisting_cluster_error "pg_upgradecluster $MAJORS[0] foo"; +check_nonexisting_cluster_error "pg_ctlcluster 4.5 foo stop"; +check_nonexisting_cluster_error "pg_ctlcluster $MAJORS[0] foo stop"; + +check_clean; + +# check that pg_dropcluster copes with partially existing cluster +# configurations (which can happen if the disk becomes full) + +mkdir '/etc/postgresql/'; +mkdir "/etc/postgresql/$MAJORS[-1]"; +mkdir "/etc/postgresql/$MAJORS[-1]/broken" or die "mkdir: $!"; +symlink "/var/lib/postgresql/$MAJORS[-1]/broken", "/etc/postgresql/$MAJORS[-1]/broken/pgdata" or die "symlink: $!"; + +unlike_program_out 0, "pg_dropcluster $MAJORS[-1] broken", 0, qr/error/i, + 'pg_dropcluster cleans up broken cluster configuration (only /etc with pgdata)'; + +check_clean; + +mkdir '/etc/postgresql/'; +mkdir '/var/lib/postgresql/'; +mkdir "/etc/postgresql/$MAJORS[-1]" and +mkdir "/etc/postgresql/$MAJORS[-1]/broken"; +mkdir "/var/lib/postgresql/$MAJORS[-1]"; +mkdir "/var/lib/postgresql/$MAJORS[-1]/broken"; +mkdir "/var/lib/postgresql/$MAJORS[-1]/broken/base" or die "mkdir: $!"; +open F, ">/etc/postgresql/$MAJORS[-1]/broken/postgresql.conf" or die "open: $!"; +print F "data_directory = '/var/lib/postgresql/$MAJORS[-1]/broken'\n"; +close F; +open F, ">/var/lib/postgresql/$MAJORS[-1]/broken/PG_VERSION" or die "open: $!"; +close F; + +unlike_program_out 0, "pg_dropcluster $MAJORS[-1] broken", 0, qr/error/i, + 'pg_dropcluster cleans up broken cluster configuration (/etc with pgdata and postgresql.conf and partial /var)'; + +check_clean; + +# vim: filetype=perl diff --git a/t/031_errors_disk_full.t b/t/031_errors_disk_full.t new file mode 100644 index 0000000..bc0a860 --- /dev/null +++ b/t/031_errors_disk_full.t @@ -0,0 +1,86 @@ +# Check for proper ENOSPC handling + +use strict; + +require File::Temp; + +use lib 't'; +use TestLib; +use Test::More tests => $ENV{NO_TMPFS} ? 1 : 22; + +# skip tests if NO_TMPFS is set +if ($ENV{NO_TMPFS}) { + pass 'Skipping disk full tests, NO_TMPFS is set'; + exit; +} + +# we are using unshare here, won't work with systemd +$ENV{_SYSTEMCTL_SKIP_REDIRECT} = 1; + +my $outref; + +# +note 'check that a failed pg_createcluster leaves no cruft behind: try creating a cluster on a 10 MB tmpfs'; +my $cmd = <<EOF; +exec 2>&1 +set -e +mount --make-rprivate / 2> /dev/null || : +mkdir -p /var/lib/postgresql +trap "umount /var/lib/postgresql" 0 HUP INT QUIT ILL ABRT PIPE TERM +mount -t tmpfs -o size=10000000 none /var/lib/postgresql +# this is supposed to fail +LC_MESSAGES=C pg_createcluster $MAJORS[-1] test && exit 1 || true +echo -n "ls>" +# should not output anything +ls /etc/postgresql +ls /var/lib/postgresql +echo "<ls" +EOF + +my $result; +$result = exec_as 'root', "echo '$cmd' | unshare -m sh", $outref; + +is $result, 0, 'script failed'; +like $$outref, qr/No space left on device/i, + 'pg_createcluster fails due to insufficient disk space'; +like $$outref, qr/\nls><ls\n/, 'does not leave files behind'; + +check_clean; + +# +note 'check disk full conditions on startup'; +my $cmd = <<EOF; +set -e +mount --make-rprivate / 2> /dev/null || : +export LC_MESSAGES=C +dirs="/etc/postgresql /var/lib/postgresql /var/log/postgresql" +mkdir -p \$dirs +trap "umount \$dirs" 0 HUP INT QUIT ILL ABRT PIPE TERM +mount -t tmpfs -o size=1000000 none /etc/postgresql +# an empty cluster needs 69MB on ppc64el, round up to 90 +mount -t tmpfs -o size=90000000 none /var/lib/postgresql +mount -t tmpfs -o size=1000000 none /var/log/postgresql +pg_createcluster $MAJORS[-1] test + +# fill up /var/lib/postgresql +! cat < /dev/zero > /var/lib/postgresql/cruft 2>/dev/null +echo '-- full lib --' +! pg_ctlcluster $MAJORS[-1] test start +echo '-- end full lib --' +echo '-- full lib log --' +cat /var/log/postgresql/postgresql-$MAJORS[-1]-test.log +echo '-- end full lib log --' +rm /var/lib/postgresql/cruft +pg_dropcluster $MAJORS[-1] test --stop +EOF + +$result = exec_as 'root', "echo '$cmd' | unshare -m sh", $outref; +is $result, 0, 'script failed'; +like $$outref, qr/^-- full lib --.*No space left on device.*^-- end full lib --/ims, + 'pg_ctlcluster prints error message'; +like $$outref, qr/^-- full lib log --.*No space left on device.*^-- end full lib log --/ims, + 'log file has error message'; + +check_clean; + +# vim: filetype=perl diff --git a/t/032_ssl_key_permissions.t b/t/032_ssl_key_permissions.t new file mode 100644 index 0000000..f0faa06 --- /dev/null +++ b/t/032_ssl_key_permissions.t @@ -0,0 +1,56 @@ +use strict; +use warnings; + +use lib 't'; +use TestLib; +use PgCommon; +use Test::More tests => $PgCommon::rpm ? 1 : 3 + 19 * @MAJORS; + +if ($PgCommon::rpm) { pass 'No ssl key checks on RedHat'; exit; } + +my ($pg_uid, $pg_gid) = (getpwnam 'postgres')[2,3]; +my $ssl_cert_gid = (getgrnam 'ssl-cert')[2]; # reset permissions +die "Could not determine ssl-cert gid" unless ($ssl_cert_gid); + +my $snakekey = '/etc/ssl/private/ssl-cert-snakeoil.key'; +is ((stat $snakekey)[4], 0, "$snakekey is owned by root"); +is ((stat $snakekey)[5], $ssl_cert_gid, "$snakekey group is ssl-cert"); +is ((stat $snakekey)[2], 0100640, "$snakekey mode is 0640"); + +foreach my $version (@MAJORS) { + my $pkgversion = `dpkg-query -f '\${Version}' -W postgresql-$version`; + note "$version ($pkgversion)"; +SKIP: { + skip "No SSL key check on <= 9.0", 19 if ($version <= 9.0); + program_ok (0, "pg_createcluster $version main"); + + my $nobody_uid = (getpwnam 'nobody')[2]; + chown $nobody_uid, 0, $snakekey; + like_program_out 'postgres', "pg_ctlcluster $version main start", 1, + qr/private key file.*must be owned by the database user or root/s, + 'ssl key owned by nobody refused'; + +SKIP: { + skip "SSL key group check skipped on Debian oldstable packages", 4 if ($version <= 9.4 and $pkgversion !~ /pgdg/); + chown 0, 0, $snakekey; + chmod 0644, $snakekey; + like_program_out 'postgres', "pg_ctlcluster $version main start", 1, + qr/private key file.*has group or world access/, + 'ssl key with permissions root:root 0644 refused'; + + chown $pg_uid, $pg_gid, $snakekey; + chmod 0640, $snakekey; + like_program_out 'postgres', "pg_ctlcluster $version main start", 1, + qr/private key file.*has group or world access/, + 'ssl key with permissions postgres:postgres 0640 refused'; +} + + chown 0, $ssl_cert_gid, $snakekey; + + program_ok (0, "pg_dropcluster $version main --stop"); + is ((stat $snakekey)[4], 0, "$snakekey is owned by root"); + is ((stat $snakekey)[5], $ssl_cert_gid, "$snakekey group is ssl-cert"); + is ((stat $snakekey)[2], 0100640, "$snakekey mode is 0640"); + check_clean; +} +} diff --git a/t/040_upgrade.t b/t/040_upgrade.t new file mode 100644 index 0000000..3ac01ce --- /dev/null +++ b/t/040_upgrade.t @@ -0,0 +1,264 @@ +# 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 : 121 * 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 config parameters +is_program_out 'postgres', "pg_conftool $MAJORS[0] upgr set log_statement all", + 0, '', 'set postgresql.conf parameter'; +SKIP: { + skip 'postgresql.auto.conf not supported before 9.4', 6 if ($MAJORS[0] < 9.4); + is_program_out 'postgres', "psql -qc \"ALTER SYSTEM SET ident_file = '/etc/postgresql/$MAJORS[0]/upgr/pg_ident.conf'\"", + 0, '', 'set ident_file in postgresql.auto.conf'; + is_program_out 'postgres', 'psql -qc "ALTER SYSTEM SET log_min_duration_statement = \'10s\'"', + 0, '', 'set log_min_duration_statement in postgresql.auto.conf'; + is_program_out 'postgres', "echo \"data_directory = '/var/lib/postgresql/$MAJORS[0]/upgr'\" >> /var/lib/postgresql/$MAJORS[0]/upgr/postgresql.auto.conf", 0, "", "Append bogus data_directory setting to postgresql.auto.conf"; +} +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, "(env LC_MESSAGES=C 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 config parameters +is_program_out 'postgres', 'psql -Atc "SHOW log_statement" test', 0, "all\n", 'check postgresql.conf parameters'; +SKIP: { + skip 'postgresql.auto.conf not supported before 9.4', 4 if ($MAJORS[0] < 9.4); + is_program_out 'postgres', 'psql -Atc "SHOW log_min_duration_statement" test', 0, "10s\n", 'check postgresql.auto.conf parameter'; + is_program_out 'postgres', "cat /var/lib/postgresql/$MAJORS[-1]/upgr/postgresql.auto.conf", 0, + "# Do not edit this file manually!\n# It will be overwritten by the ALTER SYSTEM command.\nident_file = '/etc/postgresql/$MAJORS[-1]/upgr/pg_ident.conf'\nlog_min_duration_statement = '10s'\n#data_directory = '/var/lib/postgresql/$MAJORS[0]/upgr' #not valid in postgresql.auto.conf\n"; +} +is_program_out 'postgres', 'psql -Atc "SHOW DateStyle" test', 0, "ISO, YMD\n", '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 diff --git a/t/041_upgrade_custompaths.t b/t/041_upgrade_custompaths.t new file mode 100644 index 0000000..885e70f --- /dev/null +++ b/t/041_upgrade_custompaths.t @@ -0,0 +1,51 @@ +# Test cluster upgrade with a custom data directory and custom log file. + +use strict; + +use lib 't'; +use TestLib; +use PgCommon; +use Test::More tests => (@MAJORS == 1) ? 1 : 28; + +if (@MAJORS == 1) { + pass 'only one major version installed, skipping upgrade tests'; + exit 0; +} + +ok ((system "pg_createcluster --start --datadir /tmp/postgresql-test -l /tmp/postgresql-test.log $MAJORS[0] upgr >/dev/null") == 0); + +# Upgrade to latest version +my $outref; +is ((exec_as 0, "pg_upgradecluster -v $MAJORS[-1] $MAJORS[0] upgr", $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'; + +# Check clusters +like_program_out 'nobody', 'pg_lsclusters -h', 0, + qr"$MAJORS[0] +upgr 5433 down postgres /tmp/postgresql-test +/tmp/postgresql-test.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'; + +# clean away new cluster and restart the old one +is ((system "pg_dropcluster $MAJORS[-1] upgr --stop"), 0, 'Dropping upgraded cluster'); +is_program_out 0, "pg_ctlcluster $MAJORS[0] upgr start", 0, '', 'Restarting old cluster'; +is_program_out 'nobody', 'pg_lsclusters -h', 0, + "$MAJORS[0] upgr 5433 online postgres /tmp/postgresql-test /tmp/postgresql-test.log +", 'pg_lsclusters output'; + +# Do another upgrade with using a custom defined data directory +my $outref; +is ((exec_as 0, "pg_upgradecluster -v $MAJORS[-1] $MAJORS[0] upgr /tmp/psql-common-testsuite", $outref, 0), 0, 'pg_upgradecluster succeeds'); +unlike $$outref, qr/^pg_restore: /m, 'no pg_restore error messages during upgrade'; +unlike $$outref, qr/^[A-Z]+: /m, 'no server error messages during upgrade'; +like $$outref, qr/Starting target cluster/, 'pg_upgradecluster reported cluster startup'; +like $$outref, qr/Success. Please check/, 'pg_upgradecluster reported successful operation'; + +like_program_out 'nobody', 'pg_lsclusters -h', 0, + qr"$MAJORS[0] +upgr 5432 down postgres /tmp/postgresql-test +/tmp/postgresql-test.log\n$MAJORS[-1] +upgr 5433 online postgres /tmp/psql-common-testsuite +/var/log/postgresql/postgresql-$MAJORS[-1]-upgr.log", 'pg_lsclusters output'; + +# stop servers, clean up +is ((system "pg_dropcluster $MAJORS[0] upgr"), 0, 'Dropping original cluster'); +is ((system "pg_dropcluster $MAJORS[-1] upgr --stop"), 0, 'Dropping upgraded cluster'); + +check_clean; + +# vim: filetype=perl diff --git a/t/043_upgrade_ssl_cert.t b/t/043_upgrade_ssl_cert.t new file mode 100644 index 0000000..76fee3f --- /dev/null +++ b/t/043_upgrade_ssl_cert.t @@ -0,0 +1,79 @@ +# Test cluster upgrade with a custom ssl certificate + +use strict; +use File::Temp qw/tempdir/; +use lib 't'; +use TestLib; +use PgCommon; +use Test::More tests => (@MAJORS == 1 or $PgCommon::rpm) ? 1 : 22; + +if (@MAJORS == 1) { + pass 'only one major version installed, skipping upgrade tests'; + exit 0; +} +if ($PgCommon::rpm) { + pass 'SSL certificates not handled on RedHat'; + exit 0; +} + + +ok ((system "pg_createcluster $MAJORS[0] upgr >/dev/null") == 0); + +my $tdir = tempdir (CLEANUP => 1); +my ($p_uid, $p_gid) = (getpwnam 'postgres')[2,3]; +chown $p_uid, $p_gid, $tdir; + +my $tempcrt = "$tdir/ssl-cert-snakeoil.pem"; +my $oldcrt = "/var/lib/postgresql/$MAJORS[0]/upgr/server.crt"; +my $newcrt = "/var/lib/postgresql/$MAJORS[-1]/upgr/server.crt"; + +# First upgrade +note "upgrade test: server.crt is a symlink"; +(system "cp -p /etc/ssl/certs/ssl-cert-snakeoil.pem $tempcrt") == 0 or die "cp: $!"; +unlink $oldcrt; # remove file installed by pg_createcluster +symlink $tempcrt, $oldcrt or die "symlink: $!"; + +# Upgrade to latest version +my $outref; +is ((exec_as 0, "pg_upgradecluster --start -v $MAJORS[-1] $MAJORS[0] upgr", $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'; + +if ($MAJORS[-1] >= 9.2) { + is ((-e $newcrt), undef, "new data directory does not contain server.crt"); + is ((PgCommon::get_conf_value $MAJORS[-1], 'upgr', 'postgresql.conf', 'ssl_cert_file'), + $tempcrt, "symlink server.crt target is put into ssl_cert_file"); +} else { + is ((-l $newcrt), 1, "new data directory contains server.crt"); + is ((readlink $newcrt), $tempcrt, "symlink server.crt points to correct location"); +} + +# Clean away new cluster +is ((system "pg_dropcluster $MAJORS[-1] upgr --stop"), 0, 'Dropping upgraded cluster'); +unlink $oldcrt or die "unlink: $!"; + +# Second upgrade +note "upgrade test: server.crt is a plain file"; +(system "cp -p $tempcrt $oldcrt") == 0 or die "cp: $!"; + +# Upgrade to latest version +my $outref; +is ((exec_as 0, "pg_upgradecluster --start -v $MAJORS[-1] $MAJORS[0] upgr", $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'; + +is ((-f $newcrt), 1, "new data directory contains server.crt file"); +if ($MAJORS[-1] >= 9.2) { + is ((PgCommon::get_conf_value $MAJORS[-1], 'upgr', 'postgresql.conf', 'ssl_cert_file'), + $newcrt, "server.crt is put into ssl_cert_file"); +} else { + pass "..."; +} + +# Stop servers, clean up +is ((system "pg_dropcluster $MAJORS[0] upgr"), 0, 'Dropping original cluster'); +is ((system "pg_dropcluster $MAJORS[-1] upgr --stop"), 0, 'Dropping upgraded cluster'); + +check_clean; + +# vim: filetype=perl diff --git a/t/050_encodings.t b/t/050_encodings.t new file mode 100644 index 0000000..b4e5b54 --- /dev/null +++ b/t/050_encodings.t @@ -0,0 +1,117 @@ +# Test locale and encoding settings in pg_createcluster. + +use strict; + +use lib 't'; +use TestLib; +use PgCommon; +use Test::More tests => @MAJORS * 52 + 8; + +# create a test cluster with given locale, check the locale/encoding, and +# remove it +# Arguments: <version> <locale> [<encoding>] +sub check_cluster { + my ($v, $locale, $enc) = @_; + note "Checking $v $locale"; + my $cluster_name = $locale; + $cluster_name =~ s/-//g; # strip dashes so postgresql@.service likes it + if (defined $enc) { + $cluster_name .= "_$enc"; + is ((system "LC_ALL='$locale' pg_createcluster --encoding $enc --start $v $cluster_name >/dev/null 2>&1"), 0, + "pg_createcluster version $v for $locale with --encoding succeeded"); + } else { + is ((system "pg_createcluster --start --locale=$locale $v $cluster_name >/dev/null 2>&1"), 0, + "pg_createcluster version $v for $locale without --encoding succeeded"); + } + + # check encoding + sleep 1; + my $outref; + is ((exec_as 'postgres', "psql -Atl --cluster $v/$cluster_name", $outref, 0), 0, + 'psql -l succeeds'); + my $is_unicode = 0; + $is_unicode = 1 if defined $enc && $enc =~ /(UNICODE|UTF-8)/; + $is_unicode = 1 if $locale =~ /UTF-8/; + if ($is_unicode) { + like $$outref, qr/template1.*(UNICODE|UTF8)/, 'template1 is UTF-8 encoded'; + } else { + unlike $$outref, qr/template1.*(UNICODE|UTF8)/, 'template1 is not UTF-8 encoded'; + } + + # create a table and stuff some ISO-8859-5 characters into it (для) + is ((exec_as 'postgres', "createdb test", $outref), 0, 'creating test database'); + is_program_out 'postgres', "printf '\324\333\357' | psql -qc \"set client_encoding='iso-8859-5'; + create table t (x varchar); copy t from stdin\" test", 0, '', + 'creating table with ISO-8859-5 characters'; + is_program_out 'postgres', "echo \"set client_encoding='utf8'; select * from t\" | psql -Atq test", 0, + "\320\264\320\273\321\217\n", 'correct string in UTF-8'; + is_program_out 'postgres', "echo \"set client_encoding='iso-8859-5'; select * from t\" | psql -Atq test", 0, + "\324\333\357\n", 'correct string in ISO-8859-5'; + + # do the same test with using UTF-8 as input + is_program_out 'postgres', "printf '\320\264\320\273\321\217' | psql -qc \"set client_encoding='utf8'; + delete from t; copy t from stdin\" test", 0, '', + 'creating table with UTF-8 characters'; + is_program_out 'postgres', "echo \"set client_encoding='utf8'; select * from t\" | psql -Atq test", 0, + "\320\264\320\273\321\217\n", 'correct string in UTF-8'; + is_program_out 'postgres', "echo \"set client_encoding='iso-8859-5'; select * from t\" | psql -Atq test", 0, + "\324\333\357\n", 'correct string in ISO-8859-1'; + + # check encoding of server error messages (breaks in locale/encoding mismatches, so skip that) + if (!defined $enc) { + # temporarily disable and accept English text, since Russian translations are disabled now + like_program_out 'postgres', 'psql test -c "set client_encoding = \'UTF-8\'; select sqrt(-1)"', 1, + qr/^[^?]*(брать|отрицательного|cannot take square root)[^?]*$/, 'Server error message has correct language and encoding'; + } + + # check that we do not run into 'ignoring unconvertible UTF-8 character' + # breakage on nonmatching lc_messages and client_encoding + PgCommon::set_conf_value $v, $cluster_name, 'postgresql.conf', + 'client_encoding', 'UTF-8'; + PgCommon::set_conf_value $v, $cluster_name, 'postgresql.conf', + 'lc_messages', 'POSIX'; + is_program_out 0, "pg_ctlcluster $v $cluster_name restart", 0, '', + 'cluster starts correctly with nonmatching lc_messages and client_encoding'; + + # check interception of invalidly encoded/escaped strings + if ($is_unicode) { + like_program_out 'postgres', + 'printf "set client_encoding=\'UTF-8\'; select \'\\310\\\\\'a\'" | psql -Atq template1', + 0, qr/(UNICODE|UTF8).*0x(c8.*5c|c8.*27)/, + 'Server rejects incorrect encoding (CVE-2006-2313)'; + like_program_out 'postgres', + 'printf "set client_encoding=\'SJIS\'; select \'\\\\\\\'a\'" | psql -Atq template1', + 0, qr/(\\' is insecure)|(unterminated quoted string)/, + 'Server rejects \\\' escaping in unsafe client encoding (CVE-2006-2314)'; + if ($v >= '9.1') { + like_program_out 'postgres', + "printf \"set client_encoding='UTF-8'; set escape_string_warning='off'; select '\\\\\\'a'\" | psql -Atq template1", + 0, qr/unterminated quoted string/, + 'Server rejects obsolete \\\' escaping in unsafe client encoding (CVE-2006-2314)'; + } else { + is_program_out 'postgres', + "printf \"set client_encoding='UTF-8'; set escape_string_warning='off'; select '\\\\\\'a'\" | psql -Atq template1", + 0, "'a\n", 'Server accepts \\\' escaping in safe client encoding (CVE-2006-2314)'; + } + } + + # drop cluster + is ((system "pg_dropcluster $v $cluster_name --stop"), 0, 'Dropping cluster'); +} + +foreach my $v (@MAJORS) { + check_cluster $v, 'ru_RU'; + check_cluster $v, 'ru_RU.UTF-8'; + + note "Check $v locale environment variables"; + # check LC_* over LANG domination + is ((system "LANGUAGE= LC_ALL=C LANG=bo_GUS.UTF-8 pg_createcluster --start $v main >/dev/null 2>&1"), 0, + "pg_createcluster: LC_ALL dominates LANG"); + like_program_out 'postgres', "psql -Atl --cluster $v/main", 0, + qr/template1.*ASCII/, 'template1 is ASCII encoded'; + is ((system "pg_dropcluster $v main --stop"), 0, 'Dropping cluster'); +} + +check_clean; + +# vim: filetype=perl diff --git a/t/052_upgrade_encodings.t b/t/052_upgrade_encodings.t new file mode 100644 index 0000000..f888350 --- /dev/null +++ b/t/052_upgrade_encodings.t @@ -0,0 +1,83 @@ +# Test default and explicit encoding on upgrades + +use strict; + +use lib 't'; +use TestLib; +use PgCommon; +use Test::More tests => (@MAJORS == 1) ? 1 : 43; + +if (@MAJORS == 1) { + pass 'only one major version installed, skipping upgrade tests'; + exit 0; +} + +my $outref; +my $oldv = $MAJORS[0]; +my $newv = $MAJORS[-1]; + +is ((exec_as 0, "pg_createcluster --start --locale=ru_RU $oldv main", $outref), 0, + "creating ru_RU $oldv cluster"); + +is ((exec_as 'postgres', 'psql -c "create database latintest" template1', $outref), 0, + "creating latintest DB with LATIN encoding"); +if ($oldv <= '8.3') { + is ((exec_as 'postgres', 'psql -c "create database asctest encoding = \'SQL_ASCII\'" template1', $outref), 0, + "creating asctest DB with ASCII encoding"); +} else { + is ((exec_as 'postgres', 'psql -c "create database asctest template = template0 lc_collate = \'C\' lc_ctype = \'C\' encoding = \'SQL_ASCII\'" template1', $outref), 0, + "creating asctest DB with C locale"); +} + +is ((exec_as 'postgres', "printf 'A\\324B' | psql -c \"create table t(x varchar); copy t from stdin\" latintest", $outref), + 0, 'write LATIN database content to latintest'); +is ((exec_as 'postgres', "printf 'A\\324B' | psql -c \"create table t(x varchar); copy t from stdin\" asctest", $outref), + 0, 'write LATIN database content to asctest'); + +is_program_out 'postgres', "echo \"select * from t\" | psql -Atq latintest", + 0, "A\324B\n", 'old latintest DB has correctly encoded string'; +is_program_out 'postgres', "echo \"select * from t\" | psql -Atq asctest", + 0, "A\324B\n", 'old asctest DB has correctly encoded string'; + +is ((exec_as 'postgres', 'psql -Atl', $outref), 0, 'psql -Atl on old cluster'); +ok ((index $$outref, 'latintest|postgres|ISO_8859_5') >= 0, 'latintest is LATIN encoded'); +ok ((index $$outref, 'asctest|postgres|SQL_ASCII') >= 0, 'asctest is ASCII encoded'); +ok ((index $$outref, 'template1|postgres|ISO_8859_5') >= 0, 'template1 is LATIN encoded'); + +# upgrade without specifying locales, should be kept +like_program_out 0, "pg_upgradecluster -v $newv $oldv main", 0, qr/^Success/im; + +is ((exec_as 'postgres', "psql --cluster $newv/main -Atl", $outref), 0, 'psql -Atl on upgraded cluster'); +ok ((index $$outref, 'latintest|postgres|ISO_8859_5') >= 0, 'latintest is LATIN encoded'); +ok ((index $$outref, 'asctest|postgres|SQL_ASCII') >= 0, 'asctest is ASCII encoded'); +ok ((index $$outref, 'template1|postgres|ISO_8859_5') >= 0, 'template1 is LATIN encoded'); +is_program_out 'postgres', "echo \"select * from t\" | psql --cluster $newv/main -Atq latintest", + 0, "A\324B\n", 'new latintest DB has correctly encoded string'; + +is ((system "pg_dropcluster --stop $newv main"), 0, 'dropping upgraded cluster'); +is ((system "pg_ctlcluster $oldv main start"), 0, 'restarting old cluster'); + +# upgrade with explicitly specifying other locale +like_program_out 0, "pg_upgradecluster --locale ru_RU.UTF-8 -v $newv $oldv main", 0, qr/^Success/im; + +is ((exec_as 'postgres', "psql --cluster $newv/main -Atl", $outref), 0, 'psql -Atl on upgraded cluster'); +if ($newv >= 11) { + ok ((index $$outref, 'latintest|postgres|ISO_8859_5') >= 0, 'latintest is still LATIN encoded'); +} else { + like $$outref, qr/latintest\|postgres\|(UTF8|UNICODE)/, 'latintest is now UTF8 encoded'; +} +ok ((index $$outref, 'asctest|postgres|SQL_ASCII') >= 0, 'asctest is ASCII encoded'); +like $$outref, qr/template1\|postgres\|(UTF8|UNICODE)/, 'template1 is UTF8 encoded'; +is_program_out 'postgres', "echo \"select * from t\" | psql --cluster $newv/main -Atq latintest", + 0, ($newv >= 11 ? "A\324B\n": "AдB\n"), 'new latintest DB has correctly encoded string'; +# ASCII databases don't do automatic encoding conversion, so this remains LATIN +is_program_out 'postgres', "echo \"select * from t\" | psql --cluster $newv/main -Atq asctest", + 0, "A\324B\n", 'new asctest DB has correctly encoded string'; + +is ((system "pg_dropcluster --stop $newv main"), 0, 'dropping upgraded cluster'); + +is ((system "pg_dropcluster $oldv main"), 0, 'dropping old cluster'); + +check_clean; + +# vim: filetype=perl diff --git a/t/060_obsolete_confparams.t b/t/060_obsolete_confparams.t new file mode 100644 index 0000000..fad3080 --- /dev/null +++ b/t/060_obsolete_confparams.t @@ -0,0 +1,91 @@ +# Test upgrading from the oldest version to all majors with all possible +# configuration parameters set. This checks that they are correctly +# transitioned. + +use strict; + +use lib 't'; +use TestLib; + +use Test::More tests => (@MAJORS == 1) ? 1 : (12 + @MAJORS * 9); + +if (@MAJORS == 1) { + pass 'only one major version installed, skipping upgrade tests'; + exit 0; +} + +$ENV{_SYSTEMCTL_SKIP_REDIRECT} = 1; # FIXME: testsuite is hanging otherwise + +# t/$v.conf generated using +# sed -e 's/^#//' -e 's/[ \t]*#.*//g' /etc/postgresql/9.3/foo/postgresql.conf | grep '^[a-z]' +# remove/comment data_directory, hba_file, ident_file, external_pid_file, include_dir, include +# lc_* should be 'C' +# stats_temp_directory should point to /var/run/postgresql/*.pg_stat_tmp + +# Test one particular upgrade (old version, new version) +sub do_upgrade { + my $cur = $_[0]; + my $new = $_[1]; + note "Testing upgrade $cur -> $new"; + + open C, "t/$cur.conf" or die "could not open t/$cur.conf: $!"; + my $fullconf; + { local $/; $fullconf = <C>; } + close C; + + # Write configuration file and start + my $datadir = PgCommon::cluster_data_directory $cur, 'main'; + open F, ">/etc/postgresql/$cur/main/postgresql.conf" or + die "could not open /etc/postgresql/$cur/main/postgresql.conf"; + print F $fullconf; + close F; + # restore data directory, we just scribbled over it + PgCommon::set_conf_value $cur, 'main', 'postgresql.conf', 'data_directory', $datadir; + + is ((exec_as 0, "pg_ctlcluster $cur main start"), 0, + 'pg_ctlcluster start'); + like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/$cur.*online/, + "Old $cur cluster is online"; + + # Upgrade cluster + like_program_out 0, "pg_upgradecluster -v $new $cur main", 0, qr/^Success/im; + like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/$new.*online/, + "New $new cluster is online"; + + is ((system "pg_dropcluster $cur main"), 0, "pg_dropcluster $cur main"); + + is ((exec_as 0, "pg_ctlcluster $new main stop 2>/dev/null"), 0, + "Stopping new $new pg_ctlcluster"); +} + +# create cluster for oldest version +is ((system "pg_createcluster $MAJORS[0] main >/dev/null"), 0, "pg_createcluster $MAJORS[0] main"); + +# Loop over all but the latest major version, testing N->N+1 upgrades +my @testversions = sort { $a <=> $b } @MAJORS; +while ($#testversions) { + my $cur = shift @testversions; + my $new = $testversions[0]; + do_upgrade $cur, $new; +} + +# remove latest cluster and directory +is ((system "pg_dropcluster $testversions[0] main"), 0, 'Dropping remaining cluster'); + +# now test a direct upgrade from oldest to newest, to also catch parameters +# which changed several times, like syslog -> redirect_stderr -> +# logging_collector +if ($#MAJORS > 1) { + is ((system "pg_createcluster $MAJORS[0] main >/dev/null"), 0, "pg_createcluster $MAJORS[0] main"); + do_upgrade $MAJORS[0], $MAJORS[-1]; + is ((system "pg_dropcluster $testversions[0] main"), 0, 'Dropping remaining cluster'); +} else { + pass 'only two available versions, skipping tests...'; + for (my $i = 0; $i < 10; ++$i) { + pass '...'; + } +} + +check_clean; + +# vim: filetype=perl diff --git a/t/070_non_postgres_clusters.t b/t/070_non_postgres_clusters.t new file mode 100644 index 0000000..7779408 --- /dev/null +++ b/t/070_non_postgres_clusters.t @@ -0,0 +1,116 @@ +# Test successful operation of clusters which are not owned by +# postgres. Only check the oldest and newest version. + +use strict; + +use lib 't'; +use TestLib; + +use Test::More tests => 40; + +$ENV{_SYSTEMCTL_SKIP_REDIRECT} = 1; # FIXME: testsuite is hanging otherwise + +my $owner = 'nobody'; +my $v = $MAJORS[0]; + +# create cluster +is ((system "pg_createcluster -u $owner $v main >/dev/null"), 0, + "pg_createcluster $v main for owner $owner"); + +# check if start is refused when config and data owner do not match +my $pgconf = "/etc/postgresql/$v/main/postgresql.conf"; +my ($origuid, $origgid) = (stat $pgconf)[4,5]; +chown 1, 1, $pgconf; +like_program_out 0, "pg_ctlcluster $v main start", 1, qr/do not match/, "start refused when config and data owners mismatch"; +chown $origuid, $origgid, $pgconf; +is ((system "pg_ctlcluster $v main start"), 0, "pg_ctlcluster succeeds with owner $owner"); + +# Check cluster +like_program_out $owner, 'pg_lsclusters -h', 0, + qr/^$v\s+main\s+5432\s+online\s+$owner/, + 'pg_lsclusters shows running cluster'; + +like ((ps 'postgres'), qr/^$owner.*bin\/postgres .*\/var\/lib\/postgresql\/$v\/main/m, + "postgres is running as user $owner"); + +is_program_out $owner, 'ls /tmp/.s.PGSQL.*', 0, "/tmp/.s.PGSQL.5432\n/tmp/.s.PGSQL.5432.lock\n", 'socket is in /tmp'; + +ok_dir '/var/run/postgresql', [], '/var/run/postgresql is empty'; + +# verify owner of configuration files +my @st; +my $confdir = "/etc/postgresql/$v/main"; +my ($owneruid, $ownergid) = (getpwnam $owner)[2,3]; +@st = stat $confdir; +is $st[4], $owneruid, 'conf dir is owned by user'; +is $st[5], $ownergid, 'conf dir is owned by user\'s primary group'; +my ($ok_uid, $ok_gid) = (1, 1); +opendir D, $confdir or die "opendir: $!"; +for my $f (readdir D) { + next if $f eq '.' or $f eq '..'; + @st = stat "$confdir/$f" or die "stat: $!"; + if ($st[4] != $owneruid) { + note "$f is not owned by user"; + $ok_uid = 0; + } + if ($st[5] != $ownergid) { + note "$f is not owned by user's primary group"; + $ok_gid = 0; + } +} +closedir D; +is $ok_uid, 1, "files are owned by user"; +is $ok_gid, 1, "files are owned by user's primary group"; + +# verify log file properties +@st = stat "/var/log/postgresql/postgresql-$v-main.log"; +is $st[2], 0100640, 'log file has 0640 permissions'; +is $st[4], $owneruid, 'log file is owned by user'; +# the log file gid setting works on RedHat, but nobody has gid 99 there (and +# there's not good alternative for testing) +my $loggid = $PgCommon::rpm ? (getgrnam 'adm')[2] : $ownergid; +is $st[5], $loggid, 'log file is owned by user\'s primary group'; + +if ($#MAJORS > 0) { + my $newv = $MAJORS[-1]; + + my $outref; + is ((exec_as 0, "(pg_upgradecluster -v $newv $v main | 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"; + } + + # verify file permissions + @st = stat "/etc/postgresql/$newv/main"; + is $st[4], $owneruid, 'upgraded conf dir is owned by user'; + is $st[5], $ownergid, 'upgraded conf dir is owned by user\'s primary group'; + @st = stat "/etc/postgresql/$newv/main/postgresql.conf"; + is $st[4], $owneruid, 'upgraded postgresql.conf dir is owned by user'; + is $st[5], $ownergid, 'upgraded postgresql.conf dir is owned by user\'s primary group'; + @st = stat "/var/log/postgresql/postgresql-$v-main.log"; + is $st[4], $owneruid, 'upgraded log file is owned by user'; + is $st[5], $loggid, 'upgraded log file is owned by user\'s primary group'; + + is ((system "pg_dropcluster $newv main --stop"), 0, 'pg_dropcluster'); +} else { + pass 'only one major version installed, skipping upgrade test'; + for (my $i = 0; $i < 10; ++$i) { + pass '...'; + } +} + +# Check proper cleanup +is ((system "pg_dropcluster $v main --stop"), 0, 'pg_dropcluster'); +is_program_out $owner, 'pg_lsclusters -h', 0, '', 'No clusters left'; +is ((ps 'postgres'), '', "No postgres processes left"); + +check_clean; + +# vim: filetype=perl diff --git a/t/080_start.conf.t b/t/080_start.conf.t new file mode 100644 index 0000000..016205a --- /dev/null +++ b/t/080_start.conf.t @@ -0,0 +1,145 @@ +# Check start.conf handling. + +use strict; + +use lib 't'; +use TestLib; +use PgCommon; + +use Test::More tests => 73; + +my $systemd = -d '/run/systemd/system'; + +# Do test with oldest version +my $v = $MAJORS[0]; + +# create cluster +is ((system "pg_createcluster $v main >/dev/null"), 0, "pg_createcluster $v main"); + +# Check that we start with 'auto' +note "start.conf auto"; +is ((get_cluster_start_conf $v, 'main'), 'auto', + 'get_cluster_start_conf returns auto'); +is_program_out 'nobody', "grep '^[^\\s#]' /etc/postgresql/$v/main/start.conf", + 0, "auto\n", 'start.conf contains auto'; +SKIP: { + skip 'not running under systemd', 2 unless ($systemd); + ok_dir '/run/systemd/generator/postgresql.service.wants', + ["postgresql\@$v-main.service"], + "systemd generator links cluster"; + is ((readlink "/run/systemd/generator/postgresql.service.wants/postgresql\@$v-main.service"), + "/lib/systemd/system/postgresql@.service", + "systemd generator links correct service file"); +} + +# init script should handle auto cluster +like_program_out 0, "/etc/init.d/postgresql start $v", 0, qr/Start.*($v|systemctl)/; +like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/online/, 'cluster is online'; +like_program_out 0, "/etc/init.d/postgresql stop $v", 0, qr/Stop.*($v|systemctl)/; +sleep 3 if ($systemd); # FIXME: systemctl stop postgresql is not yet synchronous (#759725) +like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/down/, 'cluster is down'; + +# change to manual, verify start.conf contents +note "start.conf manual"; +set_cluster_start_conf $v, 'main', 'manual'; + +is ((get_cluster_start_conf $v, 'main'), 'manual', + 'get_cluster_start_conf returns manual'); +is_program_out 'nobody', "grep '^[^\\s#]' /etc/postgresql/$v/main/start.conf", + 0, "manual\n", 'start.conf contains manual'; +SKIP: { + skip 'not running under systemd', 1 unless ($systemd); + system "systemctl daemon-reload"; + ok_dir '/run/systemd/generator/postgresql.service.wants', + [], "systemd generator doesn't link cluster"; +} + +# init script should not handle manual cluster ... +like_program_out 0, "/etc/init.d/postgresql start $v", 0, qr/Start.*($v|systemctl)/; +like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/down/, 'cluster is down'; + +# pg_ctlcluster should handle manual cluster +is_program_out 'postgres', "pg_ctlcluster $v main start", 0, ''; +like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/online/, 'cluster is online'; +is_program_out 'postgres', "pg_ctlcluster $v main stop", 0, ''; +like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/down/, 'cluster is down'; + +# change to disabled, verify start.conf contents +note "start.conf disabled"; +set_cluster_start_conf $v, 'main', 'disabled'; + +is ((get_cluster_start_conf $v, 'main'), 'disabled', + 'get_cluster_start_conf returns disabled'); +SKIP: { + skip 'not running under systemd', 1 unless ($systemd); + system "systemctl daemon-reload"; + ok_dir '/run/systemd/generator/postgresql.service.wants', + [], "systemd generator doesn't link cluster"; +} + +# init script should not handle disabled cluster +like_program_out 0, "/etc/init.d/postgresql start $v", 0, qr/Start.*($v|systemctl)/; +like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/down/, 'cluster is down'; + +# pg_ctlcluster should not start disabled cluster +is_program_out 'postgres', "pg_ctlcluster $v main start", 1, + "Error: Cluster is disabled\n"; +like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/down/, 'cluster is down'; + +# change back to manual, start cluster +set_cluster_start_conf $v, 'main', 'manual'; +is_program_out 'postgres', "pg_ctlcluster $v main start", 0, ''; +like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/online/, 'cluster is online'; + +# however, we want to stop disabled clusters +set_cluster_start_conf $v, 'main', 'disabled'; +is_program_out 'postgres', "pg_ctlcluster $v main stop", 0, ''; +like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/down/, 'cluster is down'; + +# set back to manual +set_cluster_start_conf $v, 'main', 'manual'; +is_program_out 'postgres', "pg_ctlcluster $v main start", 0, ''; +like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/online/, 'cluster is online'; + +# upgrade cluster +note "test upgrade"; +if ($#MAJORS == 0) { + pass 'only one major version installed, skipping upgrade test'; + pass '...'; +} else { + like_program_out 0, "pg_upgradecluster -v $MAJORS[-1] $v main", 0, qr/Success/; +} + +# check start.conf of old and upgraded cluster +is ((get_cluster_start_conf $v, 'main'), 'manual', + 'get_cluster_start_conf for old cluster returns manual'); +is ((get_cluster_start_conf $MAJORS[-1], 'main'), 'manual', + 'get_cluster_start_conf for new cluster returns manual'); + +# clean up +if ($#MAJORS == 0) { + pass '...'; +} else { + is ((system "pg_dropcluster $v main"), 0, + 'dropping old cluster'); +} + +is ((system "pg_dropcluster $MAJORS[-1] main --stop"), 0, + 'dropping upgraded cluster'); + +is_program_out 'postgres', 'pg_lsclusters -h', 0, '', 'no clusters any more'; + +# create cluster with --start-conf option +is_program_out 0, "pg_createcluster $v main --start-conf foo", 1, + "Error: Invalid --start-conf value: foo\n", + 'pg_createcluster checks --start-conf validity'; +is ((system "pg_createcluster $v main --start-conf manual >/dev/null"), 0, + 'pg_createcluster checks --start-conf manual'); +is ((get_cluster_start_conf $v, 'main'), 'manual', + 'get_cluster_start_conf returns manual'); +is ((system "pg_dropcluster $v main"), 0, + 'dropping cluster'); + +check_clean; + +# vim: filetype=perl diff --git a/t/085_pg_ctl.conf.t b/t/085_pg_ctl.conf.t new file mode 100644 index 0000000..e9adce7 --- /dev/null +++ b/t/085_pg_ctl.conf.t @@ -0,0 +1,52 @@ +# Check pg_ctl.conf handling. + +use strict; + +use lib 't'; +use TestLib; +use PgCommon; + +use Test::More tests => $MAJORS[-1] >= '8.3' ? 29 : 1; + +# Do test with newest version +my $v = $MAJORS[-1]; +if ($v < '8.3') { + pass 'Skipping core limit tests for versions < 8.3'; + exit 0; +} + +is ((system "pg_createcluster $v main >/dev/null"), 0, "pg_createcluster $v main"); + +ok (-f "/etc/postgresql/$v/main/pg_ctl.conf", "/etc/postgresql/$v/main/pg_ctl.conf exists"); + +# Default behaviour, core size=0 +is_program_out 'postgres', "pg_ctlcluster $v main start", 0, '', "starting cluster"; + +is_program_out 'postgres', "xargs -i awk '/core/ {print \$5}' /proc/{}/limits < /var/run/postgresql/$v-main.pid", 0, "0\n", "soft core size is 0"; + +# -c in pg_ctl.conf, core size=unlimited +ok (set_cluster_pg_ctl_conf($v, 'main', '-c'), "set pg_ctl default option to -c"); + +is_program_out 'postgres', "pg_ctlcluster $v main restart", 0, '', "restarting cluster"; + +is_program_out 'postgres', "xargs -i awk '/core/ {print \$5}' /proc/{}/limits < /var/run/postgresql/$v-main.pid", 0, "unlimited\n", "soft core size is unlimited"; + +# Back to default behaviour, core size=0 + +ok (set_cluster_pg_ctl_conf($v, 'main', ''), "restored pg_ctl default option"); + +is_program_out 'postgres', "pg_ctlcluster $v main restart", 0, '', "restarting cluster"; + +is_program_out 'postgres', "xargs -i awk '/core/ {print \$5}' /proc/{}/limits < /var/run/postgresql/$v-main.pid", 0, "0\n", "soft core size is 0"; + +# pg_ctl -c, core size=unlimited + +is_program_out 'postgres', "pg_ctlcluster $v main restart -- -c", 0, '', "restarting cluster with -c on the command line"; + +is_program_out 'postgres', "xargs -i awk '/core/ {print \$5}' /proc/{}/limits < /var/run/postgresql/$v-main.pid", 0, "unlimited\n", "soft core size is unlimited"; + +is ((system "pg_dropcluster $v main --stop"), 0, 'dropping cluster'); + +check_clean; + +# vim: filetype=perl diff --git a/t/090_multicluster.t b/t/090_multicluster.t new file mode 100644 index 0000000..ead6120 --- /dev/null +++ b/t/090_multicluster.t @@ -0,0 +1,310 @@ +# Check operation with multiple clusters + +use strict; + +use lib 't'; +use TestLib; +use Socket; +use PgCommon; + +use Test::More tests => 125; + +# Replace all md5 and password authentication methods with 'trust' in given +# pg_hba.conf file. +sub hba_password_to_ident { + open F, $_[0] or die "open $_[0]: $!"; + my $hba; + read F, $hba, 10000; + $hba =~ s/md5/trust/g; + $hba =~ s/password/trust/g; + close F; + open F, ">$_[0]" or die "open $_[0]: $!"; + print F $hba; + close F; + chmod 0644, $_[0] or die "chmod $_[0]: $!"; +} + +# create fake socket at 5433 to verify that this port is skipped +socket (SOCK, PF_INET, SOCK_STREAM, getprotobyname('tcp')) or die "socket: $!"; +bind (SOCK, sockaddr_in(5433, INADDR_ANY)) || die "bind: $! "; + +# create clusters +is ((system "pg_createcluster $MAJORS[0] old >/dev/null"), 0, "pg_createcluster $MAJORS[0] old"); +is ((system "pg_createcluster $MAJORS[-1] new1 >/dev/null"), 0, "pg_createcluster $MAJORS[-1] new1"); +is ((system "pg_createcluster $MAJORS[-1] new2 -p 5440 >/dev/null"), 0, "pg_createcluster $MAJORS[-1] new2"); + +my $old = "$MAJORS[0]/old"; +my $new1 = "$MAJORS[-1]/new1"; +my $new2 = "$MAJORS[-1]/new2"; + +# disable password auth for network cluster selection tests +hba_password_to_ident "/etc/postgresql/$old/pg_hba.conf"; +hba_password_to_ident "/etc/postgresql/$new1/pg_hba.conf"; +hba_password_to_ident "/etc/postgresql/$new2/pg_hba.conf"; + +is ((system "pg_ctlcluster $MAJORS[0] old start >/dev/null"), 0, "starting cluster $old"); +is ((system "pg_ctlcluster $MAJORS[-1] new1 start >/dev/null"), 0, "starting cluster $new1"); +is ((system "pg_ctlcluster $MAJORS[-1] new2 start >/dev/null"), 0, "starting cluster $new2"); + +like_program_out 'postgres', 'pg_lsclusters -h | sort -k3', 0, qr/.*5432.*5434.*5440.*/s, + 'clusters have the correct ports, skipping used 5433'; + +# move user_clusters aside for the test; this will ensure that client programs +# work correctly without any file at all +if (-f '/etc/postgresql-common/user_clusters') { + ok ((rename '/etc/postgresql-common/user_clusters', + '/etc/postgresql-common/user_clusters.psqltestsuite'), + 'Temporarily moving away /etc/postgresql-common/user_clusters'); +} else { + pass '/etc/postgresql-common/user_clusters does not exist'; +} + +# check basic cluster selection +like_program_out 0, 'createdb --version', 0, qr/^createdb \(PostgreSQL\) $MAJORS[0]/, + 'pg_wrapper selects port 5432 as default cluster'; +like_program_out 0, "createdb --cluster $new1 --version", 0, + qr/^createdb \(PostgreSQL\) $MAJORS[-1]/, + 'pg_wrapper --cluster works'; +like_program_out 0, "createdb --cluster $MAJORS[-1]/foo --version", 1, + qr/Cluster .* does not exist/, + 'pg_wrapper --cluster errors out for invalid cluster'; + +# create a database in new1 and check that it doesn't appear in new2 +is_program_out 'postgres', "createdb --cluster $new1 test", 0, ($MAJORS[-1] < 8.3 ? "CREATE DATABASE\n" : ''); +like_program_out 'postgres', "psql -Atl --cluster $new1", 0, + qr/test\|postgres\|/, + 'test db appears in cluster new1'; +unlike_program_out 'postgres', "psql -Atl --cluster $new2", 0, + qr/test\|postgres\|/, + 'test db does not appear in cluster new2'; +unlike_program_out 'postgres', "psql -Atl", 0, qr/test\|postgres\|/, + 'test db does not appear in default cluster'; + +# check network cluster selection +is_program_out 'postgres', "psql --cluster $MAJORS[0]/127.0.0.1: -Atc 'show port' template1", 0, "5432\n", + "psql --cluster $MAJORS[0]/127.0.0.1: defaults to port 5432"; +like_program_out 'postgres', "createdb --cluster $MAJORS[-1]/127.0.0.1:5432 --version", 0, + qr/^createdb \(PostgreSQL\) $MAJORS[-1]/, + "createdb --cluster $MAJORS[-1]/127.0.0.1:5432 uses latest client version"; +like_program_out 'postgres', "psql -Atl --cluster $MAJORS[-1]/localhost:5434", 0, + qr/test\|postgres\|/, "test db appears in cluster $MAJORS[-1]/localhost:5434"; +unlike_program_out 'postgres', "psql -Atl --cluster $MAJORS[-1]/localhost:5440", 0, + qr/test\|postgres\|/, "test db does not appear in cluster $MAJORS[-1]/localhost:5440"; + +# check some erroneous cluster specifications +like_program_out 'postgres', "LC_MESSAGES=C psql -Atl --cluster $MAJORS[-1]/localhost:5435", 2, + qr/could not connect/, "psql --cluster $MAJORS[-1]/localhost:5435 fails due to nonexisting port"; +like_program_out 'postgres', "LC_MESSAGES=C psql -Atl --cluster $MAJORS[-1]/localhost:a", 1, + qr/Cluster .* does not exist/, "psql --cluster $MAJORS[-1]/localhost:a fails due to invalid syntax"; +like_program_out 'postgres', "LC_MESSAGES=C psql -Atl --cluster $MAJORS[-1]/doesnotexi.st", 1, + qr/Cluster .* does not exist/, "psql --cluster $MAJORS[-1]/doesnotexi.st fails due to invalid syntax"; +like_program_out 'postgres', "psql -Atl --cluster 6.4/localhost:", 1, + qr/Invalid version/, "psql --cluster 6.4/localhost: fails due to invalid version"; + +# check that environment variables work +$ENV{'PGCLUSTER'} = $new1; +like_program_out 'postgres', "psql -Atl", 0, qr/test\|postgres\|/, + 'PGCLUSTER selection (1)'; +$ENV{'PGCLUSTER'} = $new2; +unlike_program_out 'postgres', "psql -Atl", 0, qr/test\|postgres\|/, + 'PGCLUSTER selection (2)'; +$ENV{'PGCLUSTER'} = 'foo'; +like_program_out 'postgres', "psql -l", 1, + qr/Invalid version .* specified in PGCLUSTER/, + 'invalid PGCLUSTER value'; +$ENV{'PGCLUSTER'} = "$MAJORS[-1]/127.0.0.1:"; +like_program_out 0, 'createdb --version', 0, qr/^createdb \(PostgreSQL\) $MAJORS[-1]/, + 'PGCLUSTER network cluster selection (1)'; +$ENV{'PGCLUSTER'} = "$MAJORS[-1]/localhost:5434"; +like_program_out 'postgres', 'psql -Atl', 0, + qr/test\|postgres\|/, 'PGCLUSTER network cluster selection (2)'; +$ENV{'PGCLUSTER'} = "$MAJORS[-1]/localhost:5440"; +unlike_program_out 'postgres', 'psql -Atl', 0, + qr/test\|postgres\|/, 'PGCLUSTER network cluster selection (3)'; +$ENV{'PGCLUSTER'} = "$MAJORS[-1]/localhost:5435"; +like_program_out 'postgres', 'LC_MESSAGES=C psql -Atl', 2, + qr/could not connect/, "psql --cluster $MAJORS[-1]/localhost:5435 fails due to nonexisting port"; +delete $ENV{'PGCLUSTER'}; + +# check that PGPORT works +$ENV{'PGPORT'} = '5434'; +is_program_out 'postgres', 'psql -Atc "show port" template1', 0, "5434\n", + 'PGPORT selection (1)'; +$ENV{'PGPORT'} = '5432'; +is_program_out 'postgres', 'psql -Atc "show port" template1', 0, "5432\n", + 'PGPORT selection (2)'; +$ENV{'PGCLUSTER'} = $new2; +delete $ENV{'PGPORT'}; +$ENV{'PGPORT'} = '5432'; +like_program_out 'postgres', 'createdb --version', 0, qr/^createdb \(PostgreSQL\) $MAJORS[-1]/, + 'PGPORT+PGCLUSTER, PGCLUSTER selects version'; +is_program_out 'postgres', 'psql -Atc "show port" template1', 0, "5432\n", + 'PGPORT+PGCLUSTER, PGPORT selects port'; +delete $ENV{'PGPORT'}; +delete $ENV{'PGCLUSTER'}; + +# check that PGDATABASE works +$ENV{'PGDATABASE'} = 'test'; +is_program_out 'postgres', "psql --cluster $new1 -Atc 'select current_database()'", 0, "test\n", + 'PGDATABASE environment variable works'; +delete $ENV{'PGDATABASE'}; + +# check cluster selection with an empty user_clusters +open F, '>/etc/postgresql-common/user_clusters' or die "Could not create user_clusters: $!"; +close F; +chmod 0644, '/etc/postgresql-common/user_clusters'; +like_program_out 0, 'createdb --version', 0, qr/^createdb \(PostgreSQL\) $MAJORS[0]/, + 'pg_wrapper selects port 5432 as default cluster with empty user_clusters'; +like_program_out 0, "createdb --cluster $new1 --version", 0, + qr/^createdb \(PostgreSQL\) $MAJORS[-1]/, + 'pg_wrapper --cluster works with empty user_clusters'; + +# check default cluster selection with user_clusters +open F, '>/etc/postgresql-common/user_clusters' or die "Could not create user_clusters: $!"; +print F "* * $MAJORS[-1] new1 *\n"; +close F; +chmod 0644, '/etc/postgresql-common/user_clusters'; +like_program_out 'postgres', 'createdb --version', 0, qr/^createdb \(PostgreSQL\) $MAJORS[-1]/, + "pg_wrapper selects correct cluster with user_clusters '* * $MAJORS[-1] new1 *'"; + +# check default database selection with user_clusters +open F, '>/etc/postgresql-common/user_clusters' or die "Could not create user_clusters: $!"; +print F "* * $MAJORS[-1] new1 test\n"; +close F; +chmod 0644, '/etc/postgresql-common/user_clusters'; +is_program_out 'postgres', 'psql -Atc "select current_database()"', 0, "test\n", + "pg_wrapper selects correct database with user_clusters '* * $MAJORS[-1] new1 test'"; +$ENV{'PGDATABASE'} = 'template1'; +is_program_out 'postgres', "psql -Atc 'select current_database()'", 0, "template1\n", + 'PGDATABASE environment variable is not overridden by user_clusters'; +delete $ENV{'PGDATABASE'}; + +# check by-user cluster selection with user_clusters +# (also check invalid cluster reporting) +open F, '>/etc/postgresql-common/user_clusters' or die "Could not create user_clusters: $!"; +print F "postgres * $MAJORS[-1] new1 *\nnobody * $MAJORS[0] old *\n* * 5.5 * *"; +close F; +chmod 0644, '/etc/postgresql-common/user_clusters'; +like_program_out 'postgres', 'createdb --version', 0, qr/^createdb \(PostgreSQL\) $MAJORS[-1]/, + 'pg_wrapper selects correct cluster with per-user user_clusters'; +like_program_out 'nobody', 'createdb --version', 0, qr/^createdb \(PostgreSQL\) $MAJORS[0]/, + 'pg_wrapper selects correct cluster with per-user user_clusters'; +like_program_out 0, 'createdb --version', 0, qr/user_clusters.*line 3.*version.*not exist/i, + 'pg_wrapper warning for invalid per-user user_clusters line'; + +# check by-user network cluster selection with user_clusters +# (also check invalid cluster reporting) +open F, '>/etc/postgresql-common/user_clusters' or die "Could not create user_clusters: $!"; +print F "postgres * $MAJORS[0] localhost: *\nnobody * $MAJORS[-1] new1 *\n* * $MAJORS[-1] localhost:a *"; +close F; +chmod 0644, '/etc/postgresql-common/user_clusters'; +like_program_out 'postgres', 'createdb --version', 0, qr/^createdb \(PostgreSQL\) $MAJORS[0]/, + 'pg_wrapper selects correct version with per-user user_clusters'; +like_program_out 'nobody', 'createdb --version', 0, qr/^createdb \(PostgreSQL\) $MAJORS[-1]/, + 'pg_wrapper selects correct version with per-user user_clusters'; +like_program_out 0, 'createdb --version', 0, qr/user_clusters.*line 3.*cluster.*not exist/i, + 'pg_wrapper warning for invalid per-user user_clusters line'; +# check PGHOST environment variable precedence +$ENV{'PGHOST'} = '127.0.0.2'; +like_program_out 'postgres', 'psql -Atl', 2, qr/127.0.0.2/, '$PGHOST overrides user_clusters'; +is_program_out 'postgres', "psql --cluster $MAJORS[-1]/localhost:5434 -Atc 'select current_database()' test", + 0, "test\n", '--cluster overrides $PGHOST'; +delete $ENV{'PGHOST'}; + +# check invalid user_clusters +open F, '>/etc/postgresql-common/user_clusters' or die "Could not create user_clusters: $!"; +print F 'foo'; +close F; +chmod 0644, '/etc/postgresql-common/user_clusters'; +like_program_out 'postgres', 'createdb --version', 0, qr/ignoring invalid line 1/, + 'pg_wrapper ignores invalid lines in user_clusters'; + +# remove test user_clusters +unlink '/etc/postgresql-common/user_clusters' or die + "unlink user_clusters: $!"; + +# check that pg_service.conf works +open F, '>/etc/postgresql-common/pg_service.conf' or die "Could not create pg_service.conf: $!"; +print F "[old_t1] +user=postgres +dbname=template1 +port=5432 + +[new1_test] +user=postgres +dbname=test +port=5434 + +# these do not exist +[new2_test] +user=postgres +dbname=test +port=5440 +"; +close F; +chmod 0644, '/etc/postgresql-common/pg_service.conf'; + +$ENV{'PGSERVICE'} = 'old_t1'; +is_program_out 'postgres', "psql -Atc 'select current_database()'", 0, + "template1\n", 'pg_service conf selection 1'; +$ENV{'PGSERVICE'} = 'new1_test'; +is_program_out 'postgres', "psql -Atc 'select current_database()'", 0, + "test\n", 'pg_service conf selection 2'; +$ENV{'PGSERVICE'} = 'new2_test'; +like_program_out 'postgres', "psql -Atc 'select current_database()'", 2, + qr/FATAL.*test/, 'pg_service conf selection 3'; +delete $ENV{'PGSERVICE'}; +unlink '/etc/postgresql-common/pg_service.conf'; + +# check proper error message if no cluster could be determined as default for +# pg_wrapper +is ((system "pg_ctlcluster $MAJORS[0] old stop >/dev/null"), 0, "stopping cluster $old"); +PgCommon::set_conf_value $MAJORS[0], 'old', 'postgresql.conf', 'port', '5435'; +is ((system "pg_ctlcluster $MAJORS[0] old start >/dev/null"), 0, "restarting cluster $old"); +like_program_out 'postgres', 'pg_lsclusters -h | sort -k3', 0, qr/.*5434.*5435.*5440.*/s, + 'port of first cluster was successfully changed'; +like_program_out 'postgres', "psql -l", 2, + qr/no.*default.*man pg_wrapper.*psql:.*\.s\.PGSQL.5432/is, + 'proper pg_wrapper warning and psql error if no cluster is suitable as default target'; +like_program_out 'postgres', "psql -Atl --cluster $new1", 0, + qr/test\|postgres\|/, + '--cluster selects appropriate cluster'; +like_program_out 'postgres', "psql -Atl -p 5434", 0, + qr/test\|postgres\|/, + '-p selects appropriate cluster'; +like_program_out 'postgres', "psql -Atlp 5434", 0, + qr/test\|postgres\|/, + '-Atlp selects appropriate cluster'; +like_program_out 'postgres', "psql -Atl --port 5434", 0, + qr/test\|postgres\|/, + '--port selects appropriate cluster'; +like_program_out 'postgres', "env PGPORT=5434 psql -Atl", 0, + qr/test\|postgres\|/, + '$PGPORT selects appropriate cluster'; + +# but specifying -p explicitly should work + +# restore original user_clusters +if (-f '/etc/postgresql-common/user_clusters.psqltestsuite') { + ok ((rename '/etc/postgresql-common/user_clusters.psqltestsuite', + '/etc/postgresql-common/user_clusters'), + 'Restoring original /etc/postgresql-common/user_clusters'); +} else { + pass '/etc/postgresql-common/user_clusters did not exist, not restoring'; +} + +# clean up +is ((system "pg_dropcluster $MAJORS[-1] new1 --stop"), 0, "dropping $new1"); +is ((system "pg_dropcluster $MAJORS[-1] new2 --stop"), 0, "dropping $new2"); +is ((system "pg_dropcluster $MAJORS[0] old --stop"), 0, "dropping $old"); + +# unlike the others, this tests leaves TIME_WAIT on localhost behind (seen on CentOS; seen for PG <= 9.1) +note 'waiting for TCP ports to close'; +for (1 .. 60) { + last if system 'netstat -avptn 2>/dev/null | grep -q ":543[2-9]\b"'; + sleep 1; +} + +check_clean; + +# vim: filetype=perl diff --git a/t/10.conf b/t/10.conf new file mode 100644 index 0000000..493fc51 --- /dev/null +++ b/t/10.conf @@ -0,0 +1,238 @@ +listen_addresses = 'localhost' +port = 5432 +max_connections = 100 +superuser_reserved_connections = 3 +unix_socket_directories = '/var/run/postgresql' +unix_socket_group = '' +unix_socket_permissions = 0777 +bonjour = off +bonjour_name = '' +authentication_timeout = 1min +ssl = on +ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' +ssl_prefer_server_ciphers = on +ssl_ecdh_curve = 'prime256v1' +ssl_dh_params_file = '' +ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem' +ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key' +ssl_ca_file = '' +ssl_crl_file = '' +password_encryption = md5 +db_user_namespace = off +row_security = on +krb_server_keyfile = '' +krb_caseins_users = off +tcp_keepalives_idle = 0 +tcp_keepalives_interval = 0 +tcp_keepalives_count = 0 +shared_buffers = 128MB +huge_pages = try +temp_buffers = 8MB +max_prepared_transactions = 0 +work_mem = 4MB +maintenance_work_mem = 64MB +replacement_sort_tuples = 150000 +autovacuum_work_mem = -1 +max_stack_depth = 2MB +dynamic_shared_memory_type = posix +temp_file_limit = -1 +max_files_per_process = 1000 +shared_preload_libraries = '' +vacuum_cost_delay = 0 +vacuum_cost_page_hit = 1 +vacuum_cost_page_miss = 10 +vacuum_cost_page_dirty = 20 +vacuum_cost_limit = 200 +bgwriter_delay = 200ms +bgwriter_lru_maxpages = 100 +bgwriter_lru_multiplier = 2.0 +bgwriter_flush_after = 512kB +effective_io_concurrency = 1 +max_worker_processes = 8 +max_parallel_workers_per_gather = 2 +max_parallel_workers = 8 +old_snapshot_threshold = -1 +backend_flush_after = 0 +wal_level = replica +fsync = on +synchronous_commit = on +wal_sync_method = fsync +full_page_writes = on +wal_compression = off +wal_log_hints = off +wal_buffers = -1 +wal_writer_delay = 200ms +wal_writer_flush_after = 1MB +commit_delay = 0 +commit_siblings = 5 +checkpoint_timeout = 5min +max_wal_size = 1GB +min_wal_size = 80MB +checkpoint_completion_target = 0.5 +checkpoint_flush_after = 256kB +checkpoint_warning = 30s +archive_mode = off +archive_command = '' +archive_timeout = 0 +max_wal_senders = 10 +wal_keep_segments = 0 +wal_sender_timeout = 60s +max_replication_slots = 10 +track_commit_timestamp = off +synchronous_standby_names = '' +vacuum_defer_cleanup_age = 0 +hot_standby = on +max_standby_archive_delay = 30s +max_standby_streaming_delay = 30s +wal_receiver_status_interval = 10s +hot_standby_feedback = off +wal_receiver_timeout = 60s +wal_retrieve_retry_interval = 5s +max_logical_replication_workers = 4 +max_sync_workers_per_subscription = 2 +enable_bitmapscan = on +enable_hashagg = on +enable_hashjoin = on +enable_indexscan = on +enable_indexonlyscan = on +enable_material = on +enable_mergejoin = on +enable_nestloop = on +enable_seqscan = on +enable_sort = on +enable_tidscan = on +seq_page_cost = 1.0 +random_page_cost = 4.0 +cpu_tuple_cost = 0.01 +cpu_index_tuple_cost = 0.005 +cpu_operator_cost = 0.0025 +parallel_tuple_cost = 0.1 +parallel_setup_cost = 1000.0 +min_parallel_table_scan_size = 8MB +min_parallel_index_scan_size = 512kB +effective_cache_size = 4GB +geqo = on +geqo_threshold = 12 +geqo_effort = 5 +geqo_pool_size = 0 +geqo_generations = 0 +geqo_selection_bias = 2.0 +geqo_seed = 0.0 +default_statistics_target = 100 +constraint_exclusion = partition +cursor_tuple_fraction = 0.1 +from_collapse_limit = 8 +join_collapse_limit = 8 +force_parallel_mode = off +log_destination = 'stderr' +logging_collector = off +log_directory = 'log' +log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' +log_file_mode = 0600 +log_truncate_on_rotation = off +log_rotation_age = 1d +log_rotation_size = 10MB +syslog_facility = 'LOCAL0' +syslog_ident = 'postgres' +syslog_sequence_numbers = on +syslog_split_messages = on +event_source = 'PostgreSQL' +log_min_messages = warning +log_min_error_statement = error +log_min_duration_statement = -1 +debug_print_parse = off +debug_print_rewritten = off +debug_print_plan = off +debug_pretty_print = on +log_checkpoints = off +log_connections = off +log_disconnections = off +log_duration = off +log_error_verbosity = default +log_hostname = off +log_line_prefix = '%m [%p] %q%u@%d ' +log_lock_waits = off +log_statement = 'none' +log_replication_commands = off +log_temp_files = -1 +log_timezone = 'CET' +cluster_name = '10/main' +update_process_title = on +track_activities = on +track_counts = on +track_io_timing = off +track_functions = none +track_activity_query_size = 1024 +stats_temp_directory = '/var/run/postgresql/10-main.pg_stat_tmp' +log_parser_stats = off +log_planner_stats = off +log_executor_stats = off +log_statement_stats = off +autovacuum = on +log_autovacuum_min_duration = -1 +autovacuum_max_workers = 3 +autovacuum_naptime = 1min +autovacuum_vacuum_threshold = 50 +autovacuum_analyze_threshold = 50 +autovacuum_vacuum_scale_factor = 0.2 +autovacuum_analyze_scale_factor = 0.1 +autovacuum_freeze_max_age = 200000000 +autovacuum_multixact_freeze_max_age = 400000000 +autovacuum_vacuum_cost_delay = 20ms +autovacuum_vacuum_cost_limit = -1 +client_min_messages = notice +search_path = '"$user", public' +default_tablespace = '' +temp_tablespaces = '' +check_function_bodies = on +default_transaction_isolation = 'read committed' +default_transaction_read_only = off +default_transaction_deferrable = off +session_replication_role = 'origin' +statement_timeout = 0 +lock_timeout = 0 +idle_in_transaction_session_timeout = 0 +vacuum_freeze_min_age = 50000000 +vacuum_freeze_table_age = 150000000 +vacuum_multixact_freeze_min_age = 5000000 +vacuum_multixact_freeze_table_age = 150000000 +bytea_output = 'hex' +xmlbinary = 'base64' +xmloption = 'content' +gin_fuzzy_search_limit = 0 +gin_pending_list_limit = 4MB +datestyle = 'iso, dmy' +intervalstyle = 'postgres' +timezone = 'CET' +timezone_abbreviations = 'Default' +extra_float_digits = 0 +client_encoding = sql_ascii +lc_messages = 'C' +lc_monetary = 'C' +lc_numeric = 'C' +lc_time = 'C' +default_text_search_config = 'pg_catalog.german' +dynamic_library_path = '$libdir' +local_preload_libraries = '' +session_preload_libraries = '' +deadlock_timeout = 1s +max_locks_per_transaction = 64 +max_pred_locks_per_transaction = 64 +max_pred_locks_per_relation = -2 +max_pred_locks_per_page = 2 +array_nulls = on +backslash_quote = safe_encoding +default_with_oids = off +escape_string_warning = on +lo_compat_privileges = off +operator_precedence_warning = off +quote_all_identifiers = off +standard_conforming_strings = on +synchronize_seqscans = on +transform_null_equals = off +exit_on_error = off +restart_after_crash = on +data_sync_retry = off +#include_dir = 'conf.d' +include_if_exists = 'exists.conf' +#include = 'special.conf' diff --git a/t/100_upgrade_scripts.t b/t/100_upgrade_scripts.t new file mode 100644 index 0000000..1a7f56c --- /dev/null +++ b/t/100_upgrade_scripts.t @@ -0,0 +1,148 @@ +# Check upgrade scripts + +use strict; + +use lib 't'; +use TestLib; + +my @versions = ($MAJORS[-1]); + +use Test::More tests => 27; +use PgCommon; + +# get_cluster_databases here and indirectly in run-upgrade-scripts is +# incompatible with eatmydata, remove it from the environment +if ($ENV{LD_PRELOAD} and $ENV{LD_PRELOAD} =~ /eatmydata/) { + $ENV{LD_PRELOAD} = join (' ', grep { $_ !~ /eatmydata/ } + split (/\s+/, $ENV{LD_PRELOAD})); +} + +my $shellaction = '#!/bin/sh +S=`basename $0` +SQL="INSERT INTO log VALUES (\'$S $1 $2 $3\')" +su -s /bin/sh -c "psql --cluster $1/$2 -c \"$SQL\" db1" postgres +'; + +my %test_sql_scripts = ( + 'all_all-sql-db_db.sql' => 'CREATE TABLE dbt(x int)', + 'all_all-sql-t1_t1.sql' => 'CREATE TABLE t1t(x int)', + 'all_all-sql-t0_t0.sql' => 'CREATE TABLE t0t(x int)', + '1_1-sql-db_db.sql' => 'CREATE TABLE v1t(x int)', + '2_2-sql-db_db.sql' => 'CREATE TABLE v2t(x int)', + 'all_all-sql-cluster_cluster.sql' => 'SELECT datname from pg_databases', + + 'all_all-sh-db_db.sh' => $shellaction, + 'all_all-sh-t1_t1.sh' => $shellaction, + 'all_all-sh-t0_t0.sh' => $shellaction, + '1_1-sh-db_db.sh' => $shellaction, + '2_2-sh-db_db.sh' => $shellaction, + 'all_all-sh-cluster_cluster.sh' => $shellaction, + 'all_all-shfail-cluster_cluster.sh' => 'echo "all-shfail-cluster:fail"; exit 1', + 'all_all-shnoexec-t0_t0.sh' => $shellaction +); + +# create clusters +foreach my $v (@versions) { + is ((system "pg_createcluster $v main --start >/dev/null"), 0, "pg_createcluster $v main"); + is_program_out 'postgres', "createdb --cluster $v/main db1", 0, ($v < 8.3 ? "CREATE DATABASE\n" : ''); + is_program_out 'postgres', "createdb --cluster $v/main db2", 0, ($v < 8.3 ? "CREATE DATABASE\n" : ''); + is_program_out 'postgres', "psql -q --cluster $v/main db1 -c 'CREATE TABLE log (str varchar)'", 0, ''; + my @dbs = get_cluster_databases $v, 'main'; + my @expected = ('template0', 'template1', 'db1', 'db2', 'postgres'); + if (eq_set \@dbs, \@expected) { + pass 'get_cluster_databases() works'; + } else { + fail "get_cluster_databases: got '@dbs', expected '@expected'"; + } +} + + +# create scripts +my $scriptdir = '/usr/share/postgresql-common/upgrade-scripts'; +ok_dir $scriptdir, ['SPECIFICATION'], "$scriptdir has no scripts (for the test)"; + +for my $n (keys %test_sql_scripts) { + open F, ">$scriptdir/$n" or die "could not create $scriptdir/$n: $!"; + print F $test_sql_scripts{$n}; + close F; + if ($n =~ /\.sh$/ && $n !~ /noexec/) { + chmod 0755, "$scriptdir/$n"; + } else { + chmod 0644, "$scriptdir/$n"; + } +} + +# call run-upgrade-scripts +my $outref; +is ((exec_as 0, '/usr/share/postgresql-common/run-upgrade-scripts 2 2>&1', $outref), + 0, 'run-upgrade-scripts succeeds'); + +is $$outref, "Executing upgrade script 2-sh-db... + cluster $versions[0]/main: db1 db2 +Executing upgrade script 2-sql-db... + cluster $versions[0]/main: db1 db2 +Executing upgrade script all-sh-cluster... + cluster $versions[0]/main: template1 +Executing upgrade script all-sh-db... + cluster $versions[0]/main: db1 db2 +Executing upgrade script all-sh-t0... + cluster $versions[0]/main: db1 db2 template0 template1 +Executing upgrade script all-sh-t1... + cluster $versions[0]/main: db1 db2 template1 +Executing upgrade script all-shfail-cluster... + cluster $versions[0]/main: template1[FAIL] +all-shfail-cluster:fail + +Executing upgrade script all-sql-cluster... + cluster $versions[0]/main: template1 +Executing upgrade script all-sql-db... + cluster $versions[0]/main: db1 db2 +Executing upgrade script all-sql-t0... + cluster $versions[0]/main: db1 db2 template0 template1 +Executing upgrade script all-sql-t1... + cluster $versions[0]/main: db1 db2 template1 +", 'correct run-upgrade-script output'; + +# check tables created by SQL scripts +foreach my $v (@versions) { + is_program_out 'postgres', + "psql --cluster $v/main db1 -Atc \"select tablename from pg_tables where schemaname = 'public' order by tablename\"", + 0, "dbt\nlog\nt0t\nt1t\nv2t\n", "check SQL scripts results in $v/main db1"; + is_program_out 'postgres', + "psql --cluster $v/main db2 -Atc \"select tablename from pg_tables where schemaname = 'public' order by tablename\"", + 0, "dbt\nt0t\nt1t\nv2t\n", "check SQL scripts results in $v/main db2"; +} + +# check log created by shell scripts +foreach my $v (@versions) { + is_program_out 'postgres', + "psql --cluster $v/main db1 -Atc 'select * from log order by str'", + 0, "2_2-sh-db_db.sh $v main db1 +2_2-sh-db_db.sh $v main db2 +all_all-sh-cluster_cluster.sh $v main template1 +all_all-sh-db_db.sh $v main db1 +all_all-sh-db_db.sh $v main db2 +all_all-sh-t0_t0.sh $v main db1 +all_all-sh-t0_t0.sh $v main db2 +all_all-sh-t0_t0.sh $v main template0 +all_all-sh-t0_t0.sh $v main template1 +all_all-sh-t1_t1.sh $v main db1 +all_all-sh-t1_t1.sh $v main db2 +all_all-sh-t1_t1.sh $v main template1 +", 'check shell scripts results in $v/main'; +} + +# clean up +for my $n (keys %test_sql_scripts) { + unlink "$scriptdir/$n" or die "could not remove $scriptdir/$n: $!"; +} + +ok_dir $scriptdir, ['SPECIFICATION'], "$scriptdir has no test suite scripts any more"; + +foreach (@versions) { + is ((system "pg_dropcluster $_ main --stop"), 0, "pg_dropcluster $_ main"); +} + +check_clean; + +# vim: filetype=perl diff --git a/t/11.conf b/t/11.conf new file mode 100644 index 0000000..184fd72 --- /dev/null +++ b/t/11.conf @@ -0,0 +1,252 @@ +listen_addresses = 'localhost' +port = 5432 +max_connections = 100 +superuser_reserved_connections = 3 +unix_socket_directories = '/var/run/postgresql' +unix_socket_group = '' +unix_socket_permissions = 0777 +bonjour = off +bonjour_name = '' +tcp_keepalives_idle = 0 +tcp_keepalives_interval = 0 +tcp_keepalives_count = 0 +authentication_timeout = 1min +password_encryption = md5 +db_user_namespace = off +krb_server_keyfile = '' +krb_caseins_users = off +ssl = on +ssl_ca_file = '' +ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem' +ssl_crl_file = '' +ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key' +ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' +ssl_prefer_server_ciphers = on +ssl_ecdh_curve = 'prime256v1' +ssl_dh_params_file = '' +ssl_passphrase_command = '' +ssl_passphrase_command_supports_reload = off +shared_buffers = 128MB +huge_pages = try +temp_buffers = 8MB +max_prepared_transactions = 0 +work_mem = 4MB +maintenance_work_mem = 64MB +autovacuum_work_mem = -1 +max_stack_depth = 2MB +dynamic_shared_memory_type = posix +temp_file_limit = -1 +max_files_per_process = 1000 +vacuum_cost_delay = 0 +vacuum_cost_page_hit = 1 +vacuum_cost_page_miss = 10 +vacuum_cost_page_dirty = 20 +vacuum_cost_limit = 200 +bgwriter_delay = 200ms +bgwriter_lru_maxpages = 100 +bgwriter_lru_multiplier = 2.0 +bgwriter_flush_after = 512kB +effective_io_concurrency = 1 +max_worker_processes = 8 +max_parallel_maintenance_workers = 2 +max_parallel_workers_per_gather = 2 +parallel_leader_participation = on +max_parallel_workers = 8 +old_snapshot_threshold = -1 +backend_flush_after = 0 +wal_level = replica +fsync = on +synchronous_commit = on +wal_sync_method = fsync +full_page_writes = on +wal_compression = off +wal_log_hints = off +wal_buffers = -1 +wal_writer_delay = 200ms +wal_writer_flush_after = 1MB +commit_delay = 0 +commit_siblings = 5 +checkpoint_timeout = 5min +max_wal_size = 1GB +min_wal_size = 80MB +checkpoint_completion_target = 0.5 +checkpoint_flush_after = 256kB +checkpoint_warning = 30s +archive_mode = off +archive_command = '' +archive_timeout = 0 +max_wal_senders = 10 +wal_keep_segments = 0 +wal_sender_timeout = 60s +max_replication_slots = 10 +track_commit_timestamp = off +synchronous_standby_names = '' +vacuum_defer_cleanup_age = 0 +hot_standby = on +max_standby_archive_delay = 30s +max_standby_streaming_delay = 30s +wal_receiver_status_interval = 10s +hot_standby_feedback = off +wal_receiver_timeout = 60s +wal_retrieve_retry_interval = 5s +max_logical_replication_workers = 4 +max_sync_workers_per_subscription = 2 +enable_bitmapscan = on +enable_hashagg = on +enable_hashjoin = on +enable_indexscan = on +enable_indexonlyscan = on +enable_material = on +enable_mergejoin = on +enable_nestloop = on +enable_parallel_append = on +enable_seqscan = on +enable_sort = on +enable_tidscan = on +enable_partitionwise_join = off +enable_partitionwise_aggregate = off +enable_parallel_hash = on +enable_partition_pruning = on +seq_page_cost = 1.0 +random_page_cost = 4.0 +cpu_tuple_cost = 0.01 +cpu_index_tuple_cost = 0.005 +cpu_operator_cost = 0.0025 +parallel_tuple_cost = 0.1 +parallel_setup_cost = 1000.0 +jit_above_cost = 100000 +jit_inline_above_cost = 500000 +jit_optimize_above_cost = 500000 +min_parallel_table_scan_size = 8MB +min_parallel_index_scan_size = 512kB +effective_cache_size = 4GB +geqo = on +geqo_threshold = 12 +geqo_effort = 5 +geqo_pool_size = 0 +geqo_generations = 0 +geqo_selection_bias = 2.0 +geqo_seed = 0.0 +default_statistics_target = 100 +constraint_exclusion = partition +cursor_tuple_fraction = 0.1 +from_collapse_limit = 8 +join_collapse_limit = 8 +force_parallel_mode = off +jit = off +log_destination = 'stderr' +logging_collector = off +log_directory = 'log' +log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' +log_file_mode = 0600 +log_truncate_on_rotation = off +log_rotation_age = 1d +log_rotation_size = 10MB +syslog_facility = 'LOCAL0' +syslog_ident = 'postgres' +syslog_sequence_numbers = on +syslog_split_messages = on +event_source = 'PostgreSQL' +log_min_messages = warning +log_min_error_statement = error +log_min_duration_statement = -1 +debug_print_parse = off +debug_print_rewritten = off +debug_print_plan = off +debug_pretty_print = on +log_checkpoints = off +log_connections = off +log_disconnections = off +log_duration = off +log_error_verbosity = default +log_hostname = off +log_line_prefix = '%m [%p] %q%u@%d ' +log_lock_waits = off +log_statement = 'none' +log_replication_commands = off +log_temp_files = -1 +log_timezone = 'CET' +cluster_name = '11/main' +update_process_title = on +track_activities = on +track_counts = on +track_io_timing = off +track_functions = none +track_activity_query_size = 1024 +stats_temp_directory = '/var/run/postgresql/11-main.pg_stat_tmp' +log_parser_stats = off +log_planner_stats = off +log_executor_stats = off +log_statement_stats = off +autovacuum = on +log_autovacuum_min_duration = -1 +autovacuum_max_workers = 3 +autovacuum_naptime = 1min +autovacuum_vacuum_threshold = 50 +autovacuum_analyze_threshold = 50 +autovacuum_vacuum_scale_factor = 0.2 +autovacuum_analyze_scale_factor = 0.1 +autovacuum_freeze_max_age = 200000000 +autovacuum_multixact_freeze_max_age = 400000000 +autovacuum_vacuum_cost_delay = 20ms +autovacuum_vacuum_cost_limit = -1 +client_min_messages = notice +search_path = '"$user", public' +row_security = on +default_tablespace = '' +temp_tablespaces = '' +check_function_bodies = on +default_transaction_isolation = 'read committed' +default_transaction_read_only = off +default_transaction_deferrable = off +session_replication_role = 'origin' +statement_timeout = 0 +lock_timeout = 0 +idle_in_transaction_session_timeout = 0 +vacuum_freeze_min_age = 50000000 +vacuum_freeze_table_age = 150000000 +vacuum_multixact_freeze_min_age = 5000000 +vacuum_multixact_freeze_table_age = 150000000 +vacuum_cleanup_index_scale_factor = 0.1 +bytea_output = 'hex' +xmlbinary = 'base64' +xmloption = 'content' +gin_fuzzy_search_limit = 0 +gin_pending_list_limit = 4MB +datestyle = 'iso, dmy' +intervalstyle = 'postgres' +timezone = 'CET' +timezone_abbreviations = 'Default' +extra_float_digits = 0 +client_encoding = sql_ascii +lc_messages = 'C' +lc_monetary = 'C' +lc_numeric = 'C' +lc_time = 'C' +default_text_search_config = 'pg_catalog.german' +shared_preload_libraries = '' +local_preload_libraries = '' +session_preload_libraries = '' +jit_provider = 'llvmjit' +dynamic_library_path = '$libdir' +deadlock_timeout = 1s +max_locks_per_transaction = 64 +max_pred_locks_per_transaction = 64 +max_pred_locks_per_relation = -2 +max_pred_locks_per_page = 2 +array_nulls = on +backslash_quote = safe_encoding +default_with_oids = off +escape_string_warning = on +lo_compat_privileges = off +operator_precedence_warning = off +quote_all_identifiers = off +standard_conforming_strings = on +synchronize_seqscans = on +transform_null_equals = off +exit_on_error = off +restart_after_crash = on +data_sync_retry = off +#include_dir = 'conf.d' +include_if_exists = 'exists.conf' +#include = 'special.conf' diff --git a/t/110_integrate_cluster.t b/t/110_integrate_cluster.t new file mode 100644 index 0000000..d902c0d --- /dev/null +++ b/t/110_integrate_cluster.t @@ -0,0 +1,43 @@ +# Check integration of an already existing cluster + +use strict; + +use lib 't'; +use TestLib; +use File::Temp qw/tempdir/; + +my $version = $MAJORS[-1]; + +use Test::More tests => 32; +use PgCommon; + +delete $ENV{'LANG'}; +delete $ENV{'LANGUAGE'}; +$ENV{'LC_ALL'} = 'C'; + +my $wdir = tempdir (CLEANUP => 1); +chmod 0755, $wdir or die "Could not chmod $wdir: $!"; + +# create clusters for different owners and check their integration +for my $o ('postgres', 'nobody') { + my $cdir = "$wdir/c"; + mkdir $cdir; + my $oid = getpwnam $o; + chown $oid, 0, $cdir or die "Could not chown $cdir to $oid: $!"; + like_program_out $o, "$PgCommon::binroot$version/bin/initdb $cdir/$o", + 0, qr/Success/, "creating raw initdb cluster for user $o"; + like_program_out 0, "pg_createcluster $version $o -d $cdir/$o", 0, + qr/Configuring already existing cluster/i, "integrating $o cluster"; + like_program_out 0, "pg_lsclusters", 0, + qr/$version\s+$o\s+5432\s+down\s+$o\s/, 'correct pg_lsclusters output'; + is_program_out $o, "pg_ctlcluster $version $o start", 0, '', "starting cluster $o"; + like_program_out 0, "pg_lsclusters", 0, + qr/$version\s+$o\s+5432\s+online\s+$o\s/, 'correct pg_lsclusters output'; + is ((system "pg_dropcluster $version $o --stop"), 0, "dropping cluster $o"); + ok_dir $cdir, [], 'No files in temporary cluster dir left behind'; + rmdir $cdir; +} + +check_clean; + +# vim: filetype=perl diff --git a/t/120_pg_upgradecluster_scripts.t b/t/120_pg_upgradecluster_scripts.t new file mode 100644 index 0000000..4fdc480 --- /dev/null +++ b/t/120_pg_upgradecluster_scripts.t @@ -0,0 +1,114 @@ +# Check /etc/p-c/pg_upgradecluster.d/ scripts and proper handling of already +# existing tables in the target cluster. + +use strict; + +use lib 't'; +use TestLib; +use PgCommon; +use Test::More tests => (@MAJORS == 1) ? 1 : 31; + +if (@MAJORS == 1) { + pass 'only one major version installed, skipping upgrade tests'; + exit 0; +} + + +# create old cluster +is ((system "pg_createcluster $MAJORS[0] main --start >/dev/null"), 0, "pg_createcluster $MAJORS[0] main"); + +# add data table, auxtable with 'old...' values, and an unrelated auxtable in +# another schema +is_program_out 'postgres', + 'psql template1 -qc "create table auxdata (x varchar(10)); insert into auxdata values (\'old1\'); insert into auxdata values (\'old2\')"', + 0, '', 'adding auxdata to template1 and fill in some "old..." values'; +is_program_out 'postgres', "createdb test", 0, ''; +is_program_out 'postgres', 'psql test -qc "create table userdata(x int); insert into userdata values(42); insert into userdata values(256)"', + 0, '', 'creating userdata table'; +is_program_out 'postgres', + 'psql test -qc "create schema s; create table s.auxdata (x varchar(10)); insert into s.auxdata values (\'schema1\')"', + 0, '', 'adding schema s and s.auxdata to test and fill in some values'; + +if (not -d '/etc/postgresql-common/pg_upgradecluster.d') { + mkdir '/etc/postgresql-common/pg_upgradecluster.d' or die "mkdir: $!"; +} + +# move existing files away +for my $f (glob("/etc/postgresql-common/pg_upgradecluster.d/*")) { + next if ($f =~ /\.disabled$/); + rename $f, "$f.disabled"; +} + +# create test scripts +chmod 0755, '/etc/postgresql-common/pg_upgradecluster.d' or die "chmod: $!"; +open F, '>/etc/postgresql-common/pg_upgradecluster.d/auxdata' or die "open: $!"; +print F <<EOS; +#!/bin/sh -e +# Arguments: <old version> <cluster name> <new version> <phase> +oldver=\$1 +cluster=\$2 +newver=\$3 +phase=\$4 + +if [ \$phase = init ]; then + createdb --cluster \$newver/\$cluster idb +fi + +if [ \$phase = finish ]; then + psql --cluster \$newver/\$cluster template1 <<EOF +drop table if exists auxdata; +create table auxdata (x varchar(10)); +insert into auxdata values ('new1'); +insert into auxdata values ('new2'); +EOF +fi + +EOS +close F; +chmod 0755, '/etc/postgresql-common/pg_upgradecluster.d/auxdata' or die "chmod: $!"; + +open F, '>/etc/postgresql-common/pg_upgradecluster.d/badscript' or die "open: $!"; +print F <<EOS; +#!/bin/false +EOS +close F; +chmod 0755, '/etc/postgresql-common/pg_upgradecluster.d/badscript' or die "chmod: $!"; + +# upgrade cluster +my $outref; +is ((exec_as 0, "pg_upgradecluster -v $MAJORS[-1] $MAJORS[0] main", $outref, 1), 1, 'pg_upgradecluster fails with bad script'); +like $$outref, qr/error|fail/i, 'server error messages during upgrade'; +unlink '/etc/postgresql-common/pg_upgradecluster.d/badscript'; + +is ((exec_as 0, "pg_upgradecluster -v $MAJORS[-1] $MAJORS[0] main", $outref, 0), 0, 'pg_upgradecluster succeeds'); +unlike $$outref, qr/error|fail/i, 'no server error messages during upgrade'; +like $$outref, qr/Starting target cluster/, 'pg_upgradecluster reported cluster startup'; +like $$outref, qr/Success. Please check/, 'pg_upgradecluster reported successful operation'; + +is ((system "pg_dropcluster $MAJORS[0] main --stop"), 0, 'Dropping old cluster'); + +# check new version cluster +is_program_out 'postgres', 'psql template1 -Atc "select * from auxdata order by x"', 0, + "new1\nnew2\n", 'new cluster\'s template1/auxdata table is the script\'s version'; + +like_program_out 'postgres', 'psql -Atl', 0, qr/^idb\b.*^test\b/ms, + 'upgraded cluster has idb and test databases'; + +is_program_out 'postgres', 'psql test -Atc "select * from s.auxdata"', 0, + "schema1\n", 'new cluster\'s test/auxdata table in schema s was upgraded normally'; + +# remove test script +unlink '/etc/postgresql-common/pg_upgradecluster.d/auxdata' or die "unlink: $!"; + +# restore original contents +for my $f (glob("/etc/postgresql-common/pg_upgradecluster.d/*.disabled")) { + my $f2 = $f; + $f2 =~ s/\.disabled$//; + rename $f, $f2; +} + +# clean up +is ((system "pg_dropcluster $MAJORS[-1] main --stop"), 0, "pg_dropcluster $MAJORS[-1] main"); +check_clean; + +# vim: filetype=perl diff --git a/t/130_nonroot_admin.t b/t/130_nonroot_admin.t new file mode 100644 index 0000000..312d694 --- /dev/null +++ b/t/130_nonroot_admin.t @@ -0,0 +1,51 @@ +# Check that cluster administration works as non-root if the invoker has +# sufficient permissions on directories. + +use strict; + +use lib 't'; +use TestLib; + +my $version = $MAJORS[-1]; +my $oldversion = $MAJORS[0]; + +use Test::More tests => 22; +use PgCommon; + +my $testuser = 'postgres'; +my ($uid, $gid) = (getpwnam $testuser)[2,3]; + +# pg_createcluster and pg_ctlcluster +is ((exec_as $testuser, "pg_createcluster $version main --start"), 0, + "pg_createcluster succeeds as user $testuser with appropriate owner permissions"); + +like_program_out $testuser, 'pg_lsclusters -h', 0, qr/^$version\s+main.*online/m; +like_program_out 'postgres', 'psql -Atl', 0, qr/template1.*UTF8/; + +# pg_dropcluster +is ((exec_as $testuser, "pg_dropcluster $version main --stop"), 0, + "pg_dropcluster succeeds as user $testuser with appropriate directory owner permissions"); + +# pg_upgradecluster +SKIP: { + skip 'Only one major version installed, skipping pg_upgradecluster tests', 8 if ($oldversion eq $version); + + is ((exec_as $testuser, "pg_createcluster $oldversion main --start"), 0, + "pg_createcluster succeeds as user $testuser with appropriate group permissions"); + my $outref; + is ((exec_as $testuser, "pg_upgradecluster -v $version $oldversion main", $outref, 0), 0, + "pg_upgradecluster succeeds as user $testuser"); + like $$outref, qr/Starting target cluster/, 'pg_upgradecluster reported cluster startup'; + like $$outref, qr/Success. Please check/, 'pg_upgradecluster reported successful operation'; + + like_program_out $testuser, 'pg_lsclusters -h', 0, + qr/^$oldversion\s+main.*down.*\n^$version\s+main.*online/m; + + # clean up + is ((exec_as $testuser, "pg_dropcluster $oldversion main"), 0); + is ((exec_as $testuser, "pg_dropcluster $version main --stop"), 0); +} + +check_clean; + +# vim: filetype=perl diff --git a/t/135_pg_virtualenv.t b/t/135_pg_virtualenv.t new file mode 100644 index 0000000..1662e5b --- /dev/null +++ b/t/135_pg_virtualenv.t @@ -0,0 +1,35 @@ +# check if pg_virtualenv runs ok, even under fakeroot + +use strict; +use warnings; + +use lib 't'; +use TestLib; + +use Test::More tests => 12 * @MAJORS + 8; + +foreach my $v (@MAJORS) { + my $args = 'sh -c \'echo "id|$(id -un)"; psql -AtXxc "SELECT current_user"\''; + my $virtualenv = "pg_virtualenv -v $v $args"; + + $ENV{USER} = 'root'; + like_program_out 'root', $virtualenv, 0, qr!id.root\ncurrent_user.postgres!, "running pg_virtualenv as root"; + $ENV{USER} = 'postgres'; + like_program_out 'postgres', $virtualenv, 0, qr!id.postgres\ncurrent_user.postgres!, "running pg_virtualenv as postgres"; + $ENV{USER} = 'nobody'; + like_program_out 'nobody', $virtualenv, 0, qr!id.nobody\ncurrent_user.nobody!, "running pg_virtualenv as nobody"; + + SKIP: { + skip "/usr/bin/fakeroot not available", 6 unless (-x "/usr/bin/fakeroot"); # CentOS doesn't have fakeroot + $ENV{USER} = 'root'; + like_program_out 'root', "fakeroot $virtualenv", 0, qr!id.root\ncurrent_user.postgres!, "running fakeroot pg_virtualenv as root"; + $ENV{USER} = 'postgres'; + like_program_out 'postgres', "fakeroot $virtualenv", 0, qr!id.root\ncurrent_user.postgres!, "running fakeroot pg_virtualenv as postgres"; + $ENV{USER} = 'nobody'; + like_program_out 'nobody', "fakeroot $virtualenv", 0, qr!id.root\ncurrent_user.nobody!, "running fakeroot pg_virtualenv as nobody"; + } +} + +check_clean; + +# vim: filetype=perl diff --git a/t/140_pg_config.t b/t/140_pg_config.t new file mode 100644 index 0000000..4aee247 --- /dev/null +++ b/t/140_pg_config.t @@ -0,0 +1,89 @@ +# Check pg_config output + +use strict; + +use lib 't'; +use TestLib; +use PgCommon; +use Test::More tests => 14 * @MAJORS + ($PgCommon::rpm ? 1 : 2) * 12; + +my $multiarch = ''; +unless ($PgCommon::rpm) { + $multiarch = `dpkg-architecture -qDEB_HOST_MULTIARCH 2>/dev/null`; + chomp $multiarch; +} +note "Multiarch is " . ($multiarch ? 'enabled' : 'disabled'); + +my $version; +foreach $version (@MAJORS) { + note "checking version specific output for $version"; + if ($version < '8.2') { + pass "Skipping known-broken pg_config check for version $version"; + for (my $i = 0; $i < 13; ++$i) { pass '...'; } + next; + } + is_program_out 'postgres', "$PgCommon::binroot$version/bin/pg_config --pgxs", 0, + "$PgCommon::binroot$version/lib/pgxs/src/makefiles/pgxs.mk\n"; + my $libdir = "/usr/lib" . ($version >= 9.3 and $multiarch ? "/$multiarch" : "") . "\n"; + $libdir = "$PgCommon::binroot$version/lib\n" if ($PgCommon::rpm); + is_program_out 'postgres', "$PgCommon::binroot$version/bin/pg_config --libdir", 0, + $libdir; + is_program_out 'postgres', "$PgCommon::binroot$version/bin/pg_config --pkglibdir", 0, + "$PgCommon::binroot$version/lib\n"; + is_program_out 'postgres', "$PgCommon::binroot$version/bin/pg_config --bindir", 0, + "$PgCommon::binroot$version/bin\n"; + # mkdir should be in /bin on Debian. If /bin was linked to /usr/bin at build time, usrmerge was installed + SKIP: { + skip 'MKDIR_P not present before 9.0', 2 if ($version < 9.0); + my $mkdir_path = $PgCommon::rpm ? '/usr/bin' : '/bin'; + is_program_out 'postgres', "grep ^MKDIR_P $PgCommon::binroot$version/lib/pgxs/src/Makefile.global", 0, + "MKDIR_P = $mkdir_path/mkdir -p\n"; + } + SKIP: { + skip 'build path not canonicalized on RedHat', 4 if ($PgCommon::rpm); + my $pkgversion = `dpkg-query -f '\${Version}' -W postgresql-server-dev-$version`; + # check that we correctly canonicalized the build paths + SKIP: { + skip 'abs_top_builddir introduced in 9.5', 2 if ($version < 9.5); + skip 'abs_top_builddir not patched in Debian (old)stable', 2 if ($version < 10 and $pkgversion !~ /pgdg/); + is_program_out 'postgres', "grep ^abs_top_builddir $PgCommon::binroot$version/lib/pgxs/src/Makefile.global", 0, + "abs_top_builddir = /build/postgresql-$version/build\n"; + } + SKIP: { + skip 'abs_top_srcdir not patched before 9.3', 2 if ($version < 9.3); + skip 'abs_top_srcdir not patched in Debian (old)stable', 2 if ($version < 10 and $pkgversion !~ /pgdg/); + is_program_out 'postgres', "grep ^abs_top_srcdir $PgCommon::binroot$version/lib/pgxs/src/Makefile.global", 0, + "abs_top_srcdir = /build/postgresql-$version/build/..\n"; + } + } +} + +my @pg_configs = $PgCommon::rpm ? qw(pg_config) : qw(pg_config pg_config.libpq-dev); +for my $pg_config (@pg_configs) { + if ($pg_config eq 'pg_config' or $PgCommon::rpm) { # pg_config should point at newest installed postgresql-server-dev-$version + $version = $ALL_MAJORS[-1]; + } else { # pg_config.libpq-dev should point at postgresql-server-dev-$(version of libpq-dev) + my $libpqdev_version = `dpkg-query --showformat '\${Version}' --show libpq-dev`; + $libpqdev_version =~ /^([89].\d|1.)/ or die "could not determine libpq-dev version"; + $version = $1; + } + note "checking $pg_config output (should behave like version $version)"; + + SKIP: { + my $pgc = "$PgCommon::binroot$version/bin/pg_config"; + skip "$pgc not installed, can't check full $pg_config output", 2 unless (-x $pgc); + my $full_output = `$pgc`; + is_program_out 'postgres', "$pg_config", 0, $full_output; + } + like_program_out 'postgres', "$pg_config --help", 0, qr/--includedir-server/; + is_program_out 'postgres', "$pg_config --pgxs", 0, + "$PgCommon::binroot$version/lib/pgxs/src/makefiles/pgxs.mk\n"; + my $libdir = "/usr/lib" . ($version >= 9.3 and $multiarch ? "/$multiarch" : "") . "\n"; + $libdir = "$PgCommon::binroot$version/lib\n" if ($PgCommon::rpm); + is_program_out 'postgres', "$pg_config --libdir", 0, + $libdir; + is_program_out 'postgres', "$pg_config --pkglibdir", 0, + "$PgCommon::binroot$version/lib\n"; + is_program_out 'postgres', "$pg_config --bindir", 0, + "$PgCommon::binroot$version/bin\n"; +} diff --git a/t/150_tsearch_stemming.t b/t/150_tsearch_stemming.t new file mode 100644 index 0000000..a2a6253 --- /dev/null +++ b/t/150_tsearch_stemming.t @@ -0,0 +1,108 @@ +# Check tsearch, and stemming with dynamic creation of .affix/.dict files + +use strict; + +use lib 't'; +use TestLib; +use PgCommon; + +my $version = $MAJORS[-1]; + +use Test::More tests => ($MAJORS[-1] < 8.3 or $PgCommon::rpm) ? 1 : 37; +if ($version < 8.3) { + pass 'tsearch dictionaries not tested before 8.3'; + exit; +} +if ($PgCommon::rpm) { + pass 'tsearch dictionaries not handled by postgresql-common on RedHat'; + exit; +} + +# test pg_updatedicts +unlink '/var/cache/postgresql/dicts/en_us.affix'; +unlink '/var/cache/postgresql/dicts/en_us.dict'; +unlink "/usr/share/postgresql/$version/tsearch_data/en_us.affix"; +unlink "/usr/share/postgresql/$version/tsearch_data/en_us.dict"; +is ((exec_as 0, 'pg_updatedicts'), 0, 'pg_updatedicts succeeded'); +ok -f '/var/cache/postgresql/dicts/en_us.affix', + 'pg_updatedicts created en_us.affix'; +ok -f '/var/cache/postgresql/dicts/en_us.dict', + 'pg_updatedicts created en_us.dict'; +ok -l "/usr/share/postgresql/$version/tsearch_data/en_us.affix", + "pg_updatedicts created $version en_us.affix symlink"; +ok -l "/usr/share/postgresql/$version/tsearch_data/en_us.dict", + "pg_updatedicts created $version en_us.dict symlink"; + +# create cluster +is ((system "pg_createcluster $version main --start >/dev/null"), 0, "pg_createcluster $version main"); + +# create DB with en_US text search configuration +is_program_out 'postgres', 'createdb fts', 0, ''; + +my $outref; + +is ((exec_as 'postgres', 'psql -qd fts -c " + CREATE TEXT SEARCH CONFIGURATION public.sc_english ( COPY = pg_catalog.english ); + CREATE TEXT SEARCH DICTIONARY english_ispell (TEMPLATE = ispell, DictFile = en_US, + AffFile = en_US, StopWords = english); + SET default_text_search_config = \'public.sc_english\'; + ALTER TEXT SEARCH CONFIGURATION public.sc_english + ALTER MAPPING FOR asciiword WITH english_ispell, english_stem;"', $outref), + 0, 'creating en_US full text search configuration ' . $$outref); + +# create test table and index +my $outref; +is ((exec_as 'postgres', 'psql -qd fts -c " + CREATE TABLE stuff (id SERIAL PRIMARY KEY, text TEXT, textsearch tsvector); + UPDATE stuff SET textsearch = to_tsvector(\'public.sc_english\', coalesce(text, \'\')); + CREATE INDEX textsearch_idx ON stuff USING gin(textsearch); + CREATE TRIGGER textsearch_update_trigger BEFORE INSERT OR UPDATE + ON stuff FOR EACH ROW EXECUTE PROCEDURE + tsvector_update_trigger(textsearch, \'public.sc_english\', text); + INSERT INTO stuff (text) VALUES (\'PostgreSQL rocks\'); + INSERT INTO stuff (text) VALUES (\'Linux rocks\'); + INSERT INTO stuff (text) VALUES (\'I am your father\'\'s nephew\'\'s former roommate\'); + INSERT INTO stuff (text) VALUES (\'3 cafés\'); + "'), 0, 'creating data table and search index'); + +# test stemming +is_program_out 'postgres', + 'psql -Atd fts -c "SELECT dictionary, lexemes FROM ts_debug(\'public.sc_english\', \'friendliest\')"', + 0, "english_ispell|{friendly}\n", 'stem search of correct word'; +is_program_out 'postgres', + 'psql -Atd fts -c "SELECT dictionary, lexemes FROM ts_debug(\'public.sc_english\', \'father\'\'s\')"', + 0, "english_ispell|{father}\n|\nenglish_ispell|{}\n", 'stem search of correct word'; +is_program_out 'postgres', + 'psql -Atd fts -c "SELECT dictionary, lexemes FROM ts_debug(\'public.sc_english\', \'duffles\')"', + 0, "english_stem|{duffl}\n", 'stem search of unknown word'; + +# test searching +is_program_out 'postgres', + 'psql -Atd fts -c "SELECT text FROM stuff, to_tsquery(\'rocks\') query WHERE query @@ to_tsvector(text)"', + 0, "PostgreSQL rocks\nLinux rocks\n", 'full text search, exact word'; + +is_program_out 'postgres', + 'psql -Atd fts -c "SELECT text FROM stuff, to_tsquery(\'rock\') query WHERE query @@ to_tsvector(text)"', + 0, "PostgreSQL rocks\nLinux rocks\n", 'full text search for word stem'; + +is_program_out 'postgres', + 'psql -Atd fts -c "SELECT text FROM stuff, to_tsquery(\'roc\') query WHERE query @@ to_tsvector(text)"', + 0, '', 'full text search for word substring fails'; + +is_program_out 'postgres', + 'psql -Atd fts -c "SELECT text FROM stuff, to_tsquery(\'cafés\') query WHERE query @@ to_tsvector(text)"', + 0, "3 cafés\n", 'full text search, exact unicode word'; + +is_program_out 'postgres', + 'psql -Atd fts -c "SELECT text FROM stuff, to_tsquery(\'café\') query WHERE query @@ to_tsvector(text)"', + 0, "3 cafés\n", 'full text search for unicode word stem'; + +is_program_out 'postgres', + 'psql -Atd fts -c "SELECT text FROM stuff, to_tsquery(\'afé\') query WHERE query @@ to_tsvector(text)"', + 0, '', 'full text search for unicode word substring fails'; + +# clean up +is ((system "pg_dropcluster $version main --stop"), 0); +check_clean; + +# vim: filetype=perl diff --git a/t/160_alternate_confroot.t b/t/160_alternate_confroot.t new file mode 100644 index 0000000..3ec58e7 --- /dev/null +++ b/t/160_alternate_confroot.t @@ -0,0 +1,57 @@ +# Check that we can do all operations using a per-user $PG_CLUSTER_CONF_ROOT + +use strict; + +use lib 't'; +use TestLib; + +my $version = $MAJORS[0]; + +use Test::More tests => 28; + +# prepare nobody-owned root dir for $PG_CLUSTER_CONF_ROOT +my $rootdir=`su -s /bin/sh -c 'mktemp -d' nobody`; +chomp $rootdir; +($rootdir) = $rootdir =~ m!^([a-zA-Z0-9._/]+)$!; # untaint +$ENV{'PG_CLUSTER_CONF_ROOT'} = "$rootdir/etc"; + +is ((exec_as 'nobody', "pg_createcluster $version test -d $rootdir/data/test -l $rootdir/test.log --start"), 0); + +is_program_out 'nobody', 'env -u PG_CLUSTER_CONF_ROOT pg_lsclusters -h', 0, ''; +like_program_out 'nobody', "pg_lsclusters -h", 0, + qr!^$version\s+test.*online\s+nobody\s+$rootdir/data/test\s+$rootdir/test.log$!; + +like_program_out 'nobody', "psql -Atl", 0, qr/template1.*UTF8/; + +# pg_upgradecluster +if ($MAJORS[0] ne $MAJORS[-1]) { + my $outref; + is ((exec_as 'nobody', "pg_upgradecluster --logfile $rootdir/testupgr.log -v $MAJORS[-1] $version test $rootdir/data/testupgr", $outref, 0), 0); + like $$outref, qr/Starting target cluster/, 'pg_upgradecluster reported cluster startup'; + like $$outref, qr/Success. Please check/, 'pg_upgradecluster reported successful operation'; + + like_program_out 'nobody', 'pg_lsclusters -h', 0, + qr!^$version\s+test.*down.*\n^$MAJORS[-1]\s+test.*online\s+nobody\s+$rootdir/data/testupgr\s+$rootdir/testupgr.log$!m; + + # clean up + is_program_out 'nobody', "pg_dropcluster $version test", 0, ''; + is_program_out 'nobody', "pg_dropcluster $MAJORS[-1] test --stop", 0, ''; +} else { + pass 'Only one major version installed, skipping pg_upgradecluster tests'; + for (my $i = 0; $i < 6; ++$i) { pass '...'; } + + is_program_out 'nobody', "pg_dropcluster $version test --stop", 0, ''; +} + +# pg_dropcluster +is_program_out 'nobody', "pg_lsclusters -h", 0, ''; + +ok_dir "$rootdir/data", [], 'No files in root/data left behind'; +ok_dir "$rootdir", ['etc', 'data'], 'No cruft in root dir left behind'; + +system "rm -rf $rootdir"; + +delete $ENV{'PG_CLUSTER_CONF_ROOT'}; +check_clean; + +# vim: filetype=perl diff --git a/t/170_extensions.t b/t/170_extensions.t new file mode 100644 index 0000000..8584416 --- /dev/null +++ b/t/170_extensions.t @@ -0,0 +1,85 @@ +# Check that all extensions install successfully. + +use strict; + +use lib 't'; +use TestLib; +use PgCommon; +use Test::More 0.87; # needs libtest-simple-perl backport on lenny + +foreach my $v (@MAJORS) { +note "Running tests for $v"; + +if ($v < '9.1') { + pass 'No extensions for version < 9.1'; + next; +} + +# create cluster +is ((system "pg_createcluster $v main --start >/dev/null"), 0, "pg_createcluster $v main"); + +# plpgsql is installed by default +is_program_out 'postgres', "psql -Atc 'SELECT extname FROM pg_extension'", 0, "plpgsql\n"; + +my %depends = ( + earthdistance => [qw(cube)], + hstore_plperl => [qw(hstore plperl)], + hstore_plperlu => [qw(hstore plperlu)], + hstore_plpython2u => [qw(hstore plpython2u)], + hstore_plpython3u => [qw(hstore plpython3u)], + hstore_plpythonu => [qw(hstore plpythonu)], + jsonb_plperl => [qw(plperl)], # PG 11 + jsonb_plperlu => [qw(plperlu)], # PG 11 + jsonb_plpython2u => [qw(plpython2u)], # PG 11 + jsonb_plpython3u => [qw(plpython3u)], # PG 11 + jsonb_plpythonu => [qw(plpythonu)], # PG 11 + ltree_plpython2u => [qw(ltree plpython2u)], + ltree_plpython3u => [qw(ltree plpython3u)], + ltree_plpythonu => [qw(ltree plpythonu)], +); + +foreach (</usr/share/postgresql/$v/extension/*.control>) { + my ($extname) = $_ =~ /^.*\/(.*)\.control$/; + next if ($extname eq 'plpgsql'); + + if ($depends{$extname}) { + for my $dep (@{$depends{$extname}}) { + is_program_out 'postgres', "psql -qc 'CREATE EXTENSION $dep'", 0, '', + "$extname dependency $dep installs without error"; + } + } + + if ($extname eq 'hstore' && $v eq '9.1') { + # EXFAIL: hstore in 9.1 throws a warning about obsolete => operator + like_program_out 'postgres', "psql -qc 'CREATE EXTENSION \"$extname\"'", 0, + qr/=>/, "extension $extname installs (with warning)"; + } elsif ($extname eq 'chkpass' && $v >= '9.5') { + # chkpass is slightly broken, see + # http://www.postgresql.org/message-id/20141117162116.GA3565@msg.df7cb.de + like_program_out 'postgres', "psql -qc 'CREATE EXTENSION \"$extname\"'", 0, + qr/WARNING: type input function chkpass_in should not be volatile/, + "extension $extname installs (with warning)"; + } else { + is_program_out 'postgres', "psql -qc 'CREATE EXTENSION \"$extname\"'", 0, '', + "extension $extname installs without error"; + } + + is_program_out 'postgres', "psql -qc 'DROP EXTENSION \"$extname\"'", 0, '', + "extension $extname removes without error"; + + if ($depends{$extname}) { + for my $dep (@{$depends{$extname}}) { + is_program_out 'postgres', "psql -qc 'DROP EXTENSION $dep'", 0, '', + "$extname dependency extension $dep removes without error"; + } + } +} + +# clean up +is ((system "pg_dropcluster $v main --stop"), 0, "pg_dropcluster $v main"); +check_clean; +} + +done_testing(); + +# vim: filetype=perl diff --git a/t/180_ecpg.t b/t/180_ecpg.t new file mode 100644 index 0000000..a1a0c5d --- /dev/null +++ b/t/180_ecpg.t @@ -0,0 +1,55 @@ +# Check that ecpg works + +use strict; + +use lib 't'; +use TestLib; +use PgCommon; +use Test::More tests => 17; + +my $v = $MAJORS[-1]; + +# prepare nobody-owned work dir +my $workdir=`su -s /bin/sh -c 'mktemp -d' nobody`; +chomp $workdir; +chdir $workdir or die "could not chdir to $workdir: $!"; + +# create test code +open F, '>test.pgc' or die "Could not open $workdir/test.pgc: $!"; +print F <<EOF; +#include <stdio.h> + +EXEC SQL BEGIN DECLARE SECTION; + char output[1024]; +EXEC SQL END DECLARE SECTION; + +int main() { + EXEC SQL CONNECT TO template1; + EXEC SQL SELECT current_database() INTO :output; + puts(output); + EXEC SQL DISCONNECT ALL; + return 0; +} +EOF +close F; +chmod 0644, 'test.pgc'; + +is_program_out 'nobody', 'ecpg test.pgc', 0, '', 'ecpg processing'; + +is_program_out 'nobody', 'cc -I$(pg_config --includedir) -L$(pg_config --libdir) -o test test.c -lecpg', + 0, '', 'compiling ecpg output'; +chdir '/' or die "could not chdir to /: $!"; + +# create cluster +is ((system "pg_createcluster $v main --start >/dev/null"), 0, "pg_createcluster $v main"); +is ((exec_as 'postgres', 'createuser nobody -D -R -S'), 0, 'createuser nobody'); + +is_program_out 'nobody', "$workdir/test", 0, "template1\n", + 'runs and gives correct output'; + +# clean up +system "rm -rf $workdir"; +is ((system "pg_dropcluster $v main --stop"), 0, "pg_dropcluster $v main"); +check_clean; + +# vim: filetype=perl diff --git a/t/8.1.conf b/t/8.1.conf new file mode 100644 index 0000000..01781db --- /dev/null +++ b/t/8.1.conf @@ -0,0 +1,141 @@ +port = 5432 +max_connections = 100 +superuser_reserved_connections = 2 +unix_socket_directory = '' +unix_socket_group = '' +unix_socket_permissions = 0777 +bonjour_name = '' +authentication_timeout = 60 +ssl = off +password_encryption = on +db_user_namespace = off +krb_server_keyfile = '' +krb_srvname = 'postgres' +krb_server_hostname = '' +krb_caseins_users = off +tcp_keepalives_idle = 0 +tcp_keepalives_interval = 0 +tcp_keepalives_count = 0 +shared_buffers = 1000 +temp_buffers = 1000 +max_prepared_transactions = 5 +work_mem = 1024 +maintenance_work_mem = 16384 +max_stack_depth = 2048 +max_fsm_pages = 20000 +max_fsm_relations = 1000 +max_files_per_process = 1000 +preload_libraries = '' +vacuum_cost_delay = 0 +vacuum_cost_page_hit = 1 +vacuum_cost_page_miss = 10 +vacuum_cost_page_dirty = 20 +vacuum_cost_limit = 200 +bgwriter_delay = 200 +bgwriter_lru_percent = 1.0 +bgwriter_lru_maxpages = 5 +bgwriter_all_percent = 0.333 +bgwriter_all_maxpages = 5 +fsync = on +wal_sync_method = fsync +full_page_writes = on +wal_buffers = 8 +commit_delay = 0 +commit_siblings = 5 +checkpoint_segments = 3 +checkpoint_timeout = 300 +checkpoint_warning = 30 +archive_command = '' +enable_bitmapscan = on +enable_hashagg = on +enable_hashjoin = on +enable_indexscan = on +enable_mergejoin = on +enable_nestloop = on +enable_seqscan = on +enable_sort = on +enable_tidscan = on +effective_cache_size = 1000 +random_page_cost = 4 +cpu_tuple_cost = 0.01 +cpu_index_tuple_cost = 0.001 +cpu_operator_cost = 0.0025 +geqo = on +geqo_threshold = 12 +geqo_effort = 5 +geqo_pool_size = 0 +geqo_generations = 0 +geqo_selection_bias = 2.0 +default_statistics_target = 10 +constraint_exclusion = off +from_collapse_limit = 8 +join_collapse_limit = 8 +log_destination = 'stderr' +redirect_stderr = on +log_directory = '.' +log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' +log_truncate_on_rotation = off +log_rotation_age = 1440 +log_rotation_size = 10240 +syslog_facility = 'LOCAL0' +syslog_ident = 'postgres' +client_min_messages = notice +log_min_messages = notice +log_error_verbosity = default +log_min_error_statement = panic +log_min_duration_statement = -1 +silent_mode = off +debug_print_parse = off +debug_print_rewritten = off +debug_print_plan = off +debug_pretty_print = off +log_connections = off +log_disconnections = off +log_duration = off +log_line_prefix = '' +log_statement = 'none' +log_hostname = off +log_parser_stats = off +log_planner_stats = off +log_executor_stats = off +log_statement_stats = off +stats_start_collector = on +stats_command_string = off +stats_block_level = off +stats_row_level = off +stats_reset_on_server_start = off +autovacuum = off +autovacuum_naptime = 60 +autovacuum_vacuum_threshold = 1000 +autovacuum_analyze_threshold = 500 +autovacuum_vacuum_scale_factor = 0.4 +autovacuum_analyze_scale_factor = 0.2 +autovacuum_vacuum_cost_delay = -1 +autovacuum_vacuum_cost_limit = -1 +search_path = '$user,public' +default_tablespace = '' +check_function_bodies = on +default_transaction_isolation = 'read committed' +default_transaction_read_only = on +statement_timeout = 0 +datestyle = 'iso, mdy' +timezone = 'Europe/Berlin' +australian_timezones = on +extra_float_digits = 0 +client_encoding = sql_ascii +lc_messages = 'C' +lc_monetary = 'C' +lc_numeric = 'C' +lc_time = 'C' +explain_pretty_print = on +dynamic_library_path = '$libdir' +deadlock_timeout = 1000 +max_locks_per_transaction = 64 +add_missing_from = off +backslash_quote = safe_encoding +default_with_oids = off +escape_string_warning = off +regex_flavor = advanced +sql_inheritance = on +transform_null_equals = off +custom_variable_classes = 'foo' diff --git a/t/8.2.conf b/t/8.2.conf new file mode 100644 index 0000000..bc1cc4e --- /dev/null +++ b/t/8.2.conf @@ -0,0 +1,151 @@ +external_pid_file = '(none)' +listen_addresses = 'localhost' +port = 5432 +max_connections = 100 +superuser_reserved_connections = 3 +unix_socket_directory = '' +unix_socket_group = '' +unix_socket_permissions = 0777 +bonjour_name = '' +authentication_timeout = 1min +ssl = off +password_encryption = on +db_user_namespace = off +krb_server_keyfile = '' +krb_srvname = 'postgres' +krb_server_hostname = '' +krb_caseins_users = off +tcp_keepalives_idle = 0 +tcp_keepalives_interval = 0 +tcp_keepalives_count = 0 +shared_buffers = 1000 +temp_buffers = 8MB +max_prepared_transactions = 5 +work_mem = 1MB +maintenance_work_mem = 16MB +max_stack_depth = 2MB +max_fsm_pages = 1638400 +max_fsm_relations = 1000 +max_files_per_process = 1000 +shared_preload_libraries = '' +vacuum_cost_delay = 0 +vacuum_cost_page_hit = 1 +vacuum_cost_page_miss = 10 +vacuum_cost_page_dirty = 20 +vacuum_cost_limit = 200 +bgwriter_delay = 200ms +bgwriter_lru_percent = 1.0 +bgwriter_lru_maxpages = 5 +bgwriter_all_percent = 0.333 +bgwriter_all_maxpages = 5 +fsync = on +wal_sync_method = fsync +full_page_writes = on +wal_buffers = 64kB +commit_delay = 0 +commit_siblings = 5 +checkpoint_segments = 3 +checkpoint_timeout = 5min +checkpoint_warning = 30s +archive_command = '' +archive_timeout = 0 +enable_bitmapscan = on +enable_hashagg = on +enable_hashjoin = on +enable_indexscan = on +enable_mergejoin = on +enable_nestloop = on +enable_seqscan = on +enable_sort = on +enable_tidscan = on +seq_page_cost = 1.0 +random_page_cost = 4.0 +cpu_tuple_cost = 0.01 +cpu_index_tuple_cost = 0.005 +cpu_operator_cost = 0.0025 +effective_cache_size = 128MB +geqo = on +geqo_threshold = 12 +geqo_effort = 5 +geqo_pool_size = 0 +geqo_generations = 0 +geqo_selection_bias = 2.0 +default_statistics_target = 10 +constraint_exclusion = off +from_collapse_limit = 8 +join_collapse_limit = 8 +log_destination = 'stderr' +redirect_stderr = on +log_directory = '.' +log_filename = 'postgresql-%Y-%m-%d_%H%M' +log_truncate_on_rotation = off +log_rotation_age = 1d +log_rotation_size = 10MB +syslog_facility = 'LOCAL0' +syslog_ident = 'postgres' +client_min_messages = notice +log_min_messages = notice +log_error_verbosity = default +log_min_error_statement = error +log_min_duration_statement = -1 +silent_mode = off +debug_print_parse = off +debug_print_rewritten = off +debug_print_plan = off +debug_pretty_print = off +log_connections = off +log_disconnections = off +log_duration = off +log_line_prefix = '' +log_statement = 'none' +log_hostname = off +stats_command_string = on +update_process_title = on +stats_start_collector = on +stats_block_level = off +stats_row_level = off +stats_reset_on_server_start = off +log_parser_stats = off +log_planner_stats = off +log_executor_stats = off +log_statement_stats = off +autovacuum = off +autovacuum_naptime = 1min +autovacuum_vacuum_threshold = 500 +autovacuum_analyze_threshold = 250 +autovacuum_vacuum_scale_factor = 0.2 +autovacuum_analyze_scale_factor = 0.1 +autovacuum_freeze_max_age = 200000000 +autovacuum_vacuum_cost_delay = -1 +autovacuum_vacuum_cost_limit = -1 +search_path = '"$user",public' +default_tablespace = '' +check_function_bodies = on +default_transaction_isolation = 'read committed' +default_transaction_read_only = on +statement_timeout = 0 +vacuum_freeze_min_age = 100000000 +datestyle = 'iso, mdy' +timezone = 'Europe/Berlin' +timezone_abbreviations = 'Default' +extra_float_digits = 0 +client_encoding = sql_ascii +lc_messages = 'C' +lc_monetary = 'C' +lc_numeric = 'C' +lc_time = 'C' +explain_pretty_print = on +dynamic_library_path = '$libdir' +local_preload_libraries = '' +deadlock_timeout = 1s +max_locks_per_transaction = 64 +add_missing_from = off +array_nulls = on +backslash_quote = safe_encoding +default_with_oids = off +escape_string_warning = on +standard_conforming_strings = off +regex_flavor = advanced +sql_inheritance = on +transform_null_equals = off +custom_variable_classes = 'foo' diff --git a/t/8.3.conf b/t/8.3.conf new file mode 100644 index 0000000..f02866e --- /dev/null +++ b/t/8.3.conf @@ -0,0 +1,164 @@ +external_pid_file = '(none)' +listen_addresses = 'localhost' +port = 5432 +max_connections = 100 +superuser_reserved_connections = 3 +unix_socket_directory = '' +unix_socket_group = '' +unix_socket_permissions = 0777 +bonjour_name = '' +authentication_timeout = 1min +ssl = off +ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' +password_encryption = on +db_user_namespace = off +krb_server_keyfile = '' +krb_srvname = 'postgres' +krb_server_hostname = '' +krb_caseins_users = off +krb_realm = '' +tcp_keepalives_idle = 0 +tcp_keepalives_interval = 0 +tcp_keepalives_count = 0 +shared_buffers = 24MB +temp_buffers = 8MB +max_prepared_transactions = 5 +work_mem = 1MB +maintenance_work_mem = 16MB +max_stack_depth = 2MB +max_fsm_pages = 204800 +max_fsm_relations = 1000 +max_files_per_process = 1000 +shared_preload_libraries = '' +vacuum_cost_delay = 0 +vacuum_cost_page_hit = 1 +vacuum_cost_page_miss = 10 +vacuum_cost_page_dirty = 20 +vacuum_cost_limit = 200 +bgwriter_delay = 200ms +bgwriter_lru_maxpages = 100 +bgwriter_lru_multiplier = 2.0 +fsync = on +synchronous_commit = on +wal_sync_method = fsync +full_page_writes = on +wal_buffers = 64kB +wal_writer_delay = 200ms +commit_delay = 0 +commit_siblings = 5 +checkpoint_segments = 3 +checkpoint_timeout = 5min +checkpoint_completion_target = 0.5 +checkpoint_warning = 30s +archive_mode = off +archive_command = '' +archive_timeout = 0 +enable_bitmapscan = on +enable_hashagg = on +enable_hashjoin = on +enable_indexscan = on +enable_mergejoin = on +enable_nestloop = on +enable_seqscan = on +enable_sort = on +enable_tidscan = on +seq_page_cost = 1.0 +random_page_cost = 4.0 +cpu_tuple_cost = 0.01 +cpu_index_tuple_cost = 0.005 +cpu_operator_cost = 0.0025 +effective_cache_size = 128MB +geqo = on +geqo_threshold = 12 +geqo_effort = 5 +geqo_pool_size = 0 +geqo_generations = 0 +geqo_selection_bias = 2.0 +default_statistics_target = 10 +constraint_exclusion = off +from_collapse_limit = 8 +join_collapse_limit = 8 +log_destination = 'stderr' +logging_collector = off +log_directory = 'pg_log' +log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' +log_truncate_on_rotation = off +log_rotation_age = 1d +log_rotation_size = 10MB +syslog_facility = 'LOCAL0' +syslog_ident = 'postgres' +client_min_messages = notice +log_min_messages = notice +log_error_verbosity = default +log_min_error_statement = error +log_min_duration_statement = -1 +silent_mode = off +debug_print_parse = off +debug_print_rewritten = off +debug_print_plan = off +debug_pretty_print = off +log_checkpoints = off +log_connections = off +log_disconnections = off +log_duration = off +log_hostname = off +log_line_prefix = '' +log_lock_waits = off +log_statement = 'none' +log_temp_files = -1 +log_timezone = 'Europe/Berlin' +track_activities = on +track_counts = on +update_process_title = on +log_parser_stats = off +log_planner_stats = off +log_executor_stats = off +log_statement_stats = off +autovacuum = on +log_autovacuum_min_duration = -1 +autovacuum_max_workers = 3 +autovacuum_naptime = 1min +autovacuum_vacuum_threshold = 50 +autovacuum_analyze_threshold = 50 +autovacuum_vacuum_scale_factor = 0.2 +autovacuum_analyze_scale_factor = 0.1 +autovacuum_freeze_max_age = 200000000 +autovacuum_vacuum_cost_delay = 20 +autovacuum_vacuum_cost_limit = -1 +search_path = '"$user",public' +default_tablespace = '' +temp_tablespaces = '' +check_function_bodies = on +default_transaction_isolation = 'read committed' +default_transaction_read_only = off +session_replication_role = 'origin' +statement_timeout = 0 +vacuum_freeze_min_age = 100000000 +xmlbinary = 'base64' +xmloption = 'content' +datestyle = 'iso, mdy' +timezone = 'Europe/Berlin' +timezone_abbreviations = 'Default' +extra_float_digits = 0 +client_encoding = sql_ascii +lc_messages = 'C' +lc_monetary = 'C' +lc_numeric = 'C' +lc_time = 'C' +default_text_search_config = 'pg_catalog.simple' +explain_pretty_print = on +dynamic_library_path = '$libdir' +local_preload_libraries = '' +deadlock_timeout = 1s +max_locks_per_transaction = 64 +add_missing_from = off +array_nulls = on +backslash_quote = safe_encoding +default_with_oids = off +escape_string_warning = on +regex_flavor = advanced +sql_inheritance = on +standard_conforming_strings = off +synchronize_seqscans = on +transform_null_equals = off +custom_variable_classes = '' diff --git a/t/8.4.conf b/t/8.4.conf new file mode 100644 index 0000000..61ffb51 --- /dev/null +++ b/t/8.4.conf @@ -0,0 +1,167 @@ +external_pid_file = '(none)' +listen_addresses = 'localhost' +port = 5432 +max_connections = 100 +superuser_reserved_connections = 3 +unix_socket_directory = '' +unix_socket_group = '' +unix_socket_permissions = 0777 +bonjour_name = '' +authentication_timeout = 1min +ssl = off +ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' +ssl_renegotiation_limit = 512MB +password_encryption = on +db_user_namespace = off +krb_server_keyfile = '' +krb_srvname = 'postgres' +krb_caseins_users = off +tcp_keepalives_idle = 0 +tcp_keepalives_interval = 0 +tcp_keepalives_count = 0 +shared_buffers = 24MB +temp_buffers = 8MB +max_prepared_transactions = 0 +work_mem = 1MB +maintenance_work_mem = 16MB +max_stack_depth = 2MB +max_files_per_process = 1000 +shared_preload_libraries = '' +vacuum_cost_delay = 0ms +vacuum_cost_page_hit = 1 +vacuum_cost_page_miss = 10 +vacuum_cost_page_dirty = 20 +vacuum_cost_limit = 200 +bgwriter_delay = 200ms +bgwriter_lru_maxpages = 100 +bgwriter_lru_multiplier = 2.0 +effective_io_concurrency = 1 +fsync = on +synchronous_commit = on +wal_sync_method = fsync +full_page_writes = on +wal_buffers = 64kB +wal_writer_delay = 200ms +commit_delay = 0 +commit_siblings = 5 +checkpoint_segments = 3 +checkpoint_timeout = 5min +checkpoint_completion_target = 0.5 +checkpoint_warning = 30s +archive_mode = off +archive_command = '' +archive_timeout = 0 +enable_bitmapscan = on +enable_hashagg = on +enable_hashjoin = on +enable_indexscan = on +enable_mergejoin = on +enable_nestloop = on +enable_seqscan = on +enable_sort = on +enable_tidscan = on +seq_page_cost = 1.0 +random_page_cost = 4.0 +cpu_tuple_cost = 0.01 +cpu_index_tuple_cost = 0.005 +cpu_operator_cost = 0.0025 +effective_cache_size = 128MB +geqo = on +geqo_threshold = 12 +geqo_effort = 5 +geqo_pool_size = 0 +geqo_generations = 0 +geqo_selection_bias = 2.0 +default_statistics_target = 100 +constraint_exclusion = partition +cursor_tuple_fraction = 0.1 +from_collapse_limit = 8 +join_collapse_limit = 8 +log_destination = 'stderr' +logging_collector = off +log_directory = 'pg_log' +log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' +log_truncate_on_rotation = off +log_rotation_age = 1d +log_rotation_size = 10MB +syslog_facility = 'LOCAL0' +syslog_ident = 'postgres' +silent_mode = off +client_min_messages = notice +log_min_messages = warning +log_error_verbosity = default +log_min_error_statement = error +log_min_duration_statement = -1 +debug_print_parse = off +debug_print_rewritten = off +debug_print_plan = off +debug_pretty_print = on +log_checkpoints = off +log_connections = off +log_disconnections = off +log_duration = off +log_hostname = off +log_line_prefix = '' +log_lock_waits = off +log_statement = 'none' +log_temp_files = -1 +log_timezone = 'Europe/Berlin' +track_activities = on +track_counts = on +track_functions = none +track_activity_query_size = 1024 +update_process_title = on +stats_temp_directory = '/var/run/postgresql/8.4-main.pg_stat_tmp' +log_parser_stats = off +log_planner_stats = off +log_executor_stats = off +log_statement_stats = off +autovacuum = on +log_autovacuum_min_duration = -1 +autovacuum_max_workers = 3 +autovacuum_naptime = 1min +autovacuum_vacuum_threshold = 50 +autovacuum_analyze_threshold = 50 +autovacuum_vacuum_scale_factor = 0.2 +autovacuum_analyze_scale_factor = 0.1 +autovacuum_freeze_max_age = 200000000 +autovacuum_vacuum_cost_delay = 20ms +autovacuum_vacuum_cost_limit = -1 +search_path = '"$user",public' +default_tablespace = '' +temp_tablespaces = '' +check_function_bodies = on +default_transaction_isolation = 'read committed' +default_transaction_read_only = off +session_replication_role = 'origin' +statement_timeout = 0 +vacuum_freeze_min_age = 50000000 +vacuum_freeze_table_age = 150000000 +xmlbinary = 'base64' +xmloption = 'content' +datestyle = 'iso, mdy' +intervalstyle = 'postgres' +timezone = 'Europe/Berlin' +timezone_abbreviations = 'Default' +extra_float_digits = 0 +client_encoding = sql_ascii +lc_messages = 'C' +lc_monetary = 'C' +lc_numeric = 'C' +lc_time = 'C' +default_text_search_config = 'pg_catalog.simple' +dynamic_library_path = '$libdir' +local_preload_libraries = '' +deadlock_timeout = 1s +max_locks_per_transaction = 64 +add_missing_from = off +array_nulls = on +backslash_quote = safe_encoding +default_with_oids = off +escape_string_warning = on +regex_flavor = advanced +sql_inheritance = on +standard_conforming_strings = off +synchronize_seqscans = on +transform_null_equals = off +custom_variable_classes = '' diff --git a/t/9.0.conf b/t/9.0.conf new file mode 100644 index 0000000..27b18f2 --- /dev/null +++ b/t/9.0.conf @@ -0,0 +1,178 @@ +external_pid_file = '(none)' +listen_addresses = 'localhost' +port = 5432 +max_connections = 100 +superuser_reserved_connections = 3 +unix_socket_directory = '' +unix_socket_group = '' +unix_socket_permissions = 0777 +bonjour = off +bonjour_name = '' +authentication_timeout = 1min +ssl = off +ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' +ssl_renegotiation_limit = 512MB +password_encryption = on +db_user_namespace = off +krb_server_keyfile = '' +krb_srvname = 'postgres' +krb_caseins_users = off +tcp_keepalives_idle = 0 +tcp_keepalives_interval = 0 +tcp_keepalives_count = 0 +shared_buffers = 24MB +temp_buffers = 8MB +max_prepared_transactions = 0 +work_mem = 1MB +maintenance_work_mem = 16MB +max_stack_depth = 2MB +max_files_per_process = 1000 +shared_preload_libraries = '' +vacuum_cost_delay = 0ms +vacuum_cost_page_hit = 1 +vacuum_cost_page_miss = 10 +vacuum_cost_page_dirty = 20 +vacuum_cost_limit = 200 +bgwriter_delay = 200ms +bgwriter_lru_maxpages = 100 +bgwriter_lru_multiplier = 2.0 +effective_io_concurrency = 1 +wal_level = minimal +fsync = on +synchronous_commit = on +wal_sync_method = fsync +full_page_writes = on +wal_buffers = 64kB +wal_writer_delay = 200ms +commit_delay = 0 +commit_siblings = 5 +checkpoint_segments = 3 +checkpoint_timeout = 5min +checkpoint_completion_target = 0.5 +checkpoint_warning = 30s +archive_mode = off +archive_command = '' +archive_timeout = 0 +max_wal_senders = 0 +wal_sender_delay = 200ms +wal_keep_segments = 0 +vacuum_defer_cleanup_age = 0 +hot_standby = off +max_standby_archive_delay = 30s +max_standby_streaming_delay = 30s +enable_bitmapscan = on +enable_hashagg = on +enable_hashjoin = on +enable_indexscan = on +enable_material = on +enable_mergejoin = on +enable_nestloop = on +enable_seqscan = on +enable_sort = on +enable_tidscan = on +seq_page_cost = 1.0 +random_page_cost = 4.0 +cpu_tuple_cost = 0.01 +cpu_index_tuple_cost = 0.005 +cpu_operator_cost = 0.0025 +effective_cache_size = 128MB +geqo = on +geqo_threshold = 12 +geqo_effort = 5 +geqo_pool_size = 0 +geqo_generations = 0 +geqo_selection_bias = 2.0 +geqo_seed = 0.0 +default_statistics_target = 100 +constraint_exclusion = partition +cursor_tuple_fraction = 0.1 +from_collapse_limit = 8 +join_collapse_limit = 8 +log_destination = 'stderr' +logging_collector = off +log_directory = 'pg_log' +log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' +log_truncate_on_rotation = off +log_rotation_age = 1d +log_rotation_size = 10MB +syslog_facility = 'LOCAL0' +syslog_ident = 'postgres' +silent_mode = off +client_min_messages = notice +log_min_messages = warning +log_min_error_statement = error +log_min_duration_statement = -1 +debug_print_parse = off +debug_print_rewritten = off +debug_print_plan = off +debug_pretty_print = on +log_checkpoints = off +log_connections = off +log_disconnections = off +log_duration = off +log_error_verbosity = default +log_hostname = off +log_line_prefix = '' +log_lock_waits = off +log_statement = 'none' +log_temp_files = -1 +log_timezone = 'Europe/Berlin' +track_activities = on +track_counts = on +track_functions = none +track_activity_query_size = 1024 +update_process_title = on +stats_temp_directory = '/var/run/postgresql/9.0-main.pg_stat_tmp' +log_parser_stats = off +log_planner_stats = off +log_executor_stats = off +log_statement_stats = off +autovacuum = on +log_autovacuum_min_duration = -1 +autovacuum_max_workers = 3 +autovacuum_naptime = 1min +autovacuum_vacuum_threshold = 50 +autovacuum_analyze_threshold = 50 +autovacuum_vacuum_scale_factor = 0.2 +autovacuum_analyze_scale_factor = 0.1 +autovacuum_freeze_max_age = 200000000 +autovacuum_vacuum_cost_delay = 20ms +autovacuum_vacuum_cost_limit = -1 +search_path = '"$user",public' +default_tablespace = '' +temp_tablespaces = '' +check_function_bodies = on +default_transaction_isolation = 'read committed' +default_transaction_read_only = off +session_replication_role = 'origin' +statement_timeout = 0 +vacuum_freeze_min_age = 50000000 +vacuum_freeze_table_age = 150000000 +bytea_output = 'hex' +xmlbinary = 'base64' +xmloption = 'content' +datestyle = 'iso, mdy' +intervalstyle = 'postgres' +timezone = 'Europe/Berlin' +timezone_abbreviations = 'Default' +extra_float_digits = 0 +client_encoding = sql_ascii +lc_messages = 'C' +lc_monetary = 'C' +lc_numeric = 'C' +lc_time = 'C' +default_text_search_config = 'pg_catalog.simple' +dynamic_library_path = '$libdir' +local_preload_libraries = '' +deadlock_timeout = 1s +max_locks_per_transaction = 64 +array_nulls = on +backslash_quote = safe_encoding +default_with_oids = off +escape_string_warning = on +lo_compat_privileges = off +sql_inheritance = on +standard_conforming_strings = off +synchronize_seqscans = on +transform_null_equals = off +custom_variable_classes = '' diff --git a/t/9.1.conf b/t/9.1.conf new file mode 100644 index 0000000..26a2f2e --- /dev/null +++ b/t/9.1.conf @@ -0,0 +1,188 @@ +external_pid_file = '(none)' +listen_addresses = 'localhost' +port = 5432 +max_connections = 100 +superuser_reserved_connections = 3 +unix_socket_directory = '' +unix_socket_group = '' +unix_socket_permissions = 0777 +bonjour = off +bonjour_name = '' +authentication_timeout = 1min +ssl = off +ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' +ssl_renegotiation_limit = 512MB +password_encryption = on +db_user_namespace = off +krb_server_keyfile = '' +krb_srvname = 'postgres' +krb_caseins_users = off +tcp_keepalives_idle = 0 +tcp_keepalives_interval = 0 +tcp_keepalives_count = 0 +shared_buffers = 24MB +temp_buffers = 8MB +max_prepared_transactions = 0 +work_mem = 1MB +maintenance_work_mem = 16MB +max_stack_depth = 2MB +max_files_per_process = 1000 +shared_preload_libraries = '' +vacuum_cost_delay = 0ms +vacuum_cost_page_hit = 1 +vacuum_cost_page_miss = 10 +vacuum_cost_page_dirty = 20 +vacuum_cost_limit = 200 +bgwriter_delay = 200ms +bgwriter_lru_maxpages = 100 +bgwriter_lru_multiplier = 2.0 +effective_io_concurrency = 1 +wal_level = minimal +fsync = on +synchronous_commit = on +wal_sync_method = fsync +full_page_writes = on +wal_buffers = -1 +wal_writer_delay = 200ms +commit_delay = 0 +commit_siblings = 5 +checkpoint_segments = 3 +checkpoint_timeout = 5min +checkpoint_completion_target = 0.5 +checkpoint_warning = 30s +archive_mode = off +archive_command = '' +archive_timeout = 0 +max_wal_senders = 0 +wal_sender_delay = 1s +wal_keep_segments = 0 +vacuum_defer_cleanup_age = 0 +replication_timeout = 60s +synchronous_standby_names = '' +hot_standby = off +max_standby_archive_delay = 30s +max_standby_streaming_delay = 30s +wal_receiver_status_interval = 10s +hot_standby_feedback = off +enable_bitmapscan = on +enable_hashagg = on +enable_hashjoin = on +enable_indexscan = on +enable_material = on +enable_mergejoin = on +enable_nestloop = on +enable_seqscan = on +enable_sort = on +enable_tidscan = on +seq_page_cost = 1.0 +random_page_cost = 4.0 +cpu_tuple_cost = 0.01 +cpu_index_tuple_cost = 0.005 +cpu_operator_cost = 0.0025 +effective_cache_size = 128MB +geqo = on +geqo_threshold = 12 +geqo_effort = 5 +geqo_pool_size = 0 +geqo_generations = 0 +geqo_selection_bias = 2.0 +geqo_seed = 0.0 +default_statistics_target = 100 +constraint_exclusion = partition +cursor_tuple_fraction = 0.1 +from_collapse_limit = 8 +join_collapse_limit = 8 +log_destination = 'stderr' +logging_collector = off +log_directory = 'pg_log' +log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' +log_file_mode = 0600 +log_truncate_on_rotation = off +log_rotation_age = 1d +log_rotation_size = 10MB +syslog_facility = 'LOCAL0' +syslog_ident = 'postgres' +silent_mode = off +client_min_messages = notice +log_min_messages = warning +log_min_error_statement = error +log_min_duration_statement = -1 +debug_print_parse = off +debug_print_rewritten = off +debug_print_plan = off +debug_pretty_print = on +log_checkpoints = off +log_connections = off +log_disconnections = off +log_duration = off +log_error_verbosity = default +log_hostname = off +log_line_prefix = '' +log_lock_waits = off +log_statement = 'none' +log_temp_files = -1 +log_timezone = 'Europe/Berlin' +track_activities = on +track_counts = on +track_functions = none +track_activity_query_size = 1024 +update_process_title = on +stats_temp_directory = '/var/run/postgresql/9.1-main.pg_stat_tmp' +log_parser_stats = off +log_planner_stats = off +log_executor_stats = off +log_statement_stats = off +autovacuum = on +log_autovacuum_min_duration = -1 +autovacuum_max_workers = 3 +autovacuum_naptime = 1min +autovacuum_vacuum_threshold = 50 +autovacuum_analyze_threshold = 50 +autovacuum_vacuum_scale_factor = 0.2 +autovacuum_analyze_scale_factor = 0.1 +autovacuum_freeze_max_age = 200000000 +autovacuum_vacuum_cost_delay = 20ms +autovacuum_vacuum_cost_limit = -1 +search_path = '"$user",public' +default_tablespace = '' +temp_tablespaces = '' +check_function_bodies = on +default_transaction_isolation = 'read committed' +default_transaction_read_only = off +default_transaction_deferrable = off +session_replication_role = 'origin' +statement_timeout = 0 +vacuum_freeze_min_age = 50000000 +vacuum_freeze_table_age = 150000000 +bytea_output = 'hex' +xmlbinary = 'base64' +xmloption = 'content' +datestyle = 'iso, mdy' +intervalstyle = 'postgres' +timezone = 'Europe/Berlin' +timezone_abbreviations = 'Default' +extra_float_digits = 0 +client_encoding = sql_ascii +lc_messages = 'C' +lc_monetary = 'C' +lc_numeric = 'C' +lc_time = 'C' +default_text_search_config = 'pg_catalog.simple' +dynamic_library_path = '$libdir' +local_preload_libraries = '' +deadlock_timeout = 1s +max_locks_per_transaction = 64 +max_pred_locks_per_transaction = 64 +array_nulls = on +backslash_quote = safe_encoding +default_with_oids = off +escape_string_warning = on +lo_compat_privileges = off +quote_all_identifiers = off +sql_inheritance = on +standard_conforming_strings = on +synchronize_seqscans = on +transform_null_equals = off +exit_on_error = off +restart_after_crash = on +custom_variable_classes = '' diff --git a/t/9.2.conf b/t/9.2.conf new file mode 100644 index 0000000..4dec12c --- /dev/null +++ b/t/9.2.conf @@ -0,0 +1,126 @@ +port = 5432 +max_connections = 50 +superuser_reserved_connections = 3 +unix_socket_directory = '' +unix_socket_group = '' +unix_socket_permissions = 0777 +bonjour = off +bonjour_name = '' +authentication_timeout = 1min +ssl = off +ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' +ssl_renegotiation_limit = 512MB +ssl_cert_file = 'server.crt' +ssl_key_file = 'server.key' +ssl_ca_file = '' +ssl_crl_file = '' +krb_srvname = 'postgres' +tcp_keepalives_idle = 0 +tcp_keepalives_interval = 0 +tcp_keepalives_count = 0 +shared_buffers = 24MB +temp_buffers = 8MB +max_prepared_transactions = 0 +work_mem = 1MB +maintenance_work_mem = 16MB +max_stack_depth = 2MB +temp_file_limit = -1 +max_files_per_process = 1000 +shared_preload_libraries = '' +vacuum_cost_delay = 0ms +vacuum_cost_page_hit = 1 +vacuum_cost_page_miss = 10 +vacuum_cost_page_dirty = 20 +vacuum_cost_limit = 200 +bgwriter_delay = 200ms +bgwriter_lru_maxpages = 100 +bgwriter_lru_multiplier = 2.0 +effective_io_concurrency = 1 +wal_level = minimal +fsync = on +synchronous_commit = on +wal_sync_method = fsync +full_page_writes = on +wal_buffers = -1 +wal_writer_delay = 200ms +commit_delay = 0 +commit_siblings = 5 +checkpoint_segments = 3 +checkpoint_timeout = 5min +checkpoint_completion_target = 0.5 +checkpoint_warning = 30s +archive_mode = off +archive_command = '' +archive_timeout = 0 +max_wal_senders = 0 +wal_keep_segments = 0 +replication_timeout = 60s +synchronous_standby_names = '' +vacuum_defer_cleanup_age = 0 +hot_standby = off +max_standby_archive_delay = 30s +max_standby_streaming_delay = 30s +wal_receiver_status_interval = 10s +hot_standby_feedback = off +seq_page_cost = 1.0 +random_page_cost = 4.0 +cpu_tuple_cost = 0.01 +cpu_index_tuple_cost = 0.005 +cpu_operator_cost = 0.0025 +geqo_effort = 5 +geqo_pool_size = 0 +geqo_generations = 0 +geqo_selection_bias = 2.0 +geqo_seed = 0.0 +default_statistics_target = 100 +constraint_exclusion = partition +cursor_tuple_fraction = 0.1 +join_collapse_limit = 8 +log_destination = 'stderr' +logging_collector = off +log_directory = 'pg_log' +log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' +log_file_mode = 0600 +log_truncate_on_rotation = off +log_rotation_age = 1d +log_rotation_size = 10MB +client_min_messages = notice +log_min_messages = warning +log_min_error_statement = error +log_min_duration_statement = -1 +log_error_verbosity = default +log_line_prefix = '' +log_lock_waits = off +log_statement = 'none' +log_temp_files = -1 +track_functions = none +track_activity_query_size = 1024 +stats_temp_directory = '/var/run/postgresql/9.2-main.pg_stat_tmp' +autovacuum = on +log_autovacuum_min_duration = -1 +autovacuum_max_workers = 3 +autovacuum_naptime = 1min +autovacuum_vacuum_threshold = 50 +autovacuum_analyze_threshold = 50 +autovacuum_vacuum_scale_factor = 0.2 +autovacuum_analyze_scale_factor = 0.1 +autovacuum_freeze_max_age = 200000000 +autovacuum_vacuum_cost_delay = 20ms +autovacuum_vacuum_cost_limit = -1 +search_path = '"$user",public' +default_tablespace = '' +temp_tablespaces = '' +statement_timeout = 0 +bytea_output = 'hex' +timezone_abbreviations = 'Default' +extra_float_digits = 0 +client_encoding = sql_ascii +lc_messages = 'C' +lc_monetary = 'C' +lc_numeric = 'C' +lc_time = 'C' +max_locks_per_transaction = 64 +max_pred_locks_per_transaction = 64 +backslash_quote = safe_encoding +exit_on_error = off +restart_after_crash = on diff --git a/t/9.3.conf b/t/9.3.conf new file mode 100644 index 0000000..5d718ea --- /dev/null +++ b/t/9.3.conf @@ -0,0 +1,201 @@ +external_pid_file = '(none)' +listen_addresses = 'localhost' +port = 5432 +max_connections = 100 +superuser_reserved_connections = 3 +unix_socket_directories = '/var/run/postgresql' +unix_socket_group = '' +unix_socket_permissions = 0777 +bonjour = off +bonjour_name = '' +authentication_timeout = 1min +ssl = false +ssl_ciphers = 'DEFAULT:!LOW:!EXP:!MD5:@STRENGTH' +ssl_renegotiation_limit = 512MB +ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem' +ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key' +ssl_ca_file = '' +ssl_crl_file = '' +password_encryption = on +db_user_namespace = off +krb_server_keyfile = '' +krb_srvname = 'postgres' +krb_caseins_users = off +tcp_keepalives_idle = 0 +tcp_keepalives_interval = 0 +tcp_keepalives_count = 0 +shared_buffers = 128MB +temp_buffers = 8MB +max_prepared_transactions = 0 +work_mem = 1MB +maintenance_work_mem = 16MB +max_stack_depth = 2MB +temp_file_limit = -1 +max_files_per_process = 1000 +shared_preload_libraries = '' +vacuum_cost_delay = 0 +vacuum_cost_page_hit = 1 +vacuum_cost_page_miss = 10 +vacuum_cost_page_dirty = 20 +vacuum_cost_limit = 200 +bgwriter_delay = 200ms +bgwriter_lru_maxpages = 100 +bgwriter_lru_multiplier = 2.0 +effective_io_concurrency = 1 +wal_level = minimal +fsync = on +synchronous_commit = on +wal_sync_method = fsync +full_page_writes = on +wal_buffers = -1 +wal_writer_delay = 200ms +commit_delay = 0 +commit_siblings = 5 +checkpoint_segments = 3 +checkpoint_timeout = 5min +checkpoint_completion_target = 0.5 +checkpoint_warning = 30s +archive_mode = off +archive_command = '' +archive_timeout = 0 +max_wal_senders = 0 +wal_keep_segments = 0 +wal_sender_timeout = 60s +synchronous_standby_names = '' +vacuum_defer_cleanup_age = 0 +hot_standby = off +max_standby_archive_delay = 30s +max_standby_streaming_delay = 30s +wal_receiver_status_interval = 10s +hot_standby_feedback = off +wal_receiver_timeout = 60s +enable_bitmapscan = on +enable_hashagg = on +enable_hashjoin = on +enable_indexscan = on +enable_indexonlyscan = on +enable_material = on +enable_mergejoin = on +enable_nestloop = on +enable_seqscan = on +enable_sort = on +enable_tidscan = on +seq_page_cost = 1.0 +random_page_cost = 4.0 +cpu_tuple_cost = 0.01 +cpu_index_tuple_cost = 0.005 +cpu_operator_cost = 0.0025 +effective_cache_size = 128MB +geqo = on +geqo_threshold = 12 +geqo_effort = 5 +geqo_pool_size = 0 +geqo_generations = 0 +geqo_selection_bias = 2.0 +geqo_seed = 0.0 +default_statistics_target = 100 +constraint_exclusion = partition +cursor_tuple_fraction = 0.1 +from_collapse_limit = 8 +join_collapse_limit = 8 +log_destination = 'stderr' +logging_collector = off +log_directory = 'pg_log' +log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' +log_file_mode = 0600 +log_truncate_on_rotation = off +log_rotation_age = 1d +log_rotation_size = 10MB +syslog_facility = 'LOCAL0' +syslog_ident = 'postgres' +event_source = 'PostgreSQL' +client_min_messages = notice +log_min_messages = warning +log_min_error_statement = error +log_min_duration_statement = -1 +debug_print_parse = off +debug_print_rewritten = off +debug_print_plan = off +debug_pretty_print = on +log_checkpoints = off +log_connections = off +log_disconnections = off +log_duration = off +log_error_verbosity = default +log_hostname = off +log_line_prefix = '%t ' +log_lock_waits = off +log_statement = 'none' +log_temp_files = -1 +log_timezone = 'CET' +track_activities = on +track_counts = on +track_io_timing = off +track_functions = none +track_activity_query_size = 1024 +update_process_title = on +stats_temp_directory = '/var/run/postgresql/9.3-main.pg_stat_tmp' +log_parser_stats = off +log_planner_stats = off +log_executor_stats = off +log_statement_stats = off +autovacuum = on +log_autovacuum_min_duration = -1 +autovacuum_max_workers = 3 +autovacuum_naptime = 1min +autovacuum_vacuum_threshold = 50 +autovacuum_analyze_threshold = 50 +autovacuum_vacuum_scale_factor = 0.2 +autovacuum_analyze_scale_factor = 0.1 +autovacuum_freeze_max_age = 200000000 +autovacuum_multixact_freeze_max_age = 400000000 +autovacuum_vacuum_cost_delay = 20ms +autovacuum_vacuum_cost_limit = -1 +search_path = '"$user",public' +default_tablespace = '' +temp_tablespaces = '' +check_function_bodies = on +default_transaction_isolation = 'read committed' +default_transaction_read_only = off +default_transaction_deferrable = off +session_replication_role = 'origin' +statement_timeout = 0 +lock_timeout = 0 +vacuum_freeze_min_age = 50000000 +vacuum_freeze_table_age = 150000000 +vacuum_multixact_freeze_min_age = 5000000 +vacuum_multixact_freeze_table_age = 150000000 +bytea_output = 'hex' +xmlbinary = 'base64' +xmloption = 'content' +datestyle = 'iso, dmy' +intervalstyle = 'postgres' +timezone = 'CET' +timezone_abbreviations = 'Default' +extra_float_digits = 0 +client_encoding = sql_ascii +lc_messages = 'C' +lc_monetary = 'C' +lc_numeric = 'C' +lc_time = 'C' +default_text_search_config = 'pg_catalog.german' +dynamic_library_path = '$libdir' +local_preload_libraries = '' +deadlock_timeout = 1s +max_locks_per_transaction = 64 +max_pred_locks_per_transaction = 64 +array_nulls = on +backslash_quote = safe_encoding +default_with_oids = off +escape_string_warning = on +lo_compat_privileges = off +quote_all_identifiers = off +sql_inheritance = on +standard_conforming_strings = on +synchronize_seqscans = on +transform_null_equals = off +exit_on_error = off +restart_after_crash = on +#include_dir = 'conf.d' +include_if_exists = 'exists.conf' +#include = 'special.conf' diff --git a/t/9.4.conf b/t/9.4.conf new file mode 100644 index 0000000..b3a9daf --- /dev/null +++ b/t/9.4.conf @@ -0,0 +1,209 @@ +external_pid_file = '(none)' +listen_addresses = 'localhost' +port = 5432 +max_connections = 100 +superuser_reserved_connections = 3 +unix_socket_directories = '/var/run/postgresql' +unix_socket_group = '' +unix_socket_permissions = 0777 +bonjour = off +bonjour_name = '' +authentication_timeout = 1min +ssl = true +ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' +ssl_prefer_server_ciphers = on +ssl_ecdh_curve = 'prime256v1' +ssl_renegotiation_limit = 512MB +ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem' +ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key' +ssl_ca_file = '' +ssl_crl_file = '' +password_encryption = on +db_user_namespace = off +krb_server_keyfile = '' +krb_caseins_users = off +tcp_keepalives_idle = 0 +tcp_keepalives_interval = 0 +tcp_keepalives_count = 0 +shared_buffers = 128MB +huge_pages = try +temp_buffers = 8MB +max_prepared_transactions = 0 +work_mem = 4MB +maintenance_work_mem = 64MB +autovacuum_work_mem = -1 +max_stack_depth = 2MB +dynamic_shared_memory_type = posix +temp_file_limit = -1 +max_files_per_process = 1000 +shared_preload_libraries = '' +vacuum_cost_delay = 0 +vacuum_cost_page_hit = 1 +vacuum_cost_page_miss = 10 +vacuum_cost_page_dirty = 20 +vacuum_cost_limit = 200 +bgwriter_delay = 200ms +bgwriter_lru_maxpages = 100 +bgwriter_lru_multiplier = 2.0 +effective_io_concurrency = 1 +max_worker_processes = 8 +wal_level = minimal +fsync = on +synchronous_commit = on +wal_sync_method = fsync +full_page_writes = on +wal_log_hints = off +wal_buffers = -1 +wal_writer_delay = 200ms +commit_delay = 0 +commit_siblings = 5 +checkpoint_segments = 3 +checkpoint_timeout = 5min +checkpoint_completion_target = 0.5 +checkpoint_warning = 30s +archive_mode = off +archive_command = '' +archive_timeout = 0 +max_wal_senders = 0 +wal_keep_segments = 0 +wal_sender_timeout = 60s +max_replication_slots = 0 +synchronous_standby_names = '' +vacuum_defer_cleanup_age = 0 +hot_standby = off +max_standby_archive_delay = 30s +max_standby_streaming_delay = 30s +wal_receiver_status_interval = 10s +hot_standby_feedback = off +wal_receiver_timeout = 60s +enable_bitmapscan = on +enable_hashagg = on +enable_hashjoin = on +enable_indexscan = on +enable_indexonlyscan = on +enable_material = on +enable_mergejoin = on +enable_nestloop = on +enable_seqscan = on +enable_sort = on +enable_tidscan = on +seq_page_cost = 1.0 +random_page_cost = 4.0 +cpu_tuple_cost = 0.01 +cpu_index_tuple_cost = 0.005 +cpu_operator_cost = 0.0025 +effective_cache_size = 4GB +geqo = on +geqo_threshold = 12 +geqo_effort = 5 +geqo_pool_size = 0 +geqo_generations = 0 +geqo_selection_bias = 2.0 +geqo_seed = 0.0 +default_statistics_target = 100 +constraint_exclusion = partition +cursor_tuple_fraction = 0.1 +from_collapse_limit = 8 +join_collapse_limit = 8 +log_destination = 'stderr' +logging_collector = off +log_directory = 'pg_log' +log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' +log_file_mode = 0600 +log_truncate_on_rotation = off +log_rotation_age = 1d +log_rotation_size = 10MB +syslog_facility = 'LOCAL0' +syslog_ident = 'postgres' +event_source = 'PostgreSQL' +client_min_messages = notice +log_min_messages = warning +log_min_error_statement = error +log_min_duration_statement = -1 +debug_print_parse = off +debug_print_rewritten = off +debug_print_plan = off +debug_pretty_print = on +log_checkpoints = off +log_connections = off +log_disconnections = off +log_duration = off +log_error_verbosity = default +log_hostname = off +log_line_prefix = '%t [%p-%l] %q%u@%d ' +log_lock_waits = off +log_statement = 'none' +log_temp_files = -1 +log_timezone = 'CET' +track_activities = on +track_counts = on +track_io_timing = off +track_functions = none +track_activity_query_size = 1024 +update_process_title = on +stats_temp_directory = '/var/run/postgresql/9.4-main.pg_stat_tmp' +log_parser_stats = off +log_planner_stats = off +log_executor_stats = off +log_statement_stats = off +autovacuum = on +log_autovacuum_min_duration = -1 +autovacuum_max_workers = 3 +autovacuum_naptime = 1min +autovacuum_vacuum_threshold = 50 +autovacuum_analyze_threshold = 50 +autovacuum_vacuum_scale_factor = 0.2 +autovacuum_analyze_scale_factor = 0.1 +autovacuum_freeze_max_age = 200000000 +autovacuum_multixact_freeze_max_age = 400000000 +autovacuum_vacuum_cost_delay = 20ms +autovacuum_vacuum_cost_limit = -1 +search_path = '"$user",public' +default_tablespace = '' +temp_tablespaces = '' +check_function_bodies = on +default_transaction_isolation = 'read committed' +default_transaction_read_only = off +default_transaction_deferrable = off +session_replication_role = 'origin' +statement_timeout = 0 +lock_timeout = 0 +vacuum_freeze_min_age = 50000000 +vacuum_freeze_table_age = 150000000 +vacuum_multixact_freeze_min_age = 5000000 +vacuum_multixact_freeze_table_age = 150000000 +bytea_output = 'hex' +xmlbinary = 'base64' +xmloption = 'content' +datestyle = 'iso, dmy' +intervalstyle = 'postgres' +timezone = 'CET' +timezone_abbreviations = 'Default' +extra_float_digits = 0 +client_encoding = sql_ascii +lc_messages = 'C' +lc_monetary = 'C' +lc_numeric = 'C' +lc_time = 'C' +default_text_search_config = 'pg_catalog.german' +dynamic_library_path = '$libdir' +local_preload_libraries = '' +session_preload_libraries = '' +deadlock_timeout = 1s +max_locks_per_transaction = 64 +max_pred_locks_per_transaction = 64 +array_nulls = on +backslash_quote = safe_encoding +default_with_oids = off +escape_string_warning = on +lo_compat_privileges = off +quote_all_identifiers = off +sql_inheritance = on +standard_conforming_strings = on +synchronize_seqscans = on +transform_null_equals = off +exit_on_error = off +restart_after_crash = on +#include_dir = 'conf.d' +include_if_exists = 'exists.conf' +#include = 'special.conf' diff --git a/t/9.5.conf b/t/9.5.conf new file mode 100644 index 0000000..ad1b1f3 --- /dev/null +++ b/t/9.5.conf @@ -0,0 +1,217 @@ +listen_addresses = 'localhost' +port = 5432 +max_connections = 100 +superuser_reserved_connections = 3 +unix_socket_directories = '/var/run/postgresql' +unix_socket_group = '' +unix_socket_permissions = 0777 +bonjour = off +bonjour_name = '' +authentication_timeout = 1min +ssl = true +ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' +ssl_prefer_server_ciphers = on +ssl_ecdh_curve = 'prime256v1' +ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem' +ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key' +ssl_ca_file = '' +ssl_crl_file = '' +password_encryption = on +db_user_namespace = off +row_security = on +krb_server_keyfile = '' +krb_caseins_users = off +tcp_keepalives_idle = 0 +tcp_keepalives_interval = 0 +tcp_keepalives_count = 0 +shared_buffers = 128MB +huge_pages = try +temp_buffers = 8MB +max_prepared_transactions = 0 +work_mem = 4MB +maintenance_work_mem = 64MB +autovacuum_work_mem = -1 +max_stack_depth = 2MB +dynamic_shared_memory_type = posix +temp_file_limit = -1 +max_files_per_process = 1000 +shared_preload_libraries = '' +vacuum_cost_delay = 0 +vacuum_cost_page_hit = 1 +vacuum_cost_page_miss = 10 +vacuum_cost_page_dirty = 20 +vacuum_cost_limit = 200 +bgwriter_delay = 200ms +bgwriter_lru_maxpages = 100 +bgwriter_lru_multiplier = 2.0 +effective_io_concurrency = 1 +max_worker_processes = 8 +wal_level = minimal +fsync = on +synchronous_commit = on +wal_sync_method = fsync +full_page_writes = on +wal_compression = off +wal_log_hints = off +wal_buffers = -1 +wal_writer_delay = 200ms +commit_delay = 0 +commit_siblings = 5 +checkpoint_timeout = 5min +max_wal_size = 1GB +min_wal_size = 80MB +checkpoint_completion_target = 0.5 +checkpoint_warning = 30s +archive_mode = off +archive_command = '' +archive_timeout = 0 +max_wal_senders = 0 +wal_keep_segments = 0 +wal_sender_timeout = 60s +max_replication_slots = 0 +track_commit_timestamp = off +synchronous_standby_names = '' +vacuum_defer_cleanup_age = 0 +hot_standby = off +max_standby_archive_delay = 30s +max_standby_streaming_delay = 30s +wal_receiver_status_interval = 10s +hot_standby_feedback = off +wal_receiver_timeout = 60s +wal_retrieve_retry_interval = 5s +enable_bitmapscan = on +enable_hashagg = on +enable_hashjoin = on +enable_indexscan = on +enable_indexonlyscan = on +enable_material = on +enable_mergejoin = on +enable_nestloop = on +enable_seqscan = on +enable_sort = on +enable_tidscan = on +seq_page_cost = 1.0 +random_page_cost = 4.0 +cpu_tuple_cost = 0.01 +cpu_index_tuple_cost = 0.005 +cpu_operator_cost = 0.0025 +effective_cache_size = 4GB +geqo = on +geqo_threshold = 12 +geqo_effort = 5 +geqo_pool_size = 0 +geqo_generations = 0 +geqo_selection_bias = 2.0 +geqo_seed = 0.0 +default_statistics_target = 100 +constraint_exclusion = partition +cursor_tuple_fraction = 0.1 +from_collapse_limit = 8 +join_collapse_limit = 8 +log_destination = 'stderr' +logging_collector = off +log_directory = 'pg_log' +log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' +log_file_mode = 0600 +log_truncate_on_rotation = off +log_rotation_age = 1d +log_rotation_size = 10MB +syslog_facility = 'LOCAL0' +syslog_ident = 'postgres' +event_source = 'PostgreSQL' +client_min_messages = notice +log_min_messages = warning +log_min_error_statement = error +log_min_duration_statement = -1 +debug_print_parse = off +debug_print_rewritten = off +debug_print_plan = off +debug_pretty_print = on +log_checkpoints = off +log_connections = off +log_disconnections = off +log_duration = off +log_error_verbosity = default +log_hostname = off +log_line_prefix = '%t [%p-%l] %q%u@%d ' +log_lock_waits = off +log_statement = 'none' +log_replication_commands = off +log_temp_files = -1 +log_timezone = 'CET' +cluster_name = '9.5/main' +update_process_title = on +track_activities = on +track_counts = on +track_io_timing = off +track_functions = none +track_activity_query_size = 1024 +stats_temp_directory = '/var/run/postgresql/9.5-main.pg_stat_tmp' +log_parser_stats = off +log_planner_stats = off +log_executor_stats = off +log_statement_stats = off +autovacuum = on +log_autovacuum_min_duration = -1 +autovacuum_max_workers = 3 +autovacuum_naptime = 1min +autovacuum_vacuum_threshold = 50 +autovacuum_analyze_threshold = 50 +autovacuum_vacuum_scale_factor = 0.2 +autovacuum_analyze_scale_factor = 0.1 +autovacuum_freeze_max_age = 200000000 +autovacuum_multixact_freeze_max_age = 400000000 +autovacuum_vacuum_cost_delay = 20ms +autovacuum_vacuum_cost_limit = -1 +search_path = '"$user", public' +default_tablespace = '' +temp_tablespaces = '' +check_function_bodies = on +default_transaction_isolation = 'read committed' +default_transaction_read_only = off +default_transaction_deferrable = off +session_replication_role = 'origin' +statement_timeout = 0 +lock_timeout = 0 +vacuum_freeze_min_age = 50000000 +vacuum_freeze_table_age = 150000000 +vacuum_multixact_freeze_min_age = 5000000 +vacuum_multixact_freeze_table_age = 150000000 +bytea_output = 'hex' +xmlbinary = 'base64' +xmloption = 'content' +gin_fuzzy_search_limit = 0 +gin_pending_list_limit = 4MB +datestyle = 'iso, dmy' +intervalstyle = 'postgres' +timezone = 'CET' +timezone_abbreviations = 'Default' +extra_float_digits = 0 +client_encoding = sql_ascii +lc_messages = 'C' +lc_monetary = 'C' +lc_numeric = 'C' +lc_time = 'C' +default_text_search_config = 'pg_catalog.german' +dynamic_library_path = '$libdir' +local_preload_libraries = '' +session_preload_libraries = '' +deadlock_timeout = 1s +max_locks_per_transaction = 64 +max_pred_locks_per_transaction = 64 +array_nulls = on +backslash_quote = safe_encoding +default_with_oids = off +escape_string_warning = on +lo_compat_privileges = off +operator_precedence_warning = off +quote_all_identifiers = off +sql_inheritance = on +standard_conforming_strings = on +synchronize_seqscans = on +transform_null_equals = off +exit_on_error = off +restart_after_crash = on +#include_dir = 'conf.d' +include_if_exists = 'exists.conf' +#include = 'special.conf' diff --git a/t/9.6.conf b/t/9.6.conf new file mode 100644 index 0000000..fbca9b8 --- /dev/null +++ b/t/9.6.conf @@ -0,0 +1,231 @@ +listen_addresses = 'localhost' +port = 5432 +max_connections = 100 +superuser_reserved_connections = 3 +unix_socket_directories = '/var/run/postgresql' +unix_socket_group = '' +unix_socket_permissions = 0777 +bonjour = off +bonjour_name = '' +authentication_timeout = 1min +ssl = true +ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' +ssl_prefer_server_ciphers = on +ssl_ecdh_curve = 'prime256v1' +ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem' +ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key' +ssl_ca_file = '' +ssl_crl_file = '' +password_encryption = on +db_user_namespace = off +row_security = on +krb_server_keyfile = '' +krb_caseins_users = off +tcp_keepalives_idle = 0 +tcp_keepalives_interval = 0 +tcp_keepalives_count = 0 +shared_buffers = 128MB +huge_pages = try +temp_buffers = 8MB +max_prepared_transactions = 0 +work_mem = 4MB +maintenance_work_mem = 64MB +replacement_sort_tuples = 150000 +autovacuum_work_mem = -1 +max_stack_depth = 2MB +dynamic_shared_memory_type = posix +temp_file_limit = -1 +max_files_per_process = 1000 +shared_preload_libraries = '' +vacuum_cost_delay = 0 +vacuum_cost_page_hit = 1 +vacuum_cost_page_miss = 10 +vacuum_cost_page_dirty = 20 +vacuum_cost_limit = 200 +bgwriter_delay = 200ms +bgwriter_lru_maxpages = 100 +bgwriter_lru_multiplier = 2.0 +bgwriter_flush_after = 0 +effective_io_concurrency = 1 +max_worker_processes = 8 +max_parallel_workers_per_gather = 2 +old_snapshot_threshold = -1 +backend_flush_after = 0 +wal_level = minimal +fsync = on +synchronous_commit = on +wal_sync_method = fsync +full_page_writes = on +wal_compression = off +wal_log_hints = off +wal_buffers = -1 +wal_writer_delay = 200ms +wal_writer_flush_after = 1MB +commit_delay = 0 +commit_siblings = 5 +checkpoint_timeout = 5min +max_wal_size = 1GB +min_wal_size = 80MB +checkpoint_completion_target = 0.5 +checkpoint_flush_after = 0 +checkpoint_warning = 30s +archive_mode = off +archive_command = '' +archive_timeout = 0 +max_wal_senders = 0 +wal_keep_segments = 0 +wal_sender_timeout = 60s +max_replication_slots = 0 +track_commit_timestamp = off +synchronous_standby_names = '' +vacuum_defer_cleanup_age = 0 +hot_standby = off +max_standby_archive_delay = 30s +max_standby_streaming_delay = 30s +wal_receiver_status_interval = 10s +hot_standby_feedback = off +wal_receiver_timeout = 60s +wal_retrieve_retry_interval = 5s +enable_bitmapscan = on +enable_hashagg = on +enable_hashjoin = on +enable_indexscan = on +enable_indexonlyscan = on +enable_material = on +enable_mergejoin = on +enable_nestloop = on +enable_seqscan = on +enable_sort = on +enable_tidscan = on +seq_page_cost = 1.0 +random_page_cost = 4.0 +cpu_tuple_cost = 0.01 +cpu_index_tuple_cost = 0.005 +cpu_operator_cost = 0.0025 +parallel_tuple_cost = 0.1 +parallel_setup_cost = 1000.0 +min_parallel_relation_size = 8MB +effective_cache_size = 4GB +geqo = on +geqo_threshold = 12 +geqo_effort = 5 +geqo_pool_size = 0 +geqo_generations = 0 +geqo_selection_bias = 2.0 +geqo_seed = 0.0 +default_statistics_target = 100 +constraint_exclusion = partition +cursor_tuple_fraction = 0.1 +from_collapse_limit = 8 +join_collapse_limit = 8 +force_parallel_mode = off +log_destination = 'stderr' +logging_collector = off +log_directory = 'pg_log' +log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' +log_file_mode = 0600 +log_truncate_on_rotation = off +log_rotation_age = 1d +log_rotation_size = 10MB +syslog_facility = 'LOCAL0' +syslog_ident = 'postgres' +syslog_sequence_numbers = on +syslog_split_messages = on +event_source = 'PostgreSQL' +client_min_messages = notice +log_min_messages = warning +log_min_error_statement = error +log_min_duration_statement = -1 +debug_print_parse = off +debug_print_rewritten = off +debug_print_plan = off +debug_pretty_print = on +log_checkpoints = off +log_connections = off +log_disconnections = off +log_duration = off +log_error_verbosity = default +log_hostname = off +log_line_prefix = '%t [%p-%l] %q%u@%d ' +log_lock_waits = off +log_statement = 'none' +log_replication_commands = off +log_temp_files = -1 +log_timezone = 'CET' +cluster_name = '9.6/main' +update_process_title = on +track_activities = on +track_counts = on +track_io_timing = off +track_functions = none +track_activity_query_size = 1024 +stats_temp_directory = '/var/run/postgresql/9.6-main.pg_stat_tmp' +log_parser_stats = off +log_planner_stats = off +log_executor_stats = off +log_statement_stats = off +autovacuum = on +log_autovacuum_min_duration = -1 +autovacuum_max_workers = 3 +autovacuum_naptime = 1min +autovacuum_vacuum_threshold = 50 +autovacuum_analyze_threshold = 50 +autovacuum_vacuum_scale_factor = 0.2 +autovacuum_analyze_scale_factor = 0.1 +autovacuum_freeze_max_age = 200000000 +autovacuum_multixact_freeze_max_age = 400000000 +autovacuum_vacuum_cost_delay = 20ms +autovacuum_vacuum_cost_limit = -1 +search_path = '"$user", public' +default_tablespace = '' +temp_tablespaces = '' +check_function_bodies = on +default_transaction_isolation = 'read committed' +default_transaction_read_only = off +default_transaction_deferrable = off +session_replication_role = 'origin' +statement_timeout = 0 +lock_timeout = 0 +idle_in_transaction_session_timeout = 0 +vacuum_freeze_min_age = 50000000 +vacuum_freeze_table_age = 150000000 +vacuum_multixact_freeze_min_age = 5000000 +vacuum_multixact_freeze_table_age = 150000000 +bytea_output = 'hex' +xmlbinary = 'base64' +xmloption = 'content' +gin_fuzzy_search_limit = 0 +gin_pending_list_limit = 4MB +datestyle = 'iso, dmy' +intervalstyle = 'postgres' +timezone = 'CET' +timezone_abbreviations = 'Default' +extra_float_digits = 0 +client_encoding = sql_ascii +lc_messages = 'C' +lc_monetary = 'C' +lc_numeric = 'C' +lc_time = 'C' +default_text_search_config = 'pg_catalog.german' +dynamic_library_path = '$libdir' +local_preload_libraries = '' +session_preload_libraries = '' +deadlock_timeout = 1s +max_locks_per_transaction = 64 +max_pred_locks_per_transaction = 64 +array_nulls = on +backslash_quote = safe_encoding +default_with_oids = off +escape_string_warning = on +lo_compat_privileges = off +operator_precedence_warning = off +quote_all_identifiers = off +sql_inheritance = on +standard_conforming_strings = on +synchronize_seqscans = on +transform_null_equals = off +exit_on_error = off +restart_after_crash = on +#include_dir = 'conf.d' +include_if_exists = 'exists.conf' +#include = 'special.conf' diff --git a/t/TestLib.pm b/t/TestLib.pm new file mode 100644 index 0000000..6f41d33 --- /dev/null +++ b/t/TestLib.pm @@ -0,0 +1,248 @@ +# Common functionality for postgresql-common self tests +# +# (C) 2005-2009 Martin Pitt <mpitt@debian.org> +# (C) 2013-2016 Christoph Berg <myon@debian.org> +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 2 of the License, or +# (at your option) any later version. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. + +package TestLib; +use strict; +use Exporter; +use Test::More; +use PgCommon qw/get_versions change_ugid/; + +our $VERSION = 1.00; +our @ISA = ('Exporter'); +our @EXPORT = qw/ps ok_dir exec_as deb_installed rpm_installed package_version + version_ge program_ok is_program_out like_program_out unlike_program_out + pidof pid_env check_clean + @ALL_MAJORS @MAJORS $delay/; + +our @ALL_MAJORS = get_versions(); # not affected by PG_VERSIONS/-v +our @MAJORS = $ENV{PG_VERSIONS} ? split (/\s+/, $ENV{PG_VERSIONS}) : @ALL_MAJORS; +our $delay = 500_000; # 500ms + +# called if a test fails; spawn a shell if the environment variable +# FAILURE=shell is set +sub fail_debug { + if ($ENV{'FAILURE'} eq 'shell') { + if ((system 'bash') != 0) { + exit 1; + } + } +} + +# Return whether a given deb is installed. +# Arguments: <deb name> +sub deb_installed { + open (DPKG, "dpkg -s $_[0] 2>/dev/null|") or die "call dpkg: $!"; + my $result = 0; + while (<DPKG>) { + if (/^Version:/) { + $result = 1; + last; + } + } + close DPKG; + + return $result; +} + +# Return whether a given rpm is installed. +# Arguments: <rpm name> +sub rpm_installed { + open (RPM, "rpm -qa $_[0] 2>/dev/null|") or die "call rpm: $!"; + my $out = <RPM>; # returns void or the package name + close RPM; + return ($out =~ /./); +} + +# Return a package version +# Arguments: <package> +sub package_version { + my $package = shift; + if ($PgCommon::rpm) { + return `rpm --queryformat '%{VERSION}' -q $package`; + } else { + my $version = `dpkg-query -f '\${Version}' --show $package`; + chomp $version; + return $version; + } +} + +# Return whether a version is greater or equal to another one +# Arguments: <ver1> <ver2> +sub version_ge { + my ($v1, $v2) = @_; + use IPC::Open2; + open2(\*CHLD_OUT, \*CHLD_IN, 'sort', '-Vr'); + print CHLD_IN "$v1\n"; + print CHLD_IN "$v2\n"; + close CHLD_IN; + my $v_ge = <CHLD_OUT>; + chomp $v_ge; + return $v_ge eq $v1; +} + +# Return the user, group, and command line of running processes for the given +# program. +sub ps { + return `ps h -o user,group,args -C $_[0] | grep '$_[0]' | sort -u`; +} + +# Return array of pids that match the given command name (we require a leading +# slash so the postgres children are filtered out) +sub pidof { + my $prg = shift; + open F, '-|', 'ps', 'h', '-C', $prg, '-o', 'pid,cmd' or die "open: $!"; + my @pids; + while (<F>) { + if ((index $_, "/$prg") >= 0) { + push @pids, (split)[0]; + } + } + close F; + return @pids; +} + +# Return an reference to an array of all entries but . and .. of the given directory. +sub dircontent { + opendir D, $_[0] or die "opendir: $!"; + my @e = grep { $_ ne '.' && $_ ne '..' } readdir (D); + closedir D; + return \@e; +} + +# Return environment of given PID +sub pid_env { + my ($user, $pid) = @_; + my $path = "/proc/$pid/environ"; + my @lines; + open E, "su -c 'cat $path' $user |" or warn "open $path: $!"; + { + local $/; + @lines = split '\0', <E>; + } + close E; + my %env; + foreach (@lines) { + my ($k, $v) = (split '='); + $env{$k} = $v; + } + return %env; +} + +# Check the contents of a directory. +# Arguments: <directory name> <ref to expected dir content> <test description> +sub ok_dir { + my $content = dircontent $_[0]; + if (eq_set $content, $_[1]) { + pass $_[2]; + } else { + diag "Expected directory contents: [@{$_[1]}], actual contents: [@$content]\n"; + fail $_[2]; + } +} + +# Execute a command as a different user and return the output. Prints the +# output of the command if exit code differs from expected one. +# Arguments: <user> <system command> <ref to output> [<expected exit code>] +# Returns: Program exit code +sub exec_as { + my $uid; + if ($_[0] =~ /\d+/) { + $uid = int($_[0]); + } else { + $uid = getpwnam $_[0]; + defined($uid) or die "TestLib::exec_as: target user '$_[0]' does not exist"; + } + change_ugid ($uid, (getpwuid $uid)[3]); + die "changing euid: $!" if $> != $uid; + my $out = `$_[1] 2>&1`; + my $result = $? >> 8; + $< = $> = 0; + $( = $) = 0; + die "changing euid back to root: $!" if $> != 0; + $_[2] = \$out; + + if (defined $_[3] && $_[3] != $result) { + print "command '$_[1]' did not exit with expected code $_[3] but with $result:\n"; + print $out; + fail_debug; + } + return $result; +} + +# Execute a command as a particular user, and check the exit code +# Arguments: <user> <command> [<expected exit code>] [<description>] +sub program_ok { + my ($user, $cmd, $exit, $description) = @_; + $exit ||= 0; + $description ||= $cmd; + my $outref; + ok ((exec_as $user, $cmd, \$outref, $exit) == $exit, $description); +} + +# Execute a command as a particular user, and check the exit code and output +# (merged stdout/stderr). +# Arguments: <user> <command> <expected exit code> <expected output> [<description>] +sub is_program_out { + my $outref; + my $result = exec_as $_[0], $_[1], $outref; + is $result, $_[2], $_[1] or fail_debug; + is ($$outref, $_[3], (defined $_[4] ? $_[4] : "correct output of $_[1]")) or fail_debug; +} + +# Execute a command as a particular user, and check the exit code and output +# against a regular expression (merged stdout/stderr). +# Arguments: <user> <command> <expected exit code> <expected output re> [<description>] +sub like_program_out { + my $outref; + my $result = exec_as $_[0], $_[1], $outref; + is $result, $_[2], $_[1] or fail_debug; + like ($$outref, $_[3], (defined $_[4] ? $_[4] : "correct output of $_[1]")) or fail_debug; +} + +# Execute a command as a particular user, check the exit code, and check that +# the output does not match a regular expression (merged stdout/stderr). +# Arguments: <user> <command> <expected exit code> <expected output re> [<description>] +sub unlike_program_out { + my $outref; + my $result = exec_as $_[0], $_[1], $outref; + is $result, $_[2], $_[1] or fail_debug; + unlike ($$outref, $_[3], (defined $_[4] ? $_[4] : "correct output of $_[1]")) or fail_debug; +} + +# Check that all PostgreSQL related directories are empty and no +# postgres processes are running. Should be called at the end +# of all tests. Does 10 tests. +sub check_clean { + is (`pg_lsclusters -h`, '', 'Cleanup: No clusters left behind'); + is ((ps 'postgres'), '', 'No postgres processes left behind'); + + my @check_dirs = ('/etc/postgresql', '/var/lib/postgresql', + '/var/run/postgresql'); + foreach (@check_dirs) { + if (-d) { + ok_dir $_, [], "No files in $_ left behind"; + } else { + pass "Directory $_ does not exist"; + } + } + # we always want /var/log/postgresql/ to exist, so that logrotate does not + # complain about missing directories + ok_dir '/var/log/postgresql', [], "No files in /var/log/postgresql left behind"; + + is_program_out 0, 'netstat -avptn 2>/dev/null | grep ":543[2-9]\\b"', 1, '', + 'PostgreSQL TCP ports are closed'; +} + +1; diff --git a/t/template b/t/template new file mode 100644 index 0000000..fa1df59 --- /dev/null +++ b/t/template @@ -0,0 +1,26 @@ +# Check XXX + +use strict; + +use lib 't'; +use TestLib; +use PgCommon; +use Test::More tests => 14; + +my @versions = ($MAJORS[-1]); + +# create clusters +foreach (@versions) { + is ((system "pg_createcluster $_ main --start >/dev/null"), 0, "pg_createcluster $_ main"); + is_program_out 'postgres', "createdb --cluster $_/main XXX", 0, ''; +} + +# XXX + +# clean up +foreach (@versions) { + is ((system "pg_dropcluster $_ main --stop"), 0, "pg_dropcluster $_ main"); +} +check_clean; + +# vim: filetype=perl |