cancel
Showing results for 
Search instead for 
Did you mean: 

PARTITION TABLE PG_DUMP PROBLEM

SOLVED
Adventurer

PARTITION TABLE PG_DUMP PROBLEM

CREATE TABLE test (
event_cd number NOT NULL,
dt varchar2(8)
)
PARTITION BY RANGE(dt)

(
PARTITION pt_201806 VALUES LESS THAN ('20180101'),
PARTITION pt_201812 VALUES LESS THAN ('20200101'),
PARTITION pt_max VALUES LESS THAN (MAXVALUE)
);

insert into test values(1,'20171231');
insert into test values(2,'20191231');

commit;


#################################################
pg_dump -U mig -d perf -t test (PARENTE TABLE IS SPECIFIED)

 

pg_dump of  partition table at ver EDB10, EDB11. don't working.

sorry for poor english.


#################################################
EDB 9.6 (GOOD)
#################################################
--
-- Name: test; Type: TABLE; Schema: mig; Owner: mig
--

CREATE TABLE test (
event_cd numeric NOT NULL,
dt character varying(8)
)
PARTITION BY RANGE(dt)

(
PARTITION pt_201806 VALUES LESS THAN ('20180101') OPTIONS (tablename = test_pt_201806),
PARTITION pt_201812 VALUES LESS THAN ('20200101') OPTIONS (tablename = test_pt_201812),
PARTITION pt_max VALUES LESS THAN (MAXVALUE) OPTIONS (tablename = test_pt_max)
);


ALTER TABLE test OWNER TO mig;

--
-- Data for Name: test; Type: TABLE DATA; Schema: mig; Owner: mig
--

COPY test (event_cd, dt) FROM stdin;   <================ DATA GOOD
1 20171231
2 20191231
\.


--
-- EnterpriseDB database dump complete
--
#################################################
# EDB 10 (BAD) -- data export not working
#################################################


-- EnterpriseDB database dump
--

-- Dumped from database version 10.6.13
-- Dumped by pg_dump version 10.6.13

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET edb_redwood_date = off;
SET default_with_rowids = off;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: test; Type: TABLE; Schema: mig; Owner: mig
--

CREATE TABLE mig.test (
event_cd numeric NOT NULL,
dt character varying(8)
)
PARTITION BY RANGE (dt) NULLS LAST;


ALTER TABLE mig.test OWNER TO mig;

--
-- EnterpriseDB database dump complete
--
#################################################
# EDB 11 (BAD) -- data export not working
#################################################


-- Dumped from database version 11.1.7
-- Dumped by pg_dump version 11.1.7

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET edb_redwood_date = off;
SET default_with_rowids = off;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;
SET edb_data_redaction = off;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: test; Type: TABLE; Schema: mig; Owner: mig
--

CREATE TABLE mig.test (
event_cd numeric NOT NULL,
dt character varying(8)
)
PARTITION BY RANGE (dt) NULLS LAST;


ALTER TABLE mig.test OWNER TO mig;

--
-- EnterpriseDB database dump complete
--

1 ACCEPTED SOLUTION

Accepted Solutions
EDB Team Member

Re: PARTITION TABLE PG_DUMP PROBLEM

Hi @hellower,

 

From EPAS 10 onward we have moved toward community partitioning.  This is expected, same is reproducible on community PostgreSQL as well. 

 

Note for this kind behaviour is also exist in the community documented[1]

 

 

                                              Note

When -t is specified, pg_dump makes no attempt to dump any other database objects that the selected table(s) might depend upon.Therefore, there is no guarantee that the results of a specific-table dump can be successfully restored by themselves into a clean database.

 

Reference :

[1] https://www.postgresql.org/docs/10/app-pgdump.html

 

Please let us know in case of any issues/queries.

 

Regards,

Sudhir

5 REPLIES
EDB Team Member

Re: PARTITION TABLE PG_DUMP PROBLEM

Hi hellower ,

 

Currently, we are looking into it.

 

We will update soon on this.

EDB Team Member

Re: PARTITION TABLE PG_DUMP PROBLEM

Hi @hellower

 

