On Wed, Sep 16, 2015 at 8:00 PM, Michael Paquier wrote:
> Hm. OK. I didn't get your message correctly, sorry for that. Would you
> be fine then to have a pg_regress command using parallel_schedule + an
> extra schedule launching tests related to the extensions in
> src/test/modules/pg_dumprestore then? The choice of parallel_schedule
> is based on what Windows does, aka this schedule is used in the
> equivalent of make check in vcregress.pl. The TAP script then simply
> initializes the cluster, runs pg_regress, and does the dump/restore
> job. There is no real need to worry about setval as dump is not taken
> from a standby..
So, here we go.
I have found something quite interesting when playing with the patch
attached: dump does not guarantee the column ordering across databases
for some inherited tables, see that example from the main regression
test suite the following diff between a dump taken from a source
database and a target database where the source dump has been restored
in first:
-INSERT INTO b_star (class, aa, bb, a) VALUES ('b', 3, 'mumble', NULL);
-INSERT INTO b_star (class, aa, bb, a) VALUES ('b', 4, NULL, NULL);
-INSERT INTO b_star (class, aa, bb, a) VALUES ('b', NULL, 'bumble', NULL);
-INSERT INTO b_star (class, aa, bb, a) VALUES ('b', NULL, NULL, NULL);
+INSERT INTO b_star (class, aa, a, bb) VALUES ('b', 3, NULL, 'mumble');
+INSERT INTO b_star (class, aa, a, bb) VALUES ('b', 4, NULL, NULL);
+INSERT INTO b_star (class, aa, a, bb) VALUES ('b', NULL, NULL, 'bumble');
+INSERT INTO b_star (class, aa, a, bb) VALUES ('b', NULL, NULL, NULL);
Problem is similar with --column-inserts, --inserts and COPY. We could
use --exclude-table like in the patch attached when taking the dump
from source database but that's grotty, or we could improve pg_dump
itself, though it may not be worth it for just this purpose.
Thoughts?
--
Michael
From 636f5dbb3738aab44558d9c65ee4bc61f66aaf07 Mon Sep 17 00:00:00 2001
From: Michael Paquier <mich...@otacoo.com>
Date: Wed, 9 Sep 2015 10:41:01 +0900
Subject: [PATCH] Add test facility to check dump/restore with extensions
The test added compares a dump taken from a source database containing a set
of extensions and a target database after dumping the contents from source and
restore them to target.
For now this test facility includes one extension to test dumpable objects
with foreign keys, but could be extended with more.
---
src/test/modules/Makefile | 1 +
src/test/modules/pg_dumprestore/.gitignore | 9 +++
src/test/modules/pg_dumprestore/Makefile | 24 ++++++++
src/test/modules/pg_dumprestore/README | 5 ++
.../modules/pg_dumprestore/expected/tables_fk.out | 14 +++++
src/test/modules/pg_dumprestore/extension_schedule | 3 +
src/test/modules/pg_dumprestore/sql/tables_fk.sql | 18 ++++++
.../pg_dumprestore/t/001_dump_restore_test.pl | 70 ++++++++++++++++++++++
src/test/modules/pg_dumprestore/tables_fk--1.0.sql | 20 +++++++
src/test/modules/pg_dumprestore/tables_fk.control | 5 ++
src/tools/msvc/Mkvcbuild.pm | 3 +-
src/tools/msvc/vcregress.pl | 3 +
12 files changed, 174 insertions(+), 1 deletion(-)
create mode 100644 src/test/modules/pg_dumprestore/.gitignore
create mode 100644 src/test/modules/pg_dumprestore/Makefile
create mode 100644 src/test/modules/pg_dumprestore/README
create mode 100644 src/test/modules/pg_dumprestore/expected/tables_fk.out
create mode 100644 src/test/modules/pg_dumprestore/extension_schedule
create mode 100644 src/test/modules/pg_dumprestore/sql/tables_fk.sql
create mode 100644 src/test/modules/pg_dumprestore/t/001_dump_restore_test.pl
create mode 100644 src/test/modules/pg_dumprestore/tables_fk--1.0.sql
create mode 100644 src/test/modules/pg_dumprestore/tables_fk.control
diff --git a/src/test/modules/Makefile b/src/test/modules/Makefile
index 9b96654..633dc6f 100644
--- a/src/test/modules/Makefile
+++ b/src/test/modules/Makefile
@@ -8,6 +8,7 @@ SUBDIRS = \
brin \
commit_ts \
dummy_seclabel \
+ pg_dumprestore \
test_ddl_deparse \
test_parser \
test_rls_hooks \
diff --git a/src/test/modules/pg_dumprestore/.gitignore b/src/test/modules/pg_dumprestore/.gitignore
new file mode 100644
index 0000000..2519efc
--- /dev/null
+++ b/src/test/modules/pg_dumprestore/.gitignore
@@ -0,0 +1,9 @@
+/tmp_check/
+/results/
+/sql/constraints.sql
+/sql/copy.sql
+/sql/create_function_1.sql
+/sql/create_function_2.sql
+/sql/largeobject.sql
+/sql/misc.sql
+/sql/tablespace.sql
diff --git a/src/test/modules/pg_dumprestore/Makefile b/src/test/modules/pg_dumprestore/Makefile
new file mode 100644
index 0000000..611f23a
--- /dev/null
+++ b/src/test/modules/pg_dumprestore/Makefile
@@ -0,0 +1,24 @@
+# src/test/modules/pg_dumprestore/Makefile
+
+EXTENSION = tables_fk
+DATA = tables_fk--1.0.sql
+PGFILEDESC = "pg_dumprestore - set of extensions dumped and restored"
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = src/test/modules/pg_dumprestore
+top_builddir = ../../../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
+
+check: all
+ $(prove_check)
+
+installcheck: install
+ $(prove_installcheck)
+
+temp-install: EXTRA_INSTALL=src/test/modules/pg_dumprestore
diff --git a/src/test/modules/pg_dumprestore/README b/src/test/modules/pg_dumprestore/README
new file mode 100644
index 0000000..81cc20c
--- /dev/null
+++ b/src/test/modules/pg_dumprestore/README
@@ -0,0 +1,5 @@
+pg_dumprestore
+==============
+
+Facility to test dump and restore of a PostgreSQL instance using a set
+of extensions and the main regression test suite.
diff --git a/src/test/modules/pg_dumprestore/expected/tables_fk.out b/src/test/modules/pg_dumprestore/expected/tables_fk.out
new file mode 100644
index 0000000..61cad6b
--- /dev/null
+++ b/src/test/modules/pg_dumprestore/expected/tables_fk.out
@@ -0,0 +1,14 @@
+--
+-- TABLES_FK
+--
+CREATE EXTENSION tables_fk;
+-- Insert some data before running the dump on tables of tables_fk, this
+-- is needed to check consistent data dump of tables with foreign key
+-- dependencies.
+INSERT INTO cc_tab_fkey VALUES (1);
+INSERT INTO bb_tab_fkey VALUES (1);
+INSERT INTO aa_tab_fkey VALUES (1);
+-- Create a table depending on a FK defined in tables_fk.
+CREATE TABLE dd_tab_fkey (id int REFERENCES bb_tab_fkey(id));
+INSERT INTO dd_tab_fkey VALUES (1);
+-- Do not drop objects at the end of test
diff --git a/src/test/modules/pg_dumprestore/extension_schedule b/src/test/modules/pg_dumprestore/extension_schedule
new file mode 100644
index 0000000..ecefc2d
--- /dev/null
+++ b/src/test/modules/pg_dumprestore/extension_schedule
@@ -0,0 +1,3 @@
+# src/test/modules/pg_dumprestore/extension_schedule
+# Schedule for extensions in this test module
+test: tables_fk
diff --git a/src/test/modules/pg_dumprestore/sql/tables_fk.sql b/src/test/modules/pg_dumprestore/sql/tables_fk.sql
new file mode 100644
index 0000000..063ad88
--- /dev/null
+++ b/src/test/modules/pg_dumprestore/sql/tables_fk.sql
@@ -0,0 +1,18 @@
+--
+-- TABLES_FK
+--
+
+CREATE EXTENSION tables_fk;
+
+-- Insert some data before running the dump on tables of tables_fk, this
+-- is needed to check consistent data dump of tables with foreign key
+-- dependencies.
+INSERT INTO cc_tab_fkey VALUES (1);
+INSERT INTO bb_tab_fkey VALUES (1);
+INSERT INTO aa_tab_fkey VALUES (1);
+
+-- Create a table depending on a FK defined in tables_fk.
+CREATE TABLE dd_tab_fkey (id int REFERENCES bb_tab_fkey(id));
+INSERT INTO dd_tab_fkey VALUES (1);
+
+-- Do not drop objects at the end of test
diff --git a/src/test/modules/pg_dumprestore/t/001_dump_restore_test.pl b/src/test/modules/pg_dumprestore/t/001_dump_restore_test.pl
new file mode 100644
index 0000000..7d41ef6
--- /dev/null
+++ b/src/test/modules/pg_dumprestore/t/001_dump_restore_test.pl
@@ -0,0 +1,70 @@
+use strict;
+use warnings;
+use Cwd;
+use File::Compare;
+use TestLib;
+use Test::More tests => 4;
+
+my $tempdir = tempdir;
+
+start_test_server $tempdir;
+
+# Source database name needs to be "regression" as some tests of the main
+# suite depend on that.
+my $source_db = 'regression';
+my $target_db = 'regression_2';
+my $source_dump = $tempdir . '/dump_' . $source_db . '.sql';
+my $target_dump = $tempdir . '/dump_' . $target_db . '.sql';
+system_or_bail 'createdb', $source_db;
+system_or_bail 'createdb', $target_db;
+
+my $startdir = getcwd();
+chdir "../../../..";
+my $topdir = getcwd();
+
+# Run main regression tests
+chdir "src/test/regress";
+system_or_bail($ENV{PG_REGRESS},
+ "--dlpath=.",
+ "--dbname=$source_db",
+ "--use-existing",
+ "--dlpath=.",
+ "--bindir=$topdir/src/bin/psql",
+ "--inputdir=.",
+ "--outputdir=.",
+ "--schedule=./parallel_schedule",
+ "--no-locale");
+
+# And finish with the extra schedule using extensions
+chdir $startdir;
+system_or_bail($ENV{PG_REGRESS},
+ "--dlpath=.",
+ "--dbname=$source_db",
+ "--use-existing",
+ "--dlpath=.",
+ "--bindir=$topdir/src/bin/psql",
+ "--schedule=./extension_schedule",
+ "--no-locale");
+
+# Take a dump from source then re-deploy it to target database.
+command_ok(['pg_dump',
+ '--exclude-table=f_star',
+ '--exclude-table=e_star',
+ '--exclude-table=d_star',
+ '--exclude-table=c_star',
+ '--exclude-table=b_star',
+ '--exclude-table=a_star',
+ '--exclude-table=renamecolumnanother',
+ '--exclude-table=renamecolumnchild',
+ '-d', $source_db, '-f', $source_dump],
+ 'dump taken from source database');
+command_ok(['psql', '--set=ON_ERROR_STOP=on', '-d', $target_db, '-f',
+ $source_dump], 'dump of source restored on target database');
+
+# Finish by taking a dump of the target database and then compare it
+# with the one from source.
+command_ok(['pg_dump', '-d', $target_db, '-f', $target_dump],
+ 'dump taken from target database');
+
+my $result = compare($source_dump, $target_dump) == 0;
+ok($result, "Diff between source and target dump");
diff --git a/src/test/modules/pg_dumprestore/tables_fk--1.0.sql b/src/test/modules/pg_dumprestore/tables_fk--1.0.sql
new file mode 100644
index 0000000..e424610
--- /dev/null
+++ b/src/test/modules/pg_dumprestore/tables_fk--1.0.sql
@@ -0,0 +1,20 @@
+/* src/test/modules/tables_fk/tables_fk--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION tables_fk" to load this file. \quit
+
+CREATE TABLE IF NOT EXISTS cc_tab_fkey (
+ id int PRIMARY KEY
+);
+
+CREATE TABLE IF NOT EXISTS bb_tab_fkey (
+ id int PRIMARY KEY REFERENCES cc_tab_fkey(id)
+);
+
+CREATE TABLE IF NOT EXISTS aa_tab_fkey (
+ id int REFERENCES bb_tab_fkey(id)
+);
+
+SELECT pg_catalog.pg_extension_config_dump('aa_tab_fkey', '');
+SELECT pg_catalog.pg_extension_config_dump('bb_tab_fkey', '');
+SELECT pg_catalog.pg_extension_config_dump('cc_tab_fkey', '');
diff --git a/src/test/modules/pg_dumprestore/tables_fk.control b/src/test/modules/pg_dumprestore/tables_fk.control
new file mode 100644
index 0000000..b9f31ee
--- /dev/null
+++ b/src/test/modules/pg_dumprestore/tables_fk.control
@@ -0,0 +1,5 @@
+# tables_fk extension
+comment = 'tables_fk - dumpable tables linked with foreign keys'
+default_version = '1.0'
+module_pathname = '$libdir/tables_fk'
+relocatable = true
diff --git a/src/tools/msvc/Mkvcbuild.pm b/src/tools/msvc/Mkvcbuild.pm
index 3abbb4c..71e0749 100644
--- a/src/tools/msvc/Mkvcbuild.pm
+++ b/src/tools/msvc/Mkvcbuild.pm
@@ -41,7 +41,8 @@ my $contrib_extrasource = {
'seg' => [ 'contrib/seg/segscan.l', 'contrib/seg/segparse.y' ], };
my @contrib_excludes = (
'commit_ts', 'hstore_plperl', 'hstore_plpython', 'intagg',
- 'ltree_plpython', 'pgcrypto', 'sepgsql', 'brin');
+ 'ltree_plpython', 'pgcrypto', 'sepgsql', 'brin',
+ 'pg_dumprestore');
# Set of variables for frontend modules
my $frontend_defines = { 'initdb' => 'FRONTEND' };
diff --git a/src/tools/msvc/vcregress.pl b/src/tools/msvc/vcregress.pl
index d3d736b..c1bd98b 100644
--- a/src/tools/msvc/vcregress.pl
+++ b/src/tools/msvc/vcregress.pl
@@ -344,6 +344,9 @@ sub modulescheck
my $mstat = 0;
foreach my $module (glob("*"))
{
+ # Already tested by tapcheck
+ next if ($module eq "pg_dumprestore");
+
subdircheck("$topdir/src/test/modules", $module);
my $status = $? >> 8;
$mstat ||= $status;
--
2.5.1
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
