Hi,
On 2020-07-15 15:52:03 -0400, Tom Lane wrote:
> I've been experimenting with trying to dump-and-restore the
> regression database, which is a test case that for some reason
> we don't cover in the buildfarm (pg_upgrade is not the same thing).
Yea, we really should have that. IIRC I was trying to add that, and
tests that compare dumps from primary / standby, and failed due to some
differences that were hard to fix.
A quick test with pg_dumpall shows some odd differences after:
1) create new cluster
2) installcheck-parallel
3) drop table gtest30_1, gtest1_1;
4) pg_dumpall > first.sql
5) recreate cluster
6) psql < first.sql > first.sql.log
7) pg_dumpall > second.sql
I've attached the diff between first.sql and second.sql. Here's the
highlights:
@@ -15392,9 +15392,9 @@
--
CREATE TABLE public.test_type_diff2_c1 (
+ int_two smallint,
int_four bigint,
- int_eight bigint,
- int_two smallint
+ int_eight bigint
)
INHERITS (public.test_type_diff2);
...
@@ -39194,10 +39194,10 @@
-- Data for Name: b_star; Type: TABLE DATA; Schema: public; Owner: andres
--
-COPY public.b_star (class, aa, bb, a) FROM stdin;
-b 3 mumble \N
+COPY public.b_star (class, aa, a, bb) FROM stdin;
+b 3 \N mumble
b 4 \N \N
-b \N bumble \N
+b \N \N bumble
b \N \N \N
\.
@@ -323780,7 +323780,7 @@
-- Data for Name: renamecolumnanother; Type: TABLE DATA; Schema: public;
Owner: andres
--
-COPY public.renamecolumnanother (d, a, c, w) FROM stdin;
+COPY public.renamecolumnanother (d, w, a, c) FROM stdin;
\.
The primary / standby differences are caused by sequence logging. I
wonder if there's some good way to hide those, or to force them to be
the same between primary / standby, without hiding bugs.
Greetings,
Andres Freund
--- /tmp/first.sql 2020-08-12 15:01:11.810862861 -0700
+++ /tmp/second.sql 2020-08-12 15:02:05.877709572 -0700
@@ -15392,9 +15392,9 @@
--
CREATE TABLE public.test_type_diff2_c1 (
+ int_two smallint,
int_four bigint,
- int_eight bigint,
- int_two smallint
+ int_eight bigint
)
INHERITS (public.test_type_diff2);
@@ -15406,9 +15406,9 @@
--
CREATE TABLE public.test_type_diff2_c2 (
- int_eight bigint,
int_two smallint,
- int_four bigint
+ int_four bigint,
+ int_eight bigint
)
INHERITS (public.test_type_diff2);
@@ -39194,10 +39194,10 @@
-- Data for Name: b_star; Type: TABLE DATA; Schema: public; Owner: andres
--
-COPY public.b_star (class, aa, bb, a) FROM stdin;
-b 3 mumble \N
+COPY public.b_star (class, aa, a, bb) FROM stdin;
+b 3 \N mumble
b 4 \N \N
-b \N bumble \N
+b \N \N bumble
b \N \N \N
\.
@@ -91102,10 +91102,10 @@
-- Data for Name: c_star; Type: TABLE DATA; Schema: public; Owner: andres
--
-COPY public.c_star (class, aa, cc, a) FROM stdin;
-c 5 hi mom \N
+COPY public.c_star (class, aa, a, cc) FROM stdin;
+c 5 \N hi mom
c 6 \N \N
-c \N hi paul \N
+c \N \N hi paul
c \N \N \N
\.
@@ -91381,22 +91381,22 @@
-- Data for Name: d_star; Type: TABLE DATA; Schema: public; Owner: andres
--
-COPY public.d_star (class, aa, bb, cc, dd, a) FROM stdin;
-d 7 grumble hi sunita 0 \N
-d 8 stumble hi koko \N \N
-d 9 rumble \N 1.1 \N
-d 10 \N hi kristin 10.01 \N
-d \N crumble hi boris 100.001 \N
-d 11 fumble \N \N \N
-d 12 \N hi avi \N \N
-d 13 \N \N 1000.0001 \N
-d \N tumble hi andrew \N \N
-d \N humble \N 10000.00001 \N
-d \N \N hi ginger 100000.000001 \N
+COPY public.d_star (class, aa, a, bb, cc, dd) FROM stdin;
+d 7 \N grumble hi sunita 0
+d 8 \N stumble hi koko \N
+d 9 \N rumble \N 1.1
+d 10 \N \N hi kristin 10.01
+d \N \N crumble hi boris 100.001
+d 11 \N fumble \N \N
+d 12 \N \N hi avi \N
+d 13 \N \N \N 1000.0001
+d \N \N tumble hi andrew \N
+d \N \N humble \N 10000.00001
+d \N \N \N hi ginger 100000.000001
d 14 \N \N \N \N
-d \N jumble \N \N \N
-d \N \N hi jolly \N \N
-d \N \N \N 1000000.0000001 \N
+d \N \N jumble \N \N
+d \N \N \N hi jolly \N
+d \N \N \N \N 1000000.0000001
d \N \N \N \N \N
\.
@@ -103095,14 +103095,14 @@
-- Data for Name: e_star; Type: TABLE DATA; Schema: public; Owner: andres
--
-COPY public.e_star (class, aa, cc, ee, e, a) FROM stdin;
-e 15 hi carol -1 \N \N
-e 16 hi bob \N \N \N
-e 17 \N -2 \N \N
-e \N hi michelle -3 \N \N
+COPY public.e_star (class, aa, a, cc, ee, e) FROM stdin;
+e 15 \N hi carol -1 \N
+e 16 \N hi bob \N \N
+e 17 \N \N -2 \N
+e \N \N hi michelle -3 \N
e 18 \N \N \N \N
-e \N hi elisa \N \N \N
-e \N \N -4 \N \N
+e \N \N hi elisa \N \N
+e \N \N \N -4 \N
\.
@@ -103174,23 +103174,23 @@
-- Data for Name: f_star; Type: TABLE DATA; Schema: public; Owner: andres
--
-COPY public.f_star (class, aa, cc, ee, ff, f, e, a) FROM stdin;
-f 19 hi claire -5 ((1,3),(2,4)) 10 \N \N
-f 20 hi mike -6 \N 10 \N \N
-f 21 hi marcel \N ((11,44),(22,55),(33,66)) 10 \N \N
-f 22 \N -7 ((111,555),(222,666),(333,777),(444,888)) 10 \N \N
-f \N hi keith -8 ((1111,3333),(2222,4444)) 10 \N \N
-f 24 hi marc \N \N 10 \N \N
-f 25 \N -9 \N 10 \N \N
-f 26 \N \N ((11111,33333),(22222,44444)) 10 \N \N
-f \N hi allison -10 \N 10 \N \N
-f \N hi jeff \N ((111111,333333),(222222,444444)) 10 \N \N
-f \N \N -11 ((1111111,3333333),(2222222,4444444)) 10 \N \N
-f 27 \N \N \N 10 \N \N
-f \N hi carl \N \N 10 \N \N
-f \N \N -12 \N 10 \N \N
-f \N \N \N ((11111111,33333333),(22222222,44444444)) 10 \N \N
-f \N \N \N \N 10 \N \N
+COPY public.f_star (class, aa, a, cc, ee, e, ff, f) FROM stdin;
+f 19 \N hi claire -5 \N ((1,3),(2,4)) 10
+f 20 \N hi mike -6 \N \N 10
+f 21 \N hi marcel \N \N ((11,44),(22,55),(33,66)) 10
+f 22 \N \N -7 \N ((111,555),(222,666),(333,777),(444,888)) 10
+f \N \N hi keith -8 \N ((1111,3333),(2222,4444)) 10
+f 24 \N hi marc \N \N \N 10
+f 25 \N \N -9 \N \N 10
+f 26 \N \N \N \N ((11111,33333),(22222,44444)) 10
+f \N \N hi allison -10 \N \N 10
+f \N \N hi jeff \N \N ((111111,333333),(222222,444444)) 10
+f \N \N \N -11 \N ((1111111,3333333),(2222222,4444444)) 10
+f 27 \N \N \N \N \N 10
+f \N \N hi carl \N \N \N 10
+f \N \N \N -12 \N \N 10
+f \N \N \N \N \N ((11111111,33333333),(22222222,44444444)) 10
+f \N \N \N \N \N \N 10
\.
@@ -323780,7 +323780,7 @@
-- Data for Name: renamecolumnanother; Type: TABLE DATA; Schema: public; Owner: andres
--
-COPY public.renamecolumnanother (d, a, c, w) FROM stdin;
+COPY public.renamecolumnanother (d, w, a, c) FROM stdin;
\.
@@ -323788,7 +323788,7 @@
-- Data for Name: renamecolumnchild; Type: TABLE DATA; Schema: public; Owner: andres
--
-COPY public.renamecolumnchild (d, a, w) FROM stdin;
+COPY public.renamecolumnchild (d, w, a) FROM stdin;
\.
@@ -386337,8 +386337,8 @@
-- Data for Name: test_type_diff2_c1; Type: TABLE DATA; Schema: public; Owner: andres
--
-COPY public.test_type_diff2_c1 (int_four, int_eight, int_two) FROM stdin;
-1 2 3
+COPY public.test_type_diff2_c1 (int_two, int_four, int_eight) FROM stdin;
+3 1 2
\.
@@ -386346,8 +386346,8 @@
-- Data for Name: test_type_diff2_c2; Type: TABLE DATA; Schema: public; Owner: andres
--
-COPY public.test_type_diff2_c2 (int_eight, int_two, int_four) FROM stdin;
-4 5 6
+COPY public.test_type_diff2_c2 (int_two, int_four, int_eight) FROM stdin;
+5 6 4
\.
@@ -386364,8 +386364,8 @@
-- Data for Name: test_type_diff_c; Type: TABLE DATA; Schema: public; Owner: andres
--
-COPY public.test_type_diff_c (f1, extra, f2) FROM stdin;
-1 2 3
+COPY public.test_type_diff_c (f1, f2, extra) FROM stdin;
+1 3 2
\.