Thank you for drafting your concern with us. Prior to community Postgres v10, There was no declarative(using PARTITION BY clause) method implemented in community Postgres. Hence, Oracle compatibility code in EPAS/EDB(till 9.6) didn't conflict with community supported partitioning method.

Here, declarative partitioning code was incorporated in Community postgres v10. Now,This formed some code level conflict with for existing Oracle compatibility code of EDB Postgres.

This issue seems to be part of similar conflict here. We need to escalate this issue to our engineering team.

 

Although sufficient detail furnished here. However, a little more detail would be appreciated here for escalation.

 

- Result of pg_dump command for conflicting and benign version.

- Log file for all pg_dump commnads.

 

Thanks,

Ninad

Highlighted
Adventurer

Re: PARTITION TABLE PG_DUMP PROBLEM

###################################################################################
EDBAS 10
###################################################################################
$ psql -U mig -d perf

psql.bin (10.6.13)
Type "help" for help.

mig@[local]:5444Smiley Tongueerf(13:39:39)
SQL> select version();
+--------------------------------------------------------------------------------------------------------------+
| version |
+--------------------------------------------------------------------------------------------------------------+
| EnterpriseDB 10.6.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit |
+--------------------------------------------------------------------------------------------------------------+
(1 row)

Time: 1.578 ms
mig@[local]:5444Smiley Tongueerf(13:39:42)
SQL> \q
[enterprisedb@test01:/tmp]
$ pg_dump -U mig -d perf -t test
--
-- EnterpriseDB database dump
--

-- Dumped from database version 10.6.13
-- Dumped by pg_dump version 10.6.13

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET edb_redwood_date = off;
SET default_with_rowids = off;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: test; Type: TABLE; Schema: mig; Owner: mig
--

CREATE TABLE mig.test (
event_cd numeric NOT NULL,
dt character varying(8)
)
PARTITION BY RANGE (dt) NULLS LAST;


ALTER TABLE mig.test OWNER TO mig;

--
-- EnterpriseDB database dump complete
--

[enterprisedb@test01:/tmp]

###############################################################################
# EDBAS 11
###############################################################################
[enterprisedb@test02:/usr/edb/as11]
$ psql -U mig -d perf
psql.bin (11.1.7)
Type "help" for help.

perf=# select version();
version
-----------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 11.1 (EnterpriseDB Advanced Server 11.1.7) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

perf=# \q
[enterprisedb@test02:/usr/edb/as11]
$ pg_dump -U mig -d perf -t test
--
-- EnterpriseDB database dump
--

-- Dumped from database version 11.1.7
-- Dumped by pg_dump version 11.1.7

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET edb_redwood_date = off;
SET default_with_rowids = off;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;
SET edb_data_redaction = off;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: test; Type: TABLE; Schema: mig; Owner: mig
--

CREATE TABLE mig.test (
event_cd numeric NOT NULL,
dt character varying(8)
)
PARTITION BY RANGE (dt) NULLS LAST;


ALTER TABLE mig.test OWNER TO mig;

--
-- EnterpriseDB database dump complete
--

[enterprisedb@test02:/usr/edb/as11]

EDB Team Member

Re: PARTITION TABLE PG_DUMP PROBLEM

Hi @hellower,

 

Thanks for sharing the details, we have verified that, we are also getting the same result.

 

We will discuss this issue with our development team and will get back to you as soon as possible.

 

Please let us know in case of any issues/queries.

 

Regards,

Sudhir

EDB Team Member

Re: PARTITION TABLE PG_DUMP PROBLEM

Hi @hellower,

 

From EPAS 10 onward we have moved toward community partitioning.  This is expected, same is reproducible on community PostgreSQL as well. 

 

Note for this kind behaviour is also exist in the community documented[1]

 

 

                                              Note

When -t is specified, pg_dump makes no attempt to dump any other database objects that the selected table(s) might depend upon.Therefore, there is no guarantee that the results of a specific-table dump can be successfully restored by themselves into a clean database.

 

Reference :

[1] https://www.postgresql.org/docs/10/app-pgdump.html

 

Please let us know in case of any issues/queries.

 

Regards,

Sudhir